Transpose specified columns columns to rows with grouped data

May 16, 2022

I have a dataframe like this:

  household person R01 R02 R03 R04 R05
1         1      1  NA   1   7   7  NA
2         1      2   1  NA   7   7  NA
3         1      3   3   3  NA  11  NA
4         1      4   3   3  11  NA  NA
5         2      1  NA   7  16  NA  NA
6         2      2   3  NA   7  NA  NA
7         2      3  15   3  NA  NA  NA

and I’m trying add new columns which are the grouped transposed versions of columns R01 to R05, like this:

  household person R01 R02 R03 R04 R05 R01x R02x R03x R04x R05x
1         1      1  NA   1   7   7  NA   NA    1    3    3   NA
2         1      2   1  NA   7   7  NA    1   NA    3    3   NA
3         1      3   3   3  NA  11  NA    7    7   NA   11   NA
4         1      4   3   3  11  NA  NA    7    7   11   NA   NA
5         2      1  NA   7  16  NA  NA   NA    3   15   NA   NA
6         2      2   3  NA   7  NA  NA    7   NA    3   NA   NA
7         2      3  15   3  NA  NA  NA   16    7   NA   NA   NA

I have tried various attempts using t() and reshaping using gather() and spread() but I don’t think they are designed to do this as I’m moving the data around rather than just reshaping it.

Example Code

df <- data.frame(household = c(rep(1,4),rep(2,3)),
                 person = c(1:4,1:3),
                 R01 = c(NA,1,3,3,NA,3,15),
                 R02 = c(1,NA,3,3,7,NA,3),
                 R03 = c(7,7,NA,11,16,7,NA),
                 R04 = c(7,7,11,rep(NA,4)),
                 R05 = rep(NA,7))

>Solution :

Referring to my previous answer, you can transpose the matrx within group_modify():

library(dplyr)

df %>%
  group_by(household) %>%
  group_modify(~ {
    mat <- t(.x[-1][1:nrow(.x)])
    colnames(mat) <- paste0(rownames(mat), "x")
    cbind(.x, mat)
  }) %>%
  ungroup()

# # A tibble: 7 × 11
#   household person   R01   R02   R03   R04 R05    R01x  R02x  R03x  R04x
#       <dbl>  <int> <dbl> <dbl> <dbl> <dbl> <lgl> <dbl> <dbl> <dbl> <dbl>
# 1         1      1    NA     1     7     7 NA       NA     1     3     3
# 2         1      2     1    NA     7     7 NA        1    NA     3     3
# 3         1      3     3     3    NA    11 NA        7     7    NA    11
# 4         1      4     3     3    11    NA NA        7     7    11    NA
# 5         2      1    NA     7    16    NA NA       NA     3    15    NA
# 6         2      2     3    NA     7    NA NA        7    NA     3    NA
# 7         2      3    15     3    NA    NA NA       16     7    NA    NA