Python Pandas: I'm unable to set second row as column headers

Question

I have an .xlsx file whose format is similar to... (Note that the first row is descriptive and not meant to be the column headers. Headers are on row 2)

SHEET SUBJECT, Listings for 2010,,,,
Date, Name, Name_2, Abr, Number,         <--- I want this as column headers
12/01/2010, Company Name, Somecity, Chi, 36,
12/02/2010, Company Name, Someothercity, Nyc, 156,

So when I do this_df = pd.read_excel('filename.xlsx') I get SHEET SUBJECT and Listings for 2010 followed by a series of unnamed column headers. Expected, not what I want.

And when I do this_df.columns = this_df.iloc[1], assuming I'll get the column headers set from the row at index 1, it instead gives me the data values from the row at index 2.

What am I missing? Thanks.

Your solution was the first I tried and it worked, thanks. If you wanna move it to an answer, I'll check it. — Layne
– Layne, Commented Jul 24, 2018 at 21:16

rahlf23 · Accepted Answer · 2018-07-24 21:21:33Z

21

Simply specify the row index of the header when you read the Excel file:

pd.read_excel('filename.xlsx', header = 1)

answered Jul 24, 2018 at 21:21

rahlf23

9,0494 gold badges30 silver badges57 bronze badges

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

Comments

BENY · Accepted Answer · 2018-07-24 20:54:55Z

6

Maybe you can fix it when you read the excel

df=pd.read_excel(r'TT.xlsx',skiprows=1)
df
Out[367]: 
        Date           Name          Name_2   Abr   Number           
0 2010-12-01   Company Name        Somecity   Chi       36        NaN
1 2010-12-02   Company Name   Someothercity   Nyc      156        NaN

answered Jul 24, 2018 at 20:54

BENY

324k22 gold badges176 silver badges250 bronze badges

Collectives™ on Stack Overflow

Python Pandas: I'm unable to set second row as column headers

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