Converting dataframe column from hhmmss to hh:mm:ss in python

Question

As my time-series dataframe is in the time format hhmmss, I can't plot it against my data without it skipping, for instance, 000059 -> 000100 at the end of every minute. Data Head

When I search for solutions, they all show conversion of hh:mm:ss to seconds, but my time is in hhmmss not hh:mm:ss.

I've tried converting hhmmss to a string, defining the hh, mm and ss separately using their index, converting to an integer and then converting hh and mm to seconds, then adding them all back together to get hhmmss in seconds.

data = pd.read_csv("CONT  65.754 248.750 20011120 GEODETIC nT17280", sep =" " )
data['TIME'] = pd.to_datetime(data['TIME'], format = '%H%M%S')
data['secs'] = (data.TIME.dt.hour*3600 + data.TIME.dt.minute*60) + data.TIME.dt.second

time_sec = data['secs']
X_Value = data['X']

plt.plot(time_sec, X_Value)

When I try this, I get the error: 'time data 4 does not match format '%H%M%S' (match)'

How could I convert my time column in the format hhmmss, to be in the format of seconds?
Many thanks :)

Answer 1

As an alternative approach you could accept the integers from read_csv and convert directly to seconds:

data = pd.read_csv("CONT  65.754 248.750 20011120 GEODETIC nT17280", sep =" " )
def func(x):
    s1 = x%100
    s2 = (x//100)%100
    s3 = x//10000
    return s3*3600 + s2*60 + s1


df['secs'] = df['TIME'].map(func)