I have a question about summing diagonal from left down to right top.
Here is the data (dt):
dt
date 1 2 3 4
1: 2016-01-01 47 562358 2881713 6062886
2: 2016-02-01 37 667633 2868784 6033143
3: 2016-03-01 130 546816 2292909 5092588
4: 2016-04-01 60 453094 2277252 5276752
str(dt)
Classes ‘data.table’ and 'data.frame': 4 obs. of 5 variables:
$ date : chr "2016-01-01" "2016-02-01" "2016-03-01" "2016-04-01"
$ 1 : num 47 37 130 60
$ 2 : num 562358 667633 546816 453094
$ 3 : num 2881713 2868784 2292909 2277252
$ 4 : num 6062886 6033143 5092588 5276752
What I want is to create like this:
NDate Value
2016-01-01 47
2016-02-01 562395
2016-03-01 3549476
2016-04-01 8007158
2016-05-01 8779146
2016-06-01 7369840
2016-07-01 5276752
NDate is month of date plus col number. For example: NDate == 2016-02-01, it's Value is 562395, which is 37 + 562358.
How can I do?
1) One can pass a two column matrix of i,j indexes to a data frame to get those value so:
library(data.table)
Sum <- function(i, d) sum(as.data.frame(d)[-1][cbind(1:i, i:1)])
dt[, list(date, Value = sapply(1:.N, Sum, .SD))]
giving:
date Value
1: 2016-01-01 47
2: 2016-02-01 562395
3: 2016-03-01 3549476
4: 2016-04-01 9478546
2) Omitting date row number + column number is constant on anti-diagonals so:
mat <- as.matrix(dt)[, -1]
values <- tapply(mat, row(mat) + col(mat), sum)
data.table(date = as.Date(dt$date[1]) + seq_along(values) - 1, values = values)
giving:
date values
1: 2016-01-01 47
2: 2016-01-02 562395
3: 2016-01-03 3549476
4: 2016-01-04 9478546
5: 2016-01-05 8779146
6: 2016-01-06 7369840
7: 2016-01-07 5276752
The input in reproducible form is:
Lines <- "date 1 2 3 4
2016-01-01 47 562358 2881713 6062886
2016-02-01 37 667633 2868784 6033143
2016-03-01 130 546816 2292909 5092588
2016-04-01 60 453094 2277252 5276752"
library(data.table)
dt <- fread(Lines, header = TRUE)