Save Json file contents to CSV file in python/pandas

Question

How to get the "data" information into a csv table as shown at the end (and also, the right 'headers' so that the source server doesn't throw me off thinking I am scraping)? The code I wrote so far is as below.

import requests, json

headers = {'User-Agent': 'Mozilla/5.0'}
data_json = requests.get('https://www1.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json', headers=headers)
print(data_json)

file = open('make_csv', 'w')
file.write(str(data_json))
file.close()

But as the output I receive is as follows:

<Response [200]>

and even the exported/saved file shows the same.

Here is the expected output table that I am trying to achieve:

Symbol,Open,High,Low,Last Traded Price,Change,%Change,Traded Volume(lacs),Traded Value(crs),52 Week High,52 Week Low,365 Days % Change,30 Days % Change
"LUPIN","582.45","665.90","578.00","662.00","82.95","14.33","64.93","411.13","884.00","504.75","-14.88","5.11"
"APOLLOHOSP","1,094.20","1,239.45","1,088.05","1,195.00","106.15","9.75","23.97","280.36","1,813.55","1,047.05","-4.80","-30.87"
"SUNPHARMA","343.95","389.80","340.00","376.45","32.90","9.58","285.51","1,055.40","483.90","312.00","-19.85","1.88"
"CIPLA","425.00","454.70","416.25","448.00","34.25","8.28","179.07","793.22","586.00","355.30","-14.28","11.46"
"CESC","393.00","429.80","386.25","420.00","26.85","6.83","9.30","38.63","851.70","365.25","-42.19","-34.53"
"TORNTPHARM","1,979.00","2,113.00","1,950.00","2,090.00","131.00","6.69","10.13","208.87","2,287.25","1,452.00","10.56","-1.75"
"ITC","167.90","182.75","167.00","177.50","11.10","6.67","628.68","1,100.88","310.00","134.60","-40.42","-9.11"
"OIL","82.25","85.60","80.25","84.50","5.25","6.62","27.05","22.39","189.70","63.50","-53.95","-16.91"
..........
..........

αԋɱҽԃ αмєяιcαη · Accepted Answer · 2020-04-06 05:22:30Z

1

import requests
import pandas as pd

headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:74.0) Gecko/20100101 Firefox/74.0'
}


def main(url):
    r = requests.get(url, headers=headers).json()
    x = []
    for item in r['data']:
        df = pd.DataFrame.from_dict([item])
        x.append(df)
    new = pd.concat(x, ignore_index=True)
    print(new)
    new.to_csv("Data.csv")


main("https://www1.nseindia.com/live_market/dynaContent/live_watch/stock_watch/foSecStockWatch.json")

Output: view online

edited Apr 6, 2020 at 5:22

answered Apr 5, 2020 at 18:46

αԋɱҽԃ αмєяιcαη

11.6k3 gold badges23 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lokkii9 Over a year ago

Dear αԋɱҽԃ, thank you for the solution there.. but the csv file contains the following too..("noChg,adv,dec,data,totQty,totQtyMil,totValMil,time,totVal 1,43,100,"....") Data doesn't seem to be formated as a plain csv, but looks exactly like json..is it possible to only have data with headers like symbol,open,high.... rather than the exact data of the json file pls.. it would be very kind of you to help... thank you

Lokkii9 Over a year ago

Dear αԋɱҽԃ, thanks again.. and I received an error pls.. < Traceback (most recent call last): File "tester.py", line 20, in <module> main("www1.nseindia.com/live_market/dynaContent/live_watch/…) File "tester.py", line 13, in main df = pd.DataFrame.from_dict(item, index='orient') TypeError: from_dict() got an unexpected keyword argument 'index' >> I am sorry I am unsure where the error is... kindly guide me further on this..

αԋɱҽԃ αмєяιcαη Over a year ago

@Lokkii9 code updated. sorry i was typing from mobile.

Lokkii9 Over a year ago

Thank you so very much αԋɱҽԃ αмєяιcαη... really perfect code... appreciate it... :)

Collectives™ on Stack Overflow

Save Json file contents to CSV file in python/pandas

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related