Google Ads Stream JSON解析优化：70万条数据转CSV提速方案咨询

当前用Pandas解析Google Ads Stream输出的JSON并转CSV时，70万条数据用逐行循环拼接DataFrame的方式耗时过长，以下是针对该场景的优化方案：

示例JSON结构 #

[
{
  "results": [
    {
      "customer": {
        "resourceName": "customers/12345678900",
        "id": "12345678900",
        "descriptiveName": "ABC"
      },
      "campaign": {
        "resourceName": "customers/12345678900/campaigns/12345",
        "name": "Search_Google_Generic",
        "id": "12345"
      },
      "adGroup": {
        "resourceName": "customers/12345678900/adGroups/789789",
        "id": "789789",
        "name": "adgroup_details"
      },
      "metrics": {
        "clicks": "500",
        "conversions": 200,
        "costMicros": "90000000",
        "allConversionsValue": 5000.6936,
        "impressions": "50000"
      },
      "segments": {
        "device": "DESKTOP",
        "date": "2022-10-28"
      }
    }
  ],
  "fieldMask": "segments.date,customer.id,customer.descriptiveName,campaign.id,campaign.name,adGroup.id,adGroup.name,segments.device,metrics.costMicros,metrics.impressions,metrics.clicks,metrics.conversions,metrics.allConversionsValue",
  "requestId": "fdhfgdhfgjf"
}
]

现有代码的问题 #

原代码中使用df.append()在循环里逐行添加数据，每次append都会创建新的DataFrame对象，涉及大量内存复制，时间复杂度为O(n²)，数据量达70万时性能会急剧下降。

优化方案 #

方案1：先收集数据到列表，再批量转DataFrame #

先把所有行数据存入列表，最后一次性转换为DataFrame，避免频繁内存操作：

import pandas as pd

with open('Adgroups.json', encoding='utf-8') as inputfile:
    data = pd.read_json(inputfile)

# 初始化空列表存储所有行数据
rows = []
for results in data['results']:
    for result in results:
        row = {
            'Date': result['segments']['date'],
            'Account_ID': result['customer']['id'],
            'Account': result['customer']['descriptiveName'],
            'Campaign_ID': result['campaign']['id'],
            'Campaign': result['campaign']['name'],
            'Ad_Group_ID': result['adGroup']['id'],
            'Ad_Group': result['adGroup']['name'],
            'Device': result['segments']['device'],
            'Cost': int(result['metrics']['costMicros']) / 1000000,  # 可选：转换为实际货币单位
            'Impressions': int(result['metrics']['impressions']),
            'Clicks': int(result['metrics']['clicks']),
            'Conversions': result['metrics']['conversions'],
            'Conv_Value': result['metrics']['allConversionsValue']
        }
        rows.append(row)

# 一次性生成DataFrame
df_new = pd.DataFrame(rows)
df_new.to_csv('Adgroups.csv', encoding='utf-8', index=False)

方案2：使用json_normalize扁平化嵌套JSON #

利用Pandas的json_normalize直接处理嵌套结构，代码更简洁且效率更高：

import pandas as pd
from pandas import json_normalize

with open('Adgroups.json', encoding='utf-8') as inputfile:
    data = pd.read_json(inputfile)

# 提取所有results数据并扁平化
all_results = []
for item in data['results']:
    all_results.extend(item)

df = json_normalize(all_results)

# 重命名列并选择需要的字段
df_renamed = df.rename(columns={
    'segments.date': 'Date',
    'customer.id': 'Account_ID',
    'customer.descriptiveName': 'Account',
    'campaign.id': 'Campaign_ID',
    'campaign.name': 'Campaign',
    'adGroup.id': 'Ad_Group_ID',
    'adGroup.name': 'Ad_Group',
    'segments.device': 'Device',
    'metrics.costMicros': 'Cost',
    'metrics.impressions': 'Impressions',
    'metrics.clicks': 'Clicks',
    'metrics.conversions': 'Conversions',
    'metrics.allConversionsValue': 'Conv_Value'
})[['Date', 'Account_ID', 'Account', 'Campaign_ID','Campaign',
    'Ad_Group_ID', 'Ad_Group','Device',
    'Cost', 'Impressions', 'Clicks', 'Conversions', 'Conv_Value']]

# 可选：转换Cost为实际货币单位（微转元）
df_renamed['Cost'] = df_renamed['Cost'].astype(int) / 1000000

df_renamed.to_csv('Adgroups.csv', encoding='utf-8', index=False)

方案对比 #

• 方案1通过列表收集数据，避免了append的性能问题，适合需要自定义字段转换的场景。
• 方案2使用json_normalize原生支持嵌套JSON解析，代码更简洁，性能更优，是处理此类嵌套JSON的首选方案。

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