Parse a Pandas column to Datetime when importing table from SQL database and filtering rows by date

Posted on

Question :

Parse a Pandas column to Datetime when importing table from SQL database and filtering rows by date

I have a DataFrame with column named date. How can we convert/parse the ‘date’ column to a DateTime object?

I loaded the date column from a Postgresql database using sql.read_frame(). An example of the date column is 2013-04-04.

What I am trying to do is to select all rows in a dataframe that has their date columns within a certain period, like after 2013-04-01 and before 2013-04-04.

My attempt below gives the error 'Series' object has no attribute 'read'


import dateutil

df['date'] = dateutil.parser.parse(df['date'])


AttributeError                            Traceback (most recent call last)
<ipython-input-636-9b19aa5f989c> in <module>()
     16 # Parse 'Date' Column to Datetime
---> 17 df['date'] = dateutil.parser.parse(df['date'])

C:Python27libsite-packagesdateutilparser.pyc in parse(timestr, parserinfo, **kwargs)
    695         return parser(parserinfo).parse(timestr, **kwargs)
    696     else:
--> 697         return DEFAULTPARSER.parse(timestr, **kwargs)

C:Python27libsite-packagesdateutilparser.pyc in parse(self, timestr, default, ignoretz, tzinfos, **kwargs)
    299             default =, minute=0,
    300                                                       second=0, microsecond=0)
--> 301         res = self._parse(timestr, **kwargs)
    302         if res is None:
    303             raise ValueError, "unknown string format"

C:Python27libsite-packagesdateutilparser.pyc in _parse(self, timestr, dayfirst, yearfirst, fuzzy)
    347             yearfirst = info.yearfirst
    348         res = self._result()
--> 349         l = _timelex.split(timestr)
    350         try:

C:Python27libsite-packagesdateutilparser.pyc in split(cls, s)
    142     def split(cls, s):
--> 143         return list(cls(s))
    144     split = classmethod(split)

C:Python27libsite-packagesdateutilparser.pyc in next(self)
    136     def next(self):
--> 137         token = self.get_token()
    138         if token is None:
    139             raise StopIteration

C:Python27libsite-packagesdateutilparser.pyc in get_token(self)
     66                 nextchar = self.charstack.pop(0)
     67             else:
---> 68                 nextchar =
     69                 while nextchar == 'x00':
     70                     nextchar =

AttributeError: 'Series' object has no attribute 'read'

df['date'].apply(dateutil.parser.parse) gives me the error AttributeError: '' object has no attribute 'read'

df['date'].truncate(after='2013/04/01') gives the error TypeError: can't compare datetime.datetime to long

df['date'].dtype returns dtype('O'). Is it already a datetime object?

Asked By: Nyxynyx


Answer #1:

pandas already reads that as a datetime object! So what you want is to select rows between two dates and you can do that by masking:

df_masked = df[( > '2012-04-01') & ( < '2012-04-04')]

Because you said that you were getting an error from the string for some reason, try this:

df_masked = df[( >,4,1)) & ( <,4,4))]
Answered By: Ryan Saxe

Answer #2:

Pandas is aware of the object datetime but when you use some of the import functions it is taken as a string. So what you need to do is make sure the column is set as the datetime type not as a string. Then you can make your query.

df['date']  = pd.to_datetime(df['date'])
df_masked = df[(df['date'] >,4,1)) & (df['date'] <,4,4))]
Answered By: Keith

Answer #3:

You probably need apply, so something like:

df['date'] = df['date'].apply(dateutil.parser.parse)

Without an example of the column I can’t guarantee this will work, but something in that direction should help you to carry on.

Answered By: herrfz

Answer #4:

Don’t confuse with Pandas pd.Timestamp

A “Pandas datetime series” contains pd.Timestamp elements, not elements. The recommended solution for Pandas:

s = pd.to_datetime(s)    # convert series to Pandas
mask = s > '2018-03-10'  # calculate Boolean mask against Pandas-compatible object

The top answers have issues:

  • @RyanSaxe’s accepted answer’s first attempt doesn’t work; the second answer is inefficient.
  • As of Pandas v0.23.0, @Keith’s highly upvoted answer doesn’t work; it gives TypeError.

Any good Pandas solution must ensure:

  1. The series is a Pandas datetime series, not object dtype.
  2. The datetime series is compared to a compatible object, e.g. pd.Timestamp, or string in the correct format.

Here’s a demo with benchmarking, demonstrating that the one-off cost of conversion can be immediately offset by a single operation:

from datetime import date

L = [date(2018, 1, 10), date(2018, 5, 20), date(2018, 10, 30), date(2018, 11, 11)]
s = pd.Series(L*10**5)

a = s > date(2018, 3, 10)             # accepted solution #2, inefficient
b = pd.to_datetime(s) > '2018-03-10'  # more efficient, including datetime conversion

assert a.equals(b)                    # check solutions give same result

%timeit s > date(2018, 3, 10)                  # 40.5 ms
%timeit pd.to_datetime(s) > '2018-03-10'       # 33.7 ms

s = pd.to_datetime(s)

%timeit s > '2018-03-10'                       # 2.85 ms
Answered By: jpp

Answer #5:

You should iterate over the items and parse them independently, then construct a new list.

df['date'] = [dateutil.parser.parse(x) for x in df['date']]
Answered By: ryzhiy

Leave a Reply

Your email address will not be published. Required fields are marked *