0

I am trying to convert a date & time string using Pandas 'to_datetime', but the string values is non-zero padded:

3/31/22 23:30
3/31/22 23:45
4/1/22 0:00
4/1/22 0:15

I have the following but get a mismatch error

pd.to_datetime(df.TimeStamp, format="%m/%d/%y %H:%m")

Is there a way to add the zero padding or have 'to_datetime' accept the above formatting?

2S2E
  • 21
  • 5
  • you probably want to use %M at the end instead of %m: %M is for minutes, %m for months: format="%m/%d/%y %H:%M" When using that it works in my case on pandas 1.4.3 see: https://pandas.pydata.org/docs/reference/api/pandas.Period.strftime.html – n4321d Sep 07 '22 at 23:11
  • pd.to_datetime is pretty good at inferring format. `pd.to_datetime(df['TimeStamp'])` should work just fine no? – Henry Ecker Sep 07 '22 at 23:49

2 Answers2

1
df['date'].astype('datetime64').dt.strftime('%m/%d/%y %H:%M')

0    03/31/22 23:30
1    03/31/22 23:45
2    04/01/22 00:00
3    04/01/22 00:15

data used

data = {'': {0: ' ', 1: ' ', 2: ' ', 3: ' '},
 'date': {0: '3/31/22 23:30',
  1: '3/31/22 23:45',
  2: '4/1/22 0:00',
  3: '4/1/22 0:15'}}
df=pd.DataFrame(data)
df

    date
0       3/31/22 23:30
1       3/31/22 23:45
2       4/1/22 0:00
3       4/1/22 0:15
Naveed
  • 11,495
  • 2
  • 14
  • 21
1

The trouble isn't in the padding, it's actually in your formatting call. Note the capitalization of minutes (M) vs months (m), you used (m) for both. (documentation here).

Demonstration of working code is below

pd.to_datetime(df.TimeStamp, format="%m/%d/%y %H:%m")

should be

pd.to_datetime(df.TimeStamp, format="%M/%d/%y %H:%m")

import pandas as pd
times = [
    "3/31/22 23:30",
    "3/31/22 23:45",
    "4/1/22 0:00",
    "4/1/22 0:15"
]

df = pd.DataFrame(times, columns=['TimeStamp'])
pd.to_datetime(df.TimeStamp, format="%m/%d/%y %H:%M")

>> 0   2022-03-31 23:30:00
>> 1   2022-03-31 23:45:00
>> 2   2022-04-01 00:00:00
>> 3   2022-04-01 00:15:00
>> Name: TimeStamp, dtype: datetime64[ns]

That said, if anyone lands here looking for the solution to the zero-padding, the hash/dash trick is worth further reading (though it does not work in many circumstances)

remsky
  • 121
  • 3