3

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

Example data column

Fanylion
  • 364
  • 2
  • 5
  • 14

2 Answers2

5

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
piRSquared
  • 285,575
  • 57
  • 475
  • 624
  • What 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. – Fanylion Jun 08 '17 at 21:15
  • @Fanylion then pre process with `df.Date = pd.to_datetime(df.Date)` – piRSquared Jun 08 '17 at 21:17
  • Hmm. When I do that I get is not convertible to datetime. Could it be with how I am importing my module (from datetime import datetime). – Fanylion Jun 08 '17 at 21:24
  • 1
    I 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. – piRSquared Jun 08 '17 at 21:33
  • I 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. – Fanylion Jun 08 '17 at 21:44
  • @Fanylion and what does `df.dtypes` show? By the way, its better to paste text rather than a picture. – piRSquared Jun 08 '17 at 21:45
  • In your question you stated the column was `datetime64[ns] `. Hopefully my edited post helps you out. – piRSquared Jun 08 '17 at 21:56
  • I did. I realized after the fact that one of my other columns was not giving the same date format. – Fanylion Jun 08 '17 at 22:00
  • I found the solution to my problems here: https://stackoverflow.com/questions/35241643/convert-datetime-time-into-datetime-timedelta-in-python-3-4 – Fanylion Jun 08 '17 at 22:22
-1

it would work:

df['time'].dt.total_seconds()

regards

FdMon
  • 121
  • 9
  • 1
    I 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? – Fanylion Jun 08 '17 at 20:57
  • try that: df['time'] = pd.to_datetime(df['time']) – FdMon Jun 08 '17 at 21:05
  • @FdMon I am getting same error as Fanylion It is still not resolved using pd.to_datetime() – Krissh Jan 15 '20 at 06:27