CODEWITHSUNDEEP
python-3.xpandasnumpydataframe
JackJack
JackJack

Reputation: 143

Compare three dataframe and create a new column in one of the dataframe based on a condition

I am comparing two data frames with master_df and create a new column based on a new condition if available.

for example I have master_df and two region df as asia_df and europe_df. I want to check if company of master_df is available in any of the region data frames and create a new column as region as Europe and Asia

master_df
company product
ABC    Apple
BCA    Mango
DCA    Apple
ERT    Mango
NFT    Oranges

europe_df
account sales 
ABC      12 
BCA      13
DCA      12

asia_df
account sales 
DCA      15
ERT      34

My final output dataframe is expected to be

company product region
ABC    Apple    Europe
BCA    Mango    Europe
DCA    Apple    Europe
DCA    Apple    Asia
ERT    Mango    Asia
NFT    Oranges  Others

When I try to merge and compare, some datas are removed. I need help on how to fix this issues

final_df = europe_df.merge(master_df, left_on='company', right_on='account', how='left').drop_duplicates()

final1_df = asia_df.merge(master_df, left_on='company', right_on='account', how='left').drop_duplicates()


final['region'] = np.where(final_df['account'] == final_df['company'] ,'Europe','Others')
final['region'] = np.where(final1_df['account'] == final1_df['company'] ,'Asia','Others')

Upvotes: 1

Views: 178

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71689

First using pd.concat concat the dataframes asia_df and europe_df then use DataFrame.merge to merge them with master_df, finally use Series.fillna to fill NaN values in Region with Others:

r = pd.concat([europe_df.assign(Region='Europe'), asia_df.assign(Region='Asia')])\
      .rename(columns={'account': 'company'})[['company', 'Region']]

df = master_df.merge(r, on='company', how='left')
df['Region'] = df['Region'].fillna('Others')

Result:

print(df)
  company  product  Region
0     ABC    Apple  Europe
1     BCA    Mango  Europe
2     DCA    Apple  Europe
3     DCA    Apple    Asia
4     ERT    Mango    Asia
5     NFT  Oranges  Others

Upvotes: 1

Related Questions