Merging pandas dataframes duplicates some data
Question: Thanks for taking the time to read my post.

I'm using Python pandas and merging information from a number of CSV and TSV
files. When I execute the 2nd merge data is duplicated in the resulting
dataframe. I'm assuming, I'm missing something basic with the merge call but I
haven't been able to figure it out.

Code:

    
    
    from pandas import DataFrame, read_csv
    import matplotlib.pyplot as plt
    import pandas as pd 
    import sys
    import matplotlib
    
    # Enable inline plotting
    %matplotlib inline
    
    # read data into dataframes
    ticketdata = r'/pathto.csv'
    ticketdata = r'/pathto.csv'
    userdata = r'/pathto.csv'
    shipmentdata = r'/pathto.tsv'
    
    tickets_df = pd.read_csv((ticketdata), usecols=['Id',"Requester",'Created at',"Requester email",
                                                    "Requester external id"])
    users_df = pd.read_csv((userdata), usecols=['External ID','Printers',"Organization Title"])
    shipment_df = pd.read_csv((shipmentdata), delimiter='\t', usecols=['Cust','Printer ID'])
    
    # Clean up tickets_df & shipment_df
    
    # Change "Requester external id" to "External ID" to support the merge
    tickets_df.columns = ['Ticket Id',"Requester","External ID","Requester email",'Created at']
    shipment_df.columns = ['VAR','Printers']
    # Change column order for the sake of readability
    tickets_df = tickets_df[['Ticket Id','Requester','Created at',"Requester email","External ID"]]
    
    # Replace NaN in External ID with 0 and merge data
    tickets_df.fillna(0, inplace=True)
    merge1_df = pd.merge(tickets_df, users_df, on=['External ID'], how='left')
    merge1_df = merge1_df[['Ticket Id','Created at',"Organization Title",'Requester',"Requester email","External ID",'Printers']]
    merge2_df = pd.merge(merge1_df, shipment_df, on=['Printers'], how='left')
    

merge1_df looks as expected (NaN is expected for some values):

    
    
        Ticket Id   Created at  Organization Title  Requester   Requester email     External ID     Printers
    0   1   2014-08-21 18:19    NaN     dude    dude@dude.com   0   NaN
    1   2   2014-09-09 12:04    NaN     dude1   duke1@dude.com  0   NaN
    2   3   2014-09-09 12:04    NaN     dude2   duke2@dude.com  0   NaN
    3   4   2014-09-09 12:04    NaN     dude3   duke3@dude.com  0   NaN
    

merge2_df contains thousands of dupes:

    
    
        Ticket Id   Created at  Organization Title  Requester   Requester email     External ID     Printers
    0   1   2014-08-21 18:19    NaN     dude    dude@dude.com   0   NaN
    1   1   2014-08-21 18:19    NaN     dude    dude@dude.com   0   NaN
    2   1   2014-08-21 18:19    NaN     dude    dude@dude.com   0   NaN
    3   1   2014-08-21 18:19    NaN     dude    dude@dude.com   0   NaN
    

Any idea(s) how I am messing up merge2_df?


Answer: The issue was with NaN values in the shipment_df dataframe. I added the
following to replace NaN with 0 and the duplicate entries in merge2_df were
resolved

    
    
    shipment_df.fillna(0, inplace=True)
    

