CODEWITHSUNDEEP
rsumsummary
DJack
DJack

Reputation: 4940

How to sum by group an "Origin-Destination" data frame?

I have this kind of data frame:

df<-data.frame(Origin=c(1,1,1,2,2,3,3,3),
               Var=   c(2,4,1,3,5,6,2,1),
               Desti= c(2,2,3,2,1,2,1,3))

I would like to get the sum of Var, for each value of Origin, grouped by Desti (Out.x) and by Origin (In.x). The result would be for df:

  Out.1 Out.2 Out.3 In.1 In.2 In.3
1     0     6     1    0    5    2
2     5     3     0    6    3    6
3     2     6     1    1    0    1

Any ideas ?

Upvotes: 2

Views: 180

Answers (1)

akrun
akrun

Reputation: 887048

May be this helps

res <-  cbind(xtabs(Var~., df), xtabs(Var~Desti+Origin, df))
colnames(res) <- paste(rep(c("Out", "In"), each=3), 1:3, sep=".")
res
#  Out.1 Out.2 Out.3 In.1 In.2 In.3
#1     0     6     1    0    5    2
#2     5     3     0    6    3    6
#3     2     6     1    1    0    1

Or, the above can be simplied

 r1 <- xtabs(Var~., df)
 res <- cbind(r1, t(r1)) #change the `column names` accordingly

Or using reshape2

library(reshape2)
res1 <- cbind(acast(df, Origin~Desti, value.var='Var', sum),
               acast(df, Desti~Origin, value.var='Var', sum))
colnames(res1) <- colnames(res)

Upvotes: 4

Related Questions