python pandas converting UTC integer to datetime-CodePudding

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

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.

CodePudding user response：

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

CodePudding user response：

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')