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

Final Edit (hopefully): Oh my god you solved it! After upgrading to Pandas 0.15.2, this solution seems to work:

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

Thank you very much @EDChum and @joris



I'm trying to pull some data from an Excelsheet via read_excel into a 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 the following data types:

OEDatum datetime64[ns] OEUhrzeit object ODatum datetime64[ns] OUhrzeit object

As you can see, Pandas pulled the dates as datetime64 values and the times are an object.

Now I need to combine 'OEDatum' with 'OEUhrzeit' and 'ODatum' with 'OUhrzeit' to timestamps. These timestamps should later be used for searching large tickdata files.

But it's simply not possible for me, to combine the dates with the times...

Among a lot of other tries, I wanted to change the time data to a string and use then "to_datetime":

trades.OEUhrzeit.apply(str) pd.to_datetime(trades.OEUhrzeit, utc=False, format='%H%M%S')

but then 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) does not match format '%H%M%S'

So I hope someone could show me a solution for this problem. Thx in advance.

EDIT: @EDChum you are right I use pandas 0.14.1, numpy 1.8.2 and Python 3.4.2 - think that means I have to update my pandas.......

-------------Problems Reply------------

One method would be to convert the time strings to datetime but only take the time portion and then call apply and call datetime.combine to produce your datetime for 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 like your time column is already a datetime.time object so just the 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)

Category:python Views:0 Time:2018-11-07

Related post

  • Select Earliest Date and Time from List of Distinct User Sessions 2009-09-22

    I have a table of user access sessions which records website visitor activity: accessid, userid, date, time, url I'm trying to retrieve all distinct sessions for userid 1234, as well as the earliest date and time for each of those distinct sessions.

  • Java: Get current Date and Time from Server not System clock 2010-05-12

    In my Java program, I need to create an instance of the current moment in time. I use Date date = new Date(); This gives me the current date and time as per the host machine's system clock. Is there any way I can get the current date and time from an

  • Parsing dates and times from string in many combinations 2010-09-16

    I need to parse dates and times from strings. The Problem is that the strings can have any possible format. But I also get the format strings. So i get: Date = "9/15/2010" Time = "16:12:45" DateFormat = "M/dd/yyyy" TimeFormat = "h:mm:ss" TimeZone = "

  • How do i fetch date and time from MySQL table row 2011-03-03

    i found an old blog script (very old) kicking around on my PC. im having troubles with fetching the date and time from my DB to display in PHP. Can someone help me. This is my MySQL DB setup. CREATE TABLE blog_posts ( id int(11) NOT NULL auto_increme

  • Obtain current date and time from remote windows computer on linux via telnet or smbclient 2011-04-06

    Is there a way to get current date and time from remote windows machine on linux? On remote windows machine ssh not enabled, there are no powershell. Can we achieve result via telnet or smbclient? --------------Solutions------------- You can use the

  • How to get date and time from server 2011-07-08

    I want to retrieve date and time from server and according to it do some thing. For this I used following code: $info = getdate(); $date = $info['mday']; $month = $info['mon']; $year = $info['year']; $hour = $info['hours']; $min = $info['minutes']; $

  • Get date and time from mongodb document _id field 2011-11-05

    Is it a normal practice to get date and time from document _id, so i don't need to add additional "date" field to my document? Сan _id change for some reason? --------------Solutions------------- This practice is fine. In fact, many of the 10gen supp

  • Convert date and time from UTC to client's local time on server with MYSQL 2012-01-01

    I have a query that produces a result like this: The data is sorted by date DESC, then by time DESC, but also the common 'markers_name' elements are chunked together. (They're only chunked together for each date). To do this I get the list of MAX(tim

  • Get Date and Time from Apple Server 2012-01-31

    I'm developing an app that can only be used during a certain time of the day. I can't get the local device time because the user can easily change the device time thereby allowing access to the application for any time of the day. Is there a way to g

  • Split Date and Time from system::DateTime 2012-03-10

    Is there any way to split the date and time from the System::DateTime? Now you get something like: 2012-03-01 16:12:555 Is there any to separate them? --------------Solutions------------- If you have a DateTime and you want to split it into date and

  • Android - Get date and time from incoming SMS 2012-03-29

    I am working on an android app, where I need to save sender, SMS body, date and time of the incoming SMS. Right now I can be able to capture the message body and sender. But I cant able to get the date and time of SMS. Even I checked out some of the

  • Can't change date and time from desktop since 8.1.2 update 2012-09-11

    I had 8.1 installed, and did the update from 8/12/2014 and now I can't change my date and time from the desktop, it gives me the following message; You are attempting to open a file type of 'System file' (.cpl) These files are used by the operating s

  • Accidentally removed Date and Time from taskbar Help 2013-06-05

    Hello Windows 8 Forum Communities, I appologize if there is already a thread with this information. I searched for one first, but got frustrated when I couldn't find one with in 10-15 minutes (maybe I'm searching wrong). Either way, the other day I a

  • error on change date and time from taskbar and properties from my computer 2014-06-17

    I have windows seven ultimate sp1 i had viruses in my computer two days ago i removed them but now when i press change date and time from taskbar it hangs a while but not open the same when i click on properties from my computer Rclick and the same w

  • Removing date and time from video? 2014-07-13

    I have an AVI file that has the date and time encoded in the file and displays it in the lower left of the video. Is there a way to strip this from the video or cover it or edit it out? In case it matters, it was made with a cheap dash camera. ------

  • Subtracting a date with time from another date with time to get total time elapsed in hours in this format: hh:mm:ss. 2012-02-06

    Trying to subtract a an earlier date and time from a later date and time to arrive at time elapsed in hh:mm:ss format. Each date and time is in a separate cell. Dates and times are: 04/14/2010 at 15:57:38 04/15/2010 at 08:11:27 What Excel formulas or

  • vba code to copy and past chartsheets and worksheets from excel 2007 to powerpoint 2007 2013-09-28

    We have multiple worksheets and multiple chart sheets in an excel file. We want to copy and paste the worksheets and chart sheets from excel to powerpoint, ensuring that the format is exactly the same(size, font etc). We alos need to ensure that whil

  • is it possible to write data to AccessDB from Excel macro? 2009-08-31

    is it possible to write data to AccessDB from Excel macro, how? I have a small MBD file where I want to import data from excel sheet? --------------Solutions------------- Sub DAOFromExcelToAccess() ' exports data from the active worksheet to a table

  • Converting time from UTC to Local and vice versa 2010-05-27

    I am trying to figure out a way to convert a FILETIME structure from UTC to Local and vice versa. I've been using the two functions: FileTimetoLocalFileTime() and LocalFileTimeToFileTime(). The problem seems that they do not work as expected. As an e

  • How can I drag and drop from Excel cells to a drag-enabled task pane programatically? 2010-10-31

    I am using Excel 2007, VS2008 Pro. I am building a VSTO Add-in that requires "drag and drop from Excel cells to a drag-enabled task pane". So far I notice that I can only drag and drop within the cells themselves. It does not allow me to drop into th

  • Copy and paste from Excel to MVC3 2011-06-20

    We need to be able to copy a few hundred rows at a time from Excel into a MVC3 web app. Problem: is there a better way to do this? giant text box to paste into, then string split in the controller ExtJs Grid drag n drop http://www.vinylfox.com/datadr

  • how can I copy and paste from Excel 2010 to a website, keeping the format. When I do, it doesnt separate the cells very well as they are in Excel 2013-06-27

    How can I copy and paste from Excel 2010 to a website, keeping the format. When I do, it doesnt separate the cells very well as they are in Excel --------------Solutions------------- The formatting that occurs when you paste the data depends on the w

  • Font is changing in Outlook message when pasting rows and columns from Excel 2013-11-06

    Currently we are using Exchange 2003 and MS Office 2003. When the user with Windows 7/Outlook 2003 copies rows and columns from excel and paste into a new email as Paste Special&gt;Picture (Enahnced Metafile) or Paste Special;Picture (Windows Met

  • convert date and time to day of week and time 2014-08-01

    I collected data for a few weeks every 15 minutes. I have a column containing the date and time of measurements and then columns with the measurements. I want to compare the data between weeks in a graph, to do that i need instead of date and time, t

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.095 (s). 11 q(s)