R: calculating column sums & row sums as an aggregation from a dataframe

Question

I am trying to obtain column sums & row sums by aggregating with previous row sums & column sums.

For eg.

My initial data frame is:

      Flag1   Flag2  Flag3   Type1 Type2 Type3
1     Level1    A    FIRST     2     0    0
2     Level1    A    SECOND    1     9    0
3     Level1    A    THIRD     3     7    0
4     Level1    A    FOURTH    9    18    0
5     Level1    A    FIFTH     1    22    0
6     Level1    A    SIXTH     1    13    0
7     Level1    B    FIRST     0     0    0
8     Level1    B    SECOND    3     9    0
9     Level1    B    THIRD     5    85    0
10    Level1    B    FOURTH    4    96    0
11    Level1    B    FIFTH     3    40    0
12    Level1    B    SIXTH     0    17    0
22    Level2    A    FIRST     2     0    0
23    Level2    A    SECOND    1     9    0
24    Level2    A    THIRD     3     7    0
25    Level2    A    FOURTH    9    18    0
26    Level2    A    FIFTH     1    22    0
27    Level2    A    SIXTH     1    13    0
28    Level2    B    FIRST     0     0    0
29    Level2    B    SECOND    3     9    0
30    Level2    B    THIRD     5    85    0
31    Level2    B    FOURTH    4    96    0
32    Level2    B    FIFTH     3    40    0
33    Level2    B    SIXTH     0    17    0
34    Level3    A    FIRST     2     0    0
35    Level3    A    SECOND    1     9    0
36    Level3    A    THIRD     3     7    0
37    Level3    A    FOURTH    9    18    0
38    Level3    A    FIFTH     1    22    0
39    Level3    A    SIXTH     1    13    0
40    Level3    B    FIRST     0     0    0
41    Level3    B    SECOND    3     9    0
42    Level3    B    THIRD     5    85    0
43    Level3    B    FOURTH    4    96    0
44    Level3    B    FIFTH     3    40    0
45    Level3    B    SIXTH     0    17    0

My desired result is:

    Flag1   Flag2   Flag3   Type1   Type2   Type3   Sum
1   Level1  A   FIRST   2   0   0   2
2   Level1  A   SECOND  1   9   0   12
3   Level1  A   THIRD   3   7   0   22
4   Level1  A   FOURTH  9   18  0   49
5   Level1  A   FIFTH   1   22  0   72
6   Level1  A   SIXTH   1   13  0   86
7   Level1  B   FIRST   0   0   0   0
8   Level1  B   SECOND  3   9   0   12
9   Level1  B   THIRD   5   85  0   102
10  Level1  B   FOURTH  4   96  0   202
11  Level1  B   FIFTH   3   40  0   245
12  Level1  B   SIXTH   0   17  0   262
13  Level1  (all)   FIRST   2   0   0   2
14  Level1  (all)   SECOND  4   18  0   24
15  Level1  (all)   THIRD   8   92  0   124
16  Level1  (all)   FOURTH  13  114 0   251
17  Level1  (all)   FIFTH   4   62  0   317
18  Level1  (all)   SIXTH   1   30  0   348
19  Level1  A   (all)   17  68  0   85
20  Level1  B   (all)   15  247 0   262
21  Level1  (all)   (all)   32  315 0   347
22  Level2  A   FIRST   2   0   0   2
23  Level2  A   SECOND  1   9   0   12
24  Level2  A   THIRD   3   7   0   22
25  Level2  A   FOURTH  9   18  0   49
26  Level2  A   FIFTH   1   22  0   72
27  Level2  A   SIXTH   1   13  0   86
28  Level2  B   FIRST   0   0   0   0
29  Level2  B   SECOND  3   9   0   12
30  Level2  B   THIRD   5   85  0   102
31  Level2  B   FOURTH  4   96  0   202
32  Level2  B   FIFTH   3   40  0   245
33  Level2  B   SIXTH   0   17  0   262
34  Level2  (all)   FIRST   2   0   0   2
35  Level2  (all)   SECOND  4   18  0   24
36  Level2  (all)   THIRD   8   92  0   124
37  Level2  (all)   FOURTH  13  114 0   251
38  Level2  (all)   FIFTH   4   62  0   317
39  Level2  (all)   SIXTH   1   30  0   348
40  Level2  A   (all)   17  68  0   85
41  Level2  B   (all)   15  247 0   262
42  Level2  (all)   (all)   32  315 0   347
43  Level3  A   FIRST   2   0   0   2
44  Level3  A   SECOND  1   9   0   12
45  Level3  A   THIRD   3   7   0   22
46  Level3  A   FOURTH  9   18  0   49
47  Level3  A   FIFTH   1   22  0   72
48  Level3  A   SIXTH   1   13  0   86
49  Level3  B   FIRST   0   0   0   0
50  Level3  B   SECOND  3   9   0   12
51  Level3  B   THIRD   5   85  0   102
52  Level3  B   FOURTH  4   96  0   202
53  Level3  B   FIFTH   3   40  0   245
54  Level3  B   SIXTH   0   17  0   262
55  Level3  (all)   FIRST   2   0   0   2
56  Level3  (all)   SECOND  4   18  0   24
57  Level3  (all)   THIRD   8   92  0   124
58  Level3  (all)   FOURTH  13  114 0   251
59  Level3  (all)   FIFTH   4   62  0   317
60  Level3  (all)   SIXTH   1   30  0   348
61  Level3  A   (all)   17  68  0   85
62  Level3  B   (all)   15  247 0   262
63  Level3  (all)   (all)   32  315 0   347
64  (all)   A   FIRST   6   0   0   6
65  (all)   A   SECOND  9   27  0   42
66  (all)   A   THIRD   18  48  0   108
67  (all)   A   FOURTH  45  102 0   255
68  (all)   A   FIFTH   48  168 0   471
69  (all)   A   SIXTH   51  207 0   729
70  (all)   B   FIRST   0   0   0   0
71  (all)   B   SECOND  9   27  0   36
72  (all)   B   THIRD   24  282 0   342
73  (all)   B   FOURTH  36  570 0   948
74  (all)   B   FIFTH   45  690 0   1683
75  (all)   B   SIXTH   45  741 0   2469
76  (all)   A   (all)   51  552 0   1611
77  (all)   B   (all)   45  2310    0   5478
78  (all)   (all)   (all)   96  2862    0   7089

Thanks

Also, please specify what you need that is different than your previous question, http://stackoverflow.com/questions/5863456/r-calculating-margins-or-row-col-sums-for-a-data-frame — Aaron left Stack Overflow, May 12 '11 at 19:22
apologize that the question was incomplete. In my previous question what I wanted was just plain row sums. But in this case for each value of Flag 2, I need the sum of each row to include the sum from the previous row for that Flag 1. For example row number 3, Level 1 A Third, row sum is 10, but I need the row sum from the previous two rows to be added to it, to total 12. So for each value of Flag 1 & Flag 2, this the rowsums should add not only the type 1, 2 & 3 values but also the previous row sums. — user669815, May 12 '11 at 19:51

score 3 · Accepted Answer · edited May 23 '17 at 12:16

It's not clear what you want here, since you mention "aggregating" and "previous" enitities, but if you want the rowsums followed by the colsums for a dataframe named dfrm, then this works:

dfrm$totals <- rowSums(dfrm[, 4:6])
dfrmT <- rbind(dfrm, data.frame(Flag1="all", Flag2="all", Flag3="all", 
                                  t( colSums(dfrm[, 4:7]) )
                ) )
dfrmT

EDIT: So you did want the row sums and then within levels of Flag1 and Flag2 you wanted cumsums: (And if you want the multiple subtotals, that was answered in your earlier question.) R: calculating margins or row & col sums for a data frame

dfrmT$CS <- ave(   dfrmT$totals, list(dfrmT$Flag1, dfrmT$Flag2), FUN=cumsum)
dfrmT

     Flag1 Flag2  Flag3 Type1 Type2 Type3 totals  CS
1   Level1     A  FIRST     2     0     0      2   2
2   Level1     A SECOND     1     9     0     10  12
3   Level1     A  THIRD     3     7     0     10  22
4   Level1     A FOURTH     9    18     0     27  49
5   Level1     A  FIFTH     1    22     0     23  72
6   Level1     A  SIXTH     1    13     0     14  86
7   Level1     B  FIRST     0     0     0      0   0
8   Level1     B SECOND     3     9     0     12  12
9   Level1     B  THIRD     5    85     0     90 102
10  Level1     B FOURTH     4    96     0    100 202
11  Level1     B  FIFTH     3    40     0     43 245
12  Level1     B  SIXTH     0    17     0     17 262
22  Level2     A  FIRST     2     0     0      2   2
23  Level2     A SECOND     1     9     0     10  12
24  Level2     A  THIRD     3     7     0     10  22
25  Level2     A FOURTH     9    18     0     27  49
26  Level2     A  FIFTH     1    22     0     23  72
27  Level2     A  SIXTH     1    13     0     14  86
28  Level2     B  FIRST     0     0     0      0   0
29  Level2     B SECOND     3     9     0     12  12
30  Level2     B  THIRD     5    85     0     90 102
31  Level2     B FOURTH     4    96     0    100 202
32  Level2     B  FIFTH     3    40     0     43 245
33  Level2     B  SIXTH     0    17     0     17 262
34  Level3     A  FIRST     2     0     0      2   2
35  Level3     A SECOND     1     9     0     10  12
36  Level3     A  THIRD     3     7     0     10  22
37  Level3     A FOURTH     9    18     0     27  49
38  Level3     A  FIFTH     1    22     0     23  72
39  Level3     A  SIXTH     1    13     0     14  86
40  Level3     B  FIRST     0     0     0      0   0
41  Level3     B SECOND     3     9     0     12  12
42  Level3     B  THIRD     5    85     0     90 102
341    all   all    all    89   795     0    884 884

If on the other hand you want cumsums within each Type with initialization at new Flag1 and Flag2 divisions then:

ave( dfrm[ , grep("Type", names(dfrm))], list(dfrm$Flag1, dfrm$Flag2), FUN=cumsum)
   Type1 Type2 Type3
1      2     0     0
2      3     9     0
3      6    16     0
4     15    34     0
5     16    56     0
6     17    69     0
7      0     0     0
8      3     9     0
9      8    94     0
10    12   190     0
11    15   230     0
12    15   247     0
22     2     0     0
23     3     9     0
24     6    16     0
25    15    34     0
26    16    56     0
27    17    69     0
28     0     0     0
29     3     9     0
30     8    94     0
31    12   190     0
32    15   230     0
33    15   247     0
34     2     0     0
35     3     9     0
36     6    16     0
37    15    34     0
38    16    56     0
39    17    69     0
40     0     0     0
41     3     9     0
42     8    94     0

That is suitable for cbind()-ing to dfrm but needs work on the names():

dfrmCS <- cbind(dfrm, ave( dfrm[ , grep("Type", names(dfrm))], 
                               list(dfrm$Flag1, dfrm$Flag2), FUN=cumsum) )
names(dfrmCS)[8:10] <- paste(names(dfrmCS)[8:10], "CS", sep="_")

Thanks DWin. I was able to get what I wanted. One other question too - I hadn't heard of the cumsum function before. Any idea how I can find the different such inbuilt functions? — user669815, May 18 '11 at 22:50

R: calculating column sums & row sums as an aggregation from a dataframe

1 Answers1

Linked