-3

I have two data frames. Both are different shapes. First Dataframe:- 

     start_date        end_date    id
01  15/03/19 15:30  31/03/19 15:30  11
02  31/03/19 15:30  15/04/19 15:30  12
03  15/04/19 15:30  30/04/19 15:30  13

Second data frame:-

   item_id  purchase_at    amount
0   100     15/03/19 15:33  149
1   200     8/04/19 15:47   4600
2   300     17/04/19 15:31  8200
3   400     20/04/19 16:00  350

I want the expected output:- 

   item_id  purchase_at    amount id 
0   100     15/03/19 15:33  149   11
1   200     8/04/19 15:47   4600  12
2   300     17/04/19 15:31  8200  13
3   400     20/04/19 16:00  350   13

How to get it expected output?

Armali
  • 18,255
  • 14
  • 57
  • 171
Shivam Gupta
  • 163
  • 2
  • 14
  • What have you tried so far ? – RMPR Feb 06 '20 at 10:54
  • @RMPR I have tried multiple ways but find an error one method like : https://stackoverflow.com/questions/46605913/how-to-check-if-two-dates-falls-between-two-columns-of-dataframe-representing-st but how to compare between two dataframe ? – Shivam Gupta Feb 06 '20 at 11:00

2 Answers2

1
## https://stackoverflow.com/questions/44053666/python-assign-value-to-pandas-df-if-falls-between-range-of-dates-in-another-df
s = pd.Series(df1['id'].values, pd.IntervalIndex.from_arrays(df1['start_date'], df1['end_date']))

# Map based on the date of df_a.
df2['id'] = df2['purchase_at'].map(s)

Output:-

   item_id  purchase_at    amount id 
0   100     15/03/19 15:33  149   11
1   200     8/04/19 15:47   4600  12
2   300     17/04/19 15:31  8200  13
3   400     20/04/19 16:00  350   13
Shivam Gupta
  • 163
  • 2
  • 14
0

Here's an approach for Pandas versions before 0.20 without IntervalIndex:

df2['purchase_at'] = pd.to_datetime(df2['purchase_at'], dayfirst=True)
df2.set_index('purchase_at', inplace=True)
df2['id'] = 0
for _, d in df1.iterrows(): df2.loc[d['start_date']:d['end_date'], 'id'] = d['id']

It uses datetime string indexing.

Armali
  • 18,255
  • 14
  • 57
  • 171