<p dir="auto"><code class="notranslate">read_excel()</code> seems to give the same results for a multi-index column file if <code class="notranslate">index_col=None</code> or <code class="notranslate">index_col=0</code>.  Using the file from /pandas/test/io/data:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="&gt;&gt;&gt; df = pd.read_excel(
        &quot;testmultiindex.xlsx&quot;, header=[0, 1], index_col=None,
        sheetname='mi_column_name')
       
&gt;&gt;&gt; print(df.columns)
MultiIndex(levels=[[u'bar', u'foo'], [u'a', u'b']],
       labels=[[1, 1, 0, 0], [0, 1, 0, 1]],
       names=[u'c1', u'c2'])

&gt;&gt;&gt; print(df)"><pre class="notranslate"><span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-s1">df</span> <span class="pl-c1">=</span> <span class="pl-s1">pd</span>.<span class="pl-en">read_excel</span>(
        <span class="pl-s">"testmultiindex.xlsx"</span>, <span class="pl-s1">header</span><span class="pl-c1">=</span>[<span class="pl-c1">0</span>, <span class="pl-c1">1</span>], <span class="pl-s1">index_col</span><span class="pl-c1">=</span><span class="pl-c1">None</span>,
        <span class="pl-s1">sheetname</span><span class="pl-c1">=</span><span class="pl-s">'mi_column_name'</span>)
       
<span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-en">print</span>(<span class="pl-s1">df</span>.<span class="pl-s1">columns</span>)
<span class="pl-v">MultiIndex</span>(<span class="pl-s1">levels</span><span class="pl-c1">=</span>[[<span class="pl-s">u'bar'</span>, <span class="pl-s">u'foo'</span>], [<span class="pl-s">u'a'</span>, <span class="pl-s">u'b'</span>]],
       <span class="pl-s1">labels</span><span class="pl-c1">=</span>[[<span class="pl-c1">1</span>, <span class="pl-c1">1</span>, <span class="pl-c1">0</span>, <span class="pl-c1">0</span>], [<span class="pl-c1">0</span>, <span class="pl-c1">1</span>, <span class="pl-c1">0</span>, <span class="pl-c1">1</span>]],
       <span class="pl-s1">names</span><span class="pl-c1">=</span>[<span class="pl-s">u'c1'</span>, <span class="pl-s">u'c2'</span>])

<span class="pl-c1">&gt;&gt;</span><span class="pl-c1">&gt;</span> <span class="pl-en">print</span>(<span class="pl-s1">df</span>)</pre></div>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="c1 foo             bar       
c2   a    b          a      b
0    1  2.5 2015-01-01   True
1    2  3.5 2015-01-02  False
2    3  4.5 2015-01-03  False
3    4  5.5 2015-01-04   True"><pre class="notranslate"><code class="notranslate">c1 foo             bar       
c2   a    b          a      b
0    1  2.5 2015-01-01   True
1    2  3.5 2015-01-02  False
2    3  4.5 2015-01-03  False
3    4  5.5 2015-01-04   True
</code></pre></div>
<h4 dir="auto">Expected Output</h4>
<p dir="auto">With <code class="notranslate">index_col=None</code> I would have expected the result to look something like:</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="MultiIndex(levels=[[u'bar', u'c1', u'foo'], [u'a', u'b', u'c2']],
           labels=[[1, 2, 2, 0, 0], [2, 0, 1, 0, 1]],
           names=[u'', u''])"><pre class="notranslate"><span class="pl-v">MultiIndex</span>(<span class="pl-s1">levels</span><span class="pl-c1">=</span>[[<span class="pl-s">u'bar'</span>, <span class="pl-s">u'c1'</span>, <span class="pl-s">u'foo'</span>], [<span class="pl-s">u'a'</span>, <span class="pl-s">u'b'</span>, <span class="pl-s">u'c2'</span>]],
           <span class="pl-s1">labels</span><span class="pl-c1">=</span>[[<span class="pl-c1">1</span>, <span class="pl-c1">2</span>, <span class="pl-c1">2</span>, <span class="pl-c1">0</span>, <span class="pl-c1">0</span>], [<span class="pl-c1">2</span>, <span class="pl-c1">0</span>, <span class="pl-c1">1</span>, <span class="pl-c1">0</span>, <span class="pl-c1">1</span>]],
           <span class="pl-s1">names</span><span class="pl-c1">=</span>[<span class="pl-s">u''</span>, <span class="pl-s">u''</span>])</pre></div>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="  c1 foo             bar       
  c2   a    b          a      b
0  0   1  2.5 2015-01-01   True
1  1   2  3.5 2015-01-02  False
2  2   3  4.5 2015-01-03  False
3  3   4  5.5 2015-01-04   True"><pre class="notranslate"><code class="notranslate">  c1 foo             bar       
  c2   a    b          a      b
0  0   1  2.5 2015-01-01   True
1  1   2  3.5 2015-01-02  False
2  2   3  4.5 2015-01-03  False
3  3   4  5.5 2015-01-04   True
</code></pre></div>
<h4 dir="auto">Problem description</h4>
<p dir="auto">This behavior is due to <code class="notranslate">pandas.io.excel._pop_header_name()</code>.</p>
<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto" data-snippet-clipboard-copy-content="
def _pop_header_name(row, index_col):
    &quot;&quot;&quot; (header, new_data) for header rows in MultiIndex parsing&quot;&quot;&quot;
    none_fill = lambda x: None if x == '' else x

    if index_col is None:
        # no index col specified, trim data for inference path
        return none_fill(row[0]), row[1:]
    else:
        # pop out header name and fill w/ blank
        i = index_col if not is_list_like(index_col) else max(index_col)
        return none_fill(row[i]), row[:i] + [''] + row[i + 1:]"><pre class="notranslate"><span class="pl-k">def</span> <span class="pl-en">_pop_header_name</span>(<span class="pl-s1">row</span>, <span class="pl-s1">index_col</span>):
    <span class="pl-s">""" (header, new_data) for header rows in MultiIndex parsing"""</span>
    <span class="pl-s1">none_fill</span> <span class="pl-c1">=</span> <span class="pl-k">lambda</span> <span class="pl-s1">x</span>: <span class="pl-c1">None</span> <span class="pl-k">if</span> <span class="pl-s1">x</span> <span class="pl-c1">==</span> <span class="pl-s">''</span> <span class="pl-k">else</span> <span class="pl-s1">x</span>

    <span class="pl-k">if</span> <span class="pl-s1">index_col</span> <span class="pl-c1">is</span> <span class="pl-c1">None</span>:
        <span class="pl-c"># no index col specified, trim data for inference path</span>
        <span class="pl-k">return</span> <span class="pl-en">none_fill</span>(<span class="pl-s1">row</span>[<span class="pl-c1">0</span>]), <span class="pl-s1">row</span>[<span class="pl-c1">1</span>:]
    <span class="pl-k">else</span>:
        <span class="pl-c"># pop out header name and fill w/ blank</span>
        <span class="pl-s1">i</span> <span class="pl-c1">=</span> <span class="pl-s1">index_col</span> <span class="pl-k">if</span> <span class="pl-c1">not</span> <span class="pl-en">is_list_like</span>(<span class="pl-s1">index_col</span>) <span class="pl-k">else</span> <span class="pl-en">max</span>(<span class="pl-s1">index_col</span>)
        <span class="pl-k">return</span> <span class="pl-en">none_fill</span>(<span class="pl-s1">row</span>[<span class="pl-s1">i</span>]), <span class="pl-s1">row</span>[:<span class="pl-s1">i</span>] <span class="pl-c1">+</span> [<span class="pl-s">''</span>] <span class="pl-c1">+</span> <span class="pl-s1">row</span>[<span class="pl-s1">i</span> <span class="pl-c1">+</span> <span class="pl-c1">1</span>:]</pre></div>
<p dir="auto">There are two things in the function that I do not understand</p>
<ol dir="auto">
<li><code class="notranslate">index_col is None</code> pulls out the first column and then treats it as an index.</li>
<li><code class="notranslate">index_col == 0</code>  returns something similar but adds a column of <code class="notranslate">''</code> to the front of the return row.  Why are these almost the same?</li>
</ol>
<h4 dir="auto">Output of <code class="notranslate">pd.show_versions()</code></h4>
<details>
# Paste the output here pd.show_versions() here
<h2 dir="auto">INSTALLED VERSIONS</h2>
<p dir="auto">commit: None<br>
python: 2.7.13.final.0<br>
python-bits: 32<br>
OS: Windows<br>
OS-release: 7<br>
machine: AMD64<br>
processor: Intel64 Family 6 Model 69 Stepping 1, GenuineIntel<br>
byteorder: little<br>
LC_ALL: None<br>
LANG: None<br>
LOCALE: None.None</p>
<p dir="auto">pandas: 0.19.2<br>
nose: 1.3.7<br>
pip: 9.0.1<br>
setuptools: 30.3.0<br>
Cython: 0.24<br>
numpy: 1.12.0<br>
scipy: 0.13.3<br>
xarray: None<br>
sphinx: 1.5.1<br>
patsy: 0.4.1<br>
dateutil: 2.6.0<br>
pytz: 2016.10<br>
blosc: 1.3.2<br>
bottleneck: 1.0.0<br>
tables: 3.1.0<br>
numexpr: 2.5.2<br>
matplotlib: 1.5.1<br>
openpyxl: 2.3.4<br>
xlrd: 0.9.4<br>
xlwt: 1.0.0<br>
xlsxwriter: None<br>
lxml: 3.6.0</p>
<p dir="auto">pandas_datareader: 0.3.0.post</p>
</details>
<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>