Excel to Pandas DataFrame using first column as index

Question

I have a very simple table in Excel that I'm trying to read into a DataFrame

Code:

from pandas import DataFrame, Series
import pandas as pd

df = pd.read_excel('params.xlsx', header=[0,1], index_col=None)

This results in the following DataFrame:

I didn't expect param1.key to become the index, especially after having set index_col=None. Is there a way to get the data into a DataFrame with a generated index instead of the data from the first column?

Update — here's what happens when you try reset_index() to resolve the issue:

Version info:

Python 3.5.0
pandas (0.17.1)
xlrd (0.9.4)

This seems like a bug to me. In any case, you can always do reset_index() to remediate. — joris
– joris, Commented Dec 1, 2015 at 12:10
@joris Thanks for reporting the bug. I tried the reset_index() before posting, thinking it would solve the issue. While the index is indeed reset, the labels are still messed up (see screenshot in updated question). — DocZerø
– DocZerø, Commented Dec 1, 2015 at 12:37
Ah, yes, indeed, it sees the ('param1', 'key') as the level names of the columns .. Do you have a large dataframe? I can try to come up with some complicated code to clean this up automatically, but if it is rather small dataframe, probably easier to just put the correct column names by hand — joris
– joris, Commented Dec 1, 2015 at 12:41
@joris It's a small one, no worries. Thanks for the offer though. — DocZerø
– DocZerø, Commented Dec 1, 2015 at 12:42

JP Zhang · Accepted Answer · 2021-12-29 08:37:50Z

5

You could use this:

# just use col order number in the `index_col` field, 0-indexed
df = pd.read_excel('yourFile.xlsx',index_col=0)

answered Dec 29, 2021 at 8:37

JP Zhang

8161 gold badge9 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Blue Moon · Accepted Answer · 2015-12-01 14:16:44Z

3

It seems like a bug. You can get a column out of your index by simply doing:

df['columnName'] = df.index

answered Dec 1, 2015 at 14:16

Blue Moon

4,80121 gold badges61 silver badges98 bronze badges

Collectives™ on Stack Overflow

Excel to Pandas DataFrame using first column as index

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related