Pandas Convert Column to datetime – object/string, integer, CSV & Excel

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.

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)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 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()Code language: CSS (css)

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'])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 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:

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'])Code language: Python (python)

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

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. 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 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]')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 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.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’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()Code language: PHP (php)

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.