How do I reshape wide nested data to long format in R?

Question

I have a dataset that looks something like this, where I have a different teams (A, B) for each classroom (ID), 3 scores for each team (e.g., team1_1 team1_2 team1_3) and a mean score for each team. In other words, Classroom (ID) 1 has two teams (A, B), and Classroom 2 has two teams (A, B), and so on.

df <- data.frame(ID=1:5,
                 Team1=c("A","A","A","A","A"),
                 Team1_1=c(2,3,1,3,4),
                 Team1_2=c(4,2,2,3,2),
                 Team1_3=c(5,3,4,5,4),
                 Team1_Mean=c(3.67,2.67,2.33,3.67,3.33),
                 Team2=c("B","B","B","B","B"),
                 Team2_1=c(4,3,3,5,4),
                 Team2_2=c(4,2,2,5,2),
                 Team2_3=c(4,4,4,5,3),
                 Team2_Mean=c(4,3,3,5,3))


ID Team1 Team1_1 Team1_2 Team1_3 Team1_Mean Team2 Team2_1 Team2_2 Team2_3 Team2_Mean
1    A      2       4       5      3.67       B      4       4       4         4
2    A      3       2       3      2.67       B      3       2       4         3
3    A      1       2       4      2.33       B      3       2       4         3
4    A      3       3       5      3.67       B      5       5       5         5
5    A      4       2       4      3.33       B      4       2       3         3

I would like to reshape the data so that each Team is listed in long format, which would look like this:

df2 <- data.frame(ID=c(1,1,2,2,3,3,4,4,5,5),
                  Team=c("A", "B", "A", "B", "A", "B", "A", "B", "A", "B"),
                  Score1=c(2,4,3,3,1,3,3,5,4,5),
                  Score2=c(4,4,2,2,2,2,3,5,2,2),
                  Score3=c(5,4,3,4,4,4,5,5,4,3),
                  Mean=c(3.67,4,2.67,3,2.33,3,3.67,5,3.33,3))


ID Team Score1 Score2 Score3 Mean
1   A     2      4      5    3.67
1   B     4      4      4    4.00
2   A     3      2      3    2.67
2   B     3      2      4    3.00
3   A     1      2      4    2.33
3   B     3      2      4    3.00
4   A     3      3      5    3.67
4   B     5      5      5    5.00
5   A     4      2      4    3.33
5   B     5      2      3    3.00

In my mind, I thought putting the data for each team in a list, reshaping into long format, and then unnesting the data would do the trick, but it wasn't that easy. My first step was as such:

df <- df %>% nest(items = c("Team1", "Team1_1", "Team1_2", "Team1_3", "Team1_Mean"))
names(df)[names(df) == "items"] <- "Team1"
df <- df %>% nest(items = c("Team2", "Team2_1", "Team2_2", "Team2_3", "Team2_Mean"))
names(df)[names(df) == "items"] <- "Team2"


# A tibble: 5 x 3
     ID Team1            Team2           
  <int> <list>           <list>          
1     1 <tibble [1 × 5]> <tibble [1 × 5]>
2     2 <tibble [1 × 5]> <tibble [1 × 5]>
3     3 <tibble [1 × 5]> <tibble [1 × 5]>
4     4 <tibble [1 × 5]> <tibble [1 × 5]>
5     5 <tibble [1 × 5]> <tibble [1 × 5]>

From there I tried my usual reshape2 (melt) function but R says that's deprecated, so I tried tidyr's "gather" method, but it seems these efforts do not work on lists.

My questions are:

1. How do I achieve my desired outcome?
2. My dataset contains hundreds of IDs and teams, so I do not want to have to type out each variable as I did above; how can I streamline this to reduce the use of hard coding?

Much thanks in advance!

Answer 1

An option is pivot_longer. We can rename the columns 'Team1', 'Team2' to add a delimiter (_) along with the suffix 'Team', then reshape to 'long' format with pivot_longer specifying the cols as all except the 'ID' (-ID), specify the names_sep as _ and then rename the column names that starts with digits with appending 'Score' as prefix

library(dplyr)
library(tidyr)
library(stringr)
df %>%
   rename_at(vars(matches("^Team\\d+$")), ~ str_c(., "_Team")) %>%  
   pivot_longer(cols = -ID, names_to = c("grp", ".value"), 
            names_sep="_")%>% 
   rename_at(vars(matches('^\\d+')), ~ str_c("Score", .)) %>%
   select(-grp)

-output

# A tibble: 10 x 6
#      ID Team  Score1 Score2 Score3  Mean
#   <int> <chr>  <dbl>  <dbl>  <dbl> <dbl>
# 1     1 A          2      4      5  3.67
# 2     1 B          4      4      4  4   
# 3     2 A          3      2      3  2.67
# 4     2 B          3      2      4  3   
# 5     3 A          1      2      4  2.33
# 6     3 B          3      2      4  3   
# 7     4 A          3      3      5  3.67
# 8     4 B          5      5      5  5   
# 9     5 A          4      2      4  3.33
#10     5 B          4      2      3  3   

Answer 2

Here is a base R option using stack + gsub + unstack

q <- unstack(
  transform(
    stack(df),
    ind = gsub("(?<=Team)\\d+", "", ind, perl = TRUE)
  )
)
q$ID <- rep(q$ID, max(lengths(q)) / length(q$ID))
q <- type.convert(q, as.is = TRUE)
dfout <- data.frame(q)[order(q$ID), ]

which gives

> dfout
   ID Team Team_1 Team_2 Team_3 Team_Mean
1   1    A      2      4      5      3.67
6   1    B      4      4      4      4.00
2   2    A      3      2      3      2.67
7   2    B      3      2      4      3.00
3   3    A      1      2      4      2.33
8   3    B      3      2      4      3.00
4   4    A      3      3      5      3.67
9   4    B      5      5      5      5.00
5   5    A      4      2      4      3.33
10  5    B      4      2      3      3.00

Answer 3

You can use melt from data.table and pass a list of patterns, along with the new column names:

result <- melt(setDT(df), id=c("ID"), measure = patterns(Team = "Team\\d$", 
                                                Score1 = "Team\\d_1$", 
                                                Score2 = "Team\\d_2$", 
                                                Score3 = "Team\\d_3$", 
                                                Mean = ".+Mean$"))

result[order(ID), !c("variable")] 

    ID Team Score1 Score2 Score3 Mean
 1:  1    A      2      4      5 3.67
 2:  1    B      4      4      4 4.00
 3:  2    A      3      2      3 2.67
 4:  2    B      3      2      4 3.00
 5:  3    A      1      2      4 2.33
 6:  3    B      3      2      4 3.00
 7:  4    A      3      3      5 3.67
 8:  4    B      5      5      5 5.00
 9:  5    A      4      2      4 3.33
10:  5    B      4      2      3 3.00