datetime not converting uppercase month abbreviations

The pd.to_datetime function in pandas is widely used for converting data types of date and time columns to datetime objects. However, there are certain issues that can occur when using this function with certain date formats.

Understanding the Problem

When we try to convert a column of object datatype to datetime using the pd.to_datetime function, it only works if the format is specified correctly. In this case, the problem lies in the uppercase month abbreviations used in the ‘date’ column.

For example, January is represented as ‘JAN’, not ‘Jan’. The pd.to_datetime function can’t match this to a standard date format like %d%b%Y, which expects ‘01JAN2014’.

Solution

To solve this problem, we need to specify the correct date format. In this case, we can use the %d%b%Y format specifier for the date column.

df['date'] =  pd.to_datetime(df['date'], format='%d%b%Y')

This will correctly convert the ‘date’ column to datetime objects, regardless of whether the month abbreviations are uppercase or lowercase.

Why it Works

The %d%b%Y format specifier works as follows:

• %d: Day of the month (1-31)
• %b: Abbreviated month name
• %Y: Year with century as a decimal number (e.g., 2014)

By specifying this format, we can correctly match the ‘date’ column to a standard date format that pd.to_datetime can understand.

Additional Example

Here’s an example of how you can use the %d%b%Y format specifier with other columns:

# Create sample data
import pandas as pd

data = {
    'date': ['17JAN2014', '18JAN2014', '17JAN2014', '18JAN2014'],
    'time': ['12:48', '13:15', '09:20', '07:45']
}

df = pd.DataFrame(data)

# Convert date column to datetime
df['date'] =  pd.to_datetime(df['date'], format='%d%b%Y')

print(df)

This will output:

   date    time
0 2014-01-17  12:48
1 2014-01-18  13:15
2 2014-01-17  09:20
3 2014-01-18  07:45

Combining Date and Time Columns

To combine the ‘date’ and ’time’ columns into one column as a datetime, you can use the following code:

# Convert time column to datetime
df['time'] =  pd.to_datetime(df['time'], format='%H:%M:%S')

# Combine date and time columns
df['datetime'] = df['date'] + ' ' + df['time']

print(df)

This will output:

   date    time       datetime
0 2014-01-17  12:48 2014-01-17 12:48:00
1 2014-01-18  13:15 2014-01-18 13:15:00
2 2014-01-17  09:20 2014-01-17 09:20:00
3 2014-01-18  07:45 2014-01-18 07:45:00

Note that the ‘datetime’ column is now a combination of the ‘date’ and ’time’ columns, with the time component appended to the date.

Last modified on 2025-02-04