Converting pandas date column into seconds elapsed
12,139
New Answer
Convert your text to Timedelta
df['Origin Time(Local)'] = pd.to_timedelta(df['Origin Time(Local)'])
df['Seconds'] = df['Origin Time(Local)'].dt.total_seconds()
Old Answer
Consider the dataframe df
df = pd.DataFrame(dict(Date=pd.date_range('2017-03-01', '2017-03-02', freq='2H')))
Date
0 2017-03-01 00:00:00
1 2017-03-01 02:00:00
2 2017-03-01 04:00:00
3 2017-03-01 06:00:00
4 2017-03-01 08:00:00
5 2017-03-01 10:00:00
6 2017-03-01 12:00:00
7 2017-03-01 14:00:00
8 2017-03-01 16:00:00
9 2017-03-01 18:00:00
10 2017-03-01 20:00:00
11 2017-03-01 22:00:00
12 2017-03-02 00:00:00
Subtract the most recent day from the timestamps and use total_seconds
. total_seconds
is an attribute of a Timedelta
. We get a series of Timedeltas
by taking the difference between two series of Timestamps
.
(df.Date - df.Date.dt.floor('D')).dt.total_seconds()
# equivalent to
# (df.Date - pd.to_datetime(df.Date.dt.date)).dt.total_seconds()
0 0.0
1 7200.0
2 14400.0
3 21600.0
4 28800.0
5 36000.0
6 43200.0
7 50400.0
8 57600.0
9 64800.0
10 72000.0
11 79200.0
12 0.0
Name: Date, dtype: float64
Put it in a new column
df.assign(seconds=(df.Date - df.Date.dt.floor('D')).dt.total_seconds())
Date seconds
0 2017-03-01 00:00:00 0.0
1 2017-03-01 02:00:00 7200.0
2 2017-03-01 04:00:00 14400.0
3 2017-03-01 06:00:00 21600.0
4 2017-03-01 08:00:00 28800.0
5 2017-03-01 10:00:00 36000.0
6 2017-03-01 12:00:00 43200.0
7 2017-03-01 14:00:00 50400.0
8 2017-03-01 16:00:00 57600.0
9 2017-03-01 18:00:00 64800.0
10 2017-03-01 20:00:00 72000.0
11 2017-03-01 22:00:00 79200.0
12 2017-03-02 00:00:00 0.0
Author by
Fanylion
Updated on June 05, 2022Comments
-
Fanylion almost 2 years
I have a pandas dataframe of multiple columns with a column of datetime64[ns] data. Time is in HH:MM:SS format. How can I convert this column of dates into a column of seconds elapsed? Like if the time said 10:00:00 in seconds that would be 36000. The seconds should be in a float64 type format.
Example data column
-
Fanylion almost 7 yearsI am importing the datetime module as
from datetime import datetime.
I get the 'DatetimeProperties' object has no attribute 'total_seconds' error. How to resolve this? -
FdMon almost 7 yearstry that: df['time'] = pd.to_datetime(df['time'])
-
Fanylion almost 7 yearsWhat if some of my date columns have an object attribute? Some of my files when imported have an object dtype instead of a datetime type even though its the same time format.
-
piRSquared almost 7 years@Fanylion then pre process with
df.Date = pd.to_datetime(df.Date)
-
Fanylion almost 7 yearsHmm. When I do that I get <class 'datetime.time'> is not convertible to datetime. Could it be with how I am importing my module (from datetime import datetime).
-
piRSquared almost 7 yearsI think your data is not what you said it was. I won't spend more time guessing your data when you can easily edit your post and show me the data.
-
Fanylion almost 7 yearsI added a picture of my data. The whole length of the column is very long so I couldn't post it all. The type of that column is pandas.core.series.Series and each individual element is a datetime.time type.
-
piRSquared almost 7 years@Fanylion and what does
df.dtypes
show? By the way, its better to paste text rather than a picture. -
piRSquared almost 7 yearsIn your question you stated the column was
datetime64[ns]
. Hopefully my edited post helps you out. -
Fanylion almost 7 yearsI did. I realized after the fact that one of my other columns was not giving the same date format.
-
Fanylion almost 7 yearsI found the solution to my problems here: stackoverflow.com/questions/35241643/…
-
Krissh over 4 years@FdMon I am getting same error as Fanylion It is still not resolved using pd.to_datetime()