How to get correct date format from JSON string in Python?-CodePudding

I am trying to get some data from a JSON url using Python and convert in Pandas PD. Everything is working OK. Only there is a column for date. It is coming weired. How can I format it into correct date format? My code is given below:

sym_1 = 'NIFTY'
headers_gen = {"accept-encoding": "gzip, deflate, br",
               "accept-language": "en-US,en;q=0.9",
               "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.54 Safari/537.36"}

def PCR(sym_1):
    url_pcr = "https://opstra.definedge.com/api/futures/pcr/chart/"   sym_1

    req_pcr = requests.get(url_pcr, headers=headers_gen)
    text_data_pcr= req_pcr.text
    json_dict_pcr= json.loads(text_data_pcr)

    df_pcr = pd.DataFrame.from_dict(json_dict_pcr['data'])
    print(df_pcr)
    return df_pcr

CodePudding user response：

pd.to_datetime(..., unit="ms") fixes things.

I also simplified the requests code a tiny bit and added error handling.

import pandas as pd
import requests

headers_gen = {
    "accept-encoding": "gzip, deflate",
    "accept-language": "en-US,en;q=0.9",
    "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.54 Safari/537.36",
}


def PCR(sym_1):
    req_pcr = requests.get(f"https://opstra.definedge.com/api/futures/pcr/chart/{sym_1}", headers=headers_gen)
    req_pcr.raise_for_status()
    data = req_pcr.json()
    df_pcr = pd.DataFrame.from_dict(data['data'])
    df_pcr[0] = pd.to_datetime(df_pcr[0], unit='ms')
    return df_pcr


if __name__ == '__main__':
    print(PCR('NIFTY'))

outputs

                      0         1         2  ...         6         7         8
0   2019-04-26 05:30:00  11813.50  1.661348  ...       NaN       NaN       NaN
1   2019-04-30 05:30:00  11791.55  1.587803  ...       NaN       NaN       NaN
2   2019-05-02 05:30:00  11765.40  1.634619  ...       NaN       NaN       NaN
..                  ...       ...       ...  ...       ...       ...       ...
735 2022-04-18 00:00:00  17229.60  1.169555  ...  0.963420  0.771757  1.328892
736 2022-04-19 00:00:00  16969.35  1.014768  ...  1.385167  0.980847

CodePudding user response：

sym_1 = 'NIFTY'
headers_gen = {"accept-encoding": "gzip, deflate, br",
               "accept-language": "en-US,en;q=0.9",
               "user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.54 Safari/537.36"}

def PCR(sym_1):
    url_pcr = "https://opstra.definedge.com/api/futures/pcr/chart/"   sym_1

    req_pcr = requests.get(url_pcr, headers=headers_gen)
    text_data_pcr= req_pcr.text
    json_dict_pcr= json.loads(text_data_pcr)

    df_pcr = pd.DataFrame.from_dict(json_dict_pcr['data'])
    df_pcr[0] = df_pcr[0].apply(lambda x: datetime.utcfromtimestamp(x / 1000))

    print(df_pcr)
    return df_pcr

Updated to use apply and return datetime instead of string but AKX's answer is much more elegant