CSV到JSON Python，将组类别附加到字典

Question

我要用“我是一个使用糟糕的‘数据库’的傻瓜”来开始我要说的话。下面是我的csv中当前json输出的结构（大纲如下）。本质上，我要做的是将A列（信息技术）中的“group”附加到每个“data”字典中，所以有一个“group”键：看起来像“group”的值：“Information Technology”。那么第5行（非必需消费品）下的所有内容都将具有“组”：“非必需消费品”的键，值。在

{
  "stocks": [
    {
      "data": {
        "portfolio_average_weight": "5.985"
        "portfolio_total_return": "27.948"
      },
      "name": "Google Inc              "
    },
    {
      "data": {
        "portfolio_average_weight": "2.896",
        "portfolio_total_return": "24.292"
      },
      "name": "Mastercard Inc          "
    }]
}

Column A                           Column B         Column C        Column D

Information Technology           [blank cell]     [blank cell]     [blank cell]
[blank cell]                        Google            5.985           27.948
[blank cell]                     Mastercard           2.896           24.292
Consumer Discretionary           [blank cell]     [blank cell]     [blank cell]
[blank cell]                        xxxxxx         xxxxxxxxx          xxxxxxxxx

以下是我当前的代码：

^{pr2}$

我希望最终输出是什么样子：

{
  "stocks": [
    {
      "data": {
        "portfolio_average_weight": "5.985",
        "portfolio_total_return": "27.948",
        "group": "Information Technology"
      },
      "name": "Google Inc              "
    },
    {
      "data": {
        "portfolio_average_weight": "2.896",
        "portfolio_total_return": "24.292",
        "group": "Information Technology"
      },
      "name": "Mastercard Inc          "
    }]
}

以下是csv：

Information Technology,,,
,Google Inc              ,5.985,27.948
,Mastercard Inc          ,2.896,24.292
Consumer Discretionary,,,

Answer 1

我倾向于使用csv.DictReader而不是{}，因为生成的代码更易于阅读，再加上将每一行读入字典也使代码更加统一——尤其是在处理JSON对象时，JSON对象本身通常由一个或多个字典组成。在

import csv, json

with open('csv_to_json_test.csv', 'rb') as csvfile:
    csvfields = 'group', 'name', 'average_weight', 'total_return'
    reader = csv.DictReader(csvfile, fieldnames=csvfields)
    database = {}
    stocks = database['stocks'] = []  # initialize item to be parsed
    group = None
    for row in reader:
        if row['group']:
            group = row['group']
        else:
            stocks.append(
                {
                    'data': {
                        "portfolio_average_weight": row['average_weight'],
                        "portfolio_total_return": row['total_return']
                    },
                    'name': row['name'].rstrip(),  # strips trailing spaces
                    'group': group,
                }
            )

print 'database =',
print json.dumps(database, indent=4)

输出：

^{pr2}$