<p dir="auto">Let's assume there are two excel files with almost identical content:</p>
<p dir="auto">file_1.xlsx:</p>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://camo.githubusercontent.com/9d196db59437c2d975eb12d29e883f8c1724be750d68991d21bad24c6e1c6d40/687474703a2f2f692e696d6775722e636f6d2f54424962434f752e706e67"><img src="https://camo.githubusercontent.com/9d196db59437c2d975eb12d29e883f8c1724be750d68991d21bad24c6e1c6d40/687474703a2f2f692e696d6775722e636f6d2f54424962434f752e706e67" alt="Imgur" data-canonical-src="http://i.imgur.com/TBIbCOu.png" style="max-width: 100%;"></a></p>
<p dir="auto">file_2.xlsx:</p>
<p dir="auto"><a target="_blank" rel="noopener noreferrer nofollow" href="https://camo.githubusercontent.com/80d1d42736d4196da24cdd20e7ec9f911e067e38395f6316cba1a6f984d15dda/687474703a2f2f692e696d6775722e636f6d2f4d4d6968596c362e706e67"><img src="https://camo.githubusercontent.com/80d1d42736d4196da24cdd20e7ec9f911e067e38395f6316cba1a6f984d15dda/687474703a2f2f692e696d6775722e636f6d2f4d4d6968596c362e706e67" alt="Imgur" data-canonical-src="http://i.imgur.com/MMihYl6.png" style="max-width: 100%;"></a></p>
<p dir="auto">Then reading first file in pandas:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="df1 = pd.read_excel('file_1.xlsx', 
                    header=0, index_col=None, 
                    converters={'A0': str, 'B0': str})
print(df1)"><pre class="notranslate"><code class="notranslate">df1 = pd.read_excel('file_1.xlsx', 
                    header=0, index_col=None, 
                    converters={'A0': str, 'B0': str})
print(df1)
</code></pre></div>
<p dir="auto">Would produce expected result:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="     A0    B0   C0  D0 E0
0  0001  0004  0.1   1  a
1  0002  0005  0.2   2  b
2  0003  0006  0.3   3  c"><pre class="notranslate"><code class="notranslate">     A0    B0   C0  D0 E0
0  0001  0004  0.1   1  a
1  0002  0005  0.2   2  b
2  0003  0006  0.3   3  c
</code></pre></div>
<p dir="auto">However trying the same with the second file:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="df2 = pd.read_excel('file_2.xlsx', 
                    header=[0,1], index_col=None,
                    converters={('A0', 'A1'): str, 
                                ('A0', 'B1'): str},
                   )

print(df2)"><pre class="notranslate"><code class="notranslate">df2 = pd.read_excel('file_2.xlsx', 
                    header=[0,1], index_col=None,
                    converters={('A0', 'A1'): str, 
                                ('A0', 'B1'): str},
                   )

print(df2)
</code></pre></div>
<p dir="auto">Would yield somewhat different and unexpected (in comparison with previous example) result:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="A0    A0   C0    E0
A1    B1   C1 D1 E1
1   0004  0.1  1  a
2   0005  0.2  2  b
3   0006  0.3  3  c"><pre class="notranslate"><code class="notranslate">A0    A0   C0    E0
A1    B1   C1 D1 E1
1   0004  0.1  1  a
2   0005  0.2  2  b
3   0006  0.3  3  c
</code></pre></div>
<p dir="auto">Since it is not possible to use <code class="notranslate">has_index_names=False</code> as it has been depreciated since 0.16.2, there seems to be no way to have control over how pandas imports this first column (i.e. no way to convert values before original formatting is lost).</p>
<p dir="auto">And there is no way to tell pandas DO_NOT_ASSIGN first column to index as it ignores <code class="notranslate">index_col=None</code> when <code class="notranslate">header</code> is a list.</p>
<p dir="auto">So the question is what would be the sensible way to regain control over import process of first columns with multi-index header:</p>
<ul dir="auto">
<li>revive or de-depreciate (would that be appreciate?) <code class="notranslate">has_index_names</code>;</li>
<li>make <code class="notranslate">index_col</code> play a role in parsing header?</li>
</ul>
<p dir="auto">From SO: <a href="http://stackoverflow.com/questions/34020061/excel-to-pandas-dataframe-using-first-column-as-index" rel="nofollow">http://stackoverflow.com/questions/34020061/excel-to-pandas-dataframe-using-first-column-as-index</a></p>
<p dir="auto"><a class="user-mention notranslate" data-hovercard-type="user" data-hovercard-url="/users/chris-b1/hovercard" data-octo-click="hovercard-link-click" data-octo-dimensions="link_type:self" href="https://github.com/chris-b1">@chris-b1</a> another one on the multi-index excel issues .. :-)</p>
<p dir="auto">Small test case: content of excel file:</p>
<table role="table">
<thead>
<tr>
<th>A</th>
<th>A</th>
<th>B</th>
<th>B</th>
</tr>
</thead>
<tbody>
<tr>
<td>key</td>
<td>val</td>
<td>key</td>
<td>val</td>
</tr>
<tr>
<td>1</td>
<td>2</td>
<td>3</td>
<td>4</td>
</tr>
<tr>
<td>1</td>
<td>2</td>
<td>3</td>
<td>4</td>
</tr>
</tbody>
</table>
<p dir="auto">gives:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="In [2]: pd.read_excel(&quot;test_excel_index_col.xlsx&quot;, header=[0,1], index_col=None)
Out[2]:
A     A    B
key val key  val
1     2    3   4
1     2    3   4"><pre class="notranslate"><code class="notranslate">In [2]: pd.read_excel("test_excel_index_col.xlsx", header=[0,1], index_col=None)
Out[2]:
A     A    B
key val key  val
1     2    3   4
1     2    3   4
</code></pre></div>
<p dir="auto">It's not super clear in the formatting of the dataframe, but the [1, 1] is the index and [A, key] are seen as the level names of the multi-indexed columns.</p>