Can't convert Json to pandas df-CodePudding

It should be easy but I couldn't figure out how to convert the following json file to pandas df after one hour trying: https://www.sec.gov/files/company_tickers_exchange.json

It has following uncommon format:

{"fields":["cik","name","ticker","exchange"],"data":[[320193,"Apple Inc.","AAPL","Nasdaq"],[789019,"MICROSOFT CORP","MSFT","Nasdaq"],[1652044,"Alphabet...

The DF should have following columns:

cik, name, ticker, exchange

The entries underneath it would be:

320193,"Apple Inc.","AAPL","Nasdaq"
789019,"MICROSOFT CORP","MSFT","Nasdaq"
...

Together it would look like:

cik     name            ticker      exchange
320193  Apple Inc.      AAPL        Nasdaq
789019  MICROSOFT CORP  MSFT        Nasdaq
...

Thank you in advance for any help.

CodePudding user response：

Just point to the data section inside your json.
The data in the code below is a subset of the actual data.

import pandas as pd

data = [[320193,"Apple Inc.","AAPL","Nasdaq"],[789019,"MICROSOFT CORP","MSFT","Nasdaq"],[1652044,"Alphabet Inc.","GOOG","Nasdaq"],[1018724,"AMAZON COM INC","AMZN","Nasdaq"],[1326801,"Facebook Inc","FB","Nasdaq"]]

df = pd.DataFrame(data,columns=['id','name','short_name','other'])
print(df)

output

        id            name short_name   other
0   320193      Apple Inc.       AAPL  Nasdaq
1   789019  MICROSOFT CORP       MSFT  Nasdaq
2  1652044   Alphabet Inc.       GOOG  Nasdaq
3  1018724  AMAZON COM INC       AMZN  Nasdaq
4  1326801    Facebook Inc         FB  Nasdaq