如何将Google Analytics API响应转换为Pandas DataFrame

你可以通过解析API返回的RunReportResponse结构，提取维度、指标的字段名以及对应行数据，直接构建Pandas DataFrame。以下是修改后的完整代码：

import pandas as pd
from google.analytics.data_v1beta import BetaAnalyticsDataClient
from google.analytics.data_v1beta.types import (
    DateRange,
    Dimension,
    Metric,
    RunReportRequest,
)

def sample_run_report(credentials=None, property_id="YOUR-GA4-PROPERTY-ID"):
    """Runs a simple report on a Google Analytics 4 property and returns as DataFrame."""

    client = BetaAnalyticsDataClient(credentials=credentials)
    dates = [DateRange(start_date="7daysAgo", end_date='today')]
    metrics = [Metric(name='activeUsers')]
    # 取消注释下方启用城市维度
    # dimensions = [Dimension(name='city')]
    dimensions = []
    
    request = RunReportRequest(
        property=f'properties/{property_id}',
        metrics=metrics,
        dimensions=dimensions,
        date_ranges=dates,
    )

    response = client.run_report(request)
    
    # 提取列名：维度列 + 指标列
    dim_cols = [dh.name for dh in response.dimension_headers]
    metric_cols = [mh.name for mh in response.metric_headers]
    all_cols = dim_cols + metric_cols
    
    # 提取每行数据：维度值 + 指标值
    rows_data = []
    for row in response.rows:
        dim_vals = [dv.value for dv in row.dimension_values]
        metric_vals = [mv.value for mv in row.metric_values]
        rows_data.append(dim_vals + metric_vals)
    
    # 构建DataFrame
    df = pd.DataFrame(rows_data, columns=all_cols)
    # 可选：将指标列转换为数值类型
    for col in metric_cols:
        df[col] = pd.to_numeric(df[col])
    
    return df

# 调用示例
# df = sample_run_report(credentials=your_credentials, property_id="YOUR-GA4-ID")
# print(df)

关键解析： #

• 列名提取：从response.dimension_headers和response.metric_headers中获取维度、指标的名称，作为DataFrame的列。
• 行数据提取：遍历response.rows，每个Row对象包含dimension_values和metric_values，分别提取对应值后合并成一行数据。
• 类型转换：指标值默认是字符串，通过pd.to_numeric转为数值类型，方便后续分析。

如果启用维度（比如取消dimensions = [Dimension(name='city')]的注释），代码会自动将维度列加入DataFrame，无需额外修改逻辑。

内容的提问来源于stack exchange，提问作者Linda Lawton - DaImTo