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'
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')
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)
states_df[['from_datetime','to_datetime','timestamp']].apply(lambda _: pd.to_datetime(_,format='%Y-%m-%d %H:%M:%S.%f', errors='coerce'))
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
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)
infer_datetime_format=True
can increase the parsing speed by ~5-10x, in some cases.
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.
Time Saver:
raw_data['Mycol'] = pd.to_datetime(raw_data['Mycol'])
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
Success story sharing
format
argument isn't required.to_datetime
is smart. Go ahead and try it without trying to match your data.SettingWithCopyWarning
use the @darth-behfans stackoverflow.com/a/42773096/4487805infer_datetime_format=True
could also increase parsing speed up to ~5-10x (according to pandas docs) if you don't include a format string.