Dcast multiple observation in a 1 cell in R

Question

I have one R dataframe

Customer    Month   BaseVolume  IncrementalVolume   TradeSpend
10          Jan         11            1             110
10          Feb         12            2             120
20          Jan         21            7             210
20          Feb         22            8             220

Which I want to convert it like this,

Customer    Jan                    Feb  
10          BaseVolume 11         BaseVolume 12
            IncrementalVolume 1   IncrementalVolume 2 
            TradeSpend 110        TradeSpend 120

20         BaseVolume 21          BaseVolume 22
           IncrementalVolume 7    IncrementalVolume 8 
           TradeSpend 210         TradeSpend 220     

I tried dcast (reshape) but I couldn't get this result. Please help me out

Answer 1

What you could try is the following (In your case say your data is df1 you need to do setDT(df1) before any of the actions that I mention):

library(data.table)
dt1 <- structure(list(Customer = c(10L, 10L, 20L, 20L), Month = c("Jan", 
"Feb", "Jan", "Feb"), BaseVolume = c(11L, 12L, 21L, 22L), IncrementalVolume = c(1L, 
2L, 7L, 8L), TradeSpend = c(110L, 120L, 210L, 220L)), .Names = c("Customer", 
"Month", "BaseVolume", "IncrementalVolume", "TradeSpend"), row.names = c(NA, 
-4L), class = c("data.table", "data.frame"))

res <- dcast(melt(dt1, id.vars = c("Customer", "Month")), Customer + variable~ Month)

> res
   Customer          variable Feb Jan
1:       10        BaseVolume  12  11
2:       10 IncrementalVolume   2   1
3:       10        TradeSpend 120 110
4:       20        BaseVolume  22  21
5:       20 IncrementalVolume   8   7
6:       20        TradeSpend 220 210

In case you want them together you can do the following:

update_cols <- which(!names(res) %in% c("Customer", "variable"))
res[, (update_cols):= lapply(.SD, function(x) paste(variable, x)), .SDcols = update_cols][, variable:= NULL]

Which gives:

> res
     Customer            Feb                 Jan
1:       10       BaseVolume 12       BaseVolume 11
2:       10 IncrementalVolume 2 IncrementalVolume 1
3:       10      TradeSpend 120      TradeSpend 110
4:       20       BaseVolume 22       BaseVolume 21
5:       20 IncrementalVolume 8 IncrementalVolume 7
6:       20      TradeSpend 220      TradeSpend 210

Answer 2

Although there is already an answer, I feel it can improved in some respect to come closer to the expected output:

• the OP has specified the month to appear in the order Jan, Feb
• the output is difficult to read
• munging of columns should take place before the dcast()

We'll start with reshaping the input data from wide to long format but make sure that Month will appear in the correct order:

molten <- melt(dt1, id.vars = c("Customer", "Month"))
# turn Month into factor with levels in the given order
molten[, Month := forcats::fct_inorder(Month)]

Now, a new text column is created in long format before the call to dcast():

molten[, text := paste(variable, value)]
dcast(molten, Customer + variable ~ Month, value.var = "text")[, variable := NULL][]
#   Customer                 Jan                 Feb
#1:       10       BaseVolume 11       BaseVolume 12
#2:       10 IncrementalVolume 1 IncrementalVolume 2
#3:       10      TradeSpend 110      TradeSpend 120
#4:       20       BaseVolume 21       BaseVolume 22
#5:       20 IncrementalVolume 7 IncrementalVolume 8
#6:       20      TradeSpend 210      TradeSpend 220

The result is similar to this answer but has the months in the expected order.

---

N.B. Unfortunately, the approach to collapse also the rows per Customer doesn't work as line breaks aren't respected when printed:

dcast(molten, Customer ~ Month, value.var = "text", paste0, collapse = "\n")
#   Customer                                                Jan                                                Feb
#1:       10 BaseVolume 11\nIncrementalVolume 1\nTradeSpend 110 BaseVolume 12\nIncrementalVolume 2\nTradeSpend 120
#2:       20 BaseVolume 21\nIncrementalVolume 7\nTradeSpend 210 BaseVolume 22\nIncrementalVolume 8\nTradeSpend 220

---

The text column can be left aligned by padding white space to the right (the minimum lengths is determined by the character length of the longest string):

molten[, text := paste(variable, value)]
molten[, text := stringr::str_pad(text, max(nchar(text)), "right")]
dcast(molten, Customer + variable ~ Month, value.var = "text")[, variable := NULL][]
#   Customer                 Jan                 Feb
#1:       10 BaseVolume 11       BaseVolume 12      
#2:       10 IncrementalVolume 1 IncrementalVolume 2
#3:       10 TradeSpend 110      TradeSpend 120     
#4:       20 BaseVolume 21       BaseVolume 22      
#5:       20 IncrementalVolume 7 IncrementalVolume 8
#6:       20 TradeSpend 210      TradeSpend 220     

Or, the text column can be aligned in itself:

fmt <- stringr::str_interp("%-${n}s %3i", list(n = molten[, max(nchar(levels(variable)))]))
molten[, text := sprintf(fmt, variable, value)]
dcast(molten, Customer + variable ~ Month, value.var = "text")[, variable := NULL][]
#   Customer                   Jan                   Feb
#1:       10 BaseVolume         11 BaseVolume         12
#2:       10 IncrementalVolume   1 IncrementalVolume   2
#3:       10 TradeSpend        110 TradeSpend        120
#4:       20 BaseVolume         21 BaseVolume         22
#5:       20 IncrementalVolume   7 IncrementalVolume   8
#6:       20 TradeSpend        210 TradeSpend        220

Here, the format to be used in sprintf() is also created dynamically by using string interpolation:

fmt
#[1] "%-17s %3i"

Note that the character length of the longest level of variable is used here as melt() has turned variable to factor by default.

---

The answer could have been much simpler as the latest versions of data.table allow to reshape multiple columns simultaneously:

molten <- melt(dt1, id.vars = c("Customer", "Month"))
molten[, Month := forcats::fct_inorder(Month)]
dcast(molten, Customer + variable ~ Month, value.var = c("variable", "value"))
#   Customer          variable    variable.1_Jan    variable.1_Feb value_Jan value_Feb
#1:       10        BaseVolume        BaseVolume        BaseVolume        11        12
#2:       10 IncrementalVolume IncrementalVolume IncrementalVolume         1         2
#3:       10        TradeSpend        TradeSpend        TradeSpend       110       120
#4:       20        BaseVolume        BaseVolume        BaseVolume        21        22
#5:       20 IncrementalVolume IncrementalVolume IncrementalVolume         7         8
#6:       20        TradeSpend        TradeSpend        TradeSpend       210       220

but unfortunately it is lacking an option to easily reorder the columns in alternating order, i.e., all columns belonging to Jan, then Feb etc.