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

Question

我将以“我是一个使用糟糕的‘数据库’的菜鸟”作为我要说的话的序言。下面是我的 csv 当前 json 输出的结构(概述如下)。本质上我想做的是将 A 列(信息技术)中的“组”附加到每个“数据”字典中，因此有一个“组”键:值看起来像“组”:“信息技术”。然后第 5 行(Consumer Discretionary)下的所有内容都将具有“group”:“Consumer Discretionary”键、值。

{
  "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

这是我当前的代码:

with open('test.csv', 'rU') as csvfile:
    lines = csv.reader(csvfile)
    for line in lines:
      elif line[0] == "" and line[1] != "":
        data = test_two_level(line)
        bottom_level = {
        "name": line[2],
        "data": data}

def test_two_level(line):
  data = {
      "portfolio_average_weight":line[3],
      "portfolio_total_return":line[4]}
  return data

我希望最终输出如下:

{
  "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而不是csv.reader，因为生成的代码更容易阅读，而且将每一行读入字典也使代码更容易阅读。更加统一——尤其是在处理 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)

输出:

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