I using python 3 and i have three dataframe:
df1
| PEOPLE | AMOUNT_custom_A | AMOUNT_custom_B |
|---|---|---|
| P1 | NaN | NaN |
| P2 | NaN | NaN |
| P3 | NaN | NaN |
df2:
| PEOPLE | AMOUNT |
|---|---|
| P1 | 1.0 |
| P2 | 1.0 |
df3
| PEOPLE | AMOUNT |
|---|---|
| P2 | 1.0 |
| P3 | 4.0 |
df_1= pd.merge(df_1, df2, on ='PEOPLE ', how ='outer') //(Step 1)
df_1= pd.merge(df_1, df3, on ='PEOPLE ', how ='outer') //(Step 2)
df_1= df_1.loc[:, ~df_merge.columns.str.contains('^Unnamed')]
Ouput Actual:
| PEOPLE | AMOUNT_custom_A | AMOUNT_custom_B | AMOUNT_X | AMOUNT_Y |
|---|---|---|---|---|
| P1 | NaN | NaN | 1.0 | NaN |
| P2 | NaN | NaN | 1.0 | 1.0 |
| P3 | NaN | NaN | NaN | 4.0 |
Question How to field data at (Step 1) to column AMOUNT_custom_A and field data at (Step 2) to column AMOUNT_custom_B?
Ouput Expected:
| PEOPLE | AMOUNT_custom_A | AMOUNT_custom_B |
|---|---|---|
| P1 | 1.0 | NaN |
| P2 | 1.0 | 1.0 |
| P3 | NaN | 4.0 |
Thank you !
CodePudding user response：
Add Series.fillna with DataFrame.pop:
df['AMOUNT_custom_A'] = df['AMOUNT_custom_A'].fillna(df.pop('AMOUNT_X'))
df['AMOUNT_custom_B'] = df['AMOUNT_custom_B'].fillna(df.pop('AMOUNT_Y'))
If alwyas missing columns AMOUNT_custom_A and AMOUNT_custom_B first select only PEOPLE column for df1 and rename columns names in merge:
df_1= pd.merge(df_1[['PEOPLE']], df2.rename(columns={'AMOUNT':'AMOUNT_custom_A'}), on ='PEOPLE ', how ='outer') //(Step 1)
df_1= pd.merge(df_1, df3.rename(columns={'AMOUNT':'AMOUNT_custom_B'}), on ='PEOPLE ', how ='outer') //(Step 2)
df_1= df_1.loc[:, ~df_merge.columns.str.contains('^Unnamed')]