CODEWITHSUNDEEP
pythonpandasdatetime
Azee.
Azee.

Reputation: 723

Comparing date column values in one dateframe with two date column in another dataframe by row in Pandas

I have a dataframe like this with two date columns and a quamtity column :

     start_date       end_date          qty
1    2018-01-01      2018-01-08         23
2    2018-01-08      2018-01-15         21           
3    2018-01-15      2018-01-22         5
4    2018-01-22      2018-01-29         12

I have a second dataframe with just column containing yearly holidays for a couple of years, like this:

         holiday
1       2018-01-01 
2       2018-01-27
3       2018-12-25
4       2018-12-26

I would like to go through the first dataframe row by row and assign boolean value to a new column holidays if a date in the second data frame falls between the date values of the first date frame. The result would look like this:

  start_date       end_date          qty         holidays
1    2018-01-01      2018-01-08         23       True
2    2018-01-08      2018-01-15         21       False  
3    2018-01-15      2018-01-22         5        False
4    2018-01-22      2018-01-29         12       True

When I try to do that with a for loop I get the following error:

ValueError: Can only compare identically-labeled Series objects

An answer would be appreciated.

Upvotes: 1

Views: 838

Answers (4)

PMende
PMende

Reputation: 5460

If you want a fully-vectorized solution, consider using the underlying numpy arrays:

import numpy as np


def holiday_arr(start, end, holidays):
    start = start.reshape((-1, 1))
    end = end.reshape((-1, 1))
    holidays = holidays.reshape((1, -1))
    result = np.any(
        (start <= holiday) & (holiday <= end),
        axis=1
    )
    return result

If you have your dataframes as above (calling them df1 and df2), you can obtain your desired result by running: 

df1["contains_holiday"] = holiday_arr(
    df1["start_date"].to_numpy(),
    df1["end_date"].to_numpy(),
    df2["holiday"].to_numpy()
)

df1 then looks like:

  start_date   end_date  qty  contains_holiday
1 2018-01-01 2018-01-08   23              True
2 2018-01-08 2018-01-15   21             False
3 2018-01-15 2018-01-22    5             False
4 2018-01-22 2018-01-29   12              True

Upvotes: 2

Andy L.
Andy L.

Reputation: 25239

Try IntervalIndex.contains with list comprehensiont and np.sum

iix = pd.IntervalIndex.from_arrays(df1.start_date, df1.end_date, closed='both')
df1['holidays'] = np.sum([iix.contains(x) for x in df2.holiday], axis=0) >= 1

Out[812]:
  start_date   end_date  qty  holidays
1 2018-01-01 2018-01-08   23      True
2 2018-01-08 2018-01-15   21     False
3 2018-01-15 2018-01-22    5     False
4 2018-01-22 2018-01-29   12      True

Note: I assume start_date, end_date, holiday columns are in datetime format. If they are not, you need to convert them before run above command as follows

df1.start_date = pd.to_datetime(df1.start_date)
df1.end_date = pd.to_datetime(df1.end_date)
df2.holiday = pd.to_datetime(df2.holiday)

Upvotes: 0

thushv89
thushv89

Reputation: 11333

I'm not sure why you would want to go row-by-row. But boolean comparisons would be way faster. 

df['holiday'] = ((df2.holiday >= df.start_date) &  (df2.holiday <= df.end_date))

Time 

>>> 1000 loops, best of 3: 1.05 ms per loop

Quoting @hchw solution (row-by-row)

def _is_holiday(row, df2):
    return ((df2['holiday'] >= row['start_date']) & (df2['holiday'] <= row['end_date'])).any()

df.apply(lambda x: _is_holiday(x, df2), axis=1)

>>> The slowest run took 4.89 times longer than the fastest. This could mean that an intermediate result is being cached.
100 loops, best of 3: 4.46 ms per loop

Upvotes: 0

hchw
hchw

Reputation: 1416

try:

def _is_holiday(row, df2):
    return ((df2['holiday'] >= row['start_date']) & (df2['holiday'] <= row['end_date'])).any()

df1.apply(lambda x: _is_holiday(x, df2), axis=1)

Upvotes: 0

Related Questions