Unknown string format pd.to_datetime in Python. Having issues trying to convert this format to a datetime format

Question

My column headers in my DF are of the following format -->

df_weather.columns

['Max-09-23', 'Min-09-23', 'Max-09-24', 'Min-09-24', 'Max-09-25',
       'Min-09-25', 'Max-09-26', 'Min-09-26', 'Max-09-27', 'Min-09-27',
       'Max-09-28', 'Min-09-28', 'Max-09-29', 'Min-09-29', 'Max-09-30',
       'Min-09-30', 'Max-10-01', 'Min-10-01', 'Max-10-02', 'Min-10-02']

I would like to convert them to a datetime format but the Min/Max is causing issues. Any help appreciated.

Hi and welcome to stackoverflow. Please post also, what you already have tried to solve your issue — MichaelJanz, Sep 22 '20 at 15:43
I've tried a few different methods with datetime and parsing the string but no luck... — Edi Lacic, Sep 22 '20 at 15:45

score 0 · Answer 1 · answered Sep 22 '20 at 16:49

An option might be to replace the "Min" / "Max" with a specific year and create a datetime index from the result:

import pandas as pd

# let's create a dummy df...
cols = ['Max-09-23', 'Min-09-23', 'Max-09-24', 'Min-09-24', 'Max-09-25',
       'Min-09-25', 'Max-09-26', 'Min-09-26', 'Max-09-27', 'Min-09-27',
       'Max-09-28', 'Min-09-28', 'Max-09-29', 'Min-09-29', 'Max-09-30',
       'Min-09-30', 'Max-10-01', 'Min-10-01', 'Max-10-02', 'Min-10-02']
df_weather = pd.DataFrame({k: [0,0] for k in cols})

# set a default year...
year = '2020'

# to datetime...
dti = pd.to_datetime(df_weather.columns.str.replace('(Max)|(Min)', year))

dti
DatetimeIndex(['2020-09-23', '2020-09-23', '2020-09-24', '2020-09-24',
               '2020-09-25', '2020-09-25', '2020-09-26', '2020-09-26',
               '2020-09-27', '2020-09-27', '2020-09-28', '2020-09-28',
               '2020-09-29', '2020-09-29', '2020-09-30', '2020-09-30',
               '2020-10-01', '2020-10-01', '2020-10-02', '2020-10-02'],
              dtype='datetime64[ns]', freq=None)

Otherwise, you could also replace with "" and cast to datetime with a defined format (format="-%m-%d"). But that would add a default year of 1900.

Unknown string format pd.to_datetime in Python. Having issues trying to convert this format to a datetime format

1 Answers1