Transpose specified columns columns to rows with grouped data

Question

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))

Answer 1

df %>%
  left_join(pivot_longer(.,starts_with('R'), names_to = 'name', 
                         names_pattern = "(\\d+)", values_drop_na = TRUE, 
                         names_transform = list(name = as.integer)) %>%
              pivot_wider(c(household,name), names_from = person,
                          names_glue = "R0{person}x"),
            by = c('household', person = 'name'))

 household person R01 R02 R03 R04 R05 R01x R02x R03x R04x
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

---

Another solution:

df %>%
  left_join(
    reshape2::recast(.,household+variable~person,id.var = c('household', 'person'))%>%
    group_by(household) %>%
    mutate(person = seq_along(variable), variable = NULL))

  household person R01 R02 R03 R04 R05  1  2  3  4
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

Answer 2

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

Answer 3

Here's a way to do it.

library(dplyr)

transposed_df <- df %>%
  group_split(household) %>%
  lapply(\(x){
    select(x, -1:-2) %>%
      t() %>%
      head(nrow(x)) %>%
      as_tibble() %>%
      setNames(paste0(names(x)[-1:-2], 'x'))
  }) %>%
  bind_rows()

df %>%
  bind_cols(transposed_df)
#>   household person R01 R02 R03 R04 R05 R01x R02x R03x R04x
#> 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

Answer 4

Partly using a previous answer, here's a way to do it.

1. Split the dataframe according to their group
2. Get their number of columns with at least one non-NA (important to do the transposition)
3. Reduce their size using the length size created in step 2, and do the transposition.
4. Swap (again) the colnames and rownames which were swapped (first) in the transposition.
5. Bind the columns with the original dataframe.

l <- split(df[startsWith(colnames(df), "R")], df$household)
len <- lapply(l, \(l) ncol(l) - (sum(sapply(l, \(x) any(!is.na(x))))))
l <- mapply(\(x, y) t(x[1:(length(x) - y)]), l, len, SIMPLIFY = F)

l <- lapply(l, function(x){
  r <- paste0(rownames(x), "x")
  c <- colnames(x)
  rownames(x) <- c
  colnames(x) <- r
  data.frame(x)
})

cbind(df, bind_rows(l))

output

  household person R01 R02 R03 R04 R05 R01x R02x R03x R04x
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