In Pandas, you can convert a column (string/object or integer type) to datetime using the to_datetime() and astype() methods. Furthermore, you can also specify the data type (e.g., datetime) when reading your data from an external source, such as CSV or Excel.
In this Pandas tutorial, we will learn how to convert a column, containing dates in string format, to datetime. First, we will look at converting objects (i.e., strings) to datetime using the to_datetime() method. When working with to_datetime(), one neat thing is that we can work with the format parameter. We will also look at how to get the correct format when converting. After that, we will carry out this conversion task with the astype() method.
In the two last sections, we will import data from the disk. First, we will look at how to work with datetime when reading .csv files. Second, we will import data from an Excel file. Now, depending on how we want our dataframe, we can parse the dates in our data files as indexes or specify the column(s). Furthermore, in both examples, we will work with the parse_date argument. For more posts about converting objects in Pandas dataframe:
Outline
In this post, you will find a comprehensive guide on converting columns to datetime using Pandas. In the first section, we will create some practice data to work with. Following the creation of example data, we will explore converting an object (string) column to datetime using the to_datetime() method. Next, we will convert an integer column to datetime using the same method. Moving on, we will discuss an alternative method for column conversion using the astype() method in Pandas.
Furthermore, we will learn how to convert a column to datetime while reading a CSV file. In addition to CSV files, we will explore converting a column to datetime when reading an Excel file. Throughout this post, we will provide code examples and step-by-step instructions to ensure a clear understanding of the conversion process. By the end, you will have a solid foundation in converting columns to datetime using Pandas.
Example Data
First, before going on to the two examples, we will create a Pandas dataframe from a dictionary. Here, we are going to create a dictionary:
data = {'Salary':[1000, 2222, 3321, 4414, 5151],
'Name': ['Pete', 'Steve',
'Brian',
'Ryan', 'Jim'],
'Share':[29.88, 19.05, 8.17,
7.3, 6.15],
'Date':['11/24/2020', '12/21/2019',
'10/14/2018', '12/13/2017', '01/08/2017'],
'Date2': [20120902, 20130413, 20140921, 20140321,
20140321],
'Date3': [20220902, 20230413, 20170921, 20190321,
20220321]}
print(data)Code language: Python (python)
Notice how we now have a dictionary with strings containing datetime (i.e., the values found if using the ‘Date’ key). Creating a dataframe is the next step, then. First, we import pandas, and then we use the pd.DataFrame class with the dictionary as input:
import pandas as pd
df = pd.DataFrame(data)
df.head()Code language: Python (python)
In the image above, we can see that we have four columns, and the last columns contain the datetime strings that we want to convert. First, however, we can look at the data types of the dataframe. This can be done using the info() method:
df.info()Code language: CSS (css)
As evident in the output, the data type of the ‘Date’ column is an object (i.e., a string), and the ‘Date2’ is an integer. Note you can convert a NumPy array to a Pandas dataframe, as well, if needed. In the next section, we will use the to_datetime() method to convert both these data types to datetime.
Pandas Convert Column with the to_datetime() Method
In this section, we will work with the to_datetime() to 1) convert strings, and 2) convert integers to datetime, of course.
Example 1: Convert an Object (string) Column
Now, here is how to convert a column to datetime:
# convert column to datetime pandas
df['Date'] = pd.to_datetime(df['Date'])Code language: Python (python)In the code chunk above, we used our dataframe (i.e., df) and the brackets. Furthermore, within the brackets we put a string with the column that we wanted to convert. Note, if you want this to be a new column its to change ‘Date’ to i.e. ‘Datetime’. That would add a new column to the dataframe. On the right side of the equal sign (“=”) we used the to_datetime() method. As we are not working with any formatting, we use the column here, again, that we wanted to convert. Here’s the column converted to datetime:
Example 2: Convert an Integer Column:
Here is how to convert integer to datetime in the dataframe:
# pandas convert column with integers to date time
df['Date2'] = pd.to_datetime(df['Date2'])Code language: Python (python)In the code snippet above, we use Pandas’ pd.to_datetime() function to convert an integer-based ‘Date2’ column into datetime format within the dataframe df. As we can see in the output below, the type of the ‘Date2’ column has been converted to datetime.
Note if your dates are formatted differently; you can also use the format parameter in these examples. For more information about the to_datetime() method check out the documentation. Now that you have changed the data type in the dataframe, you can, for example, use Pandas value_count() method to count occurrences in a column. In the next section, we will perform the same conversion task using the astype() method.
Pandas Convert Multiple Objects to datetime
Here is how to convert multiple objects to datetime:
import pandas as pd
# Display the original DataFrame
print("Original DataFrame:")
print(df)
# Convert 'Date' and 'Date2' columns to datetime
df2[['Date2', 'Date3']] = df[['Date', 'Date2']].apply(pd.to_datetime)
# Display the updated DataFrame with datetime columns
print("\nDataFrame with Datetime Columns:")
print(df)Code language: PHP (php)In the code chunk above, we use the pd.to_datetime() function from the Pandas library to efficiently convert columns in a DataFrame to the datetime data type.
We have a DataFrame named df containing ‘Date2’ and ‘Date3’ columns, which store date information as strings in the ‘YYYY-MM-DD’ format. To seamlessly convert these columns to the desired datetime format, we apply the pd.to_datetime() function to the specified columns. This is achieved by harnessing the combined power of the .apply() and lambda() functions, enabling us to transform the entire column content simultaneously.
Pandas Convert Column with the astype() Method
In this section, we are going to use the astype() method. Here is how to convert a column containing strings to datetime with the astype() method:
# Datetime conversion using astype:
df['Date'] = df['Date'].astype('datetime64[ns]')Code language: PHP (php)Notice how we put datetime[ns] as the only argument. This will produce the following output, similar to what we have seen earlier.
As a final note, it is impossible to change the type from integer to datetime with the same code above. In the following two sections, we will convert to datetime when reading data from disk.
Convert Column to datetime when Reading a CSV File
Here is how to change a column to datetime while using Pandas read_csv method:
df = pd.read_csv('pandas_datetime_example.csv', index_col=0, parse_dates=[3])
df.dtypesCode language: Python (python)
As can be seen in the example code above, we used the parse_date parameter and set the column number in which our dates are stored. As usual, when working with Python, the indexes start at 0. Noteworthy here is that we also used the index_col parameter and set this to the first column (0) in the datafile. If we, on the other hand, would have had the dates in the first column (i.e., in the .csv file), we can set the dates as index:
df = pd.read_csv('convert_pandas_column_to_datetime.csv',
index_col=0, parse_dates=True)
df.info()Code language: Python (python)
Finally, if you want the date column to be index, this can be done after reading the .csv file as well. Here, you will just make the column index in the Pandas dataframe with the set_index() method.
Convert Column to datetime when Reading an Excel File
Here is how to change a column to datetime when importing data using Pandas read_excel:
df = pd.read_excel('pandas_convert_column_to_datetime.xlsx',
index_col=0, parse_dates=True)
df.info()Code language: PHP (php)As you can see, in the code chunk above, we used the same parameter when reading a CSV file (i.e., parse_date). Note, here we set the date column, in the Excel file, as indexes. If you want this to be a column, change True to [0].
Now that you have converted your dates to datetime object, you can start working with other date-related methods such as TimeDelta. For example, you can now calculate the difference between two dates. Furthermore, you can also plot your data in a time-series plot using, e.g., a Seaborn line plot.
As a final note, on both methods above, is that if you have many columns that need to be converted to datetime you can add each index of these columns in the list. Here’s some example code:
Conclusion
In this post, you have converted strings and integers (in dataframe columns) to datetime. First, you have learned how to use the to_datetime() method. Second, you learned how to use the astype() method. Remember, it is not possible to change the data type from integer to datetime if you use the latter method. Finally, you have also learned how to specify which columns that are of datetime type when reading a CSV and Excel file.
Resources
Here are a couple of resources related to Python that you might find helpful:
- Wilcoxon Signed-Rank test in Python
- Your Guide to Reading Excel (xlsx) Files in Python
- Python Scientific Notation & How to Suppress it in Pandas & NumPy
- How to use Python to Perform a Paired Sample T-test
- How to use Square Root, log, & Box-Cox Transformation in Python
