I am struggling with creating a new column based on conditions in two other rows.
My goal is to assign a number in a new column if the SiteCode is "a" and the date range falls in between a specific time frame.
I am not even sure where to start. Any help would be great.
My dummy data looks like this:
Date SiteCode 1/1/2020 a 1/2/2020 a 1/3/2020 a 1/4/2020 a 1/5/2020 a 1/6/2020 a 1/7/2020 a 1/8/2020 a 1/9/2020 a 1/10/2020 a 1/1/2020 b 1/2/2020 b 1/3/2020 b 1/4/2020 b 1/5/2020 b 1/6/2020 b 1/7/2020 b 1/8/2020 b 1/9/2020 b 1/10/2020 b
This is what I'm trying to do:
If the date range is between 1/1/2020 - 1/3/2020 and the SiteCode is a, then newcol = 1.
If the date range is between 1/4/2020 - 1/7/2020 and the SiteCode is a, then newcol=2.
If the date range is between 1/8/2020 - 1/10/2020 and the SiteCode is a, then newcol=3.
If the date range is between 1/1/2020 - 1/6/2020 and the SiteCode is b, then newcol = 1.
If the date range is between 1/7/2020 - 1/8/2020 and the SiteCode is b, then newcol=2.
If the date range is between 1/9/2020 - 1/10/2020 and the SiteCode is b, then newcol=3.
I simplified this data to a single year, but my data spans multiple years so any recommendation would be immensely helpful.
I was thinking of writing ifelse statements, but I do not know how to string together two conditionals with date ranges.
Thank you for your help in advance. I'm learning my way around here.