I have 2 dataframes one is the lookup dataframe called 'database' and another dataframe called 'df' for which the values are to be filled conditionally with the values from the 'database' dataframe column called 'Value'
- I have tried -- df['Value'] = pd.np.where but it doesn't work
- I have tried -- np.equal.outer but it doesn't work either
I tried merge and it works but it doesn't conditionally fill in values, I want to fill in the values for every row with '1' in the 'Variab' column of df, 'col2' is just an empty column so ignore that.
Thanks in advance!
Before: df
| ID | Variab | col2 |
|---|---|---|
| A2 | 1 | |
| A2 | 1 | |
| A3 | 0 | |
| A3 | 1 | |
| A4 | 1 | |
| A3 | 0 | |
| A3 | 1 | |
| A4 | 1 | |
| A3 | 0 | |
| A3 | 1 | |
| A4 | 1 |
Before: database
| ID | Value |
|---|---|
| A2 | Ball |
| A3 | Sky |
| A4 | Bird |
| A5 | Fly |
| A6 | Pizza |
After: (the objective)
| ID | Variab | Value | col 2 |
|---|---|---|---|
| A2 | 1 | Ball | |
| A2 | 1 | Ball | |
| A3 | 0 | ||
| A3 | 1 | Sky | |
| A4 | 1 | Bird | |
| A3 | 0 | ||
| A3 | 1 | Sky | |
| A4 | 1 | Bird | |
| A3 | 0 | ||
| A3 | 1 | Sky | |
| A4 | 1 | Bird |
CodePudding user response：
Use mask before merge:
df['Value'] = df.mask(df['Variab'].eq(0)).merge(db, on='ID', how='left')['Value']
print(df)
# Output
ID Variab col2 Value
0 A2 1.0 NaN Ball
1 A2 1.0 NaN Ball
2 A3 0.0 NaN NaN
3 A3 1.0 NaN Sky
4 A4 1.0 NaN Bird
5 A3 0.0 NaN NaN
6 A3 1.0 NaN Sky
7 A4 1.0 NaN Bird
8 A3 0.0 NaN NaN
9 A3 1.0 NaN Sky
10 A4 1.0 NaN Bird