I have a JSON file which I want to convert to a CSV file:

[
  {
    "key-1": "value-1",
    "attributes": {
      "trait": "name",
      "value": "Some value"
    }
  },
  {
    "key-2": "value-2",
    "attributes": {
      "trait": "name",
      "value": "Some values"
    }
  }
]

I want to convert it into something like this:

[
  {
    "key-1": "value-1",
    "name": "Some value"
  },
  {
    "key-2": "value-2",
    "name": "Some values"
  }
]

How can I go about doing it using Python? I have tried using the flatten_json package but it gives me the following:

[
  {
    "key-1": "value-1",
    "attributes_0_trait": "name",
    "attributes_0_value": "Some value"
  },
  {
    "key-2": "value-2",
    "attributes_0_trait": "name",
    "attributes_0_value": "Some values"
  }
]

My current code:

import json
import csv
from flatten_json import flatten

with open('_metadata.json') as json_file:
    json_data = json.load(json_file)

csv_file = open('metadata.csv', 'w')
csv_writer = csv.writer(csv_file)

count = 0

for data in json_data:
    data = flatten(data)
    keys = data.keys()
    if count == 0:
        csv_writer.writerow(data.keys())

    csv_writer.writerow(data.values())
    count  = 1

csv_file.close()

CodePudding user response：

Not a thoroughly difficult transformation:

data = [
    {
        "key-1": "value-1",
        "attributes": {"trait": "name", "value": "Some value"},
    },
    {
        "key-2": "value-2",
        "attributes": {"trait": "name", "value": "Some values"},
    },
]

out_data = []

for item in data:
    item = item.copy()
    attrs = item.pop("attributes")
    item[attrs["trait"]] = attrs["value"]
    out_data.append(item)

print(out_data)

This prints out

[
  {'key-1': 'value-1', 'name': 'Some value'},
  {'key-2': 'value-2', 'name': 'Some values'},
]

If the items have more keys than key-1 etc., those will be retained in the output dicts.

CodePudding user response：

something like the below

data = [
    {
        "key-1": "value-1",
        "attributes": {
            "trait": "name",
            "value": "Some value"
        }
    },
    {
        "key-2": "value-2",
        "attributes": {
            "trait": "name",
            "value": "Some values"
        }
    }
]

new_data = []

for entry in data:
    temp = {}
    for k, v in entry.items():
        if k.startswith('key'):
            temp[k] = v
        else:
            temp['name'] = v['value']
    new_data.append(temp)
print(new_data)

output

[{'key-1': 'value-1', 'name': 'Some value'}, {'key-2': 'value-2', 'name': 'Some values'}]

CodePudding user response：

One-liner with a combined dictionary and list comprehension:

data2 = [{('name' if k == 'attributes' else k): (v['value'] if k == 'attributes' else v) 
         for k, v in di.items()} for di in data]

assuming the JSON is stored in data. Whether this is obscure or neat compared to a for loop is a matter of taste. Also, depending on where the input comes from, you may want to e.g. do some error checking.