Sum Values in a Rolling/Sliding Window

Moving sum of a certain window in R

First, please consider to read How to make a great R reproducible example? and add sample data using dput next time.

df <- data.frame(t = c(1,3,5,2,1,3,4,6,7,2,1))

library(zoo)
library(dplyr)

df$V2 <- rollsum(lead(df$t), 5, align = "left", fill = NA)
df

   t V2
1  1 14
2  3 15
3  5 16
4  2 21
5  1 22
6  3 20
7  4 NA
8  6 NA
9  7 NA
10 2 NA
11 1 NA

Sum values in a rolling/sliding window

What you have is a vector, not an array. You can use rollapply function from zoo package to get what you need.

> x <- c(1, 2, 3, 10, 20, 30)
> #library(zoo)
> rollapply(x, 3, sum)
[1]  6 15 33 60

Take a look at ?rollapply for further details on what rollapply does and how to use it.

Summing the counts in a data frame using sliding window

Using the data.table package, I would approach it as follows:

library(data.table)
setDT(df)[, .(w1 = sum(Values[1:(3*(.N%/%3))]),
              w2 = sum(Values[2:(3*((.N-1)%/%3)+1)]),
              w3 = sum(Values[3:(3*((.N-2)%/%3)+2)]))
          , by = ID]

which gives:

   ID  w1  w2  w3
1: A1 102 113  77
2: A2 206 195 161
3: A3 198 163 175

Or to avoid the repetition (thanx to @Cath):

setDT(df)[, lapply(1:3, function(i) {sum(Values[i:(3*((.N-i+1)%/%3)+(i-1))])})
          , by = ID]

If you want to rename the V1, V2 & V3 variables, you can do that afterwards, but you can also do:

cols <- c("w1","w2","w3")

setDT(df)[, (cols) := lapply(1:3, function(i) {sum(Values[i:(3*((.N-i+1)%/%3)+(i-1))])})
          , by = ID]

Rolling sum on a dynamic window

You can create a custom function for use with df.apply, eg:

def lookback_window(row, values, lookback, method='sum', *args, **kwargs):
    loc = values.index.get_loc(row.name)
    lb = lookback.loc[row.name]
    return getattr(values.iloc[loc - lb: loc + 1], method)(*args, **kwargs)

Then use it as:

df['new_col'] = df.apply(lookback_window, values=df['Data'], lookback=df['Lookback'], axis=1)

There may be some corner cases but as long as your indices align and are unique - it should fulfil what you're trying to do.

Rolling sum with varying window of a data.table

Another option using frollsum mentioned by jangorecki in the comments and cumsum from Cole's answer:

sz <- seq(10, 20, 2)
DT[, c(t(outer(names(DT), sz, paste0))) := {

        #use frollsum with centering alignment
        C <- matrix(unlist(frollsum(.SD, 2L*sz + 1L, align="center")), nrow=.N)

        #largest window size
        winsz <- 2L*last(sz)+1L

        #extract head and tail of data and reverse row order of tail
        H <- head(.SD, winsz)
        B <- tail(.SD, winsz)[.N:1L]

        #calculate sums of those head and tail using frollmean and cumsum
        U <- matrix(unlist(frollsum(H, sz+1L, align="left")), nrow=winsz) +
            rep(H[, as.matrix(lapply(.SD, cumsum) - .SD)], length(sz))
        D <- matrix(unlist(frollsum(B, sz+1L, align="left")), nrow=winsz) +
            rep(B[, as.matrix(lapply(.SD, cumsum) - .SD)], length(sz))
        D <- D[rev(seq_len(nrow(D))), ]

        #update NAs in C with values from U and D
        C[is.na(C) & row(C) <= winsz] <- U[is.na(C) & row(C) <= winsz]
        C[is.na(C) & row(C) >= .N - winsz] <- D[is.na(C) & row(C) >= .N - winsz]
        as.data.table(C)
    }]

output:

          money       debt      misc   money10   money12   money14   money16   money18   money20    debt10   debt12   debt14   debt16   debt18    debt20    misc10    misc12    misc14    misc16    misc18   misc20
 1: 0.089669720 0.09104731 0.7268889 0.6411836 0.6794367 0.7865494 0.9133034 1.0842559  1.200004 0.8763139 1.041279 1.157053 1.277840 1.436872  1.602857  4.271920  5.814550  7.411962  8.334052  9.779066 10.83659
 2: 0.026550866 0.08235301 0.4299947 0.6617810 0.7495166 1.5007527 0.9850653 1.1236370  1.930694 0.9679968 1.103519 1.847868 1.352394 1.507213  2.303379  4.294875  5.968677  7.894473  8.517160  9.815212 11.14334
 3: 0.037212390 0.08914664 0.3590845 0.6794367 0.8437291 1.9539665 1.0842559 1.2571837  2.355352 0.9840995 1.157053 2.261323 1.379692 1.602857  2.723974  4.773886  6.428479  8.334052  8.738403  9.853108 12.13639
 4: 0.057285336 0.07765182 0.7692328 0.7481390 0.9726475 2.3476005 1.1222594 1.4025885  2.742391 0.9944051 1.212026 2.607193 1.398099 1.667537  3.060532  5.241983  6.641051  9.154379  9.088518  9.889917 12.78821
 5: 0.090820779 0.07648598 0.2425576 0.7865494 1.0427839 3.1587385 1.2000039 1.4441692  3.466894 1.0275726 1.277840 3.381874 1.473377 1.740491  3.834656  5.337479  6.389050  9.779066  8.762108 10.191386 13.04075
 6: 0.020168193 0.08946781 0.7255652 0.8635335 1.1002110 3.3484789 1.2934745 1.4950018  3.645353 1.0968807 1.353772 3.618287 1.552392 1.807110  4.063629  5.668253  7.043305 10.451054  8.917119 10.677133 13.21366
 7: 0.089838968 0.05116656 0.1656073 0.9133034 1.2687012 4.1316204 1.3146887 1.5768569  4.389362 1.0933946 1.436872 4.350028 1.556045 1.889654  4.773653  5.402436  7.031895 10.836591  9.204771 10.293583 13.71787
 8: 0.094467527 0.07386150 0.2832141 0.9850653 1.2680323 4.3008593 1.3798561 1.5649066  4.466469 1.2079988 1.507213 4.529149 1.661338 1.952555  4.976409  6.146994  7.589442 11.143338  9.780822 10.352046 14.64574
 9: 0.066079779 0.08661569 0.1861392 1.0842559 1.3251708 4.5108201 1.3924116 1.5829119  4.693454 1.3117050 1.602857 4.811455 1.764487 2.026482  5.239974  6.582934  7.765626 12.136388  9.844623 10.646906 15.26456
10: 0.062911404 0.08463658 0.2776479 1.1222594 1.4391772 4.6960468 1.4191337 1.6047869  4.900483 1.3615106 1.667537 4.977598 1.806852 2.114798  5.433264  7.134863  7.972850 12.788207  9.897467 11.475253 16.24193
11: 0.006178627 0.07388098 0.1059877 1.2000039 1.4821167 4.9233389 1.4577451 1.6647508  5.149254 1.6028572 1.740491 5.253153 1.859054 2.169010  5.693229 10.836591  8.772889 13.040754 10.186943 11.901064 16.98713
12: 0.020597457 0.09306047 0.6601738 1.2038047 1.6149864 5.0498700 1.4590841 1.8194223  5.297271 1.5764900 1.807110 5.348161 1.879667 2.262776  5.817308 10.416449  9.392601 13.213658 11.015005 12.846320 17.37602
13: 0.017655675 0.07190486 0.8824558 1.1984681 1.3924116 5.7280578 1.4973229 1.9259202  5.996804 1.5670903 1.889654 5.989201 1.861417 2.329730  6.451755 10.979504 13.040754 13.717870 10.994251 13.024409 17.83592
14: 0.068702285 0.06223986 0.7899689 1.2264231 1.3294640 6.5941969 1.5842920 2.0283774  6.910958 1.5445634 1.861507 6.888068 1.900933 2.421702  7.328995 11.272238 12.486769 14.645741 11.106813 12.602749 18.02672
15: 0.038410372 0.05353395 0.8074434 1.1816933 1.3415245 1.4973229 1.6183269 2.0818716  7.650847 1.5494551 1.853082 2.169010 1.974350 2.489036  8.130541 10.755553 12.560987 15.264564 11.130749 12.334920 18.08914
16: 0.076984142 0.05497331 0.4825107 1.1175946 1.3056511 1.4946223 1.6665349 2.1463493  8.502617 1.5358700 1.852251 2.171729 2.051198 2.555725  8.979061 10.685899 13.129773 15.515037 10.750607 11.771812 18.81893
17: 0.049769924 0.06581359 0.4395799 1.1360378 1.2866046 1.5021063 1.7264915 2.1429602  8.974990 1.5203294 1.828811 2.156329 2.489036 2.629542  9.499815 10.464546 12.979363 15.830245 17.835919 11.406698 18.96696
18: 0.071761851 0.07593171 0.8203267 1.0475379 1.2827529 1.5131019 1.6861759 2.2417412  9.444224 1.5574784 1.846091 2.159155 2.464677 2.724152  9.943081 11.226810 13.714167 15.860051 17.299831 11.762719 19.44093
19: 0.099190609 0.08310025 0.6246866 0.9913091 1.2966196 1.5157732 1.6782468 1.9440514 10.203584 1.5378292 1.823577 2.148837 2.456142 2.817369 10.776342 11.562419 13.733805 15.550718 16.932260 18.966957 20.09902
20: 0.038003518 0.07034151 0.6719877 1.0121984 1.2549887 1.4743065 1.7237717 1.9338057 10.801451 1.5449796 1.864382 2.139040 2.461605 2.795821 11.415959 12.353642 13.957088 15.498962 17.302964 18.714038 21.09127
21: 0.077744522 0.09564380 0.3855374 0.9833219 1.2204777 1.4727601 1.7333331 1.9180492  2.147768 1.5272967 1.857855 2.123390 2.477170 2.802334  3.145498 12.821197 14.133774 14.835820 16.681756 18.942139 21.61208
22: 0.093470523 0.06468017 0.3067471 1.0253513 1.2037521 1.4656585 1.7219363 1.9532079 11.672550 1.5453877 1.837928 2.166833 2.470184 2.811218 12.045236 13.104099 14.138436 15.400001 16.913168 19.575302 20.88519
23: 0.021214252 0.07295329 0.9930499 1.0647104 1.1594624 1.4380376 1.7125625 1.9370500 10.674164 1.5196614 1.827109 2.186188 2.469582 2.805300 11.042786 12.903826 13.923212 15.264812 16.845699 19.326883 20.45520
24: 0.065167377 0.06661973 0.6518186 1.0964089 1.2360211 1.4513818 1.6950946 2.4167050 10.050300 1.5144453 1.847838 2.169072 2.516446 2.728814 10.368440 12.212171 14.547012 15.552643 17.672339 11.598836 20.09612
25: 0.012555510 0.08254352 0.2525477 1.0463284 1.2822703 1.3992648 1.6417545 2.3265488  9.753023 1.5260230 1.847995 2.173302 2.514318 2.639346 10.120784 11.484626 14.034863 15.933091 17.909938 11.043275 19.32688
26: 0.026722067 0.06290084 0.1729037 1.0906553 1.3440387 1.4654572 1.5756747 2.2005311  9.516020 1.5670990 1.845589 2.197452 1.963393 2.588179  9.934296 11.406970 13.626378 16.819350 10.054008 10.987671 19.08433
27: 0.038611409 0.07392726 0.5042121 1.0805179 1.2861307 1.4859872 1.5127633 2.0707477  9.030251 1.6053427 1.872215 2.176124 1.914072 2.514318  9.414543 11.072484 13.494505 16.679990 10.241961 11.134741 18.35876
28: 0.001339033 0.08831553 0.9278707 1.1101719 1.3200915 7.7459901 1.5065847 1.9176914  8.013881 1.6090285 1.916712 8.039861 1.927168 2.427702  8.523821 11.106875 13.679308 15.797534 11.062505 11.788157 18.19315
29: 0.038238796 0.05421235 0.6188229 1.0492044 1.2842348 7.0967037 1.4859872 1.8388064  7.338829 1.6219629 1.933472 7.413011 1.850081 2.343065  7.885349 10.944644 13.717611 15.007565 10.982915 12.075120 17.90994
30: 0.086969085 0.09376607 0.9773622 1.0223849 1.5537110 6.1679003 1.4683316 1.8258308  6.382356 1.6368935 1.867658 6.475881 1.784973 2.269184  6.915137 11.312204  8.790596 14.200122 10.990853 12.852728 17.72380
31: 0.034034900 0.06695365 0.7452029 1.0255088 1.4490304 5.3797481 1.3996293 1.7723146  5.608277 1.6382850 1.791727 5.742658 1.755652 2.176124  6.152251 11.161030  8.648518 13.717611 10.912052 12.870804 17.44615
32: 0.048208012 0.09197202 0.3888906 0.9477643 1.3060758 4.9892956 1.3612189 1.7108949  5.261416 1.3000778 1.708626 5.379926 1.745882 2.104219  5.781452  7.020662  8.320750 13.278031 10.445291 12.285267 17.34016
33: 0.059956583 0.06733417 0.4599000 0.8542938 1.2606947 4.5175904 1.2842348 1.6348151  4.840875 1.2427752 1.638285 4.911428 1.698286 2.041979  5.295826  7.113858  8.041328 12.457704 10.362724 11.887864 16.67999
34: 0.049354131 0.06668875 0.1908010 0.8330796 1.1656155 4.2769529 1.2344648 1.5790701  4.681772 1.1871565 1.542641 4.704216 1.649807 1.988445  5.099809  6.262255  7.779903 11.833018 10.064591 11.204532 15.79753
35: 0.018621760 0.07381756 0.0624237 0.7679122 1.0169492 4.1951475 1.1627030 1.4468901  4.569268 1.1757326 1.477961 4.645268 1.629071 1.933472  5.048963  5.654238  7.461762 11.161030  9.288066 10.710628 15.00757
36: 0.082737332 0.09460992 0.7297878 0.7553567 0.9838624 3.4703525 1.0635124 1.3852476  3.883807 1.1050617 1.405008 3.914447 1.557844 1.867658  4.360251  6.048741  7.103890 10.775493  9.310430 10.906226 14.20012
37: 0.066846674 0.09321697 0.1480250 0.7286346 0.8923247 3.2957036 1.0255088 1.2871155  3.725645 1.0685311 1.338388 3.794958 1.513872 1.791727  4.250469  5.957016  6.506880 10.468746  8.719620 10.140707 13.71761
38: 0.079423986 0.06949948 0.4739701 0.6900232 0.8896937 2.8799432 0.9477643 1.1978494  3.281329 0.9846794 1.255844 3.317534 1.408304 1.708626  3.780185  5.847350  6.658803  9.475696  8.728629  9.920491 13.27803
39: 0.010794363 0.08886603 0.6580960 0.6886842 0.7848999 2.1674742 0.8542938 1.0817742  2.562265 0.9744355 1.192944 2.681685 1.421696 1.638285  3.135023  5.566781  7.055129  8.823877  9.069183  9.817733 12.45770
40: 0.072371095 0.09803090 0.9922467 0.6504454 0.7206287 1.2350431 0.8330796 0.9783699  1.543199 0.9458830 1.119016 1.704925 1.374402 1.542641  2.157707  5.867833  7.445133  8.571329  8.996009 10.326412 11.83302
41: 0.041127443 0.07173297 0.5208139 0.5634763 0.6886842 0.7286346 0.7679122 0.8542938  1.025509 0.8827224 1.030701 1.192944 1.338388 1.477961  1.638285  5.370158  6.966343  8.398426  8.823877 10.468746 11.16103
          money       debt      misc   money10   money12   money14   money16   money18   money20    debt10   debt12   debt14   debt16   debt18    debt20    misc10    misc12    misc14    misc16    misc18   misc20

data:

library(data.table)
N <- 41
set.seed(0)
data <- data.table(money=runif(N, min=0, max=.1),
    debt=runif(N, min=.05, max=.1),
    misc =  runif(N, min=.05, max=1))
DT <- copy(data)

Efficient sum of expensive rolling window products

Yes, if you compute the reverse partial products carefully, you don't need to divide.

def window_products(seq, g):
    lst = list(seq)
    reverse_products = lst[:]
    for i in range(len(lst) - 2, -1, -1):
        if i % g != len(lst) % g:
            reverse_products[i] *= reverse_products[i + 1]
    product = 1
    for i in range(len(lst) - g + 1):
        yield reverse_products[i] * product
        if i % g == len(lst) % g:
            product = 1
        else:
            product *= lst[i + g]


print(list(window_products(range(10), 1)))
print(list(window_products(range(10), 2)))
print(list(window_products(range(10), 3)))

Algorithm for calculating the sum-of-squares distance of a rolling window from a given line function

If you expand the term (Yx - (a*x + b))^2 the terms break into three parts:

Terms of only a,x and b. These produce some constant when summed over n and can be ignored.
Terms of only Yx and b. These can be handled in the style of a boxcar integrator as @Xion described.
One term of -2*Yx*a*x. The -2*a is a constant so ignore that part. Consider the partial sum S = Y1*1 + Y2*2 + Y3*3 ... Yn*n. Given Y1 and a running sum R = Y1 + Y2 + ... + Yn you can find S - R which eliminates Y1*1 and reduces each of the other terms, leaving you with Y2*1 + Y3*2 + ... + Yn*(n-1). Now update the running sum R as for (2) by subtracting off Y1 and adding Y(n+1). Add the new Yn*n term to S.

Now just add up all those partial terms.