Split a data frame into overlapping dataframes

Question

I'm trying to write a function that behaves as follows, but it is proving very difficult:

DF <- data.frame(x = seq(1,10), y = rep(c('a','b','c','d','e'),2))
> DF
    x y
1   1 a
2   2 b
3   3 c
4   4 d
5   5 e
6   6 a
7   7 b
8   8 c
9   9 d
10 10 e

>OverLapSplit(DF,nsplits=2,overlap=2)
[[1]]
  x y
1 1 a
2 2 b
3 3 c
4 4 d
5 5 e
6 6 a

[[2]]
   x y
1  5 a
2  6 b
3  7 c
4  8 d
5  9 e
6 10 a

>OverLapSplit(DF,nsplits=1)
[[1]]
    x y
1   1 a
2   2 b
3   3 c
4   4 d
5   5 e
6   6 a
7   7 b
8   8 c
9   9 d
10 10 e

>OverLapSplit(DF,nsplits=2,overlap=4)
[[1]]
  x y
1 1 a
2 2 b
3 3 c
4 4 d
5 5 e
6 6 a
7 7 b

[[2]]
   x y
1  4 e
2  5 a
3  6 b
4  7 c
5  8 d
6  9 e
7 10 a

>OverLapSplit(DF,nsplits=5,overlap=1)
[[1]]
  x y
1 1 a
2 2 b
3 3 c

[[2]]
  x y
1 3 c
2 4 d
3 5 e

[[3]]
  x y
1 5 e
2 6 a
3 7 b

[[4]]
  x y
1 7 b
2 8 c
3 9 d

[[5]]
   x y
1  8 d
2  9 e
3 10 f

I haven't thought a lot about what would happen if you tried something like OverLapSplit(DF,nsplits=2,overlap=1)

Maybe the following:

[[1]]
  x y
1 1 a
2 2 b
3 3 c
4 4 d
5 5 e

[[2]]
   x y
1  5 a
2  6 b
3  7 c
4  8 d
5  9 e
6 10 a

Thanks!

Rigsdaler

So does this function exist, or you don't know how to handle edge cases? – Leffert

@Leffert the function doesn't exist. If I get a workable (however inelegant) version coded, I will post it. – Rigsdaler

@Rigsdaler is this Q apropos your earlier Q? https://mcmap.net/q/939427/-parallelize-a-rolling-window-regression-in-r/429846 – Deckhand

@Gavin Simpson: Yes, this question is based on my previous one. Basically, I'm trying to develop a way to parallelize the rollapply function. Perhaps I should just ask the question directly? – Rigsdaler

Note 100% sure this is going to help there then, you probably want to break the data into the chunk sizes you want, 1:31, 2:32 etc and spew them out to your nodes - what @Joris and I have done is split the data in equal overlapping sections and that isn't really what I though your rollapply() code was doing. – Deckhand

@ Gavin Simpson: The idea is to minimize the amount of data I'm spewing out to my chunks, and then run rollapply on each chunk. for example, rather than splitting my data 1:31... to 100:131 it might make more sense to split it 1:81 and 50:131. – Rigsdaler

@Rigsdaler - I'm with you now. How big is your data set that you'd need to do this, and importantly check it works, after the speed-up I showed by using lm.fit()? Interesting problem though. – Deckhand

@Gavin Simpson: For the dataset I currently have, the lm.fit() code you wrote works great, and there's little need for parallelization. The problem is that in the future I may be using glm, glmnet or some other algorithmic if I find that it yields better predictive results. Therefore, I'm trying to find a way to parallelize the analysis. – Rigsdaler

@Gavin Simpson: I also asked this question a while ago, which you can see is would make 1:31 2:32 splits suitable for farming out as you described. #5543887 – Rigsdaler

Think about about the vector of indices you'd use to subset each data frame - it should only be a couple of lines of code – Throat

Answer 1

Try something like :

OverlapSplit <- function(x,nsplit=1,overlap=2){
    nrows <- NROW(x)
    nperdf <- ceiling( (nrows + overlap*nsplit) / (nsplit+1) )
    start <- seq(1, nsplit*(nperdf-overlap)+1, by= nperdf-overlap )

    if( start[nsplit+1] + nperdf != nrows )
        warning("Returning an incomplete dataframe.")

    lapply(start, function(i) x[c(i:(i+nperdf-1)),])
}

with nsplit the number of splits! (nsplit=1 returns 2 dataframes). This will render an incomplete last dataframe in case the overlap splits don't really fit in the dataframe, and issues a warning.

> OverlapSplit(DF,nsplit=3,overlap=2)
[[1]]
  x y
1 1 a
2 2 b
3 3 c
4 4 d

[[2]]
  x y
3 3 c
4 4 d
5 5 e
6 6 a

[[3]]
  x y
5 5 e
6 6 a
7 7 b
8 8 c

[[4]]
    x y
7   7 b
8   8 c
9   9 d
10 10 e

And one with a warning

> OverlapSplit(DF,nsplit=1,overlap=1)
[[1]]
  x y
1 1 a
2 2 b
3 3 c
4 4 d
5 5 e
6 6 a

[[2]]
    x    y
6   6    a
7   7    b
8   8    c
9   9    d
10 10    e
NA NA <NA>

Warning message:
In OverlapSplit(DF, nsplit = 1, overlap = 1) :
  Returning an incomplete dataframe.

Coycoyle

+1 nice answer from first principles --- I'm too [lazy | stupid]* for first princples. [*delete as applicable] ;-) – Deckhand

@ Gavin Simpson: I posted my own answer with the complete workflow I have in mind. There's definitely room for improvement, but I think it will serve my needs for now. Thanks for all of the suggestions! – Rigsdaler

@Joris Meys how would you go about not including the "incomplete" overlapping dataframes (i.e., going a step further past just a warning) – Crazed

Answer 2

This uses the shingle idea from Lattice graphics and so leverages code from package lattice to generate the intervals and then uses a loop to break the original DF into the correct subsets.

I wasn't exactly sure what is meant by overlap = 1 - I presume you meant overlap by 1 sample/observation. If so, the code below does this.

OverlapSplit <- function(x, nsplits = 1, overlap = 0) {
    stopifnot(require(lattice))
    N <- seq_len(nr <- nrow(x))
    interv <- co.intervals(N, nsplits, overlap / nr)
    out <- vector(mode = "list", length = nrow(interv))
    for(i in seq_along(out)) {
        out[[i]] <- x[interv[i,1] < N & N < interv[i,2], , drop = FALSE]
    }
    out
}

Which gives:

> OverlapSplit(DF, 2, 2)
[[1]]
  x y
1 1 a
2 2 b
3 3 c
4 4 d
5 5 e
6 6 a

[[2]]
    x y
5   5 e
6   6 a
7   7 b
8   8 c
9   9 d
10 10 e

> OverlapSplit(DF)
[[1]]
    x y
1   1 a
2   2 b
3   3 c
4   4 d
5   5 e
6   6 a
7   7 b
8   8 c
9   9 d
10 10 e

> OverlapSplit(DF, 4, 1)
[[1]]
  x y
1 1 a
2 2 b
3 3 c

[[2]]
  x y
3 3 c
4 4 d
5 5 e

[[3]]
  x y
6 6 a
7 7 b
8 8 c

[[4]]
    x y
8   8 c
9   9 d
10 10 e

Deckhand

Just be careful with the definition of overlap; co.intervals() wants the fraction of overlap not the absolute number of overlapping samples, so there could be a round-off issue in some situations. If that happens and you get one fewer/more overlaps than you want – Deckhand

+1 woo-yeah! never thought of hacking lattice to do that for me. Nice one – Coycoyle

Answer 3

Just to make it clear what I'm doing here:

#Load Libraries
library(PerformanceAnalytics)
library(quantmod)

#Function to Split Data Frame
OverlapSplit <- function(x,nsplit=1,overlap=0){
    nrows <- NROW(x)
    nperdf <- ceiling( (nrows + overlap*nsplit) / (nsplit+1) )
    start <- seq(1, nsplit*(nperdf-overlap)+1, by= nperdf-overlap )

    if( start[nsplit+1] + nperdf != nrows )
        warning("Returning an incomplete dataframe.")

    lapply(start, function(i) x[c(i:(i+nperdf-1)),])
}

#Function to run regression on 30 days to predict the next day
FL <- as.formula(Next(HAM1)~HAM1+HAM2+HAM3+HAM4)
MyRegression <- function(df,FL) {
  df <- as.data.frame(df)
  model <- lm(FL,data=df[1:30,])
  predict(model,newdata=df[31,])
}

#Function to roll the regression
RollMyRegression <- function(data,ModelFUN,FL) {
  rollapply(data, width=31,FUN=ModelFUN,FL,
    by.column = FALSE, align = "right", na.pad = FALSE)
}

#Load Data
data(managers)

#Split Dataset
split.data <- OverlapSplit(managers,2,30)
sapply(split.data,dim)

#Run rolling regression on each split
output <- lapply(split.data,RollMyRegression,MyRegression,FL)
output
unlist(output)

In this manner, you can replace lapply at the end with a parallel version of lapply and increase your speed somewhat.

Of course, now there's the issue of optimizing the split/overlap, given you number of processors and the size of your dataset.

Rigsdaler