0

I have a data set with counts of observations per organization, and I need to convert it to individual rows for each observation. I got close with melt(), but still can't figure out how to get individual rows for each observation. My data is like this:

df <- data.frame(org = c("A", "B", "C"),
                 var1 = c(2, 3, 1),
                 var2 = c(4, 6, 1),
                 var3 = c(0, 4, 2))

#   org var1 var2 var3
# 1   A    2    4    0
# 2   B    3    6    4
# 3   C    1    1    2

Using melt() get's me from wide to tall data:

melt(df, id = "org")

#   org variable value
# 1   A     var1     2
# 2   B     var1     3
# 3   C     var1     1
# 4   A     var2     4
# ...

But my desired output is:

#    org variable
# 1    A     var1
# 2    A     var1
# 3    B     var1
# 4    B     var1
# 5    B     var1
# 6    C     var1
# 7    A     var2
# 8    A     var2
# 9    A     var2
# 10   A     var2
# ...

I've hit a wall on this one, so appreciate any help/pointers in the right direction.

M--
  • 25,431
  • 8
  • 61
  • 93
tim
  • 879
  • 1
  • 8
  • 26
  • Also possible duplicate of this question: https://stackoverflow.com/q/53105730/ – Dunois Jul 08 '19 at 19:02
  • Apologies for the duplicate. I may have not been as clear as I could have been in the question, and after going through all the options, it seems using `melt()` like I did initially and then using `uncount()` was my needed solution. Thanks! – tim Jul 08 '19 at 21:19

1 Answers1

1
library(tidyr)
library(dplyr)

df %>%
  gather(var, value, -org) %>% 
  group_by_all %>% 
  expand(value = 1:value) %>% 
  filter(value != 0) %>% 
  ungroup %>% 
  select(-value)

#> # A tibble: 24 x 2
#>    org   var  
#>    <fct> <chr>
#>  1 A     var1 
#>  2 A     var1 
#>  3 A     var2 
#>  4 A     var2 
#>  5 A     var2 
#>  6 A     var2 
#>  7 A     var3 
#>  8 B     var1 
#>  9 B     var1 
#> 10 B     var1 
#> # ... with 14 more rows
M--
  • 25,431
  • 8
  • 61
  • 93
  • Is piping really necessary for something like this? Wouldn't `df <- gather(df, key = var, value = val, var1:var3)` suffice? – Dunois Jul 08 '19 at 19:03
  • @Dunois well for `gather` yes, but what about `expand`, `filter`, etc.? – M-- Jul 08 '19 at 19:06
  • @joran Well, considering that step only, mine is also a one liner, instead of `uncount` I have `expand` but this question also needs wide to long and removing "uncounted" column, etc. This has couple more steps than the dupe (not arguing this should be reopened). For implementation of my approach in the case of your dupe-target please see my answer under that post. Cheers. – M-- Jul 08 '19 at 20:07