CODEWITHSUNDEEP
raggregate
GeoSolve
GeoSolve

Reputation: 13

Sum column when creating table

I have the following data frame in R:

   origin destination amount
1       1           2     50
2       1           2    100
3       1           2     20
4       1           3    100
5       2           3     30
6       2           3     50
7       2           1     20
8       3           2     10
9       3           2     40
10      3           1     50

And want to get it in a table like this:

     1   2   3
   1 0  170 100
   2 20  0   80
   3 50  50  0

I assume it should be the table function but this only gets me the following:

   1 2 3
 1 0 3 1
 2 1 0 2
 3 1 2 0

and I can't figure out how I get the count of the amountcolumn as the value in the contiguency table. Any tips?

Upvotes: 1

Views: 53

Answers (1)

akrun
akrun

Reputation: 886938

You can try xtabs

 xtabs(amount~origin+destination, df1)
 #        destination
 #origin   1   2   3
 #     1   0 170 100
 #     2  20   0  80
 #     3  50  50   0

Or use tapply amd replace NA with 0

 with(df1, tapply(amount, list(origin, destination), FUN=sum))

Or as @David Arenburg mentioned, this could be also done with packages like reshape2, tidyr

 library(reshape2)
 acast(df1, origin~destination, value.var='amount', sum)

Upvotes: 2

Related Questions