Merging two varying sized data frames on 2 columns in Python

Question

I am looking to merge 2 dataframes in python, df1 and df2, on 2 columns, Site and Building, with different row quantities as a way of attaining a "safe" value for each generator value in df1. Below is a demonstration code, though I've created the dataframes in the below example (which appears to work), the data for each table in the actual issue comes from SQL queries, which leads me to believe the merge is having issues due to data type.

import pandas as pd

df = {'Site': ['Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Belgium','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Holland','Greece','Greece','Greece','Greece','Greece','Greece'],
        'Building' : ['X1','X1','X1','X1','X1','X1','X2','X2','X2','X2','X2','X2','X3','X3','X3','X3','X3','X3','X4','X4','X4','X4','X4',   'X4','X5','X5','X5','X5','X5','X5','X1','X1',   'X1','X1',  'X1','X1','X2','X2','X2','X2','X2','X2','X3','X3','X3','X3','X3','X3','X1', 'X1','X1',  'X1','X1',  'X1'],
        'Generator' : ['DE','NDE',  'GBX1','GBX2','GBX3','GBX4','DE','NDE','GBX1','GBX2','GBX3','GBX4','DE','NDE',  'GBX1','GBX2','GBX3','GBX4','DE','NDE','GBX1','GBX2','GBX3','GBX4','DE','NDE','GBX1','GBX2','GBX3','GBX4',  'DE','NDE','GBX1','GBX2','GBX3','GBX4','DE',    'NDE','GBX1','GBX2','GBX3','GBX4',  'DE','NDE','GBX1','GBX2','GBX3','GBX4','DE','NDE','GBX1','GBX2','GBX3','GBX4']}

df1 = pd.DataFrame(df1, columns = ['Site', 'Building', 'Generator'])


df15 = {'Building' : ['X1','X2','X3','X4','X5','X1','X2','X3','X1'],
        'Site': ['Belgium','Belgium','Belgium','Belgium','Belgium','Holland','Holland','Holland','Greece'],
        'Safe' : [1,    1,  1,  1,  1,  0,  1,  1,  0]}


df2 = pd.DataFrame(df15, columns = ['Site', 'Building', 'Safe'])


df3 = df1.merge(df2, how = 'left', on = ['Site', 'Building'], indicator = True)

Desired outcome

I've also tried changing the data types of each to string as per pandas - Merging on string columns not working (bug?),

df1['Site'] = df1['Site'].astype('str')
df1['Building']=df1['Building'].astype('str')
df1['Site'] = df1['Site'].astype('str')
df1['Building']=df1['Building'].astype('str')

and also the step that mentions checking encoding as per below, but all seem to match ie. there are no visible dependencies in content;

df1['Building'] = df1['Building'].str.encode('UTF-8')
df1['Site'] = df1['Site'].str.encode('UTF-8')

Datatypes:

df2.datatypes:

    Site                           object
    Building                       object
    Safe                           object
    dtype: object


    df1.datatypes:


    Building      object
    Site          object
    Generator     object
    dtype:        object

I've tried the below code:

df3 = df1.merge(df2, left_on = ['Site', 'Building'], right_on = ['Site', 'Building'], how = 'left', indicator = 'indicator')

or:

df3 = df1.merge(df2, on = ['Site', 'Building'], how = 'left', indicator = 'indicator')

but the outcome ends up with only the data from left ie outcome 1.

Undesired outcome 1

I've tried outer join as per below, which yields outcome 2:

df3 = df1.merge(df2, on = ['Site', 'Building'], how = 'outer', indicator = 'indicator')

Undesired Outcome 2

Apologies for my relative ignorance with respect to pandas.

Please take the time to read this post on [how to provide a great pandas example](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) also [Please don't post images of code/data (or links to them)](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question) — anky, Jan 03 '20 at 17:36

Roshan Santhosh · Answer 1 · 2020-01-05T17:51:15.633

I noticed a small error in the code you shared.

df1 = pd.DataFrame(df1, columns = ['Site', 'Building', 'Generator']) should be df1 = pd.DataFrame(df, columns = ['Site', 'Building', 'Generator']). The variable that you should be passing to pd.Dataframe should be df and not df1.

After this step, simply doing a merge using the dataframes gives the desired outcome

pd.merge(df1,df2, on=['Building','Site'])

Output looks like :

       Site Building Generator  Safe
0   Belgium       X1        DE     1
1   Belgium       X1       NDE     1
2   Belgium       X1      GBX1     1
3   Belgium       X1      GBX2     1
4   Belgium       X1      GBX3     1
5   Belgium       X1      GBX4     1
6   Belgium       X2        DE     1
7   Belgium       X2       NDE     1
8   Belgium       X2      GBX1     1
9   Belgium       X2      GBX2     1
10  Belgium       X2      GBX3     1
11  Belgium       X2      GBX4     1
12  Belgium       X3        DE     1

Hi Roshan, thanks for the response. I have since ran demo code with syntax, which works. The datatypes have been added to the example above (both are objects, ie.pointing to another source) but i can confirm that the original data is from SQL queries and that the dataframes have been pivoted and index reset etc. I have tried to set all applicable columns to string for both dataframes using the code mentioned in the question, but it doesn't seem to have worked. — Chev O Keefe, Jan 05 '20 at 12:42

Merging two varying sized data frames on 2 columns in Python

1 Answers1