python - Pulling date and time from Excel to Pandas and combining it to a timestamp -

final edit (hopefully): oh god solved it! after upgrading pandas 0.15.2, solution seems work:

trades['oedatum'] = (trades[['oedatum', 'oeuhrzeit']].apply                          (lambda x: dt.datetime.combine                           (x['oedatum'].date(), x['oeuhrzeit']), axis=1)) 

thank @edchum , @joris

---

i'm trying pull data excelsheet via read_excel pandas dataframe:

        asset    oedatum oeuhrzeit     odatum  ouhrzeit l/s  entrykurs  \ trade                                                                     1      eurusd 2014-06-12  12:00:00 2014-06-12  12:23:09   l     1.2456    2      usdjpy 2014-11-11  10:15:35 2014-11-11  10:34:50   s   126.6300    3      eurjpy 2014-12-23  13:15:24 2014-12-23  13:25:45   l   114.4600    4      gbpjpy 2014-12-23  14:27:36 2014-12-23  14:35:56   s   156.6000 

the values i'm interested in, have following data types:

oedatum      datetime64[ns] oeuhrzeit            object odatum       datetime64[ns] ouhrzeit             object 

as can see, pandas pulled dates datetime64 values , times object.

now need combine 'oedatum' 'oeuhrzeit' , 'odatum' 'ouhrzeit' timestamps. these timestamps should later used searching large tickdata files.

but it's not possible me, combine dates times...

among lot of other tries, wanted change time data string , use "to_datetime":

trades.oeuhrzeit.apply(str) pd.to_datetime(trades.oeuhrzeit, utc=false, format='%h%m%s') 

but comes this:

    traceback (most recent call last):   file "f:\python projekte\test und funktionsenwicklung\src\tupel_und_atr_updater.py", line 251, in <module>     trades_ohne_tupel()   file "f:\python projekte\test und funktionsenwicklung\src\tupel_und_atr_updater.py", line 173, in trades_ohne_tupel     **pd.to_datetime(trades.oeuhrzeit, utc=false, format='%h%m%s')   file "c:\python34\lib\site-packages\pandas\tseries\tools.py", line 320, in to_datetime     values = _convert_listlike(arg.values, false, format)**   file "c:\python34\lib\site-packages\pandas\tseries\tools.py", line 313, in _convert_listlike     raise e   file "c:\python34\lib\site-packages\pandas\tseries\tools.py", line 287, in _convert_listlike     arg, format, coerce=coerce   file "tslib.pyx", line 1579, in pandas.tslib.array_strptime (pandas\tslib.c:25541) valueerror: time data datetime.time(12, 0) not match format '%h%m%s' 

so hope show me solution problem. thx in advance.

edit: @edchum right use pandas 0.14.1, numpy 1.8.2 , python 3.4.2 - think means have update pandas.......

one method convert time strings datetime take time portion , call apply , call datetime.combine produce datetime both columns:

in [61]:     df['oetime'] = pd.to_datetime(df['oeuhrzeit']).dt.time df['oedatum'] = df[['oedatum','oetime']].apply(lambda x: dt.datetime.combine(x['oedatum'].date(),x['oetime']), axis=1)  in [62]:     df['ouhrtime'] = pd.to_datetime(df['ouhrzeit']).dt.time df['odatum'] = df[['odatum','ouhrtime']].apply(lambda x: dt.datetime.combine(x['odatum'].date(),x['ouhrtime']), axis=1) df  out[62]:    trade   asset             oedatum oeuhrzeit              odatum  ouhrzeit  \ 0      1  eurusd 2014-06-12 12:00:00  12:00:00 2014-06-12 12:23:09  12:23:09    1      2  usdjpy 2014-11-11 10:15:35  10:15:35 2014-11-11 10:34:50  10:34:50    2      3  eurjpy 2014-12-23 13:15:24  13:15:24 2014-12-23 13:25:45  13:25:45    3      4  gbpjpy 2014-12-23 14:27:36  14:27:36 2014-12-23 14:35:56  14:35:56       l/s  entrykurs    oetime  ouhrtime   0   l     1.2456  12:00:00  12:23:09   1   s   126.6300  10:15:35  10:34:50   2   l   114.4600  13:15:24  13:25:45   3   s   156.6000  14:27:36  14:35:56   

edit

it looks time column datetime.time object following should work:

df['oedatum'] = df[['oedatum','oeuhrzeit']].apply(lambda x: dt.datetime.combine(x['oedatum'].date(),x['oeuhrzeit']), axis=1) df['odatum'] = df[['odatum','ouhrzeit']].apply(lambda x: dt.datetime.combine(x['odatum'].date(),x['ouhrzeit']), axis=1)