Converting a List of Dictionaries to Pandas Dataframe

Question

I have this code:

import urllib.request, json
url = "https://api.wto.org/timeseries/v1/indicator_categories?lang=1"

hdr ={
    # Request headers
    'Cache-Control': 'no-cache',
    'Ocp-Apim-Subscription-Key': '21cda66d75fc4010b8b4d889f4af6ccd',
    }

req = urllib.request.Request(url, headers=hdr)

req.get_method = lambda: 'GET'
response = urllib.request.urlopen(req)
    #print(response.getcode())
var = print(response.read().decode('ASCII'))

I tried this:

import pandas as pd
df = pd.DataFrame(var)

I am only getting an empty dataframe. The data is lost somehow. What am I doing wrong?

Also tried:

var = (response.read().decode('ASCII'))

This returns Byte String which I am not able to convert using eval():

eval(var)

NameError: name 'null' is not defined

I don't know how to proceed.

Answer 1

import urllib.request, json
import pandas as pd

url = "https://api.wto.org/timeseries/v1/indicator_categories?lang=1"
hdr = {
    'Cache-Control': 'no-cache',
    'Ocp-Apim-Subscription-Key': '21cda66d75fc4010b8b4d889f4af6ccd',
}

# Prepare and make the request
req = urllib.request.Request(url, headers=hdr)
with urllib.request.urlopen(req) as response:
    data = response.read().decode('utf-8')  # Decode response

# Load JSON data
json_data = json.loads(data)

# Determine if the JSON data is a list or a dictionary
if isinstance(json_data, dict):
    # If the JSON is a dictionary, you might want to access a particular key
    # Adjust 'your_key_here' to the actual key you need, e.g., 'results'
    if 'your_key_here' in json_data:
        data_to_load = json_data['your_key_here']
        df = pd.DataFrame(data_to_load)
    else:
        print("Expected key not found in JSON data")
elif isinstance(json_data, list):
    # If the JSON is directly a list
    df = pd.DataFrame(json_data)
else:
    print("Unexpected JSON structure")

print(df.head())  # Display the first few rows to verify