0

Im looking for an R algorithm to take this table:

One Two  Value
  A   B   1002
  A   C   2312
  B   A   6543
  B   C    387
  C   A   9364
  C   B   8429

And transform that into this kind of matrix:

##       A     B     C
## A     0  6543  9364 
## B  1002     0  8429    
## C  2312   387     0

Any considerations? I don't know what the name of the first kind of table is, if there is any. Thanks!

JellisHeRo
  • 537
  • 1
  • 7
  • 17
  • 1
    the package `tidyr` and the function `spread` inside it should be your friend. – denrou Nov 30 '17 at 13:13
  • 2
    adding to above comment, you probably just need `df %>% spread(Two, Value, fill = 0)`? – Jaap Nov 30 '17 at 13:15
  • 2
    To give you more "searchable" jargon, the operation you're looking for can be called pivot (vs unpivot), or cast (vs melt), or spread (vs gather, that's `tidyr` jargon indeed), or wide format (vs long format)... and I might be forgetting some variations... – Aurèle Nov 30 '17 at 13:15
  • Possible duplicate: https://stackoverflow.com/q/5890584/2204410 – Jaap Nov 30 '17 at 13:18
  • 2
    `xtabs(Value ~ ., df)`... or transpose it to get your exact expected output `t(xtabs(Value ~ ., df))` – Sotos Nov 30 '17 at 13:20

1 Answers1

0

You can go from one representation to the other using xtabs and as.data.frame. If x is the original data:

> xtabs(Value ~ Two + One, x)
   One
Two    A    B    C
  A    0 6543 9364
  B 1002    0 8429
  C 2312  387    0

> as.data.frame(xtabs(Value ~ Two + One, x), responseName = 'Value')
  Two One Value
1   A   A     0
2   B   A  1002
3   C   A  2312
4   A   B  6543
5   B   B     0
6   C   B   387
7   A   C  9364
8   B   C  8429
9   C   C     0
Ernest A
  • 7,526
  • 8
  • 34
  • 40