How to increment a month in a row based on the month in the previous row?

user_7624835 0
Viewed 188

I have a dataframe which has a value column and "month year" column. In the first row Aug 2018 is written for the month year column. Is there a possibility that the following rows which hava a value in the value column are automatically filled with the next month respectively? So that row two is filled with Sep 2018 and row three with Oct 2018 and so on?

Actual result: 

value  month
645    Aug 2018
589    NA
465    NA
523    NA
632    NA
984    NA

Expected results:

value  month
645    Aug 2018
589    Sep 2018
465    Okt 2018
523    Nov 2018
632    Dez 2018
984    Jan 2019
increment r date
2 Answers

In base R, you could do something like this to create a monthly sequence

df$month <- format(seq(as.Date(paste("01", df$month[1]), "%d %b %Y"),
                             length.out = nrow(df), by = "month"), "%b %Y")

df
#  value    month
#1   645 Aug 2018
#2   589 Sep 2018
#3   465 Oct 2018
#4   523 Nov 2018
#5   632 Dec 2018
#6   984 Jan 2019

Important assumption to note here is you have only one value of month which is present in the first row and you want to replace all other values of month by incrementing 1 month from the previous entry.

user_39629140

We can do this with as.yearmon from zoo. Used package version 1.8.3

library(zoo)
df$month <- head(as.yearmon(df$month[1]) + c(0, seq_len(nrow(df)))/12, -1)

df
#   value    month
#1   645 Aug 2018
#2   589 Sep 2018
#3   465 Oct 2018
#4   523 Nov 2018
#5   632 Dec 2018
#6   984 Jan 2019

data

df <- structure(list(value = c(645L, 589L, 465L, 523L, 632L, 984L), 
month = c("Aug 2018", NA, NA, NA, NA, NA)), class = "data.frame", 
 row.names = c(NA, -6L))
user_37322710
Related