Combine two dataframes with two common columns - python

Question

I have two dataframes, df1

        JobId   WorkOrderNo TradeName       TradeMasterId   TechnicianId    Rating
    0   10112   samsung10112    Plumbing        1074              NaN          NaN
    1   10112   samsung10112    Plumbing        1074              NaN          NaN
    2   10112   samsung10112    Plumbing        1074              1332.0    2.962963
   295  10161   samsung10161    Carpenter       1090                NaN       NaN
   296  10161   samsung10161    Carpenter       1090              1337.0    3.724138
   297  10161   samsung10161    Carpenter       1090              1296.0    3.526316
   298  10161   samsung10161    Carpenter       1090              1296.0    3.526316

df2,

    TechnicianId    UserName    TradeMasterId   LateNight   Weekends    WeekDays
0   1296            ramzee rr   1090                True    False        True
1   1325            Arun Guna   1089                True    True         True
2   1326          Rajesh Thiru  1074                False   False        True
4   1336              RR RR     1068                False   False        True

Now i want to merge this two dataframes using two common columns in this two dataframes. I want the final dataset to look like,

            JobId   WorkOrderNo TradeName       TradeMasterId   TechnicianId         Rating        LateNight   Weekends   Weekdays

        0   10112   samsung10112    Plumbing        1074              NaN          NaN            False  False       True
        1   10112   samsung10112    Plumbing        1074              NaN          NaN            False  False       True 
        2   10112   samsung10112    Plumbing        1074              1332.0    2.962963          False  False       True
       295  10161   samsung10161    Carpenter       1090                NaN       NaN             True   False       True
       296  10161   samsung10161    Carpenter       1090              1337.0    3.724138          True   
False        True         
       297  10161   samsung10161    Carpenter       1090              1296.0    3.526316          True   
False        True
       298  10161   samsung10161    Carpenter       1090              1296.0    3.526316          True   
False        True

Can anyone help me out?

`df1.merge(df2)` if the only two columns matching in both dataframes are the same, then you don't need to use the `on` parameter. If not, then and you are joining on columns named the same in both dataframes, then use df1.merge(df2, on=['col1','col2']). Else, you can use `right_on` and `left_on` parameters in merge. — Scott Boston, May 19 '20 at 15:37
But both the dataframes has different number of rows. Will that work? — , May 19 '20 at 15:38

score 0 · Answer 1 · answered May 19 '20 at 15:38

0

Try this:

new_df = pd.merge(df1, df2, on=["TradeMasterId", "TechnicianId"])

answered May 19 '20 at 15:38

maya-ami

172
5

score -1 · Answer 2 · answered May 19 '20 at 15:38

You're probably wanting to use a left join. Depending one what your key is you can use.

mergeddf = df.set_index("TechnicianId").join(df2.set_index("TechnicianId"))

Check out the pandas.Dataframe.join documentation for more information.

Hope this helps.

Combine two dataframes with two common columns - python

2 Answers2