How do I convert strings in a Pandas data frame to a 'date' data type?

Question

I have a Pandas data frame, one of the column contains date strings in the format YYYY-MM-DD

For e.g. '2013-10-28'

At the moment the dtype of the column is object.

How do I convert the column values to Pandas date format?

score 153 · Answer 1 · edited May 18 '21 at 18:44

153

Essentially equivalent to @waitingkuo, but I would use pd.to_datetime here (it seems a little cleaner, and offers some additional functionality e.g. dayfirst):

In [11]: df
Out[11]:
   a        time
0  1  2013-01-01
1  2  2013-01-02
2  3  2013-01-03

In [12]: pd.to_datetime(df['time'])
Out[12]:
0   2013-01-01 00:00:00
1   2013-01-02 00:00:00
2   2013-01-03 00:00:00
Name: time, dtype: datetime64[ns]

In [13]: df['time'] = pd.to_datetime(df['time'])

In [14]: df
Out[14]:
   a                time
0  1 2013-01-01 00:00:00
1  2 2013-01-02 00:00:00
2  3 2013-01-03 00:00:00

Handling ValueErrors
If you run into a situation where doing

df['time'] = pd.to_datetime(df['time'])

Throws a

ValueError: Unknown string format

That means you have invalid (non-coercible) values. If you are okay with having them converted to pd.NaT, you can add an errors='coerce' argument to to_datetime:

df['time'] = pd.to_datetime(df['time'], errors='coerce')

edited May 18 '21 at 18:44

Trenton McKinney

56,955
33
144
158

answered May 31 '13 at 09:46

Andy Hayden

359,921
101
625
535

Hi Guys, @AndyHayden can you remove the time part from the date? I don't need that part? – yoshiserry Mar 14 '14 at 01:31
In pandas' 0.13.1 the trailing 00:00:00s aren't displayed. – Andy Hayden Mar 14 '14 at 01:33
and what about in other versions, how do we remove / and or not display them? – yoshiserry Mar 14 '14 at 01:37
I don't think this can be done in a nice way, there is discussion to add date_format like float_format (which you've seen). I recommend upgrading anyway. – Andy Hayden Mar 14 '14 at 01:39
my problem is my date is in this format... 41516.43, and I get this error. I would expect it to return something like 2014-02-03 in the new column?! THE ERROR: #convert date values in the "load_date" column to dates budget_dataset['date_last_load'] = pd.to_datetime(budget_dataset['load_date']) budget_dataset -c:2: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_index,col_indexer] = value instead – yoshiserry Mar 14 '14 at 02:07
@yoshiserry if the date isn't being converted properly that sounds like a different problem. Could you post this as a separate question, tricky to diagnose here. – Andy Hayden Mar 14 '14 at 02:17
yeah its a numerical representation of a date, but it has two decimal places. Like 41516.426 is actually = 30/08/2013 10:13:26 AM, I'd like first to be able to return the (date and time), and then i'd like to split them into two columns. – yoshiserry Mar 14 '14 at 02:20
**If more than 1 datetime columns conversion**:`df[['col1', 'col2']].apply(pd.to_datetime, axis=0)` – Sumax Aug 10 '20 at 13:41
@yoshiserry, is the date `41516.43` from Excel? This might help: https://stackoverflow.com/q/38454403/13608599 – jsmart Aug 16 '20 at 07:15

score 138 · Accepted Answer · answered May 31 '13 at 08:36

138

Use astype

In [31]: df
Out[31]: 
   a        time
0  1  2013-01-01
1  2  2013-01-02
2  3  2013-01-03

In [32]: df['time'] = df['time'].astype('datetime64[ns]')

In [33]: df
Out[33]: 
   a                time
0  1 2013-01-01 00:00:00
1  2 2013-01-02 00:00:00
2  3 2013-01-03 00:00:00

answered May 31 '13 at 08:36

waitingkuo

89,478
28
112
118

1

Nice - thank you - how do I get rid of the 00:00:00 at the end of each date? – user7289 May 31 '13 at 08:39
1

The pandas timestamp have both date and time. Do you mean convert it into python date object? – waitingkuo May 31 '13 at 08:42
Yep, but I guess the python date object is not supported by Pandas. – user7289 May 31 '13 at 10:28
14

You can convert it by `df['time'] = [time.date() for time in df['time']]` – waitingkuo May 31 '13 at 10:30
4

what does the [ns] mean, can you make the text string a date and remove the time part of that date? – yoshiserry Mar 13 '14 at 23:40
1

@yoshiserry it's nanoseconds, and is the way the dates are stored under the hood once converted properly (epoch-time in nanoseconds). – Andy Hayden Mar 14 '14 at 01:35
1

Why use this over `pandas.to_datetime()` ? – AMC Feb 18 '20 at 23:57

fantabolous · Answer 3 · 2023-05-05T05:12:51.640

47

I imagine a lot of data comes into Pandas from CSV files, in which case you can simply convert the date during the initial CSV read:

dfcsv = pd.read_csv('xyz.csv', parse_dates=[0]) where the 0 refers to the column the date is in.
You could also add , index_col=0 in there if you want the date to be your index.

See https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html

You can also select column(s) to parse by name rather than position, e.g. parse_dates=['thedate']

edited May 05 '23 at 05:12

answered Mar 19 '14 at 04:15

fantabolous

21,470
7
54
51

Thanks, that was exactly what I needed. The documentation has moved, though, you can find it here: https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_csv.html – Sastibe Jan 30 '20 at 07:40

score 28 · Answer 4 · answered Nov 07 '15 at 00:22

28

Now you can do df['column'].dt.date

Note that for datetime objects, if you don't see the hour when they're all 00:00:00, that's not pandas. That's iPython notebook trying to make things look pretty.

answered Nov 07 '15 at 00:22

szeitlin

3,197
2
23
19

3

This one does not work for me, it complains: Can only use .dt accessor with datetimelike values – smishra May 23 '18 at 15:26
3

you may have to do `df[col] = pd.to_datetime(df[col])` first to convert your column to date time objects. – szeitlin May 24 '18 at 23:42
2

The issue with this answer is that it converts the column to `dtype = object` which takes up considerably more memory than a true `datetime dtype` in pandas. – elPastor Jan 10 '19 at 15:19
`.dt.date` absolutely should not be used with any sizable dataset because: (1) It uses dtype `object` which is very difficult to work with in Pandas if the dataset is large (having 200 million or more rows) (2) It fails to be written to parquet by `fastparquet`. – Asclepius Feb 14 '21 at 18:50
1

This person didn't say anything about the size of the dataset, or about parquet. – szeitlin Feb 17 '21 at 17:41

score 13 · Answer 5 · answered Dec 06 '19 at 19:50

13

If you want to get the DATE and not DATETIME format:

df["id_date"] = pd.to_datetime(df["id_date"]).dt.date

answered Dec 06 '19 at 19:50

David Valenzuela Urrutia

670
1
10
23

3

`.dt.date` absolutely should not be used with any sizable dataset because: (1) It uses dtype `object` which is very difficult to work with in Pandas if the dataset is large (having 200 million or more rows) (2) It fails to be written to parquet by `fastparquet`. – Asclepius Feb 14 '21 at 18:50

score 7 · Answer 6 · answered Apr 29 '19 at 14:18

7

Another way to do this and this works well if you have multiple columns to convert to datetime.

cols = ['date1','date2']
df[cols] = df[cols].apply(pd.to_datetime)

answered Apr 29 '19 at 14:18

SSS

300
4
10

Question ask for date not datetime. – Mark Andersen Jul 03 '19 at 14:34
@MarkAndersen aslong as you have `date` only values in your columns, convertion to datetime will retain pertaining information only. If you explicity convert using `df['datetime_col'].dt.date` that will result to an `object` dtype; loss in memory management. – Sumax Aug 10 '20 at 13:33
There is no reason to use `.apply` here, considering a direct use of `pd.to_datetime` works. – Asclepius Feb 14 '21 at 18:51

score 2 · Answer 7 · answered Oct 17 '17 at 21:30

It may be the case that dates need to be converted to a different frequency. In this case, I would suggest setting an index by dates.

#set an index by dates
df.set_index(['time'], drop=True, inplace=True)

After this, you can more easily convert to the type of date format you will need most. Below, I sequentially convert to a number of date formats, ultimately ending up with a set of daily dates at the beginning of the month.

#Convert to daily dates
df.index = pd.DatetimeIndex(data=df.index)

#Convert to monthly dates
df.index = df.index.to_period(freq='M')

#Convert to strings
df.index = df.index.strftime('%Y-%m')

#Convert to daily dates
df.index = pd.DatetimeIndex(data=df.index)

For brevity, I don't show that I run the following code after each line above:

print(df.index)
print(df.index.dtype)
print(type(df.index))

This gives me the following output:

Index(['2013-01-01', '2013-01-02', '2013-01-03'], dtype='object', name='time')
object
<class 'pandas.core.indexes.base.Index'>

DatetimeIndex(['2013-01-01', '2013-01-02', '2013-01-03'], dtype='datetime64[ns]', name='time', freq=None)
datetime64[ns]
<class 'pandas.core.indexes.datetimes.DatetimeIndex'>

PeriodIndex(['2013-01', '2013-01', '2013-01'], dtype='period[M]', name='time', freq='M')
period[M]
<class 'pandas.core.indexes.period.PeriodIndex'>

Index(['2013-01', '2013-01', '2013-01'], dtype='object')
object
<class 'pandas.core.indexes.base.Index'>

DatetimeIndex(['2013-01-01', '2013-01-01', '2013-01-01'], dtype='datetime64[ns]', freq=None)
datetime64[ns]
<class 'pandas.core.indexes.datetimes.DatetimeIndex'>

score 1 · Answer 8 · answered Aug 16 '20 at 02:13

For the sake of completeness, another option, which might not be the most straightforward one, a bit similar to the one proposed by @SSS, but using rather the datetime library is:

import datetime
df["Date"] = df["Date"].apply(lambda x: datetime.datetime.strptime(x, '%Y-%d-%m').date())

donDrey · Answer 9 · 2020-06-24T03:35:29.853

0

 #   Column          Non-Null Count   Dtype         
---  ------          --------------   -----         
 0   startDay        110526 non-null  object
 1   endDay          110526 non-null  object

import pandas as pd

df['startDay'] = pd.to_datetime(df.startDay)

df['endDay'] = pd.to_datetime(df.endDay)

 #   Column          Non-Null Count   Dtype         
---  ------          --------------   -----         
 0   startDay        110526 non-null  datetime64[ns]
 1   endDay          110526 non-null  datetime64[ns]

edited Jun 24 '20 at 03:35

answered Jun 24 '20 at 03:28

donDrey

41
4

1

No, this converts it to a 'datetime64[ns]' type not a 'date' type. Those are different things. – smci Mar 23 '21 at 22:20

score 0 · Answer 10 · answered Apr 11 '23 at 04:36

0

You can use the pandas.to_datetime()

answered Apr 11 '23 at 04:36

shubham maini

21
5

score -1 · Answer 11 · answered Feb 13 '20 at 16:59

-1

Try to convert one of the rows into timestamp using the pd.to_datetime function and then use .map to map the formular to the entire column

answered Feb 13 '20 at 16:59

Mwanaidi Nicole

1
1

How do I convert strings in a Pandas data frame to a 'date' data type?

11 Answers11

Linked

Related