CODEWITHSUNDEEP
python
robothead
robothead

Reputation: 323

Converting and inserting timestamp in pandas

I'm having an issue converting time. Column[0] is a timestamp, I want to insert a new column at[1] for now its called timestamp2. I'm trying to then use the for statement to convert column[0] to a readable time and add it to column[1]. Currently I get the new column inserted but I get this error:

raise TypeError(f"cannot convert the series to {converter}") TypeError: cannot convert the series to <class 'int'>

I added .astype(int) to the timestamp variable but that didn't help. Code:

import requests
import json
import pandas as pd
from datetime import datetime


url = 'https://us.market-api.kaiko.io/v2/data/trades.v1/exchanges/cbse/spot/btc-usd/aggregations/count_ohlcv_vwap?interval=1h&page_size=1000'

KEY = 'xxx'

headers = {
   "X-Api-Key": KEY,
   "Accept": "application/json",
   "Accept-Encoding": "gzip"
}

res = requests.get(url, headers=headers)
j_data = res.json()
parse_data = j_data['data']

# create dataframe
df = pd.DataFrame.from_dict(pd.json_normalize(parse_data), orient='columns')
df.insert(1, 'timestamp2', ' ')    
 
for index, row in df.iterrows(): 
    timestamp = df['timestamp'].astype(int)
    dt = datetime.fromtimestamp(timestamp)
    df.at[index, "timestamp2"] = dt
       
                 
             
print(df)

df.to_csv('test.csv', index=False, encoding='utf-8')

Parsed data:

timestamp,timestamp2,open,high,low,close,volume,price,count
1611169200000,5,35260,35260.6,35202.43,35237.93,7.1160681299999995,35231.58133242965,132
1611165600000,5,34861.78,35260,34780.26,35260,1011.0965832999998,34968.5318431902,11313
1611162000000,5,34730.11,35039.98,34544.33,34855.43,1091.5246025199979,34794.45207484006,12877

In this example I set 'df.at[index, "timestamp2"] = dt' to 5 just to make sure it inserted in each row, it does so I just need to convert column[0] to a readable time for column[1].

Upvotes: 0

Views: 464

Answers (1)

oli5679
oli5679

Reputation: 1749

If you convert the timestamp to integer, it seems to be milliseconds since the epoc based on the magnitudes of the values.

Here is some more details on unix-time if you are interested. https://en.wikipedia.org/wiki/Unix_time

You can convert this to datetime using pd.to_datetime.

It is a vectorised operation so you don't need to use the loop through the dataframe. Both pd.to_numeric and pd.to_datetime can be applied to an entire series.

It's hard to debug without all your data but the below should work. .astype(int) is an alternative to pd.to_numeric, the only difference is pd.to_numeric gives you more flexibility in the treatment of errors, allowing you to coerce to nan (not sure if this is wanted or not).

import pandas as pd
df = pd.DataFrame({'timestamp':['1611169200000']})
# convert to integer. If there are invalid entries this will set to nan. Depends on your case how you want to treat these.
timestamp_num = pd.to_numeric(df['timestamp'],errors='ignore')
df['timestamp2'] pd.to_datetime(timestamp_num,unit='ms')
print(df.to_dict())
#{'timestamp': {0: '1611169200000'}, 'timestamp2': {0: Timestamp('2021-01-20 19:00:00')}}

Upvotes: 1

Related Questions