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 are going to learn how to convert a column, containing dates in string format, to datetime. First, we are going to have a look at converting objects (i.e., strings) to datetime using the to_datetime() method. One neat thing when working with to_datetime() is that we can work with the format parameter. That is, we will also have a look at how to get the correct format when converting. After that, we will go on and carry out this conversion task with the astype() method.

convert pandas column to datetime
  • Save
Two methods to convert column to datetime

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 either 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. 

Example Data

First, before going on to the two examples, we are going to 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]} print(data)
Python dictionary to be converted to pandas dataframe
  • Save

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()
Pandas dataframe with 5 columns
  • Save
Object (string) and integer columns to convert to datetime

In the image above, we can see that we have four columns and the last most contains the datetime strings that we want to convert. First, however, we can have a look at the data types of the dataframe. This can be done using the info() method:

df.info()
Pandas dataframe data types
  • Save

As evident in the output, the data types of the ‘Date’ column is object (i.e., a string) and the ‘Date2’ is 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 are going to work with the to_datetime() to 1) convert strings, and 2) convert integers. Both to datetime, of course.

1 Convert an Object (string) Column:

Now, here’s how to convert a column to datetime:

# convert column to datetime pandas df['Date'] = pd.to_datetime(df['Date'])

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 just 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’re not working with any formatting we just use the column here, again, that we wanted to convert. Here’s the column converted to datetime:

String/object column converted to datetime
  • Save

2 Convert an Integer Column:

Here’s how to convert integer to datetime in the dataframe:

# pandas convert column with integers to date time df['Date2'] = pd.to_datetime(df['Date2'])

As we can see in the output above, the type of the ‘Date2’ column has been converted to datetime.

converted strings to datetime index in Pandas dataframe
  • Save

Note, if your dates are formatted differently and in these examples you can use the format parameter as well. For more information about the to_datetime() method check out the documentation. In the next section, we will carry out the same conversion task but using the astype() method. 

Pandas Convert Column with the astype() Method

In this section, we are going to use the astype() method. Here’s how to convert a column, containing strings, to datetime with the astype() method:

# Datetime conversion using astype: df['Date'] = df['Date'].astype('datetime64[ns]')

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 not possible to change the type from integer to datetime with the same code above. In the next two sections, we are going to convert to datetime when reading data from disk.

Convert Column to datetime when Reading a CSV File

Here’s 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.dtypes
column converted to datetime when reading csv
  • Save

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()
converted strings to datetime index in Pandas dataframe
  • Save

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’s 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()

As you can see, in the code chunk above, we used the same parameter as 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. 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 later method. Finally, you have also learned how to specify which columns that are of datetime type when reading a CSV and Excel file.

pandas convert column to datetime
  • Save
Share via
Copy link
Powered by Social Snap