r语言 - 如何在其他列的基础上合并多个列?

Question

我有一个这样组织的数据集:

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  

我想把它转换成这样:

期望输出值

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

我很纠结于如何使用我所拥有的来获得想要的输出。我应该用pivot_longer吗?当我尝试它时，我得到这个，这是不对的:

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 

Answer 1

# 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

Answer 2

可能是这样的?

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

也可以拆分，重命名，然后重新加入。

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)

Answer 3

我假设如下:

• 4游戏
• 应该导致一个理想的8行表

我已经为每个游戏引入了一个ID。

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