CODEWITHSUNDEEP
pythonpandasdatetimetimestamp
zob
zob

Reputation: 169

Adding and subtracting seconds column from timestamp column pandas

import pandas as pd
import numpy as np 
example=[["11/19/20","9:40:28","9:40:00:0","00:00:00.2","101"],
     ["12/22/20","9:29:28","9:29:28:15", "00:10:28.0","102"],
     ["2/17/21","9:20:20","9:20:20:2","0:00:05.2","206"]]

example_table= pd.DataFrame(example,columns=["Date","Start_Time","timestamp","Seconds","ID"])

desired_info=[["11/19/20","9:40:28","9:40:00:0","00:00:00.2","101", "9:40:00:2"],
     ["12/22/20","9:29:28","9:29:28:15", "00:10:28.0","102", "9:40:56:15"],
     ["2/17/21","9:20:20","9:20:20:2","0:00:05.2","206","9:20:25:4"]]

desired_table= pd.DataFrame(desired_info,columns=["Date","Start_Time","timestamp","Seconds", "CID","Finish_Time"])

# I can convert one of my time columns 
example_table.Seconds=example_table.Seconds.apply(pd.to_timedelta)
example_table.Seconds=example_table.Seconds.dt.total_seconds()
example_table['Start_Time']=pd.to_datetime(example_table['Start_Time'], format= '%H:%M:%S').dt.time

Ultimately, I want to be able to add the seconds column which contains miliseconds to the timestamp column.

When I try the following:

example_table["Finish"]=example_table['timestamp']+example_table['Seconds']

# I get the error: 
# can only concatenate str (not "float") to str

Since I'm getting desperate, so I think, maybe I can settle for using the Start_Time in the calculation instead.

# if I try with the Start_Time column:
["Finish"]=example_table['Start_Time']+example_table['Seconds']

# unsupported operand type(s) for +: 'datetime.time' and 'float'

So Next I try to convert the timestamp column using different strategies. ```# when I try to convert the timestamp column, I get many different errors depending on the strategy

pd.to_timedelta(example_table.timestamp, unit='ms')
#Error: unit must not be specified if the input contains a str
pd.to_timedelta(example_table.timestamp)
#Error: expected hh:mm:ss format```

Eventually, I will use the Finish Time, which is really my offset time in another experiment to find other information as shown here, Find a subset of columns based on another dataframe?

Upvotes: 1

Views: 946

Answers (1)

Umar.H
Umar.H

Reputation: 23099

first you need to create a proper datetime object.

df = example_table

df['desired_date'] = pd.to_datetime(df['Date'] + ' ' 
                     + df['timestamp'],format='%m/%d/%y %H:%M:%S:%f')

then convert the Seconds column into a timesdelta and add it to the desired date.

we'll have to add some formatting to get your target string format.

df['desired_date'] =  (
 df['desired_date'] 
 + 
 pd.to_timedelta(df['Seconds'])
 ).dt.strftime('%H:%M:%S:%f').str.rstrip('0')


print(df)

       Date Start_Time   timestamp     Seconds   ID desired_date
0  11/19/20    9:40:28   9:40:00:0  00:00:00.2  101   09:40:00:2
1  12/22/20    9:29:28  9:29:28:15  00:10:28.0  102  09:39:56:15
2   2/17/21    9:20:20   9:20:20:2   0:00:05.2  206   09:20:25:4

Upvotes: 2

Related Questions