By using pandas to_datetime() & astype() functions you can convert column to DateTime format (from String and Object to DateTime). If your DataFrame holds the DateTime in a string column in a specific format, you can convert it by using to_datetime() function as it accepts the format param to specify the format date & time.
In this article, I will explain how to convert the String/Object column holding data & time to Datetime format which ideally converts string type to datetime64[ns] type. You can also use the same approach to convert the integer column holding date & time to datetime64[ns] column.
Key Points –
- Use
pd.to_datetime()to convert a column to datetime format. errorsparameter can handle errors gracefully. Set to'coerce'to convert invalid parsing toNaT.- Specify the
formatparameter for faster conversion, especially when the format is consistent. infer_datetime_format=Truecan speed up parsing by inferring the format automatically.- Use
apply(pd.to_datetime)for converting multiple columns across a DataFrame. - If the column has mixed date formats, setting
errors='coerce'can handle incompatible values. - Extract only the date part by converting the datetime to
.dt.date.
Quick Examples of Convert Column To DateTime
Following are quick examples of converting the column to DataTime.
# Quick examples of pandas convert column to datetime
# Example 1: Using pandas.to_datetime()
# To convert pandas column to datetUse apply(pd.to_datetime) for converting multiple columns across a DataFrame.ime
df['Inserted'] = pd.to_datetime(df['Inserted'], format="%m/%d/%Y, %H:%M:%S")
# Example 2: Using pandas.to_datetime()
df['Inserted'] = pd.to_datetime(df['Inserted'])
print(df)
# Example 3: Using DataFrame.apply() and lambda function
df['Inserted'] = df['Inserted'].apply(lambda _: datetime.strptime(_,"%m/%d/%Y, %H:%M:%S"))
# Example 4: To pandas.to_datetime()
# Using infer_datetime_format=True
df['Inserted'] = pd.to_datetime(df['Inserted'], infer_datetime_format=True)
# Example 5: Convert pandas column to datetime
# Using Series.astype() method
df['Inserted'] = df['Inserted'].astype('datetime64[ns]')
# Example 6: Convert pandas multiple columns to datetime
df[['Inserted','Updated']] = df[['Inserted','Updated']].apply(pd.to_datetime, errors='coerce')
First, let’s create a Pandas DataFrame.
# Create DataFrame
import pandas as pd
from datetime import datetime, timedelta
from pandas import DataFrame
df = DataFrame.from_dict(
{'Courses':["Spark","Hadoop","pandas"],
'Fee' :[20000,25000,30000],
'Duration':['30days','40days','35days'],
'Discount':[1000,2500,1500],
'Inserted': ["11/22/2021, 10:39:24","11/22/2021, 10:39:24","11/22/2021, 10:39:24"]},
orient='index',
columns=['A','B','C']).T
print("Create DataFrame:\n", df)
Yields below output. Note that Inserted column on the DataFrame has DateTime in the format of "%m/%d/%Y, %H:%M:%S"
Convert Column to DateTime
Use pandas to_datetime() function to convert the column to DateTime on DataFrame. Use the format parameter of this method to specify the pattern of the DateTime string you wanted to convert.
Note that this function doesn’t modify the DataFrame in place hence, you need to assign the returned column back to the DataFrame to update.
# Using pandas.to_datetime() to convert pandas column to DateTime
df['Inserted'] = pd.to_datetime(df['Inserted'], format="%m/%d/%Y, %H:%M:%S")
print(" After converting column to datetime:\n", df)
Yields below output.
Since we have the Datetime in the default format "%m/%d/%Y, %H:%M:%S", you can convert without specifying the format param.
# Using pandas.to_datetime()
df['Inserted'] = pd.to_datetime(df['Inserted'])
print(" After converting column to datetime:\n", df)
Yields the same output as above.
Using Series.astype() Method
Use astype() function to convert the string column to DateTime data type in pandas DataFrame. The data type of the DateTime isdatetime64[ns]; should be given as the parameter.
# Convert pandas column to DateTime using Series.astype() method
df['Inserted'] = df['Inserted'].astype('datetime64[ns]')
print(" After converting column to datetime:\n", df)
Yields the same output as above.
Convert String to DateTime Using Lambda Function
You can also use the DataFrame.apply() and lambda function to operate on the values, here I will be using datetime.strptime() function to convert. Use the lambda expression in the place of func for simplicity. Make sure you import datatime before using it.
# Using DataFrame.apply() and lambda function
from datetime import datetime
df['Inserted'] = df['Inserted'].apply(lambda _: datetime.strptime(_,"%m/%d/%Y, %H:%M:%S"))
print(" After converting column to datetime:\n", df)
Yields below output.
# Output:
Courses Fee Duration Discount Inserted
A Spark 20000 30days 1000 2021-11-22 10:39:24
B Hadoop 25000 40days 2500 2021-11-22 10:39:24
C pandas 30000 35days 1500 2021-11-22 10:39:24
Using infer_datetime_format=True
When you use the to_datetime() function to parse the column as DateTime, use infer_datetime_format=True where it will automatically detect the format and convert the mentioned column to DateTime.
# To pandas.to_datetime() using infer_datetime_format=True
df['Inserted'] = pd.to_datetime(df['Inserted'], infer_datetime_format=True)
print(" After converting column to datetime:\n", df)
Yields below output.
# Output:
Courses Fee Duration Discount Inserted
A Spark 20000 30days 1000 2021-11-22 10:39:24
B Hadoop 25000 40days 2500 2021-11-22 10:39:24
C pandas 30000 35days 1500 2021-11-22 10:39:24
Convert Multiple Columns to DateTime
You can also convert multiple string columns to DateTime in Panadas DataFrame, here you have two columns Inserted and Updated that are strings holding DateTime.
import pandas as pd
from datetime import datetime, timedelta
from pandas import DataFrame
df = DataFrame.from_dict(
{'Courses':["Spark","Hadoop","pandas"],
'Fee' :[20000,25000,30000],
'Duration':['30days','40days','35days'],
'Discount':[1000,2500,1500],
'Inserted': ["10/02/2021, 10:39:24","09/12/2021, 08:09:24","01/22/2021, 10:29:14"],
'Updated': ["11/12/2021, 11:39:24","10/22/2021, 10:39:34","05/12/2021, 04:49:04"]},
orient='index',
columns=['A', 'B', 'C']).T
print(df)
Yields below output.
# Output:
Courses Fee Duration Discount Inserted Updated
A Spark 20000 30days 1000 10/02/2021, 10:39:24 11/12/2021, 11:39:24
B Hadoop 25000 40days 2500 09/12/2021, 08:09:24 10/22/2021, 10:39:34
C pandas 30000 35days 1500 01/22/2021, 10:29:14 05/12/2021, 04:49:04
Now let’s convert Inserted and Updated columns to datetime.
# Convert pandas multiple columns to Datetime
df[['Inserted','Updated']] = df[['Inserted','Updated']].apply(pd.to_datetime, errors='coerce')
print(df)
Yields below output
# Output:
Courses Fee Duration Discount Inserted Updated
A Spark 20000 30days 1000 2021-10-02 10:39:24 2021-11-12 11:39:24
B Hadoop 25000 40days 2500 2021-09-12 08:09:24 2021-10-22 10:39:34
C pandas 30000 35days 1500 2021-01-22 10:29:14 2021-05-12 04:49:04
Alternatively, you can also use pandas astype() function to cast multiple columns.
# Convert multiple columns using astype()
df2 = df.astype({'Inserted':'datetime64[ns]','Updated':'datetime64[ns]'})
print(df2)
Complete Example of Convert Column To DateTime
import pandas as pd
from datetime import datetime, timedelta
from pandas import DataFrame
df = DataFrame.from_dict(
{'Courses':["Spark","Hadoop","pandas"],
'Fee' :[20000,25000,30000],
'Duration':['30days','40days','35days'],
'Discount':[1000,2500,1500],
'Inserted': ["11/22/2021, 10:39:24","11/22/2021, 10:39:24","11/22/2021, 10:39:24"]},
orient='index',
columns=['A','B','C']).T
print(df)
# Using pandas.to_datetime() to convert pandas column to DateTime
df['Inserted'] = pd.to_datetime(df['Inserted'], format="%m/%d/%Y, %H:%M:%S")
print(df)
# Using pandas.to_datetime()
df['Inserted'] = pd.to_datetime(df['Inserted'])
print(df)
# Using DataFrame.apply() and lambda function
df['Inserted'] = df['Inserted'].apply(lambda _: datetime.strptime(_,"%m/%d/%Y, %H:%M:%S"))
print(df)
# To pandas.to_datetime() using infer_datetime_format=True
df['Inserted'] = pd.to_datetime(df['Inserted'], infer_datetime_format=True)
print(df)
# Convert pandas column to DateTime using Series.astype() method
df['Inserted'] = df['Inserted'].astype('datetime64[ns]')
print(df)
# Convert pandas multiple columns to Datetime
df[['Inserted','Updated']] = df[['Inserted','Updated']].apply(pd.to_datetime, errors='coerce')
print(df)
FAQ on Convert Column To DateTime
You can specify a custom date format using the format parameter in the pd.to_datetime() function. This is particularly useful when your date column doesn’t follow the standard date formats.
If your date information is split across multiple columns, you can combine them into a single column and then convert it to DateTime format.
converting a large dataset can be resource-intensive. Consider using the infer_datetime_format=True parameter if the date format is consistent across the column, as it can improve performance.
You can use the .dt accessor in Pandas to extract specific components (such as year, month, day) from a DateTime column. For example, the .dt.year, .dt.month, and .dt.day accessors are used to extract the year, month, and day components, respectively.
Conclusion
In conclusion, Pandas offers versatile methods for converting columns to DateTime format. Through the usage of pd.to_datetime() and DataFrame.astype() functions, one can seamlessly transform string and object columns into DateTime format, enhancing the data manipulation capabilities within Python.
Happy Learning !!
Related Articles
- Find Unique Values From Columns
- Sort Pandas DataFrame by Single Column
- Rename Index Values of Pandas DataFrame
- Pandas DatetimeIndex Usage Explained
- pandas Convert Datetime to Seconds
- Sort Pandas DataFrame by Date (Datetime)
- Pandas Extract Year from Datetime
- Pandas Get Day, Month and Year from DateTime
- Insert or Add a Row to Pandas DataFrame Examples
- Set and Get Index Title/Name of Pandas DataFrame