Converting A Json With A Nested Array To Csv

Here is a template of my JSON: { 'field 1': [ { 'id': '123456' }, { 'about': 'YESH' }, { 'can_post': true }, { 'category': 'Co

Solution 1:

import csv
import json

json.load( json_data) Deserialize the json_data ( json document(txt/ binary file)) to python object.

withopen('jsn.txt','r') as json_data:
    json_dict = json.load(json_data)

since your field names( keys that will act as fieldname) are inside different dicts, we have to go over this dicts and put them in list field_names.

field_names = [ 'field']
fordin json_dict['field1']:
    field_names.extend(d.keys())

with open('mycsvfile.csv', 'w') as f:  
    w = csv.DictWriter(f, fieldnames = fieild_names)
    w.writeheader()

    fork1, arr_v in json_dict.items():
        temp = {k2:v fordin arr_v fork2,v in d.items()}
        temp['field'] = k1
        w.writerow(temp)

Output

field,id,about,can_post,category
field 1,123456,YESH,True,Community
field 2,123456,YESH,True,Community

If you find above dict comprehension confusing

      k1  : arr_v 
'field 1' = [{ "id": "123456" },...{"category": "Community"}]

            for d in arr_v:                 
                        k2 : v
               d --> { "id": "123456" }

Solution 2:

how about this, if you have json like data

data = [
   {
    "site": "field1",
    "id": "123456",
    "about": "YESH",
    "can_post": True,
    "category": "Community"
  },
  {
    "site": "field2",
    "id": "123456",
    "about": "YESH",
    "can_post": True,
    "category": "Community"
  }
]
# also use True instead of truedf = pd.DataFrame.from_dict(data)

print(df)
# use df.to_csv('filename.csv') for csv

output:

  about  can_post   category      id    site
0  YESH      True  Community  123456  field1
1  YESH      True  Community  123456  field2

Solution 3:

The hard part here is that you json initial structure is not simply a list of mappings but a mapping where the values are in turn lists of mappings.

IMHO, you have to pre-process your input, or process it element by element to get a list or a mapping that can be converted to a csv row. Here is a possible solution that:

• extract the keys for the first element and use them to build a DictWriter
• build a mapping for every element and store it in the DictWriter

Code could be:

import json
import csv

# read the json datawithopen("input.json") as fd:
    data = json.load(fd)

# extract the field names (using 'field' for the key):
names = ['field']
for d innext(iter(data.values())):
    names.extend(d.keys())

# open the csv file as a DictWriter using those nameswithopen("output.csv", "w", newline='') as fd:
    wr = csv.DictWriter(fd, names)
    wr.writeheader()
    for field, vals in data.items():
        d['field'] = field
        for inner in vals:
            for k,v in inner.items():
                d[k] = v
        wr.writerow(d)

With your data it gives:

field,id,about,can_post,category
field 1,123456,YESH,True,Community
field 2,123456,YESH,True,Community