MongoDB/CSV数据集按国家-城市层级重组的简便方法咨询

一、MongoDB聚合管道（最简便，数据库内直接处理） #

直接用MongoDB的聚合框架就能完成结构转换，不需要导出数据到外部工具，步骤如下：

执行以下Mongo Shell命令：

db.your_collection_name.aggregate([
  {
    $group: {
      _id: "$Country",
      cityData: {
        $push: {
          k: "$City",
          v: {
            _id: "$_id",
            "Country Code": "$Country Code",
            "Year": "$Year",
            "Wind Electricity Produced": "$Wind Electricity Produced",
            "Hydro Electricity Produced": "$Hydro Electricity Produced",
            "Solar Electricity Produced": "$Solar Electricity Produced",
            "Renewable bioenergy (TWh)": "$Renewable bioenergy (TWh)",
            "Total Percentage of Renewables": "$Total Percentage of Renewables",
            "Population Rank": "$Population Rank",
            "Population": "$Population",
            "Hydro ELECTRIC SHARE ONLY  (% electricity)": "$Hydro ELECTRIC SHARE ONLY  (% electricity)",
            "Hydro SHARE ALL (% equivalent primary energy)": "$Hydro SHARE ALL (% equivalent primary energy)",
            "Electricity CONSUMPTION From Hydro ": "$Electricity CONSUMPTION From Hydro ",
            "Wind SHARE ELECTRIC ONLY  (% electricity)": "$Wind SHARE ELECTRIC ONLY  (% electricity)",
            "Wind SHARE ALL  (% equivalent primary energy)": "$Wind SHARE ALL  (% equivalent primary energy)",
            "Electricty Generated From Wind ": "$Electricty Generated From Wind ",
            "Solar ELECTRICITY ONLY (% electricity)": "$Solar ELECTRICITY ONLY (% electricity)",
            "Solar SHARE ALL (% equivalent primary energy)": "$Solar SHARE ALL (% equivalent primary energy)",
            "Solar Electricity Usage": "$Solar Electricity Usage",
            "Air Pollution": "$Air Pollution",
            "Coordinates": "$Coordinates"
          }
        }
      }
    }
  },
  {
    $project: {
      _id: 0,
      Country: "$_id",
      City: { $arrayToObject: "$cityData" }
    }
  },
  // 可选：将结果写入新集合，方便后续查询
  { $out: "structured_energy_data" }
])

说明：

• 替换your_collection_name为你的目标集合名
• $group按国家分组，收集每个城市的键值对数据
• $arrayToObject将数组转换为以城市名为键的对象
• $out可将转换后的结果存入新集合，直接用于后续查询

二、Python pandas groupby 处理 #

如果习惯用Python处理数据，可以读取Mongo数据或直接读取CSV，通过groupby构造层级结构：

import pandas as pd
from pymongo import MongoClient
import json

# 连接MongoDB并读取数据
client = MongoClient('mongodb://localhost:27017/')
db = client['your_database_name']
collection = db['your_collection_name']
df = pd.DataFrame(list(collection.find()))

# 构建层级数据
structured_data = []
for country, country_group in df.groupby('Country'):
    city_dict = {}
    for _, row in country_group.iterrows():
        # 移除Country和City字段，保留其余数据
        city_data = row.drop(['Country', 'City']).to_dict()
        city_dict[row['City']] = city_data
    structured_data.append({
        'Country': country,
        'City': city_dict
    })

# 保存为JSON文件或写入Mongo
with open('structured_data.json', 'w') as f:
    json.dump(structured_data, f, indent=2)

三、JavaScript（Node.js 或 Mongo Shell 脚本） #

如果在Node.js环境下处理本地JSON文件，或者编写Mongo Shell脚本，示例如下：

// Node.js 示例（假设已读取flat_data.json文件）
const fs = require('fs');
const flatData = JSON.parse(fs.readFileSync('flat_data.json', 'utf8'));

const structuredMap = {};

flatData.forEach(item => {
    const { Country, City, ...cityData } = item;
    if (!structuredMap[Country]) {
        structuredMap[Country] = {
            Country: Country,
            City: {}
        };
    }
    structuredMap[Country].City[City] = cityData;
});

// 转换为数组格式（与示例结构一致）
const resultArray = Object.values(structuredMap);
fs.writeFileSync('structured_data.json', JSON.stringify(resultArray, null, 2));

方法对比 #

• MongoDB聚合：最简便，无需导出数据，直接在数据库内完成转换，适合后续直接基于新集合查询
• Python groupby：适合已经在Python生态中处理数据的场景，灵活度高，可配合其他数据清洗操作
• JavaScript：适合前端或Node.js环境下处理本地JSON文件的场景

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