conditional merge with pandas

user_17561414 0
Viewed 35

I have 3 excel files. Lets say file A.xlsx is the main file where I work and in it I have 2 columns: Material, Location.

In file B.xlsx I have Material and Seasonality columns.

Same in fil C.xlsx, Material and Seasonality.

In the Location I have only two countries. ( France, Spain, blank values).

The file B only has the information on seasonality about France.

and file C only has the information on seasonality about Spain.

I would like to get the seasonality information from files B and C and put in a separate column (named "Seasonality) in file A corresponding to the Location value.

df1

Material    Location  
  2527       France         
  2528       Spain       
  2627       Spain      
  2628       NaN        
  2725       France

df2

**

Material  Seasonality
2527       Summer
2725       Autumn

**

df3

 Material  Seasonality
    2528       Winter
    2627       Spring

desired output

Material    Location         Seasonality
      2527       France      Summer
      2528       Spain       Winter
      2627       Spain       Spring
      2628       NaN         Nan
      2725       France      Autumn

The code I have tried is this. I know its totally wrong but I saw someone recommended to someone something like this

import pandas as pd

df= pd.read_excel('A.xlsx')
df1 = pd.read_excel('B.xlsx')
df3 = pd.read_excel('C.xlsx')


df4 = pd.merge(df,df1[['Material','Seasonality']], on='Material', how='left') #merge for France
df5=pd.merge(df,df3[['Material', 'Seasonality']], on = 'Material', how='left') if df['Location']== 'Spain'

print(df5)

Concatonating B and C files are not possible

p.s. I dont know if it will helps or not but I already have merged the file A and B with the seasonality information on France and dataframe has now values corresponding France and NaNs (corresponding to Spain and Blank values from from Location column). Could you please help?

pandas python
1 Answers

Let us try concat then groupby

out = pd.concat([df1,df2,df3]).groupby('Material',as_index=False).first()
Out[90]: 
   Material Location Seasonality
0      2527   France      Summer
1      2528    Spain      Winter
2      2627    Spain      Spring
3      2628     None        None
4      2725   France      Autumn
user_79645270
Related