如何将GoToWebinar API返回的JSON转换为Pandas DataFrame？

Got it, let's get this sorted for you! The issue here is that your JSON response has nested structures—specifically the webinars array inside _embedded, and each webinar has a times array holding start/end times. Regular from_dict or read_json calls struggle with this because they don't automatically flatten nested arrays into usable DataFrame columns.

最佳解决方案：使用Pandas的json_normalize #

Pandas' built-in json_normalize is made exactly for this kind of nested JSON data. It can flatten arrays and preserve associated metadata. Here are two approaches depending on your analysis needs:

方法1：每个Webinar一行（适合单一场次的研讨会）

If every webinar only has one entry in the times array, this will flatten the start/end times into the same row as the rest of the webinar details:

import pandas as pd

# 提取核心的webinars列表
webinars_data = webinars_json["_embedded"]["webinars"]

# 扁平化JSON并提取times里的时间
df = pd.json_normalize(webinars_data).assign(
    # 从times数组中提取第一个（也是唯一的）startTime和endTime
    startTime=lambda x: x["times"].str[0].str["startTime"],
    endTime=lambda x: x["times"].str[0].str["endTime"]
).drop(columns=["times"])  # 移除原始的嵌套times列

# 检查结果
print(df[["webinarId", "subject", "startTime", "endTime"]].head())

方法2：每个场次一行（适合系列研讨会）

If you have webinars with multiple sessions (multiple entries in times), this approach will expand each session into its own row while keeping all the webinar's metadata attached—this is usually better for trend analysis or session-level metrics:

import pandas as pd

# 提取核心的webinars列表
webinars_data = webinars_json["_embedded"]["webinars"]

# 展开times数组，同时保留所有webinar字段
df = pd.json_normalize(
    data=webinars_data,
    record_path="times",  # 指定要展开的嵌套数组
    meta=[  # 列出所有需要保留的webinar顶级字段
        "webinarKey", "webinarId", "organizerKey", "omid", "accountKey",
        "recurrenceKey", "subject", "description", "timeZone", "locale",
        "status", "approvalType", "registrationUrl", "impromptu",
        "isPasswordProtected", "recurrenceType", "experienceType",
        "registrationSettingsKey"
    ]
)

# 检查结果
print(df[["webinarId", "subject", "startTime", "endTime"]].head())

整合到你的现有代码 #

Here's how the full workflow looks with your existing API call code:

'''Getting the webinar lists'''
import pandas as pd
import requests

base_url = 'https://api.getgo.com/G2W/rest/v2'
##setting up paramters
param_1 = '2019-07-01T10%3A00%3A00Z'
param_2 = '2019-09-01T10%3A00%3A00Z'
##buidling the path
path = base_url + '/accounts/' + account_key + '/webinars?fromTime=' + param_1 +'&toTime=' + param_2
print(path)
headers = {'accept' : 'application/json' , 'Authorization' : access_token}

# 初始化会话并请求数据
session = requests.Session()
webinars_req = session.get(path, headers = headers)
webinars_json = webinars_req.json()

# 选择适合你的转换方法（二选一）
# --- 方法1：单一场次一行 ---
# df = pd.json_normalize(webinars_json["_embedded"]["webinars"]).assign(
#     startTime=lambda x: x["times"].str[0].str["startTime"],
#     endTime=lambda x: x["times"].str[0].str["endTime"]
# ).drop(columns=["times"])

# --- 方法2：每个场次一行 ---
df = pd.json_normalize(
    data=webinars_json["_embedded"]["webinars"],
    record_path="times",
    meta=[
        "webinarKey", "webinarId", "organizerKey", "omid", "accountKey",
        "recurrenceKey", "subject", "description", "timeZone", "locale",
        "status", "approvalType", "registrationUrl", "impromptu",
        "isPasswordProtected", "recurrenceType", "experienceType",
        "registrationSettingsKey"
    ]
)

# 现在可以进行后续分析了！
print(df.info())

为什么之前的方法失败？ #

• DataFrame.from_dict(webinars_json) tries to convert the entire JSON object (including _embedded and page) into a DataFrame, which results in a single row with nested values instead of expanding the webinars array.
• pd.read_json() expects a JSON string, but even if you pass webinars_req.text, it will treat the top-level object as a single entry rather than extracting the webinars array.

内容的提问来源于stack exchange，提问作者Elettra D