Python pandas read excel - create index from two columns

Question

I regularly face an issue when I have an excel file which looks like the following:

 Date     ,      Hour

 2015-12-15 ,      1

(Please note that this data are read from excel so the date is in excel whatever format)

Now I tried:

df2=pd.read_excel(name, sheetname=0,skiprows=17, parse_dates="Date")
df2["test"]=df2.apply(lambda val: val["Date"]+timedelta(hours=int(df2["Hour"])-1))

However, here I get the error:

KeyError: 'Date', ' occured at index Date'

My df2 have the following types:

Date:  datetime64
Hour:  object

In addition I tried:

df2.set_index(df2["Date"]+df2["Hour"])

but without success.

Does anybody has a clue how to avoid this error, or how to get an index column in the following format:

2015-12-15 00:00:00

JamMasterJoe · Accepted Answer · 2018-02-06 09:36:21Z

2

Your code would have worked if instead of;

df2["test"]=df2.apply(lambda val: val["Date"]+timedelta(hours=int(df2["Hour"])-1))

you should have used;

df2["test"]=df2.apply(lambda val: val["Date"]+timedelta(hours=int(df2["Hour"])-1), axis=1)

This will make sure that .apply works on an entire pandas dataframe instead of series.

answered Feb 6, 2018 at 9:36

JamMasterJoe

363 bronze badges

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

Comments

Ezer K · Accepted Answer · 2015-12-17 06:18:55Z

1

For this xlsx file:

Date        Hour
12/12/2015  1
12/13/2015  2
12/14/2015  3
12/15/2015  4
12/16/2015  5
12/17/2015  6

This worked for me:

from datetime import datetime
import pandas as pd

df2 = pd.read_excel('dates.xlsx')

index_candidate = [(str(x).split()[0]+' '+str(y)) for x,y in zip(df2['Date'],df2['Hour'])]
index_candidate = [datetime.strptime(a, '%Y-%m-%d %H') for a in index_candidate]

df2.set_index(pd.DatetimeIndex(index_candidate),inplace=True)

edited Dec 17, 2015 at 6:18

answered Dec 15, 2015 at 21:31

Ezer K

3,7615 gold badges25 silver badges50 bronze badges

4 Comments

Johannes Over a year ago

Hi, ufnfortunately I got the error that datetime.time has no attribute mktime. What does mktime do? thanks

Ezer K Over a year ago

oops, sorry for that, time and datetime are not the same, I hink this import should fix it: from time import mktime from datetime import datetime, I got it from here

Johannes Over a year ago

hm.. thanks I figured that out myself but then I got an error: "tuple or struct_time argument required...

Ezer K Over a year ago

Check my new answer, let me know if it works for you

Collectives™ on Stack Overflow

Python pandas read excel - create index from two columns

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related