Convert Pandas Column to DateTime
PythonDatetimePandasPython Problem Overview
I have one field in a pandas DataFrame that was imported as string format. It should be a datetime variable. How do I convert it to a datetime column and then filter based on date.
Example:
- DataFrame Name: raw_data
- Column Name: Mycol
- Value Format in Column: '05SEP2014:00:00:00.000'
Python Solutions
Solution 1 - Python
Use the to_datetime
function, specifying a format to match your data.
raw_data['Mycol'] = pd.to_datetime(raw_data['Mycol'], format='%d%b%Y:%H:%M:%S.%f')
Solution 2 - Python
If you have more than one column to be converted you can do the following:
df[["col1", "col2", "col3"]] = df[["col1", "col2", "col3"]].apply(pd.to_datetime)
Solution 3 - Python
You can use the DataFrame method .apply()
to operate on the values in Mycol:
>>> df = pd.DataFrame(['05SEP2014:00:00:00.000'],columns=['Mycol'])
>>> df
Mycol
0 05SEP2014:00:00:00.000
>>> import datetime as dt
>>> df['Mycol'] = df['Mycol'].apply(lambda x:
dt.datetime.strptime(x,'%d%b%Y:%H:%M:%S.%f'))
>>> df
Mycol
0 2014-09-05
Solution 4 - Python
Use the pandas to_datetime
function to parse the column as DateTime. Also, by using infer_datetime_format=True
, it will automatically detect the format and convert the mentioned column to DateTime.
import pandas as pd
raw_data['Mycol'] = pd.to_datetime(raw_data['Mycol'], infer_datetime_format=True)
Solution 5 - Python
raw_data['Mycol'] = pd.to_datetime(raw_data['Mycol'], format='%d%b%Y:%H:%M:%S.%f')
works, however it results in a Python warning of
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value
instead
I would guess this is due to some chaining indexing.
Solution 6 - Python
Time Saver:
raw_data['Mycol'] = pd.to_datetime(raw_data['Mycol'])
Solution 7 - Python
It is important to note that pandas.to_datetime will almost never return a datetime.datetime. From the docs
> Blockquote
Returns datetime
If parsing succeeded. Return type depends on input:
list-like: DatetimeIndex
Series: Series of datetime64 dtype
scalar: Timestamp
In case when it is not possible to return designated types (e.g. when any element
of input is before Timestamp.min or after Timestamp.max) return will have
datetime.datetime type (or corresponding array/Series).
> Blockquote