reshape a data.frame in R

Question

I have a data.frame in R.

df = data.frame(custid=c(1,2,3,4),prod1=c('jeans','tshirt','jacket','tshirt'),prod1_hnode1=c(1,2,3,2),prod1_hnode2=c(6,7,8,7),prod2=c('tshirt','jeans','jacket','shirt'),prod2_hnode1=c(2,1,3,4),prod2_hnode2=c(7,6,8,7))

> df
  custid  prod1 prod1_hnode1 prod1_hnode2  prod2 prod2_hnode1 prod2_hnode2
1      1  jeans            1            6 tshirt            2            7
2      2 tshirt            2            7  jeans            1            6
3      3 jacket            3            8 jacket            3            8
4      4 tshirt            2            7  shirt            4            7

How can I reshape it to?

  custid  prod    rec hnode1 hnode2
1      1 prod1  jeans      1      6
2      1 prod2 tshirt      2      7
3      2 prod1 tshirt      2      7
4      2 prod2  jeans      1      6
5      3 prod1 jacket      3      8
6      3 prod2 jacket      3      8
7      4 prod1 tshirt      2      7
8      4 prod2  shirt      4      7

Just got this answered how to do this in python here. Curious about R solution too.

score 3 · Accepted Answer · answered Jan 12 '18 at 10:34

We can do this with melt from data.table

library(data.table)
melt(setDT(df), measure = patterns("^prod\\d+$", "hnode1", "hnode2"), 
    value.name = c("rec", "hnode1", "hnode2"), variable.name = 'prod')[, 
        prod := paste0("prod", prod)][order(custid)]
#    custid  prod    rec hnode1 hnode2
#1:      1 prod1  jeans      1      6
#2:      1 prod2 tshirt      2      7
#3:      2 prod1 tshirt      2      7
#4:      2 prod2  jeans      1      6
#5:      3 prod1 jacket      3      8
#6:      3 prod2 jacket      3      8
#7:      4 prod1 tshirt      2      7
#8:      4 prod2  shirt      4      7

@TUSHAr `gather` from `tidyr` can do reshaping but one at a time — akrun, Jan 12 '18 at 10:43

score 1 · Answer 2 · answered Jan 12 '18 at 17:23

Another approach is to use base R's reshape function.

Try:

long <- reshape(df, direction = "long", idvar = "custid", 
                varying = list(c(2, 5), c(3, 6), c(4, 7)), 
                sep = "", times = c("prod1", "prod2"))

You're pretty much done at this point, but you can also pretty up your row and column names:

rownames(long) <- NULL    
colnames(long) <- c("custid", "prod", "rec", "hnode1", "hnode2")
long
#   custid  prod    rec hnode1 hnode2
# 1      1 prod1  jeans      1      6
# 2      2 prod1 tshirt      2      7
# 3      3 prod1 jacket      3      8
# 4      4 prod1 tshirt      2      7
# 5      1 prod2 tshirt      2      7
# 6      2 prod2  jeans      1      6
# 7      3 prod2 jacket      3      8
# 8      4 prod2  shirt      4      7

I can't really think of a "tidyverse" approach that doesn't involve combining two subsets of the data. Here's what got to the desired output:

library(tidyverse)

left <- df %>% 
  select(custid, prod1, prod2) %>%
  gather(prod, rec, -custid) %>%
  arrange(custid) 

right <- df %>%
  select(custid, contains("node")) %>%
  gather(var, val, -custid) %>%
  mutate(var = sub(".*_", "", var)) %>%
  group_by(custid, var) %>%
  mutate(ind = sequence(n())) %>%
  spread(var, val) %>%
  ungroup() %>%
  select(-ind, -custid)

cbind(left, right)

Great answer. Love the base-R approach. – tushaR Jan 13 '18 at 03:58 — tushaR, Jan 13 '18 at 03:58

reshape a data.frame in R

2 Answers2