Transpose elements in a column into multiple columns by group in R

user_10447756 0
Viewed 53

Given the data

test_id <- c(1, 1, 1, 2, 2, 2)
test_values <- c(1, 2, 3, 4, 5, 6)
test_df <- data.frame(test_id, test_values)
test_df

  test_id       test_values
        1           1
        1           2
        1           3
        2           4
        2           5
        2           6

I would like to transpose the value column into a row

test_df$x1 <- c(1, 1, 1, 4, 4, 4)
test_df$x2 <- c(2, 2, 2, 5, 5, 5)
test_df$x3 <- c(3, 3, 3, 6, 6, 6)
test_df

 test_id        test_values   x1           x2           x3
        1           1          1            2            3
        1           2          1            2            3
        1           3          1            2            3
        2           4          4            5            6
        2           5          4            5            6
        2           6          4            5            6

I originally had in mind something like

test_df <- test_df %>%
  group_by(test_id) %>%
  mutate(X = t(test_values))%>%
  ungroup()

but this does not allow splitting the column into multiple entries - x1,x2,x3. Any suggestion about how to tackle the issue would be much appreciated!

transpose vector r
3 Answers

You could do it with pivots and joins:

test_id <- c(1, 1, 1, 2, 2, 2)
test_values <- c(1, 2, 3, 4, 5, 6)
test_df <- data.frame(test_id, test_values)

test_df %>% 
  group_by(test_id) %>% 
  mutate(var = seq_along(test_values)) %>% 
  pivot_wider(values_from="test_values", names_from="var", names_prefix="x") %>% 
  left_join(test_df %>% dplyr::select(test_id, test_values), .)
# Joining, by = "test_id"
#   test_id test_values x1 x2 x3
# 1       1           1  1  2  3
# 2       1           2  1  2  3
# 3       1           3  1  2  3
# 4       2           4  4  5  6
# 5       2           5  4  5  6
# 6       2           6  4  5  6

Or, if you only wanted one row per test_id you could just remove the last line:

test_df %>% 
  group_by(test_id) %>% 
  mutate(var = seq_along(test_values)) %>% 
  pivot_wider(values_from="test_values", names_from="var", names_prefix="x") 
# # A tibble: 2 x 4
# # Groups:   test_id [2]
#   test_id    x1    x2    x3
#      <dbl> <dbl> <dbl> <dbl>
# 1       1     1     2     3
# 2       2     4     5     6
user_82064340

Here is a data.table option

setDT(test_df)[,c(.(test_values = test_values),data.frame(outer(rep(1,.N),test_values))),test_id]

where outer is applied to make the transpose, such that

   test_id test_values X1 X2 X3
1:       1           1  1  2  3
2:       1           2  1  2  3
3:       1           3  1  2  3
4:       2           4  4  5  6
5:       2           5  4  5  6
6:       2           6  4  5  6
user_121587570

Using unnest_wider from tidyr

library(dplyr)
library(tidyr)
test_df %>%
    group_by(test_id) %>%  
    transmute(col1 = list(test_values)) %>%
    unnest_wider(c(col1)) %>% 
    rename_at(vars(starts_with("...")),
       ~ str_replace(., fixed("..."), "x")) %>% 
    ungroup

-output

# A tibble: 6 x 4
#  test_id    x1    x2    x3
#    <dbl> <dbl> <dbl> <dbl>
#1       1     1     2     3
#2       1     1     2     3
#3       1     1     2     3
#4       2     4     5     6
#5       2     4     5     6
#6       2     4     5     6

Or using base R

test_df[paste0('x', 1:3)] <-  do.call(rbind, with(test_df, 
          split(test_values, test_id)))[test_df$test_id,]
user_37322710
Related