json - How to Convert API as Pandas readable

Question

I have a live stock API for ML implementation i'm using Google colab as the environment and i have established contact with API and the status is success and i have received a json text file and i have parsed as a .json() and make as viewable and indented.

{
   "Meta Data": {
    "1. Information": "Daily Prices and Volumes for Digital Currency",
    "2. Digital Currency Code": "BTC",
    "3. Digital Currency Name": "Bitcoin",
    "4. Market Code": "CNY",
    "5. Market Name": "Chinese Yuan",
    "6. Last Refreshed": "2021-01-22 00:00:00",
    "7. Time Zone": "UTC"
},
"Time Series (Digital Currency Daily)": {
    "2021-01-22": {
        "1a. open (CNY)": "199365.55938000",
        "1b. open (USD)": "30851.99000000",
        "2a. high (CNY)": "199443.03876000",
        "2b. high (USD)": "30863.98000000",
        "3a. low (CNY)": "188839.21986000",
        "3b. low (USD)": "29223.03000000",
        "4a. close (CNY)": "192933.34920000",
        "4b. close (USD)": "29856.60000000",
        "5. volume": "12132.72474600",
        "6. market cap (USD)": "12132.72474600"
    },
    "2021-01-21": {
        "1a. open (CNY)": "229195.70226000",
        "1b. open (USD)": "35468.23000000",
        "2a. high (CNY)": "230047.20000000",
        "2b. high (USD)": "35600.00000000",
        "3a. low (CNY)": "194318.80200000",
        "3b. low (USD)": "30071.00000000",
        "4a. close (CNY)": "199353.54006000",
        "4b. close (USD)": "30850.13000000",
        "5. volume": "131803.18292600",
        "6. market cap (USD)": "131803.18292600" 

Now QUESTION is How can we make this json file as pandas readable .Any Suggestion are welcomed

question from:https://stackoverflow.com/questions/65848125/how-to-convert-api-as-pandas-readable

Answer 1

read in the timeseries, then transpose(), T. Then rename your columns.

df = pd.DataFrame(d["Time Series (Digital Currency Daily)"]).T
df.columns = ['open CNY', 'open USD', 'high CNY', 'high USD',
   'low CNY', 'low USD', 'close CNY', 'close USD',
   'volume', 'market cap USD']

Output

                open CNY  open USD      high CNY  high USD       low CNY   low USD     close CNY close USD         volume market cap USD
2021-01-22  199365.55938  30851.99  199443.03876  30863.98  188839.21986  29223.03  192933.34920  29856.60   12132.724746   12132.724746
2021-01-21  229195.70226  35468.23  230047.20000  35600.00  194318.80200  30071.00  199353.54006  30850.13  131803.182926  131803.182926