Is there a better way to spread a 'long' table with mutlple columns into a 'wide' one?

Question

I want to reshape a long dataframe to a wide one. That is, I want to go from this:

   file label val1 val2
1   red     A   12    3
2   red     B    4    2
3   red     C    5    8
4 green     A    3    3
5 green     B    6    5
6 green     C    9    6
7  blue     A    3    3
8  blue     B    1    2
9  blue     C    4    6

to this:

   file value1_A value1_B value1_C value2_A value2_B value2_C
1   red       12        4        5        3        2        8
2 green        3        6        9        3        5        6
3  blue        3        1        4        3        2        6

My best attempt thus far is as follows:

library(tidyverse)

dat <- 
  structure(list(file = structure(c(3L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L),
                                .Label = c("blue", "green", "red"),
                                class = "factor"), 
               label = structure(c(1L, 2L, 3L, 1L, 2L, 3L, 1L, 2L, 3L),
                                 .Label = c("A", "B", "C"),
                                 class = "factor"),
               val1 = c(12L, 4L, 5L, 3L, 6L, 9L, 3L, 1L, 4L),
               val2 = c(3L, 2L, 8L, 3L, 5L, 6L, 3L, 2L, 6L)),
          class = "data.frame", row.names = c(NA, -9L))

dat %>%
  group_by(file) %>%
  mutate(values1 = paste('value1', label, sep='_'),
         values2 = paste('value2', label, sep='_')) %>%
  spread(values1, val1) %>%
  spread(values2, val2) %>%
  select(-label)
# # A tibble: 9 x 7
# # Groups:   file [3]
#   file  value1_A value1_B value1_C value2_A value2_B value2_C
#   <fct>    <int>    <int>    <int>    <int>    <int>    <int>
# 1 blue         3       NA       NA        3       NA       NA
# 2 blue        NA        1       NA       NA        2       NA
# 3 blue        NA       NA        4       NA       NA        6
# 4 green        3       NA       NA        3       NA       NA
# 5 green       NA        6       NA       NA        5       NA
# 6 green       NA       NA        9       NA       NA        6
# 7 red         12       NA       NA        3       NA       NA
# 8 red         NA        4       NA       NA        2       NA
# 9 red         NA       NA        5       NA       NA        8

The output is unsatisfactory since what should be on one row occupies three, with multiple 'NA'. This seems to be due to using spread twice, but I don't know how else to achieve the result I desire. I'd very much appreciate any advice on how to do this.

Many thanks in advance, -R

score 3 · Accepted Answer · answered Dec 07 '18 at 06:30

Here's a way

library(tidyverse)
dat %>%
  # first move to long form so we can
  # see the original column names as strings
  gather("variable_name", "value", contains("val")) %>%
  # create the new column names from the variable name and the label
  mutate(new_column_name = paste(variable_name, label, sep="_")) %>%
  # get rid of the pieces we used to make the column names
  select(-label, -variable_name) %>%
  # now spread
  spread(new_column_name, value)

score 3 · Answer 2 · answered Dec 07 '18 at 06:38

here's the data.table way. all in one line of code...

library( data.table )
dcast( setDT( dat ), file ~ label, value.var = c("val1", "val2"))

#     file val1_A val1_B val1_C val2_A val2_B val2_C
# 1:  blue      3      1      4      3      2      6
# 2: green      3      6      9      3      5      6
# 3:   red     12      4      5      3      2      8

Is there a better way to spread a 'long' table with mutlple columns into a 'wide' one?

2 Answers2