2

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
Johannes
  • 147
  • 3
  • 14

2 Answers2

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.

JamMasterJoe
  • 36
  • 3
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)
Ezer K
  • 3,637
  • 3
  • 18
  • 34
  • Hi, ufnfortunately I got the error that datetime.time has no attribute mktime. What does mktime do? thanks – Johannes Dec 16 '15 at 12:47
  • 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](http://stackoverflow.com/questions/1697815/how-do-you-convert-a-python-time-struct-time-object-into-a-datetime-object) – Ezer K Dec 16 '15 at 15:59
  • hm.. thanks I figured that out myself but then I got an error: "tuple or struct_time argument required... – Johannes Dec 17 '15 at 04:05
  • Check my new answer, let me know if it works for you – Ezer K Dec 17 '15 at 06:19