Drey Drey - 1 month ago 18
R Question

Spreading key value pairs into columns

I'm stuck with the following data wrangling problem. Each dataset has multiple values of

aValue
per one value of
aName
. This can be easily represented in a tidy data frame.

someDatasets <- list(dataset1 = data.frame(aName = c("a", "a", "a", "b", "b"), aValue = 1:5, dataset = "ds1"),
dataset2 = data.frame(aName = c("a", "a", "a", "b", "c", "c"), aValue = (1:6)*10 , dataset = "ds2"),
dataset3 = data.frame(aName = c("a", "c", "c", "c"), aValue = (1:4)*100, dataset = "ds3"))

tidyData <- Reduce(dplyr::bind_rows, someDatasets)


I would like to "spread" the dataset variable into individual columns. (I was not able to use
tidyr::spread
to create desired output because of duplicate keys.)

###
# Desired output
###
# aName ds1 ds2 ds3
# a 1 10 100
# a 2 20 NA
# a 3 30 NA
# b 4 40 NA
# b 5 NA NA
# c NA 50 200
# c NA 60 300
# c NA NA 400


Is there a tidy way to generate desired output ?

ps: I'm aware of spread-key-value-pairs-when-keys-are-in-different-columns question but the solution

dcast(melt(someDatasets, id = "aName", na.rm = TRUE), aName~value)


does not produce the desired output because an aggregate function
length
is used.

Answer

As stated in comments by @lukeA and @A Handcart and Mohair, you can add an additional ID to your data to avoid the duplicate keys problem.

library(dplyr)
library(tidyr)

tidyData = bind_rows(someDatasets) %>% 
   group_by(dataset, aName) %>% 
   mutate(id = paste0(aName, 1:n())) %>% 
   ungroup() %>% 
   select(-aName)

# head(tidyData)
# Source: local data frame [6 x 3]
# 
#   aValue dataset    id
#    <dbl>   <chr> <chr>
# 1      1     ds1    a1
# 2      2     ds1    a2
# 3      3     ds1    a3
# 4      4     ds1    b1
# 5      5     ds1    b2
# 6     10     ds2    a1

id is now unique within each group (dataset) so we can proceed with spreading:

tidyData %>% 
   spread(dataset, aValue) %>% 
   mutate(id = substr(id, 1, 1))

# Source: local data frame [10 x 4]
# 
#      id   ds1   ds2   ds3
#   <chr> <dbl> <dbl> <dbl>
# 1     a     1    10   100
# 2     a     2    20    NA
# 3     a     3    30    NA
# 4     b     4    40    NA
# 5     b     5    NA    NA
# 6     c    NA    50   200
# 7     c    NA    60   300
# 8     c    NA    NA   400
Comments