Problems when pandas reading Excel file that has blank top row and left column
<p>I tried to read an Excel file that looks like below, 
<a href="https://i.stack.imgur.com/vD3Di.png" rel="nofollow"><img src="https://i.stack.imgur.com/vD3Di.png" alt="enter image description here"></a></p>

<p>I was using pandas like this</p>

<pre><code>xls = pd.ExcelFile(file_path)
assets = xls.parse(sheetname="Sheet1", header=1, index_col=1)
</code></pre>

<p>But I got error</p>

<blockquote>
  <p>ValueError: Expected 4 fields in line 3, saw 5</p>
</blockquote>

<p>I also tried </p>

<pre><code>assets = xls.parse(sheetname="Sheet1", header=1, index_col=1, parse_cols="B:E")
</code></pre>

<p>But I got misparsed result as follows</p>

<p><a href="https://i.stack.imgur.com/rCP9E.png" rel="nofollow"><img src="https://i.stack.imgur.com/rCP9E.png" alt="enter image description here"></a></p>

<p>Then tried </p>

<pre><code>assets = xls.parse(sheetname="Sheet1", header=1, index_col=0, parse_cols="B:E")
</code></pre>

<p>Finally works, but why index_col=0 and parse_cols="B:E"? This makes me confused becasue based on pandas <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html" rel="nofollow">documents</a>, <code>assets = xls.parse(sheetname="Sheet1", header=1, index_col=1)</code> should just be fine. Have I missed something?</p>

<p>The <code>read_excel</code> <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_excel.html" rel="nofollow">documentation</a> is not clear on a point.</p>

<ul>
<li><code>skiprows=1</code> to skip the first empty row at the top of the file or <code>header=1</code> also works to use the second row has column index.</li>
<li><code>parse_cols='B:E'</code> is a way to skip the first empty column at the left of the file</li>
<li><code>index_col=0</code> is optional and permits to define the first parsed column (B in this example) as the <code>DataFrame</code> index. The mistake is here since <code>index_col</code> is relative to columns selected though the <code>parse_cols</code> parameter.</li>
</ul>

<p>With your example, you can use the following code</p>

<pre><code>pd.read_excel('test.xls', sheetname='Sheet1', skiprows=1, 
              parse_cols='B:E', index_col=0)

#           AA  BB  CC
# 10/13/16   1  12  -1
# 10/14/16   3  12  -2
# 10/15/16   5  12  -3
# 10/16/16   3  12  -4
# 10/17/16   5  23  -5
</code></pre>

python python-3.x pandas