如何将指定CSV数据转换为定制化嵌套JSON字典结构？

可行性说明 #

完全可行。给定的CSV数据包含目标JSON结构所需的全部字段信息，转换规则清晰明确，通过数据分组、字段映射即可完成格式转换，无需额外补充数据。

实现步骤（以Python为例） #

用Python的csv和json模块即可快速完成转换，具体实现如下：

1. 导入依赖模块 #

import csv
import json

2. 读取CSV并构建目标JSON结构 #

# 初始化结果容器
result = {"platform": {}}

# CSV原始数据（也可替换为读取本地CSV文件的逻辑）
csv_content = """platform_region_combined,platform,cloudregion,on_demand_price_usd,on_demand_price_eur,capacity_storage_price_usd,capacity_storage_price_eur,standard_tier_price_eur,standard_tier_price_usd,standard_tier_price_gbp,enterprise_tier_price_eur,enterprise_tier_price_usd,enterprise_tier_price_gbp,business-critical_tier_price_eur,business-critical_tier_price_usd,business-critical_tier_price_gbp
amazonwebservicesaws_asiapacificmumbai,amazonwebservicesaws,asiapacificmumbai,46.00,38.33,25.00,20.83,1.83,2.20,,2.75,3.30,,3.67,4.40,
amazonwebservicesaws_asiapacificosaka,amazonwebservicesaws,asiapacificosaka,46.00,38.33,25.00,20.83,2.38,2.85,,3.58,4.30,,4.75,5.70,
amazonwebservicesaws_asiapacificseoul,amazonwebservicesaws,asiapacificseoul,46.00,38.33,25.00,20.83,2.29,2.75,,3.38,4.05,,4.58,5.50,
amazonwebservicesaws_asiapacificsingapore,amazonwebservicesaws,asiapacificsingapore,46.00,38.33,25.00,20.83,2.08,2.50,,3.08,3.70,,3.38,5.00,
amazonwebservicesaws_asiapacificsydney,amazonwebservicesaws,asiapacificsydney,46.00,38.33,25.00,20.83,2.29,2.75,,3.38,4.05,,4.58,5.50,
googlecloudplatform_europewest2london,googlecloudplatform,europewest2london,40.00,33.33,23.00,19.17,2.25,2.70,,3.33,4.00,,4.50,5.40,
googlecloudplatform_europewest4netherlands,googlecloudplatform,europewest4netherlands,35.00,33.33,20.00,19.17,2.17,2.60,,3.25,3.90,,4.33,5.20,
googlecloudplatform_uscentral1iowa,googlecloudplatform,uscentral1iowa,35.00,29.17,20.00,16.67,1.67,2.00,,2.50,3.00,,3.33,4.00,
microsoftazure_centralusiowa,microsoftazure,centralusiowa,40.00,33.33,23.00,19.17,1.67,2.00,,2.50,3.00,,3.33,4.00,
microsoftazure_eastus2virginia,microsoftazure,eastus2virginia,40.00,33.33,23.00,19.17,1.67,2.00,,2.50,3.00,,3.33,4.00,
microsoftazure_japaneasttokyo,microsoftazure,japaneasttokyo,46.00,38.33,25.00,20.83,2.38,2.85,,3.58,4.30,,4.75,5.70,"""

# 解析CSV内容
reader = csv.DictReader(csv_content.splitlines())

for row in reader:
    platform = row["platform"]
    region = row["cloudregion"]
    
    # 初始化平台和区域层级结构
    if platform not in result["platform"]:
        result["platform"][platform] = {}
    if region not in result["platform"][platform]:
        result["platform"][platform][region] = {}
    
    # 构建按需价格结构
    result["platform"][platform][region]["on_demand_price"] = {
        "usd": row["on_demand_price_usd"] or "",
        "eur": row["on_demand_price_eur"] or "",
        "gbp": row.get("on_demand_price_gbp", "") or ""
    }
    
    # 构建存储容量价格结构
    result["platform"][platform][region]["capacity_storage_price"] = {
        "usd": row["capacity_storage_price_usd"] or "",
        "eur": row["capacity_storage_price_eur"] or "",
        "gbp": row.get("capacity_storage_price_gbp", "") or ""
    }
    
    # 构建层级价格结构
    result["platform"][platform][region]["tier"] = {
        "standard": {
            "eur": row["standard_tier_price_eur"] or "",
            "usd": row["standard_tier_price_usd"] or "",
            "gbp": row["standard_tier_price_gbp"] or ""
        },
        "enterprise": {
            "eur": row["enterprise_tier_price_eur"] or "",
            "usd": row["enterprise_tier_price_usd"] or "",
            "gbp": row["enterprise_tier_price_gbp"] or ""
        },
        "business-critical": {
            "eur": row["business-critical_tier_price_eur"] or "",
            "usd": row["business-critical_tier_price_usd"] or "",
            "gbp": row["business-critical_tier_price_gbp"] or ""
        }
    }

# 输出格式化后的JSON
print(json.dumps(result, indent=2))

代码核心逻辑说明 #

• 分组处理：按platform和cloudregion逐层初始化字典，确保每个平台和区域的结构唯一。
• 字段映射：将CSV中带货币后缀的字段，对应到目标结构的指定层级下。
• 空值处理：用or ""处理CSV中的空字段，避免出现None值，保证JSON格式规范。

其他实现方式 #

也可使用jq命令行工具（适用于Linux/macOS环境），先将CSV转为JSON，再通过jq的过滤语法重组层级结构，不过Python实现更直观，便于后续调整逻辑。

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