Merging two Excel files by ID and combining columns with same name (python, pandas)
Question: I am new to stackoverflow and pandas for python. I found part of my answer in
the post [Looking to merge two Excel files by ID into one Excel file using
Python 2.7](http://stackoverflow.com/questions/17661836/looking-to-merge-two-
excel-files-by-id-into-one-excel-file-using-python-2-7)

However, I also want to merge or combine columns from the two excel files with
the same name. I thought the following post would have my answer but I guess
it's not titled correctly: [Merging Pandas DataFrames with the same column
name](http://stackoverflow.com/questions/20862068/merging-pandas-dataframes-
with-the-same-column-name)

Right now I have the code:

    
    
    import pandas as pd
    
    file1 = pd.read_excel("file1.xlsx")
    file2 = pd.read_excel("file2.xlsx")
    
    file3 = file1.merge(file2, on="ID", how="outer")
    
    file3.to_excel("merged.xlsx")
    

file1.xlsx

ID,JanSales,FebSales,test  
1,100,200,cars  
2,200,500,  
3,300,400,boats

file2.xlsx

ID,CreditScore,EMMAScore,test  
2,good,Watson,planes  
3,okay,Thompson,  
4,not-so-good,NA,

what I get is merged.xlsx

ID,JanSales,FebSales,**test_x** ,CreditScore,EMMAScore,**test_y**  
1,100,200,cars,NaN,NaN,  
2,200,500,,good,Watson,planes  
3,300,400,boats,okay,Thompson,  
4,NaN,NaN,,not-so-good,NaN,

what I want is merged.xlsx

ID,JanSales,FebSales,CreditScore,EMMAScore,**test**  
1,100,200,NaN,NaN,cars  
2,200,500,good,Watson,planes  
3,300,400,okay,Thompson,boats  
4,NaN,NaN,not-so-good,NaN,NaA

In my real data, there are 200+ columns that correspond to the "test" column
in my example. I want the program to find these columns with the same names in
both file1.xlsx and file2.xlsx and combine them in the merged file.


Answer: OK, here is a more dynamic way, after merging we assume that clashes will
occur and result in 'column_name_x' or '_y'.

So first figure out the common column names and remove 'ID' from this list

    
    
    In [51]:
    
    common_columns = list(set(list(df1.columns)) & set(list(df2.columns)))
    common_columns.remove('ID')
    common_columns
    Out[51]:
    ['test']
    

Now we can iterate over this list to create the new column and use `where` to
conditionally assign the value dependent on which value is not null.

    
    
    In [59]:
    
    for col in common_columns:
        df3[col] = df3[col+'_x'].where(df3[col+'_x'].notnull(), df3[col+'_y'])
    df3
    Out[59]:
       ID  JanSales  FebSales test_x  CreditScore EMMAScore  test_y    test
    0   1       100       200   cars          NaN       NaN     NaN    cars
    1   2       200       500    NaN         good    Watson  planes  planes
    2   3       300       400  boats         okay  Thompson     NaN   boats
    3   4       NaN       NaN    NaN  not-so-good       NaN     NaN     NaN
    
    [4 rows x 8 columns]
    

Then just to finish off drop all the extra columns:

    
    
    In [68]:
    
    clash_names = [elt+suffix for elt in common_columns for suffix in ('_x','_y') ]
    clash_names
    df3.drop(labels=clash_names, axis=1,inplace=True)
    df3
    Out[68]:
       ID  JanSales  FebSales  CreditScore EMMAScore    test
    0   1       100       200          NaN       NaN    cars
    1   2       200       500         good    Watson  planes
    2   3       300       400         okay  Thompson   boats
    3   4       NaN       NaN  not-so-good       NaN     NaN
    
    [4 rows x 6 columns]
    

The snippet above is from this :[Prepend prefix to list elements with list
comprehension](http://stackoverflow.com/questions/3330880/prepend-prefix-to-
list-elements-with-list-comprehension)

