Luca91
Reputation: 609
Pandas: check if date from one dataframe is between two dates from another dataframe and sobstitute values
I have 2 dataframes:
df1
date event group failure
2018-04-19 02:07:00 1 E1 0
2018-04-19 02:07:00 2 E2 1
df2:
start_time end_time group failure
2018-04-01 00:00:00+01:00 2018-04-01 23:59:59+01:00 E1 1
2018-04-27 19:00:00+01:00 2018-04-27 21:29:59+01:00 E1 1
2018-04-27 06:00:00+01:00 2018-04-27 12:59:59+01:00 E1 1
2018-04-26 19:00:00+01:00 2018-04-26 21:29:59+01:00 E1 1
2018-04-26 06:00:00+01:00 2018-04-26 12:59:59+01:00 E1 1
2018-04-25 19:00:00+01:00 2018-04-25 21:29:59+01:00 E1 1
2018-04-25 06:00:00+01:00 2018-04-25 12:59:59+01:00 E1 1
2018-04-24 19:00:00+01:00 2018-04-24 21:29:59+01:00 E1 1
2018-04-24 06:00:00+01:00 2018-04-24 12:59:59+01:00 E1 1
2018-04-23 19:00:00+01:00 2018-04-23 21:29:59+01:00 E1 1
2018-04-23 06:00:00+01:00 2018-04-23 12:59:59+01:00 E1 1
2018-04-16 00:00:00+01:00 2018-04-22 23:59:59+01:00 E1 1
2018-04-28 00:00:00+01:00 2018-04-29 23:59:59+01:00 E1 1
2018-04-07 00:00:00+01:00 2018-04-08 23:59:59+01:00 E1 1
2018-04-06 19:00:00+01:00 2018-04-06 21:29:59+01:00 E1 1
2018-04-06 06:00:00+01:00 2018-04-06 12:59:59+01:00 E1 1
2018-04-09 00:00:00+01:00 2018-04-15 23:59:59+01:00 E1 1
2018-04-05 19:00:00+01:00 2018-04-05 21:29:59+01:00 E1 1
2018-04-04 06:00:00+01:00 2018-04-04 12:59:59+01:00 E1 1
2018-04-03 06:00:00+01:00 2018-04-03 12:59:59+01:00 E1 1
2018-04-02 00:00:00+01:00 2018-04-02 23:59:59+01:00 E1 1
2018-04-04 19:00:00+01:00 2018-04-04 21:29:59+01:00 E1 1
2018-04-05 06:00:00+01:00 2018-04-05 12:59:59+01:00 E1 1
2018-04-03 19:00:00+01:00 2018-04-03 21:29:59+01:00 E1 1
2018-04-27 06:00:00+01:00 2018-04-27 12:59:59+01:00 E2 1
2018-04-02 00:00:00+01:00 2018-04-02 23:59:59+01:00 E2 1
2018-04-26 19:00:00+01:00 2018-04-26 21:29:59+01:00 E2 1
2018-04-25 06:00:00+01:00 2018-04-25 12:59:59+01:00 E2 1
2018-04-03 06:00:00+01:00 2018-04-03 12:59:59+01:00 E2 1
2018-04-26 06:00:00+01:00 2018-04-26 12:59:59+01:00 E2 1
2018-04-27 19:00:00+01:00 2018-04-27 21:29:59+01:00 E2 1
2018-04-01 00:00:00+01:00 2018-04-01 23:59:59+01:00 E2 1
2018-04-25 19:00:00+01:00 2018-04-25 21:29:59+01:00 E2 1
2018-04-03 19:00:00+01:00 2018-04-03 21:29:59+01:00 E2 1
2018-04-24 19:00:00+01:00 2018-04-24 21:29:59+01:00 E2 1
2018-04-04 06:00:00+01:00 2018-04-04 12:59:59+01:00 E2 1
2018-04-24 06:00:00+01:00 2018-04-24 12:59:59+01:00 E2 1
2018-04-23 19:00:00+01:00 2018-04-23 21:29:59+01:00 E2 1
2018-04-04 19:00:00+01:00 2018-04-04 21:29:59+01:00 E2 1
2018-04-23 06:00:00+01:00 2018-04-23 12:59:59+01:00 E2 1
2018-04-16 00:00:00+01:00 2018-04-22 23:59:59+01:00 E2 1
2018-04-05 06:00:00+01:00 2018-04-05 12:59:59+01:00 E2 1
2018-04-09 00:00:00+01:00 2018-04-15 23:59:59+01:00 E2 1
2018-04-07 00:00:00+01:00 2018-04-08 23:59:59+01:00 E2 1
2018-04-05 19:00:00+01:00 2018-04-05 21:29:59+01:00 E2 1
2018-04-06 19:00:00+01:00 2018-04-06 21:29:59+01:00 E2 1
2018-04-06 06:00:00+01:00 2018-04-06 12:59:59+01:00 E2 1
2018-04-28 00:00:00+01:00 2018-04-29 23:59:59+01:00 E2 1
I have to check if:
df1(date) is between df2(start_time) and df2(end_time)
df1(group)=df2(group)
then replace df2(failure) with df1(failure). The desired outcome looks like:
start_time end_time group failure
2018-04-01 00:00:00+01:00 2018-04-01 23:59:59+01:00 E1 1
2018-04-27 19:00:00+01:00 2018-04-27 21:29:59+01:00 E1 1
2018-04-27 06:00:00+01:00 2018-04-27 12:59:59+01:00 E1 1
2018-04-26 19:00:00+01:00 2018-04-26 21:29:59+01:00 E1 1
2018-04-26 06:00:00+01:00 2018-04-26 12:59:59+01:00 E1 1
2018-04-25 19:00:00+01:00 2018-04-25 21:29:59+01:00 E1 1
2018-04-25 06:00:00+01:00 2018-04-25 12:59:59+01:00 E1 1
2018-04-24 19:00:00+01:00 2018-04-24 21:29:59+01:00 E1 1
2018-04-24 06:00:00+01:00 2018-04-24 12:59:59+01:00 E1 1
2018-04-23 19:00:00+01:00 2018-04-23 21:29:59+01:00 E1 1
2018-04-23 06:00:00+01:00 2018-04-23 12:59:59+01:00 E1 1
2018-04-16 00:00:00+01:00 2018-04-22 23:59:59+01:00 E1 0
2018-04-28 00:00:00+01:00 2018-04-29 23:59:59+01:00 E1 1
2018-04-07 00:00:00+01:00 2018-04-08 23:59:59+01:00 E1 1
2018-04-06 19:00:00+01:00 2018-04-06 21:29:59+01:00 E1 1
2018-04-06 06:00:00+01:00 2018-04-06 12:59:59+01:00 E1 1
2018-04-09 00:00:00+01:00 2018-04-15 23:59:59+01:00 E1 1
2018-04-05 19:00:00+01:00 2018-04-05 21:29:59+01:00 E1 1
2018-04-04 06:00:00+01:00 2018-04-04 12:59:59+01:00 E1 1
2018-04-03 06:00:00+01:00 2018-04-03 12:59:59+01:00 E1 1
2018-04-02 00:00:00+01:00 2018-04-02 23:59:59+01:00 E1 1
2018-04-04 19:00:00+01:00 2018-04-04 21:29:59+01:00 E1 1
2018-04-05 06:00:00+01:00 2018-04-05 12:59:59+01:00 E1 1
2018-04-03 19:00:00+01:00 2018-04-03 21:29:59+01:00 E1 1
2018-04-27 06:00:00+01:00 2018-04-27 12:59:59+01:00 E2 1
2018-04-02 00:00:00+01:00 2018-04-02 23:59:59+01:00 E2 1
2018-04-26 19:00:00+01:00 2018-04-26 21:29:59+01:00 E2 1
2018-04-25 06:00:00+01:00 2018-04-25 12:59:59+01:00 E2 1
2018-04-03 06:00:00+01:00 2018-04-03 12:59:59+01:00 E2 1
2018-04-26 06:00:00+01:00 2018-04-26 12:59:59+01:00 E2 1
2018-04-27 19:00:00+01:00 2018-04-27 21:29:59+01:00 E2 1
2018-04-01 00:00:00+01:00 2018-04-01 23:59:59+01:00 E2 1
2018-04-25 19:00:00+01:00 2018-04-25 21:29:59+01:00 E2 1
2018-04-03 19:00:00+01:00 2018-04-03 21:29:59+01:00 E2 1
2018-04-24 19:00:00+01:00 2018-04-24 21:29:59+01:00 E2 1
2018-04-04 06:00:00+01:00 2018-04-04 12:59:59+01:00 E2 1
2018-04-24 06:00:00+01:00 2018-04-24 12:59:59+01:00 E2 1
2018-04-23 19:00:00+01:00 2018-04-23 21:29:59+01:00 E2 1
2018-04-04 19:00:00+01:00 2018-04-04 21:29:59+01:00 E2 1
2018-04-23 06:00:00+01:00 2018-04-23 12:59:59+01:00 E2 1
2018-04-16 00:00:00+01:00 2018-04-22 23:59:59+01:00 E2 1
2018-04-05 06:00:00+01:00 2018-04-05 12:59:59+01:00 E2 1
2018-04-09 00:00:00+01:00 2018-04-15 23:59:59+01:00 E2 1
2018-04-07 00:00:00+01:00 2018-04-08 23:59:59+01:00 E2 1
2018-04-05 19:00:00+01:00 2018-04-05 21:29:59+01:00 E2 1
2018-04-06 19:00:00+01:00 2018-04-06 21:29:59+01:00 E2 1
2018-04-06 06:00:00+01:00 2018-04-06 12:59:59+01:00 E2 1
2018-04-28 00:00:00+01:00 2018-04-29 23:59:59+01:00 E2 1
I have tried with if functions, but I get the error: Can only compare identically-labeled Series objects. Any suggestion? Thank you in advance!
Upvotes: 0
Views: 3377
Answers (1)
jimmy
Reputation: 496
I could compare the dates after doing the following:-
e1['date'] = e1['date'].apply( lambda x: pd.to_datetime(x).tz_localize('US/Eastern'))
e2['start_time'] = e2['start_time'].apply( lambda x:
pd.to_datetime(x).tz_localize('US/Eastern'))
e2['end_time'] = e2['end_time'].apply( lambda x: pd.to_datetime(x).tz_localize('US/Eastern'))
I merged both tables and then checked if date is between start time and end time to replace failure variable.
failure_x is of E2 while failure_y is of E1 dataframes:-
df = e2.merge(e1,on='group',how='left')
df['failure_x'] = np.where((df['start_time'] <= df['date']) & (df['date'] <= df['end_time']), df['failure_y'], df['failure_x'])
Upvotes: 3
Related Questions
- How to check if dates in one pandas dataframe fall within ranges of dates in another?
- Check if dataframe records are between dates and match value within another dataframe
- How do I check if a date in a date column is between two dates in different columns using pandas?
- Pandas: Check if one date column falls between two date columns, if true populates output
- How to determine if values in a datetime column are between any pair of datetimes from another dataframe
- Pandas - Calculate which dates another date is between
- Checking if date is between multiple date ranges
- Python Pandas how to compare date from one Dataframe with dates in another Dataframe?
- Check if date in one dataframe is between two dates in another dataframe, by group
- python assign value to pandas df if falls between range of dates in another df