How to consolidate multiple columns based on other columns?-CodePudding

I have a dataset that is organized like this:

What I have

data <- tribble(~team_one, ~team_two, ~score_one, ~score_two,
                "Australia", "New Zealand", 214, 170,
                "Australia", "New Zealand", 214, 170,
                "New Zealand", "Australia", 214, 170,
                "New Zealand", "Australia", 214, 170)

team_one      team_two      score_one  score_two
Australia     New Zealand   214        170  
Australia     New Zealand   214        170  
New Zealand   Australia     214        170  
New Zealand   Australia     214        170

I'd like to convert that to this:

Desired Output

team          score
Australia     214           
Australia     214           
New Zealand   170           
New Zealand   170

I'm quite stuck on figuring out how to use what I have to get the desired output. Should I use pivot_longer? When I tried it, I get this, which isn't right:

What I tried

data |> 
  pivot_longer(cols = c("score_one", "score_two"), names_to = "name", values_to = "value")

team_one      team_two       name        value 
Australia     New Zealand   score_one   214 
Australia     New Zealand   score_two   170 
Australia     New Zealand   score_one   214 
Australia     New Zealand   score_two   170 
New Zealand   Australia     score_one   214 
New Zealand   Australia     score_two   170 
New Zealand   Australia     score_one   214 
New Zealand   Australia     score_two   170

CodePudding user response：

Something like this maybe?

data |> 
    pivot_longer(cols = c("score_one", "score_two"), names_to = "name", values_to = "value") |> 
    pivot_longer(cols = c("team_one", "team_two"), names_to = "t", values_to = "team") |> 
    select(team, score = value)

   team        score
   <chr>       <dbl>
 1 Australia     214
 2 New Zealand   214
 3 Australia     170
 4 New Zealand   170
 5 Australia     214
 6 New Zealand   214
 7 Australia     170
 8 New Zealand   170
 9 New Zealand   214
10 Australia     214
11 New Zealand   170
12 Australia     170
13 New Zealand   214
14 Australia     214
15 New Zealand   170
16 Australia     170

Alternatively one could also divide, rename and then rejoin.

one = data |> 
    select(ends_with("one")) |> 
    rename_with(~ gsub("_one", "", .x))

two = data |>
    select(ends_with("two")) |> 
    rename_with(~ gsub("_two", "", .x))

bind_rows(one, two)

CodePudding user response：

# I think that the values in your data have same meaning (team A:team B = team B:team A)
# So I extracted the first two rows
> data_c<-data[1:2,]

#Change the data format
> data_p<-pivot_longer(data_c, cols=everything(),
             names_to='.value',
             names_pattern='([A-Za-z] )\\d?')

#Sort the team column in ascending order as you required
> arrange(data_p, team)

# A tibble: 4 x 2
#  team        score
#  <chr>       <dbl>
#1 Australia     214
#2 Australia     214
#3 New Zealand   170
#4 New Zealand   170

CodePudding user response：

I assume the following:

4 games
should lead to a desired table of 8 rows

I have introduced an ID for each game.

library(tidyverse)

data %>% 
  # one id per game
  rowid_to_column('id') %>%
  group_by(id) %>% 
  pivot_longer(-c(id, score_one, score_two), names_to='Team', values_to='team') %>% 
  # get the score from either the first or second team
  mutate(score = ifelse(str_detect(Team,'_one'), score_one, score_two)) %>% 
  select(team, score)
#> Adding missing grouping variables: `id`
#> # A tibble: 8 × 3
#> # Groups:   id [4]
#>      id team        score
#>   <int> <chr>       <dbl>
#> 1     1 Australia     214
#> 2     1 New Zealand   170
#> 3     2 Australia     214
#> 4     2 New Zealand   170
#> 5     3 New Zealand   214
#> 6     3 Australia     170
#> 7     4 New Zealand   214
#> 8     4 Australia     170