Currently using python to create the JSON, here is a snippet of my output:

"{\"ownerName\":{\"0\":\"VANGUARD GROUP INC\",\"1\":\"BLACKROCK INC.\"

...and so on

The code I've used is below:

import requests
import pandas as pd
import json

headers = {
    'accept': 'application/json, text/plain, */*',
    'origin': 'https://www.nasdaq.com',
'User-Agent': 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.5112.79 Safari/537.36'
}

pd.set_option('display.max_columns', None)
pd.set_option('display.max_colwidth', None)

url = 'https://api.nasdaq.com/api/company/AAPL/institutional-holdings?limit=10&offset=0&type=TOTAL&sortColumn=marketValue&sortOrder=DESC'
r = requests.get(url, headers=headers)
df = pd.json_normalize(r.json()['data']['holdingsTransactions']['table']['rows'])
df1 = df.replace("\ "," ")
df2 = df1.to_json()

with open('AAPL_institutional_table_MRKTVAL.json', 'w') as f:
    json.dump(df2, f)

I included the line df2 = df1.to_json() otherwise without it the "JSON is not steralizable". I have also attempted to include df1 = df.replace("\ "," ") as an amateur approach to replace the backslahses with nothing, but still no luck.

CodePudding user response：

You're double-encoding the Json, so that's why you have the escaped output. Try:

import requests
import pandas as pd
import json

headers = {
    "accept": "application/json, text/plain, */*",
    "origin": "https://www.nasdaq.com",
    "User-Agent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/104.0.5112.79 Safari/537.36",
}

pd.set_option("display.max_columns", None)
pd.set_option("display.max_colwidth", None)

url = "https://api.nasdaq.com/api/company/AAPL/institutional-holdings?limit=10&offset=0&type=TOTAL&sortColumn=marketValue&sortOrder=DESC"
r = requests.get(url, headers=headers)
df = pd.json_normalize(
    r.json()["data"]["holdingsTransactions"]["table"]["rows"]
)

df.to_json("AAPL_institutional_table_MRKTVAL.json", indent=4)  # <-- write `df` directly to file as Json

Creates AAPL_institutional_table_MRKTVAL.json:

{
    "ownerName":{
        "0":"VANGUARD GROUP INC",
        "1":"BLACKROCK INC.",
        "2":"BERKSHIRE HATHAWAY INC",
        "3":"STATE STREET CORP",
        "4":"FMR LLC",
        "5":"GEODE CAPITAL MANAGEMENT, LLC",
        "6":"PRICE T ROWE ASSOCIATES INC \/MD\/",
        "7":"MORGAN STANLEY",
        "8":"NORTHERN TRUST CORP",
        "9":"BANK OF AMERICA CORP \/DE\/"
    },
    "date":{
        "0":"09\/30\/2022",
        "1":"09\/30\/2022",
        "2":"09\/30\/2022",
        "3":"09\/30\/2022",
        "4":"09\/30\/2022",
        "5":"09\/30\/2022",
        "6":"09\/30\/2022",
        "7":"09\/30\/2022",
        "8":"09\/30\/2022",
        "9":"09\/30\/2022"
    },
    "sharesHeld":{
        "0":"1,272,378,901",
        "1":"1,020,245,185",
        "2":"894,802,319",
        "3":"591,543,874",
        "4":"350,900,116",
        "5":"279,758,518",
        "6":"224,863,541",
        "7":"182,728,771",
        "8":"176,084,862",
        "9":"142,260,591"
    },
    "sharesChange":{
        "0":"-4,940,153",
        "1":"-8,443,132",
        "2":"0",
        "3":"-6,634,650",
        "4":"6,582,142",
        "5":"1,502,326",
        "6":"-13,047,242",
        "7":"278,206",
        "8":"-3,744,060",
        "9":"-6,873,324"
    },
    "sharesChangePCT":{
        "0":"-0.387%",
        "1":"-0.821%",
        "2":"0%",
        "3":"-1.109%",
        "4":"1.912%",
        "5":"0.54%",
        "6":"-5.484%",
        "7":"0.152%",
        "8":"-2.082%",
        "9":"-4.609%"
    },
    "marketValue":{
        "0":"$192,498,204",
        "1":"$154,352,894",
        "2":"$135,374,643",
        "3":"$89,494,673",
        "4":"$53,087,679",
        "5":"$42,324,666",
        "6":"$34,019,605",
        "7":"$27,645,036",
        "8":"$26,639,879",
        "9":"$21,522,605"
    },
    "url":{
        "0":"\/market-activity\/institutional-portfolio\/vanguard-group-inc-61322",
        "1":"\/market-activity\/institutional-portfolio\/blackrock-inc-711679",
        "2":"\/market-activity\/institutional-portfolio\/berkshire-hathaway-inc-54239",
        "3":"\/market-activity\/institutional-portfolio\/state-street-corp-6697",
        "4":"\/market-activity\/institutional-portfolio\/fmr-llc-12407",
        "5":"\/market-activity\/institutional-portfolio\/geode-capital-management-llc-396991",
        "6":"\/market-activity\/institutional-portfolio\/price-t-rowe-associates-inc-md-2145",
        "7":"\/market-activity\/institutional-portfolio\/morgan-stanley-5929",
        "8":"\/market-activity\/institutional-portfolio\/northern-trust-corp-10923",
        "9":"\/market-activity\/institutional-portfolio\/bank-of-america-corp-de-15519"
    }
}