Converting a JSON with a nested array to CSV

Question

Here is a template of my JSON:

{
  "field 1": [
    {
      "id": "123456"
    },
    {
      "about": "YESH"
    },
    {
      "can_post": true
    },
    {
      "category": "Community"
    }
  ],
  "field 2": [
    {
      "id": "123456"
    },
    {
      "about": "YESH"
    },
    {
      "can_post": true
    },
    {
      "category": "Community"
    }
  ]
}

I would like to convert this JSON into a csv in the following format using Python:

0 field 1, id, about, can_post, category

1 field 2, id, about, can_post, category

I tried using pandas to read_json and then to_csv but it didn't work.

Thanks

Answer 1

import csv
import json

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

with open('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']
for d in json_dict['field 1']:
    field_names.extend(d.keys())

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

    for k1, arr_v in json_dict.items():
        temp = {k2:v for d in arr_v for k2,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" }