0

I am calling some financial data from an API which is storing the time values as (I think) UTC (example below):

enter image description here

I cannot seem to convert the entire column into a useable date, I can do it for a single value using the following code so I know this works, but I have 1000's of rows with this problem and thought pandas would offer an easier way to update all the values.

from datetime import datetime
tx = int('1645804609719')/1000
print(datetime.utcfromtimestamp(tx).strftime('%Y-%m-%d %H:%M:%S'))

Any help would be greatly appreciated.

Cunnial
  • 1
  • 3
  • 2
    UTC is a timezone, not a storage format. There's no UTC integer. `1645804609719` is most likely a Unix timestamp. What's the expected date ? – Panagiotis Kanavos Feb 28 '22 at 16:49
  • Hi Panagiotis, thanks for the quick reply! so using the latest entry in the output and the code above ```tx = int('1646044696278')/1000 ``` I get: 2022-02-28 10:38:16. – Cunnial Feb 28 '22 at 16:55
  • Does this answer your question? [Convert unix time to readable date in pandas dataframe](https://stackoverflow.com/questions/19231871/convert-unix-time-to-readable-date-in-pandas-dataframe) – Panagiotis Kanavos Feb 28 '22 at 16:56
  • 1
    You can use `df['date'] = pd.to_datetime(df['date'],unit='ms')` to convert the millisecond-based Unix timestamp to a datetime64 – Panagiotis Kanavos Feb 28 '22 at 16:58
  • This worked thank you for such a quick reply. I was going nuts as newbie to coding! – Cunnial Feb 28 '22 at 17:04

2 Answers2

0

You can use "to_numeric" to convert the column in integers, "div" to divide it by 1000 and finally a loop to iterate the dataframe column with datetime to get the format you want.

import pandas as pd
import datetime

df = pd.DataFrame({'date': ['1584199972000', '1645804609719'], 'values': [30,40]})
df['date'] = pd.to_numeric(df['date']).div(1000)
for i in range(len(df)):
    df.iloc[i,0] = datetime.utcfromtimestamp(df.iloc[i,0]).strftime('%Y-%m-%d %H:%M:%S')
print(df)

Output:

                  date  values
0  2020-03-14 15:32:52      30
1  2022-02-25 15:56:49      40
Edoardo Berardo
  • 142
  • 2
  • 9
  • Hi Edoardo, thanks for this, however it converts the time column to 1970, which is one of many problem I was facing when first trying to solve this: ```1970-01-01 00:27:26.070645411``` – Cunnial Feb 28 '22 at 17:57
  • Hi Cunnial, I updated the code. I think for this format you must iterate with a loop. – Edoardo Berardo Mar 01 '22 at 11:22
0

Simply use pandas.DataFrame.apply:

df['date'] = df.date.apply(lambda x: datetime.utcfromtimestamp(int(x)/1000).strftime('%Y-%m-%d %H:%M:%S'))

Another way to do it is by using pd.to_datetime as recommended by Panagiotos in the comments:

df['date'] = pd.to_datetime(df['date'],unit='ms')
Mohammad Ayoub
  • 379
  • 2
  • 9