BUG: read_excel with multi-indexed column ignores index_col=None

[jorisvandenbossche]

From SO: http://stackoverflow.com/questions/34020061/excel-to-pandas-dataframe-using-first-column-as-index

@chris-b1 another one on the multi-index excel issues .. :-)

Small test case: content of excel file:

A	A	B	B
key	val	key	val
1	2	3	4
1	2	3	4

gives:

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

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.

[chris-b1]

We don't support writing to this format (multi-index columns w/ no row index) because it's ambiguous on the way back in. But it seems reasonable to support reading it, I'll take a look.

[pheman]

I got the same problem

[chris-b1]

From #15180 (comment) - consider changing default of index_col to some kind of sentinel (e.g. 'infer') so that passing None means something.

[stephenrauch]

@chris-b1 @jreback I have a proposed fix here: stephenrauch@1204b31 Before I do all the docs and stuff I wanna make sure I am headed in the right direction. Thanks.

[stephenrauch]

Per review comment from @jreback here is proposed api for read_excel. It is the same as read_csv.

index_col : int or sequence or False, default None

Column (0-indexed) to use as the row labels of the DataFrame. If a
sequence is given, those columns will be combined into a MultiIndex.
If None (default), pandas will use the first column as the
index. If False, force pandas to not use the first column as the index
(row names).

Updates are here:

stephenrauch@9b37ff9

So if this proposed API looks ok, I will do the PR.

[chris-b1]

Thanks @stephenrauch, that api looks good to me, please open it as a PR. One subtlety for the docs, if not using a MultiIndex header, index_col=None won't always use the first column as an index, it takes an inference path based on the shape of the data (same as for read_csv IIRC). e.g.

In [31]: df = pd.DataFrame({'a': [1, 2, 3], 'b': [4, 5, 5]})

In [32]: df.to_excel('temp.xlsx', index=False)

In [33]: pd.read_excel('temp.xlsx')

   a  b
0  1  4
1  2  5
2  3  5

[stephenrauch]

it takes an inference path based on the shape of the data

@chris-b1 @jreback Are there any test cases (or maybe some other description) that show how index_col=None works for something other than the index in the first column? Either for the csv or excel cases? I am having trouble grokking what is meant by the shape of the data in this case.