r- reshape table grouped by 3 variables (2 in rows)

Question

I need to reshape to wide format the following table:

> data
   dia cli llam elegidos  cumllam
1 1-11   a    1        1        1
2 2-11   a    2        1        3
3 1-11   b    2        1        2
4 2-11   b    1        1        3
5 2-11   c    1        0        1

I need to have days in rows and cumllam in columns and the quantity of clients as a value.

I wrote:

library(reshape2) 
my.f <-  function (v) {if (length(v) == 0) 0 else length(v)} 
series<-data.frame(dcast(data, dia~cumllam , 
            fun.aggregate=my.f,  value.var='cli'))

And I get:

> series
   dia X1 X2 X3
1 1-11  1  1  0
2 2-11  1  0  2

But I need to split it by column "elegidos". My table should be:

elegidos    dia    X1   X2  X3
0           2-11    1   0   0
1           1-11    1   1   0
1           2-11    0   0   2

I tried:

  data.frame(dcast(data, c(elegidos,dia)~cumllam ,
              fun.aggregate=my.f,  value.var='cli'))

But I get a wrong result:

  c.elegidos..dia. X1 X2 X3
1                0  1  0  0
2                1  2  2  2
3                2  1  0  2

I could filter the table 1st, then run dcast as in the first code and the rbind, but I'm sure there's a way to do it in one step.

It is unclear what you want your final result to be. Could you clarify what the final dataframe should look like? — cdeterman, Nov 04 '14 at 15:15
using @cdeterman's data, `with(df, ftable(dia, elgidos, cumllam))` — rawr, Nov 04 '14 at 15:40

score 2 · Accepted Answer · answered Nov 04 '14 at 15:19

If your goal is the second to last matrix in your question you can just use:

df <- read.table(header=T, text = '  dia cli llam elegidos  cumllam
 1-11   a    1        1        1
 2-11   a    2        1        3
 1-11   b    2        1        2
 2-11   b    1        1        3
 2-11   c    1        0        1
')

require(reshape2)
dcast(df, formula=elegidos + dia ~ cumllam, length)
  elegidos  dia 1 2 3
1        0 2-11 1 0 0
2        1 1-11 1 1 0
3        1 2-11 0 0 2

score 1 · Answer 2 · answered Nov 04 '14 at 15:31

Using base R:

> reshape(ddf[,-2], idvar=c('dia','elegidos'), timevar='cumllam', direction='wide')
   dia elegidos llam.1 llam.3 llam.2
1 1-11        1      1     NA      2
2 2-11        1     NA      2     NA
5 2-11        0      1     NA     NA

To replace NA by 0:

> rr = reshape(ddf[,-2], idvar=c('dia','elegidos'), timevar='cumllam', direction='wide')
> rr[is.na(rr)] <- 0
> rr
   dia elegidos llam.1 llam.3 llam.2
1 1-11        1      1      0      2
2 2-11        1      0      2      0
5 2-11        0      1      0      0

r- reshape table grouped by 3 variables (2 in rows)

2 Answers2

Linked