Converting an SQL Query to Pandas

Question

I have the following SQL left join query to be converted to Pandas join.

select df1.pc, df1.si, df1.cat,
        df2.cid As mid, df2.sd as md, df2.cid As bi, df2.sd as bd, sum(df1.sal) as Sal
        from df1
        left join df3
        on df1.cat = df3.cd 
        left join df2
        on df2.chd = df3.mid
        and df1.id = df2.id 
        left join df3
        on df1.cat = df3.cd 
        left join df2
        on df2.chd = df3.bid
        and df1.id = df2.id 
            group by df1.pc, df1.si, df1.cat, df2.cid, df2.sd, df2.cid, df2.sd ;

I tried the following code in pandas

final = pd.merge(
        pd.merge(
        pd.merge(df1, df3,how='left', left_on=['cat'], right_on=['cid']),
                    df2,how='left', left_on=['chd','id'], right_on=['mid','id']),
                        df3,how='left', left_on=['cat'], right_on=['cid']),
                            df2,how='left', left_on=['cid','id'], right_on=['bid','id'])

but some where I am getting wrong. Is this equivalent one? or any suggestions please suggest...struck here

Shili Ho · Accepted Answer · 2021-12-22 03:02:11Z

2

When you are using the merge in pandas, if you did not specify the suffix for the columns with the same names in advance, it will add _x for the left columns and _y for the right ones.

import pandas as pd

dict = {'Name':['Martha', 'Tim', 'Rob', 'Georgia'],
        'Maths':[87, 91, 97, 95],
        'Science':[83, 99, 84, 76]
       }
  
df1 = pd.DataFrame(dict)
df2 = pd.DataFrame(dict)
df3 = pd.DataFrame(dict)

df1.merge(df2,how = 'left',left_on=['Maths'], right_on=['Science']).merge(df3,how = 'left',left_on=['Maths_x','Name_x'], right_on=['Science','Name'])

And I saw the latter 2 merges are the same as the first 2s. It seems to be duplicative to me.

The screenshot of the result1

answered Dec 22, 2021 at 3:02

Shili Ho

515 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RSK Rao Over a year ago

Thank you for the reply. Able to do merge the dfs in the same way that you suggested.

Collectives™ on Stack Overflow

Converting an SQL Query to Pandas

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related