Pandas Convert String Column To DateTime

By using pandas.to_datetime() & astype() function you can convert String and Object column to DateTime format. If your DataFrame holds the date time string in a specific format, to_datetime() function accepts the format param to specify the format of the string column that holds datetime.

In this article, I will explain how to convert the DataFrame String/Object column holding date & 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 Convert Pandas Column To DateTime

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


# 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)

2. pandas.to_datetime() Syntax & Examples

Below is the syntax of the pandas.to_datetime() method.


# pandas.to_datetime() syntax
pandas.to_datetime(arg, errors='raise', dayfirst=False, yearfirst=False, 
     utc=None, format=None, exact=True, unit=None, 
     infer_datetime_format=False, origin='unix', cache=True)
  • arg – An integer, string, float, list, or DataFrame/dict_like object to convert into a Datetime object.
  • errors – Take values raise, ignore or coerce. if ‘raise’ used, raise a KeyError when a dict-like mapper, index, or columns contains labels that are not present in the Index being transformed. Default set to ignore.
  • dayfirst – default set False, Boolean value places day first if True.
  • yearfirst – Boolean value places year first if True, the default set False.
  • utc – Boolean value, Returns the time in UTC DatetimeIndex if True.
  • format – String input to tell the position of the day, month, and year. default set None.
  • exact – Boolean value, If True, require an exact format match. – If False, allow the format to match anywhere in the target string.
  • infer_datetime_formatbool – If True and no format is given, attempt to infer the format of the datetime strings based on the first non-NaN element. the default set False.

Now, let’s create a DataFrame with a few rows and columns, execute these 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 date and time 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

3. Use pandas.to_datetime() to Convert String Column to DateTime

Use pandas.to_datetime() function to convert the given string column to DateTime. Use the format parameter to specify the pattern of the DateTime string you wanted to convert. It is the same with the format in stftime or strptime. pandas.to_datetime() function doesn’t modify the DataFrame in-place hence, you need to assign the returned column back to the DataFrame.


# 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 default pandas format "%m/%d/%Y, %H:%M:%S", we don’t have to specify the format param. For example, df['Datetime']=pd.to_datetime(df['Datetime']) converts to DateTime in a smart way without being given the DateTime format string.


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

Yields same output as above.

4. Convert Column to DateTime Using DataFrame.apply() and Lambda Function

You can use the DataFrame.apply() and lambda function to operate on the values in DataTime. Use the lambda function in the place of func for simplicity.


# 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. To pandas.to_datetime() Using infer_datetime_format=True

Use the pandas.to_datetime() function to parse the column as DateTime. Also, by using infer_datetime_format=True, 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 Column to DateTime Using Series.astype() Method

Use series.astype() method to convert the string column to datetime data type. 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.

7. Convert Pandas Multiple Columns to Datetime

You can also convert multiple string columns to DateTime in panadas DataFrame, you have two columns Inserted and Updated that are DateTime strings.


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

8. Complete Example For Convert Pandas 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 pandas convert string column to DataTime using pandas.to_datetime() & astype() function you can convert String and Object column to DateTime format.

Happy Learning !!

You May Also Like

References

Leave a Reply

Pandas Convert String Column To DateTime