Split a column into multiple columns of its factors by unique ID

Question

I have a dataframe of transactional data, for example:

ID   TYPE    QUANTITY
01    A    2
01    B    1
01    C    4
02    A    3
02    C    2
03    C    3

I need to split ID by the factors in TYPE and from those factors create new columns that contain the QUANTITY value for each column.

So that it will look like this:

ID    A    B    C
01    2    1    4
02    3    0    2
03    0    0    3

score 2 · Answer 1 · answered Dec 22 '17 at 18:50

2

use the tidyr package and make your long format to a wide format

library(tidyr)
spread(df, key = 'TYPE', value= 'QUANTITY')

answered Dec 22 '17 at 18:50

Linus

pogibas · Accepted Answer · 2017-12-22T19:04:28.897

1

You can use dcast() from reshape2 package.

library(reshape2)
dcast(df, ID ~ TYPE, fun.aggregate = sum)
  ID A B C
1  1 2 1 4
2  2 3 0 2
3  3 0 0 3

edited Dec 22 '17 at 19:04

answered Dec 22 '17 at 19:01

pogibas

hehe, lets see who wins this time, reshape2 or tidyr ;-) – Linus Dec 22 '17 at 19:04
@Linus It's on :-D – pogibas Dec 22 '17 at 19:04
Both are valid answers! I am assuming the greater benefit of the answer from @PoGibas is this will include the sum of `QUANTITY` in rare cases that `ID` and `TYPE` are both the same? – STACKin Dec 22 '17 at 19:34
@STACKin yes and it doesn’t introduce ‘NA’ – pogibas Dec 22 '17 at 19:41

score 0 · Answer 3 · answered Dec 22 '17 at 19:18

0

This is basically using base r

reshape(data,v.names="QUANTITY",timevar = "TYPE",idvar = "ID",direction = "wide")

answered Dec 22 '17 at 19:18

Onyambu

Although the use of `cast` or `dcast` or `recast` or even `acast` from `reshape` and `reshape2` packages depending on what is needed might be quite easy to follow and manipulate. – Onyambu Dec 22 '17 at 19:18

3 Answers3