I have 2 dataframes:
df1:
RB BeginDate EndDate Valindex0
0 00 19000100 19811231 45
1 00 19820100 19841299 47
2 00 19850100 20010699 50
3 00 20010700 99999999 39
df2:
RB IssueDate gs
0 L3 19990201 8
1 00 19820101 G
2 48 19820101 G
3 50 19820101 G
4 50 19820101 G
how to merge this 2 dataframes in the condition of:
if df1['BeginDate'] <= df2['IssueDate'] <= df1['EndDate'] and df1['RB']==df2['RB']:
merge the value of df1['Valindex0'] to df2
The output should be:
df2:
RB IssueDate gs Valindex0
0 L3 19990201 8 None
1 00 19820101 G 47 # df2['RB']==df1['RB'] and df2['IssueDate'] between df1['BeginDate'] and df1['EndDate'] of this row
2 48 19820101 G None
3 50 19820101 G None
4 50 19820101 G None
I know one method to do this,but it is very slow:
conditions = []
for index, row in df1.iterrows():
conditions.append((df2['IssueDate']>= df1['BeginDate']) &
(df2['IssueDate']<= df1['BeginDate'])&
(df2['RB']==df1['RB']))
df2['Valindex0'] = np.select(conditions, df1['Valindex0'], default=None)
Any faster solution?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…