Sairam Reddy Sairam Reddy - 1 month ago 9
R Question

Add 7 days to the existing date starting from a condition in r

I have a data frame in this format:

df1:
Date Week Visitors
2016-09-25 40 100
2016-10-02 41 200
.
.
2016-12-25 52 600
2017-01-08 01 500
2017-01-15 02 1000
2017-01-22 03 2000
.
.


I would like to subtract 7 days starting from Date when Week ==1 and Day(2017-01-08) >=7 for the rest of the data.

My Data Frame would be:

df1:
Date Week Visitors
2016-09-25 40 100
2016-10-02 41 200
.
.
2016-12-25 01 600
2017-01-01 01 500
2017-01-08 02 1000
2017-01-15 03 2000
.
.


Can anyone point me to the right direction?

Answer

I think what you're asking turns out to be significantly different to what I thought, so I've edited my answer. I think what you're after is a check for each year, such that if "Week == 1" for that year is before Jan-07, then assume the first week is "missing", and act on that by moving all dates backward by 7 days. Here's how that could be achieved.

First, we'll make a column to specify just the year:

library( data.table )
setDT( df1 )
df1[ , Year := format( Date, "%Y" )
     ]

Then, we'll define a function to check for the missing first week, and move the dates if appropriate:

checkAndMove <- function( d, w ) {
    month.day <- as.integer( format( d, "%m%d" ) )
    if( month.day[ w == 1 ] > 0106 ) {
        d <- d - 7
    }
    return( d )
}

Then, we can either apply this function to a single year of your choice:

df1[ Year == 2017, Date := checkAndMove( d = Date, w = Week ) ]

Or, we can let R run the check for all years at once, applying the change only when the function deems it appropriate:

df1[ , Date := checkAndMove( d = Date, w = Week ), by = Year ]

NOTE: I haven't tested this code, since I don't have much data to work with. If you can provide a little more data, I'm happy to run a test to make sure it behaves as expected.