title: Pandas "ValueError: columns overlap but no suffix specified" with .xlsx files but not with .txt files
body: <p>I get &quot;ValueError: columns overlap but no suffix specified&quot; when I run my code for .xlsx files but not for .txt files. The data in these two different filetypes are identical. The following works fine:</p>
  <pre><code>import os
  import pandas as pd
  
  path = r'C:\Users\Me\1Test'
  filelist = []
  for root, dirs, files in os.walk(path):
      for f in files:
          if not f.endswith('.txt'):
              continue
          filelist.append(os.path.join(root, f))
  for f in filelist:
      df = pd.read_table(f)
      col = df.iloc[ : , : -3] 
      df['Average'] = col.mean(axis = 1)
      col1 = df.iloc[ :, 1 : -3]
      df['Err'] = col1.sem(axis = 1)
      out = (df.join(df.drop(df.columns[[-3,-1]], axis=1)
                 .sub(df[df.columns[-3]], axis=0)
                 .add_suffix(' - Background')))
      out.to_excel(f.replace('txt', 'xlsx'), 'Analyzed Data')
  </code></pre>
  <p>The following gets the ValueError:</p>
  <pre><code>import os
  import pandas as pd
  
  path = r'C:\Users\Me\1Test'
  filelist = []
  for root, dirs, files in os.walk(path):
      for f in files:
          if not f.endswith('.xlsx'):
              continue
          filelist.append(os.path.join(root, f))
  for f in filelist:
      df = pd.read_excel(f)
      col = df.iloc[ : , : -3] 
      df['Average'] = col.mean(axis = 1)
      col1 = df.iloc[ :, 1 : -3]
      df['Err'] = col1.sem(axis = 1)
      out = (df.join(df.drop(df.columns[[-3,-1]], axis=1)
                 .sub(df[df.columns[-3]], axis=0)
                 .add_suffix(' - Background')))
      out.to_excel('Analyzed Data')
  </code></pre>
  <p>Each file has a different amount of columns named 'ROI' + numbers and the 3rd to last column has a random name, which is the background. I want to run through the above functions for each file. Example df:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;"></th>
  <th style="text-align: left;">ROI005</th>
  <th style="text-align: left;">ROI008</th>
  <th style="text-align: left;">53141</th>
  <th style="text-align: left;">AVG</th>
  <th style="text-align: left;">ERR</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">2.67</td>
  <td style="text-align: left;">1.2</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">4</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">2.67</td>
  <td style="text-align: left;">.67</td>
  </tr>
  <tr>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Desired output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;"></th>
  <th style="text-align: left;">ROI005</th>
  <th style="text-align: left;">ROI008</th>
  <th style="text-align: left;">53141</th>
  <th style="text-align: left;">AVG</th>
  <th style="text-align: left;">ERR</th>
  <th style="text-align: left;">ROI005 - Background</th>
  <th style="text-align: left;">ROI008 - Background</th>
  <th style="text-align: left;">Average - Background</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">5</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">3.5</td>
  <td style="text-align: left;">1.5</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">4</td>
  <td style="text-align: left;">2.5</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">4</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">0</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">2</td>
  <td style="text-align: left;">2</td>
  </tr>
  </tbody>
  </table>
  </div>