1

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
Improve this question

2 Answers 2

Reset to default
1

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
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0 
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).astimezone(pytz.timezone('Asia/Kolkata')))

    print(df_pcr)
    return df_pcr

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

Updated to use IST

Improve this answer

4 Comments

This probably should use apply, not iterrows, and OP probably wants a datetime, not a string?
@AKX tbf i had never used python before. i'm just trying to answer some random questions to get enough reputation to post comments
Great. I saw the date and time is of GMT. Can we convert it to IST (GMT+5:30) ??
@IndrajitMukherjee I updated the answer to use IST

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.