I have two dataframes and I want to merge them but put th the value of one of the dataframes in the other. How can I do it?
Dataframe 1:
| Date | dif |
|---|---|
| 2023-01-01 | -5.0 |
| 2023-01-02 | -4.0 |
| 2023-01-03 | 0.0 |
| 2023-01-04 | 1.0 |
Dataframe 2:
| Porcentaje | Inc |
|---|---|
| -3.0 | 0.0 |
| 1.0 | 2.0 |
Result:
| Date | dif | Inc |
|---|---|---|
| 2023-01-01 | -5.0 | 0.0 |
| 2023-01-02 | -4.0 | 0.0 |
| 2023-01-03 | 0.0 | 2.0 |
| 2023-01-04 | 1.0 | 2.0 |
The result must be a merge between the two dfs by the interval of the dif an de Porcentaje. For example, if dif < -3.0 then Inc would be 0.0.
The dif never is going to be greater than the max value of porcentaje in this example = 1.0
CodePudding user response：
This is a merge_asof:
out = pd.merge_asof(df1.sort_values(by='dif'),
df2.sort_values('Porcentaje'),
left_on='dif', right_on='Porcentaje',
direction='forward')
Output:
Date dif Porcentaje Inc
0 2023-01-01 -5.0 -3.0 0.0
1 2023-01-02 -4.0 -3.0 0.0
2 2023-01-03 0.0 1.0 2.0
3 2023-01-04 1.0 1.0 2.0
You can avoid having the Porcentaje column by using rename first to match the key in df1:
out = pd.merge_asof(df1.sort_values(by='dif'),
df2.sort_values('Porcentaje')
.rename(columns={'Porcentaje': 'dif'}),
on='dif', direction='forward')
Output:
Date dif Inc
0 2023-01-01 -5.0 0.0
1 2023-01-02 -4.0 0.0
2 2023-01-03 0.0 2.0
3 2023-01-04 1.0 2.0