以下是我的2个数据帧:df1
eid start_dt end_dt flag
1 2020-12-01 2020-12-07 0
1 2020-12-08 2020-12-15 0
1 2020-12-16 2020-12-23 1
2 2020-12-01 2020-12-07 0
df2
eid event_dt col1 col2
1 2020-12-01 . .
1 2020-12-09 . .
1 2020-12-17 . .
2 2020-12-02 . .
输出df。
- If in df1 and df2, the eids match AND event_dt is between start_dt,end_dt
-- add a new column
-- update the flag
输出数据帧df看起来像这个
eid event_dt col1 col2 flag
1 2020-12-01 . . 0
1 2020-12-09 . . 0
1 2020-12-17 . . 1
2 2020-12-02 . . 0
我该怎么做?
尝试merge
和query
:
df2['flag'] = (df1.assign(idx=df1.index)
.merge(df2, on='eid', how='left')
.query('start_dt <= event_dt <= end_dt')
.set_index('idx')
['flag']
)
输出:
eid event_dt col1 col2 flag
0 1 2020-12-01 . . 0
1 1 2020-12-09 . . 0
2 1 2020-12-17 . . 1
3 2 2020-12-02 . . 0
更新:对于较大的数据集,上述方法可能会产生MemoryError
。改为使用pd.merge_asof
:
df2['flag'] = (pd.merge_asof(df2.sort_values('event_dt'),
df1.assign(idx=df1.index).sort_values('end_dt'),
by='eid', left_on='event_dt',
right_on='start_dt')
.query('event_dt<=end_dt')
.set_index('idx')
['flag']
)