Linked Questions

Popular Questions

Date Range Matching Function pandas

Asked by At

I have a dataframe called df1 that looks like this:

  Loc    Start      End 
  CA     2013-11-08 2014-04-14
  CO     2014-04-14 2014-04-16
  CA     2014-04-16 2014-04-18
  CO     2014-04-18 2014-04-23

And I have another dataframe called df2 that looks like this:

Date       Loc Flag
2014-04-14  CO   0
2014-04-14  CO   0
2014-04-14  CO   0
2014-04-15  CO   0
2014-04-15  CO   0
2014-04-16  CO   0
2014-04-16  CO   0
2014-04-16  VA   0
2014-04-16  CA   0

I want to build a function such that for each Start and End period in df1, the function checks whether the rows in df2 that fall in that date range match locations, i.e. do the loc's match. Where they do not match, I want the Flag to be marked with a 1. Here is the code I tried:

for i in range(len(df1)):
    for j in range(len(df2)):
        if df2['Date'][j] <= df1['End Date'][i] and \
        df2['Date'][j] >= df1['Start Date'][i]: 
            if df2['Loc'][j] != df1['Loc'][i]:
                df2['flag'][j] = 1

My code puts 1's where the locations actually match. I think it is because of the overlapping Start and End dates. Any tips on how I can go about correcting this? Thank you

Related Questions