| FMS_ID | date | code1 |
|---|---|---|
| 18866 | 2022-01-01 | 3103 |
| 18866 | 2022-01-22 | 3103 |
| 18867 | 2022-10-23 | 3103 |
| 18867 | 2022-06-04 | 3103 |
| FMS_ID | Fdate | Tdate | code2 |
|---|---|---|---|
| 18866 | 2021-01-01 | 2022-01-21 | 1126 |
| 18866 | 2022-01-22 | 2022-11-01 | 8102 |
| 18867 | 2022-05-03 | 2022-08-01 | 3101 |
| 18867 | 2022-09-04 | 2022-11-01 | 1150 |
I want to take code from df2 and update code in df1 but FMS_ID should match in df2 FMS_ID and date should be between Fdate & Tdate. Many thanks.
output table: df1
| FMS_ID | date | code1 |
|---|---|---|
| 18866 | 2022-01-01 | 1126 |
| 18866 | 2022-01-22 | 8102 |
| 18867 | 2022-10-23 | 1150 |
| 18867 | 2022-06-04 | 3101 |
CodePudding user response：
Since there are two of the FMS IDs you gave as the key, a new row will be created for each combination. So FMS_ID is not unique key. I think you are aware of that. If these are ok, you can merge these two df's with merge and check the dates.
final = df.merge(df2,how='left',on=['FMS_ID'])
final=final[(final['date'].ge(final['Fdate']) & (final['date'].le(final['Tdate'])))].drop(['code1','Fdate','Tdate'],axis=1)
#or
final=final[(final['date'] >= final['Fdate']) & (final['date'] <= final['Tdate'])].drop(['code1','Fdate','Tdate'],axis=1)
Output:
FMS_ID date code2
0 18866 2022-01-01 1126
3 18866 2022-01-22 8102
5 18867 2022-10-23 1150
6 18867 2022-06-04 3101