I have two dataframe:
df1:
| ID | Goal |
|---|---|
| 1 | 5 |
| 2 | 10 |
| 3 | 1 |
df2:
| Lower | Upper |
|---|---|
| 2 | 10 |
| 1 | 7 |
| 3 | 5 |
I am trying to create a new column in df1 where:
If the values of the Goal falls within the Lower and Upper bound of df2, we select the Goal value.
If the value is lower than the Lower bound we select the Lower bound.
If value is higher than the Upper bound, we select the Upper bound.
Expected output:
| ID | Goal | New Goal |
|---|---|---|
| 1 | 5 | 5 |
| 2 | 10 | 7 |
| 3 | 1 | 3 |