pandas datetime - unconverted data remains

[questio...@gmail.com]

I would like to convert 'mytime' column to a different format but I receive an error at datetime.strptime command because my dates appear to have some strange value appearing after them. Is there a way to strip this?

See below for details.

Any feedback will be greatly appreciated.

import pandas as pd

from datetime import datetime

import numpy as np

filepath="/Users/Data/mydata.xlsx"

df=pd.read_excel(filepath,'Sheet1', skiprows=2 )

df['Datetime'] = pd.to_datetime(df['mytime'], format='%Y-%m-%d %H:%M:%S', coerce=True)

df2 = df[pd.notnull(df['Datetime'])]

print df2['Datetime'].dtype

d = datetime.strptime(str(df2['Datetime']), '%Y-%m-%d %H:%M:%S')

date_string = d.strftime('%Y%m%d_%H%M')

%run /Users/timeseries_datetimequestion.py
datetime64[ns]
---------------------------------------------------------------------------
ValueError Traceback (most recent call last)
/Applications/Canopy.app/appdata/canopy-1.4.0.1938.macosx-x86_64/Canopy.app/Contents/lib/python2.7/site-packages/IPython/utils/py3compat.pyc in execfile(fname, *where)
202 else:
203 filename = fname
--> 204 __builtin__.execfile(filename, *where)

/Users/timeseries_datetimequestion.py in <module>()
10 df2 = df[pd.notnull(df['Datetime'])]
11 print df2['Datetime'].dtype
---> 12 d = datetime.strptime(str(df2['Datetime']), '%Y-%m-%d %H:%M:%S')
13
14 date_string = d.strftime('%Y%m%d_%H%M')

/Applications/Canopy.app/appdata/canopy-1.4.0.1938.macosx-x86_64/Canopy.app/Contents/lib/python2.7/_strptime.pyc in _strptime(data_string, format)
323 if not found:
324 raise ValueError("time data %r does not match format %r" %
--> 325 (data_string, format))
326 if len(data_string) != found.end():
327 raise ValueError("unconverted data remains: %s" %

ValueError: time data '0 2014-09-08 17:30:00\n1 2014-09-08 18:45:00\n2 2014-09-28 16:33:00\n3 2014-09-29 13:49:00\n4 2014-10-22 23:40:00\n5 2014-10-31 16:48:00\n6 2014-11-12 11:44:00\n7 2014-11-12 17:01:00\n8 2014-11-15 16:50:00\n9 2014-11-18 11:00:00\n10 2014-11-22 13:27:00\n11 2014-07-18 13:20:00\n12 2014-10-21 10:51:00\n13 2014-10-06 15:18:00\n14 2014-10-31 14:35:00\n15 2014-11-11 13:40:00\n16 2014-11-13 18:55:00\n17 2014-11-17 18:00:00\n18 2014-11-19 10:10:00\n19 2014-11-28 15:15:00\n20 2014-09-09 01:15:00\n21 2014-10-19 17:50:00\n22 2014-10-20 15:30:00\n23 2014-11-10 14:00:00\n24 2014-11-14 12:00:00\n25 2014-11-30 16:00:00\n26 2014-11-30 17:40:00\n27 2014-11-30 17:49:00\n28 2014-09-15 14:42:00\n29 2014-09-15 14:49:00\n ... \n25735 1972-12-06 19:45:00\n25736 1972-12-29 10:45:00\n25737 1972-12-14 14:15:00\n25738 1973-01-08 20:00:00\n25739 1972-12-15 16:50:00\n25740 1973-01-12 12:25:00\n25741 1973-01-05 15:05:00\n25742 1973-04-04 14:20:00\n25743 1973-01-17 01:05:00\n25744 1972-09-05 11:15:00\n25745 1972-12-14 10:40:00\n25746 1972-11-28 16:45:00\n25747 1972-12-01 20:00:00\n25748 1972-12-01 14:40:00\n25749 1972-12-14 15:30:00\n25750 1972-12-08 16:45:00\n25751 1972-12-24 08:05:00\n25752 1972-12-14 15:52:00\n25753 1972-12-31 01:05:00\n25754 1972-12-19 17:50:00\n25755 1973-01-01 07:30:00\n25756 1972-12-20 14:30:00\n25757 1973-01-20 17:45:00\n25758 1972-12-20 17:15:00\n25759 1973-01-21 13:10:00\n25760 1973-01-18 14:05:00\n25761 1973-02-13 19:35:00\n25762 1973-02-14 14:00:00\n25763 1973-02-02 16:10:00\n25764 1973-04-04 13:40:00\nName: Datetime, dtype: datetime64[ns]' does not match format '%Y-%m-%d %H:%M:%S'

[Joris Van den Bossche]

With

str(df2['Datetime'])

you are trying to convert a full column to a single string. To convert each value to a string, you can do df2['Datetime'].astype(str)

By the way, to convert the datetime to a string, you need strftime instead of strptime

Joris

--
You received this message because you are subscribed to the Google Groups "PyData" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pydata+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[questions anon]

Thanks for responding - however I still seem to be having issues:

If I make the suggested changes I receive the following error:

strdate=df2['Datetime'].astype(str)

d = datetime.strptime(strdate, '%Y-%m-%d %H:%M:%S')

TypeError: must be string, not Series

and when I skip the datetime.strptime step and go straight for strftime I receive the following error

strdate=df2['Datetime'].astype(str)

date_string = datetime.strftime(strdate,'%Y%m%d_%H%M')

TypeError: descriptor 'strftime' requires a 'datetime.date' object but received a 'Series'

Any feedback will be greatly appreciated.

[Joris Van den Bossche]

The error message says it all: datetime.strptime is a function that expects a string, and not a Series.

But you can apply it on each value of the Series using 'apply' method.

What you is this I think:

df2['Datetime'].apply(lambda x: datetime.strftime(x, '%Y%m%d_%H%M')

There is no need to first convert it to strings with astype(str), and then to datetimes with strptime to then convert it to strings. After to_datetime, you already have datetimes, so you can apply strftime on that.

Joris

[questions anon]

wonderful, thank you!