[FIXED] Left joining multiple datasets with same column headers

Issue

not sure if this question is answered ,Please help me to solve this . I have tried my max to explain this . Please refer the images to understand my query .

I want my below query solved in Python . The query is : I need to left merge a dataframe with 3 other dataframes . But the tricky part is all the dataframes are having same column headers , and I want the same column to overlap the preceeding column in my output dataframe . But while I use left merge in python , the column headers of all the dataframes are printed along with sufix "_x" and "_y". The below are my 4 dataframes:

df1 = pd.DataFrame({"Fruits":['apple','banana','mango','strawberry'],
                    "Price":[100,50,60,70],
                    "Count":[1,2,3,4],
                    "shop_id":['A','A','A','A']})

df2 = pd.DataFrame({"Fruits":['apple','banana','mango','chicku'],
                    "Price":[10,509,609,1],
                    "Count":[8,9,10,11],
                    "shop_id":['B','B','B','B']})

df3 = pd.DataFrame({"Fruits":['apple','banana','chicku'],
                    "Price":[1000,5090,10],
                    "Count":[5,6,7],
                    "shop_id":['C','C','C']})

df4 = pd.DataFrame({"Fruits":['apple','strawberry','mango','chicku'],
                    "Price":[50,51,52,53],
                    "Count":[11,12,13,14],
                    "shop_id":['D','D','D','D']})

Now I want to left join df1 , with df2 , df3 and df4.

from functools import reduce

data_frames = [df1, df2,df3,df4]

df_merged = reduce(lambda  left,right: pd.merge(left,right,on=['Fruits'],
                                            how='left'), data_frames)

But this produces an output as below : The same columns are printed in the o/p dataset with suffix _x and _y

I want only a single Price , shop_id and count column like below:

Solution

It looks like what you want is combine_first, not merge:

from functools import reduce
data_frames = [df1, df2,df3,df4]

df_merged = reduce(lambda  left,right: right.set_index('Fruits').combine_first(left.set_index('Fruits')).reset_index(),
                   data_frames)

output:

       Fruits  Price  Count shop_id
0       apple     50     11       D
1      banana   5090      6       C
2      chicku     53     14       D
3       mango     52     13       D
4  strawberry     51     12       D

To filter the output to get only the keys from df1:

df_merged.set_index('Fruits').loc[df1['Fruits']].reset_index()

output:

       Fruits  Price  Count shop_id
0       apple     50     11       D
1      banana   5090      6       C
2       mango     52     13       D
3  strawberry     51     12       D

NB. everything would actually be easier if you set Fruits as index

Answered By - mozway