title: Merging two Excel files by ID and combining columns with same name (python, pandas)
body: <p>I am new to stackoverflow and pandas for python.  I found part of my answer in the post <a href="https://stackoverflow.com/questions/17661836/looking-to-merge-two-excel-files-by-id-into-one-excel-file-using-python-2-7">Looking to merge two Excel files by ID into one Excel file using Python 2.7</a></p>
  
  <p>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: <a href="https://stackoverflow.com/questions/20862068/merging-pandas-dataframes-with-the-same-column-name">Merging Pandas DataFrames with the same column name</a></p>
  
  <p>Right now I have the code:</p>
  
  <pre><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")
  </code></pre>
  
  <p>file1.xlsx</p>
  
  <p>ID,JanSales,FebSales,test<br>
  1,100,200,cars<br>
  2,200,500,<br>
  3,300,400,boats  </p>
  
  <p>file2.xlsx</p>
  
  <p>ID,CreditScore,EMMAScore,test<br>
  2,good,Watson,planes<br>
  3,okay,Thompson,<br>
  4,not-so-good,NA,  </p>
  
  <p>what I get is merged.xlsx</p>
  
  <p>ID,JanSales,FebSales,<strong>test_x</strong>,CreditScore,EMMAScore,<strong>test_y</strong><br>
  1,100,200,cars,NaN,NaN,<br>
  2,200,500,,good,Watson,planes<br>
  3,300,400,boats,okay,Thompson,<br>
  4,NaN,NaN,,not-so-good,NaN,  </p>
  
  <p>what I want is merged.xlsx</p>
  
  <p>ID,JanSales,FebSales,CreditScore,EMMAScore,<strong>test</strong><br>
  1,100,200,NaN,NaN,cars<br>
  2,200,500,good,Watson,planes<br>
  3,300,400,okay,Thompson,boats<br>
  4,NaN,NaN,not-so-good,NaN,NaA  </p>
  
  <p>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.</p>