python - Timestamp format not being matched in pandas to_datetime format specifier

Question

I have a pandas dataframe with some timestamp values in a column. I wish to get the sum of values grouped by every hour.

 Date_and_Time  Frequency
0   Jan 08 15:54:39 NaN
1   Jan 09 10:48:13 NaN
2   Jan 09 10:42:24 NaN
3   Jan 09 20:18:46 NaN
4   Jan 09 12:08:23 NaN

I started off removing the leading days in the column and then typed the following to convert the values to date_time compliant format:

dateTimeValues['Date_and_Time'] = pd.to_datetime(dateTimeValues['Date_and_Time'], format='%b %d %H:%M:%S')

After doing so, I receive the following error:

ValueError: time data 'Jan 08 12:41:' does not match format '%b %d %H:%M:%S' (match)

On checking my input CSV, I can confirm that no column containing the above data are incomplete.

I'd like to know how to resolve this issue and successfully process my timestamps to their desired output format.

question from:https://stackoverflow.com/questions/65864234/timestamp-format-not-being-matched-in-pandas-to-datetime-format-specifier

Answer 1

I suggest you create a self defined lambda function which selects the needed format string. You may have to edit the lambda function:

df = pd.DataFrame({'Date_and_Time':['Jan 08 15:54:39', 'Jan 09 10:48:']})
df
>>>
     Date_and_Time
0  Jan 08 15:54:39
1    Jan 09 10:48:

With one typo in line 1. Now selected the format string for every item with the lambda function.

def my_lambda(x):
    f = '%b %d %H:%M:%S' 
    if x.endswith(':'):
        f = '%b %d %H:%M:'
    return pd.to_datetime(x , format=f)

df['Date_and_Time'] = df['Date_and_Time'].apply(my_lambda)
>>> df
        Date_and_Time
0 1900-01-08 15:54:39
1 1900-01-09 10:48:00