Pandas Convert Column To DateTime

  • Post author:
  • Post category:Pandas / Python
  • Post last modified:January 29, 2023
Spread the love

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.

1. Quick Examples of pandas Convert Column To DateTime

If you are in a hurry, below are some quick examples of how to convert the column to DataTime.


# Below are quick example
# 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)

Now, let’s create a DataFrame with a few rows and columns, execute the above examples and validate results. Our DataFrame contains column names Courses, Fee, Duration, Discount and Inserted.


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)

Yields below output. Note that Inserted column on the DataFrame has DateTime in the format of "%m/%d/%Y, %H:%M:%S"


  Courses    Fee Duration Discount              Inserted
A   Spark  20000   30days     1000  11/22/2021, 10:39:24
B  Hadoop  25000   40days     2500  11/22/2021, 10:39:24
C  pandas  30000   35days     1500  11/22/2021, 10:39:24

2. 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(df)

Yields below 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

Since we have the Datetime in the default format "%m/%d/%Y, %H:%M:%S", you can convert with out specifying the format param.


# Using pandas.to_datetime() 
df['Inserted'] = pd.to_datetime(df['Inserted'])
print(df)

Yields same output as above.

3. 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(df)

Yields same output as above.

4. 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(df)

Yields below 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

5. 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(df)

Yields below 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

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


  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


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)

7. Complete Example of pandas 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)

Conclusion

In this article, you have learned how to convert columns to DataTime using pandas.to_datetime() & DataFrame.astype() function. Using these you can convert String and Object columns to DateTime format.

Happy Learning !!

References

You may also like reading:

  1. Pandas Convert Multiple Columns To DateTime Type
  2. Pandas Convert Date (datetime) to String Format
  3. Pandas Filter DataFrame Rows on Dates
  4. Pandas Change String to Date in DataFrame
  5. Pandas Convert Datetime to Date Column
  6. Pandas Extract Month and Year from Datetime
  7. Pandas Convert Integer to Datetime Type
  8. pandas DataFrame.astype() – Examples
  9. Pandas Operator Chaining to Filter DataFrame Rows
  10. Pandas Get Column Names from DataFrame
Tags: ,

Leave a Reply

You are currently viewing Pandas Convert Column To DateTime