R lang table rows to columns spread

Question

I am looking for a way to spread N rows into columns with R. Example input -> output

given a table, I want to spread that years rows into columns

id | year | val_1 | val_2 | val_3
---|------|-------|-------|------
1  | 2001 | 10    | 11    | 12
2  | 2001 | 21    | 22    | 22
3  | 2002 | 31    | 32    | 32
3  | 2002 | 41    | 42    | 42

to look like the following:

id | year | val_1_1 | val_2_1 | val_3_1 | val_1_2 | val_2_2 | val_3_2
---|------|---------|---------|---------|---------|---------|--------
1  | 2001 | 10      | 11      | 12      | 20      | 21      | 22
2  | 2002 | 30      | 31      | 32      | 40      | 41      | 42

score 1 · Answer 1 · answered Mar 05 '18 at 22:33

If using the tidyverse, you'll need to reshape to long form first so you can make the new column names first, then reshape to the wider form:

library(tidyverse)

df <- data_frame(id = c(1L, 2L, 3L, 3L), 
                 year = c(2001L, 2001L, 2002L, 2002L), 
                 val_1 = c(10L, 21L, 31L, 41L), 
                 val_2 = c(11L, 22L, 32L, 42L), 
                 val_3 = c(12L, 22L, 32L, 42L))

df %>% 
    gather(val_n, val, contains('val')) %>%    # gather to long form
    group_by(year, val_n) %>% 
    mutate(val_n_n = paste(val_n, row_number(), sep = '_')) %>%    # make new column names
    ungroup() %>% select(-id, -val_n) %>%    # clean up
    spread(val_n_n, val)    # spread back to wide form
#> # A tibble: 2 x 7
#>    year val_1_1 val_1_2 val_2_1 val_2_2 val_3_1 val_3_2
#>   <int>   <int>   <int>   <int>   <int>   <int>   <int>
#> 1  2001      10      21      11      22      12      22
#> 2  2002      31      41      32      42      32      42

R lang table rows to columns spread

1 Answers1