CODEWITHSUNDEEP
rdplyr
Tyler
Tyler

Reputation: 53

How to convert column values to rows based on other column values

Basically, I want to take the numerical value in one column to duplicate or spread the categorical values in other columns.

I have something like this:

df <- data.frame(a = c(1:3), 
             b = c(2:4), 
             c = c(1:3), 
             d = c("cow", "moose", "chicken"))
# > df
#   a b c       d
# 1 1 2 3     cow
# 2 2 3 4   moose
# 3 3 4 5 chicken

And I want something like this:

df2 <- data.frame(col1, col2)
col1 <- c("a", "b", "b", "c", "c", "c", "a","a")
col2 <- c( "cow", "cow", "cow", "cow", "cow", "cow", "moose", "moose")

# > df2
#   col1  col2
# 1    a   cow
# 2    b   cow
# 3    b   cow
# 4    c   cow
# 5    c   cow
# 6    c   cow
# 7    a moose
# 8    a moose ....and so on.

Upvotes: 1

Views: 83

Answers (2)

akrun
akrun

Reputation: 887213

Here is one option with data.table

library(data.table)
melt(setDT(df), id.var = 'd', variable.name = 'key'
       )[rep(seq_len(.N), value)][, value := NULL][]
#          d key
# 1:     cow   a
# 2:   moose   a
# 3:   moose   a
# 4: chicken   a
# 5: chicken   a
# 6: chicken   a
# 7:     cow   b
# 8:     cow   b
# 9:   moose   b
#10:   moose   b
#11:   moose   b
#12: chicken   b
#13: chicken   b
#14: chicken   b
#15: chicken   b
#16:     cow   c
#17:   moose   c
#18:   moose   c
#19: chicken   c
#20: chicken   c
#21: chicken   c

Upvotes: 1

markus
markus

Reputation: 26343

Here is one option that uses tidyr's gather to rehsape the data from wide to long format and expandRows from splitstackshape to, well, expand the rows of the dataframe.

library(splitstackshape)
library(tidyr)
df %>% 
  gather(key, value, -d) %>% 
  expandRows(., count = 'value')
#          d key
#1       cow   a
#2     moose   a
#2.1   moose   a
#3   chicken   a
#3.1 chicken   a
#3.2 chicken   a
#4       cow   b
#4.1     cow   b
#5     moose   b
#5.1   moose   b
#5.2   moose   b
#6   chicken   b
#6.1 chicken   b
#6.2 chicken   b
#6.3 chicken   b
#7       cow   c
#8     moose   c
#8.1   moose   c
#9   chicken   c
#9.1 chicken   c
#9.2 chicken   c

data

df <- data.frame(a = c(1:3), 
                 b = c(2:4), 
                 c = c(1:3), 
                 d = c("cow", "moose", "chicken"))

Upvotes: 2

Related Questions