how to replace the header row when reading from Excel- Python

Question

I am reading an Excel file, and I want to extract some tables from it, and put the same header for each of them.

It is now taking the first row as the header, while the actual header is the 3rd row.

Here is what I did:

new_header = df.iloc[1]
df = df[3:] #choose the data
df.rename(columns = new_header)

But it does not change the header row. Any help is appreciated.

UPDATE: @EdChum's answer solves the header issue. However I have problem with the format of the header. The header is date in the format of Month-Year and I want to keep it that way. But when it reades it, it changes the format to "2014-01-01 00:00:00". I wrote the following peice to fix it, but it only changes the firt cell format and thus, cannot use it as the new header.

new_header = datetime.datetime.strptime("2014-01-01 00:00:00", '%Y-%m-%d %H:%M:%S').strftime('%b-%y')

This could be just a string display issue, can you confirm whether the header dtype is str or datetime, thanks — EdChum
– EdChum, Commented Nov 10, 2015 at 18:34
@EdChum, before I change the format it shows the dtype as object, after that it just prints 'Jan-14' which is the string I gave as the first argument, not the whole row. — Ana
– Ana, Commented Nov 10, 2015 at 19:06
it looks like your values are strings you should be able to convert them using pd.to_datetime, try df.columns = pd.todatetime(df.columns) — EdChum
– EdChum, Commented Nov 11, 2015 at 9:16
You'll have to post your full data and code then as I can't keep guessing what may be happening — EdChum
– EdChum, Commented Nov 11, 2015 at 15:50

EdChum · Accepted Answer · 2015-11-10 17:11:49Z

1

Assign directly

df.columns = new_header

Also you need to assign the result back:

df = df.rename(columns = new_header)

as inplace=False is the default value, most pandas ops return a copy and are not inplace.

If you did

df.rename(columns = new_header, inplace=True)

it would work

answered Nov 10, 2015 at 17:11

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

2 Comments

Ana Over a year ago

Thanks. It works, but it changed the format of values. The values are dates in the form of Jan-14 but it prints it as 2014-01-01 00:00:00. How can I fix it?

EdChum Over a year ago

Can you post data and code, difficult to comment otherwise

Collectives™ on Stack Overflow

how to replace the header row when reading from Excel- Python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related