CODEWITHSUNDEEP
rdate
Alex
Alex

Reputation: 21

R: create a new column based on multiple conditions of two columns with dates

I have a data frame that looks like this (short example):

df

date (dd-mm-yyyy)

date1        date2        Value
01-01-2016   01-01-2016   100
01-02-2016   01-01-2016   90
01-03-2016   01-01-2016   110
01-02-2016   01-02-2016   180
01-03-2016   01-02-2016   80
01-04-2016   01-02-2016   100
01-05-2016   01-02-2016   70
01-03-2016   01-03-2016   90
01-04-2016   01-03-2016   100
01-05-2016   01-03-2016   80

I want to create a new column based on the following criteria: if date1 = date2 (e.g. 01-02-2016 = 01-02-2016) I need to put in the new column "Value_new" the value from column "Value" from the row, where date1 = date2 - one month (e.g. 01-02-2016 = 01-01-2016). In case that there is no value for date1 = date2 - one month available, new value should be equal 0.

Output should be like this: 

date1        date2        Value  Value_new
01-01-2016   01-01-2016   100    0
01-02-2016   01-01-2016   90     0
01-03-2016   01-01-2016   110    0
01-02-2016   01-02-2016   180    90
01-03-2016   01-02-2016   80     0
01-04-2016   01-02-2016   100    0
01-05-2016   01-02-2016   70     0
01-03-2016   01-03-2016   90     80
01-04-2016   01-03-2016   100    0
01-05-2016   01-03-2016   80     0

I've tried to solve this by using the next code, but it doesn't work properly:

df$Value_new <- 0
df$Value_new[df$date1==df$date2] <- df$Value[(df$date1 == (df$date2 - months(1))]

I'm new in R programming and would be thankful for every suggestion.

Upvotes: 2

Views: 138

Answers (1)

DPek
DPek

Reputation: 300

You need to be more specific about which Value column observation you're grabbing from. I can't code anything because even I am unsure. If I were you I would convert everything out of dates into a numeric format to keep things simple. That way you can still preserve 1 month out by finding the difference between observations in and across columns. The difference would be equal to 10,000. However, I don't understand what you're trying to return in Value_new. I understand you're trying to subtract a value from the value in the given row where date1==date2, but that's it. There are repeats of months in the data frame so how will R know which one to grab from?

Upvotes: 1

Related Questions