Convert datetime to seconds for multiple dates in time series data-CodePudding

I have date in this format '26:10:2022 09:49:46:000' which continues for multiple days in a time series dataset. I want to convert it to seconds for each time step, i.e., the output should be amount of seconds for the particular day in that year. For e.g. the number of seconds at 4:1:2022 00:00:00:000 should be 259,200 seconds from first day of its year 1:1:2022 00:00:00:000 and so on. Could someone please help in this matter?

I am unable to do it using below way as it resets to zero after each day.

df['DateTime'] = pd.to_datetime(df['DateTime'].str.strip(), format="%d:%m:%Y %H:%M:%S:%f")
df['Seconds_data'] = df['DateTime'].dt.second

CodePudding user response：

How about manually calculating the seconds past? Transform the past days, past months and past minutes into seconds and then adding them together

CodePudding user response：

I hope I've understood your question correctly, you can get the year from DateTime column, construct the date 1-1-<year>, convert it to datetime and substract it from from the DateTime column.

Then use .dt.total_seconds() function:

df["DateTime"] = pd.to_datetime(
    df["DateTime"].str.strip(), format="%d:%m:%Y %H:%M:%S:%f"
)

df["Seconds_data"] = (
    (
        df["DateTime"]
        - pd.to_datetime("1-1-"   df["DateTime"].dt.year.astype(str))
    )
    .dt.total_seconds()
    .astype(int)
)

print(df)

Prints:

             DateTime  Seconds_data
0 2022-10-26 09:49:46      25782586
1 2022-01-04 00:00:00        259200