CODEWITHSUNDEEP
pythonpandasdataframe
schywalker
schywalker

Reputation: 113

Convert column of timestamp with a different UTC base to current UTC using Python

I have a dataframe with timestamps that are number of seconds since Jan 1,2010 midnight UTC time zone. I need to convert them to the present UTC time. I am able to do that for a given row using timedelta but not able to implement that for the entire timestamp column.

    # The base of my timestamp is UTC(2010 Jan 1, midnight)
    # able to do it for a specific delta i.e. df["timestamp][0]
    sec_shift = pd.Timedelta(seconds=201852000)
    new_time = datetime.datetime(2010,1,1)+sec_shift

    # how do i do this for the entire df["timestamp"] column?
    df = pd.DataFrame({"timestamp":[201852000,201852060,201852120,201852180,201852240], "B":[160863892,160864264,160864637,160865009,160865755]})

Upvotes: 4

Views: 823

Answers (4)

schywalker
schywalker

Reputation: 113

I came up with this solution that works, but if there is a better solution please update. It would be great to find better ways of doing this. 

    # Calculating the seconds between 1/1/1970 - 1/1/2010
    # Adding that to the timestamp column to get the seconds from 1/1/1970
    seconds_from_2010= (datetime.datetime(2010,01,01,0,0) - datetime.datetime(1970,1,1)).total_seconds()
    df["new_timestamp"]= pd.to_datetime(df["timestamp"]+seconds_from_2010,unit='s')

Upvotes: 4

piRSquared
piRSquared

Reputation: 294308

df = pd.DataFrame(range(201851000, 201852000, 100), columns=['seconds'])
df['timedelta'] = df.seconds.apply(lambda x: pd.Timedelta(seconds=x))
df['UpdatedTimestamp'] = df.timedelta + pd.Timestamp('2010-01-01')

df

enter image description here

Upvotes: 2

unutbu
unutbu

Reputation: 879671

You can add a Series of Timedeltas to a Timestamp:

df['date'] = pd.Timestamp('2010-1-1')+pd.to_timedelta(df['timestamp'], unit='s')

For example,

import pandas as pd

df = pd.DataFrame({"timestamp":[201852000,201852060,201852120,201852180,201852240], 
                   "B":[160863892,160864264,160864637,160865009,160865755]})

df['date'] = pd.Timestamp('2010-1-1')+pd.to_timedelta(df['timestamp'], unit='s')
print(df)

yields

           B  timestamp                date
0  160863892  201852000 2016-05-25 06:00:00
1  160864264  201852060 2016-05-25 06:01:00
2  160864637  201852120 2016-05-25 06:02:00
3  160865009  201852180 2016-05-25 06:03:00
4  160865755  201852240 2016-05-25 06:04:00

Upvotes: 4

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210852

try this:

dif = (datetime.datetime(2010,1,1) - datetime.datetime(1970,1,1)).total_seconds()
sec_shift = 4*60*60
pd.to_datetime(df.timestamp + diff + sec_shift, unit='s')

demo:

In [29]: pd.to_datetime(df.timestamp + dif + sec_shift, unit='s')
Out[29]:
0   2016-05-25 10:00:00
1   2016-05-25 10:01:00
2   2016-05-25 10:02:00
3   2016-05-25 10:03:00
4   2016-05-25 10:04:00
Name: timestamp, dtype: datetime64[ns]

PS i would recommend you to use standard solutions, for example to store number of seconds since Jan 1,1970 midnight UTC (Standard UNIX timestamp) - this will make your work bit simpler

Upvotes: 4

Related Questions