4

I would like to move down my entire data by a factor of two months. For example, if my data starts on Jan 01, i want to move the data in such a way that the data corresponds to March 01. Likewise, November data would become January data for the next year. Here is my sample code 

DF <- data.frame(seq(as.Date("2001-01-01"), to= as.Date("2003-12-31"), by="day"),
                     A = runif(1095, 0,10),
                     D = runif(1095,5,15))
colnames(DF) <-  c("Date", "A", "B")

I tried DF$Date <- DF$Date + 61 but this moved the entire data.frame by a factor of two months including the dates- I just want the data to moved down.

Hydro
  • 1,057
  • 12
  • 25
  • So you would like to leave 2001-01-01... at the top of the DF, but with NA for data? – M.Viking Mar 20 '20 at 16:41
  • Yes, exactly. I would also like to truncate the data at the end of the defined period. Because my data is for the 3 years and that we are moving down the data by a factor of two months- i am OK if i do not have data for the last two months during that period. – Hydro Mar 20 '20 at 16:49
  • Somebody just closed the question by associating it with a different answer which does not answer what i was looking for. – Hydro Mar 20 '20 at 17:08
  • Hydro, I agree (since reopened), though it doesn't really matter ... I think you have what you need, and you were able to accept the answer. Thanks! – r2evans Mar 20 '20 at 18:20

1 Answers1

1

I think a simple merge with itself will work here.

First, random data that you can reproduce,

headtail <- function(x, n = 4) { print(head(x, n=n)); print(tail(x, n=n)); }

set.seed(42)
DF <- data.frame(seq(as.Date("2001-01-01"), to= as.Date("2003-12-31"), by="day"),
                     A = runif(1095, 0,10),
                     D = runif(1095,5,15))
colnames(DF) <-  c("Date", "A", "B")
headtail(DF)
#         Date        A         B
# 1 2001-01-01 9.148060 10.049361
# 2 2001-01-02 9.370754 10.324953
# 3 2001-01-03 2.861395  5.868702
# 4 2001-01-04 8.304476 14.156014
#            Date         A         B
# 1092 2003-12-28 0.3284422  6.449250
# 1093 2003-12-29 7.7729724  7.270769
# 1094 2003-12-30 5.2614178 11.023033
# 1095 2003-12-31 2.6612188 13.923079

Now the merge. I use just the Date on the first frame so that the shifted Date will attached the data from the second frame.

out <- merge(DF["Date"], transform(DF, Date = Date + 61),
             by = "Date", all = TRUE)
headtail(out)
#         Date  A  B
# 1 2001-01-01 NA NA
# 2 2001-01-02 NA NA
# 3 2001-01-03 NA NA
# 4 2001-01-04 NA NA
#            Date         A         B
# 1153 2004-02-27 0.3284422  6.449250
# 1154 2004-02-28 7.7729724  7.270769
# 1155 2004-02-29 5.2614178 11.023033
# 1156 2004-03-01 2.6612188 13.923079

That is preserving all data. If you want just up until the last date of the original frame, then just change all= to all.x=:

out <- merge(DF["Date"], transform(DF, Date = Date + 61),
             by = "Date", all.x = TRUE)
headtail(out)
#         Date  A  B
# 1 2001-01-01 NA NA
# 2 2001-01-02 NA NA
# 3 2001-01-03 NA NA
# 4 2001-01-04 NA NA
#            Date         A         B
# 1092 2003-12-28 9.7939015 14.165207
# 1093 2003-12-29 1.7047221  8.269991
# 1094 2003-12-30 0.4273437  8.041551
# 1095 2003-12-31 1.4283236  5.053276

dplyr

library(dplyr)
as_tibble(DF) %>%
  mutate(Date = Date + 61) %>%
  full_join(., select(DF, Date), by = "Date") %>%
  arrange(Date)
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • Thanks, it is very close to what i am looking for. How i can truncate the data at the end of my defined date. I am ok if i do not get the last two months data. right now, it exeeds the end date by two months. – Hydro Mar 20 '20 at 17:10
  • A simple `data.frame` subsetting should work, such as `out[ out$Date <= max(DF$Date),]`. (Or change to `all.x=`, see my edit.) – r2evans Mar 20 '20 at 17:13
  • How about with `dplyr`? thats seems very neat too. – Hydro Mar 20 '20 at 17:38
  • 1
    Use `right_join` instead of `full_join`. If you're not familiar/comfortable with the concepts of `merge` and `*_join`, I suggest you read answers at: https://stackoverflow.com/q/448023 and https://stackoverflow.com/q/1299871, as it's a very powerful and useful method once you have mastered enough of it. – r2evans Mar 20 '20 at 17:46