3

I have a dataframe of incident cases of a disease. by year and age, which looks like this (it is much larger than this example)

     88  89  90  91      
22   1   2   5   14 
23   1   6   9   15   
24   2   5   12  11  
25   3   3   7   20

What I would like to do is iteratively sum the diagonals, to get this result

     88  89  90  91      
22   1   2   5   14 
23   1   7   11  20   
24   2   6   19  22  
25   3   5   13  39

Or, put another way; original dataset:

     Y1  Y2  Y3  Y4      
22   A1  B1  C1  D1 
23   A2  B2  C2  D2   
24   A3  B3  C3  D3   
25   A4  B4  C4  D4

Final dataset:

     Y1  Y2     Y3        Y4      
22   A1  B1     C1        D1 
23   A2  A1+B2  B1+C2     C1+D2   
24   A3  A2+B3  A1+B2+C3  B1+C2+D3   
25   A4  A3+B4  A2+B3+C4  A1+B2+C3+D4

Is there any way to do this in R?

I have seen this question How to sum over diagonals of data frame, but he only wants the total sum, I want the iterative sum.

Thanks.

Laura
  • 499
  • 5
  • 13

1 Answers1

6

Use ave noting that row(m) - col(m) is constant on diagonals:

ave(m, row(m) - col(m), FUN = cumsum)
##    88 89 90 91
## 22  1  2  5 14
## 23  1  7 11 20
## 24  2  6 19 22
## 25  3  5 13 39

It is assumed that m is a matrix as in the Note below. If you have a data frame then convert it to a matrix first.

Note

The input matrix m in reproducible form is:

Lines <- "     88  89  90  91      
22   1   2   5   14 
23   1   6   9   15   
24   2   5   12  11  
25   3   3   7   20"
m <- as.matrix(read.table(text = Lines, check.names = FALSE))
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • 2
    Thanks very much for this helpful answer. Unfortunately, my dataframe is a rectangle, not a square, of 30 columns and 49 rows. Your code works for the central diagonal, but not the rest. Do you know how to edit this code so that Y2:22 = B1, and Y3:23 = B1+C2 etc. Thanks. – Laura Jan 31 '18 at 22:25
  • `row(m)-col(m)` is constant on diagonals of non-square matrices as well so it works for those too. Note that it works on matrices, not data frames. If you have a data frame you must do `m <- as.matrix(DF)` first. – G. Grothendieck Jan 31 '18 at 23:13