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

i don't understand why you use array for key field1

Nihal
– Nihal

2018-10-11 09:33:07 +00:00
Commented Oct 11, 2018 at 9:33 — Nihal
– Nihal, Commented Oct 11, 2018 at 9:33

Tanmay jain · Accepted Answer · 2018-10-11 11:10:26Z

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

Nihal · Accepted Answer · 2018-10-11 09:52:01Z

1

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 true

df = 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

edited Oct 11, 2018 at 9:52

answered Oct 11, 2018 at 9:44

Nihal

5,3547 gold badges26 silver badges41 bronze badges

1 Comment

Guy Shoshan Over a year ago

This was my result: field 1 field 2 0 {u’id’: u'123456'} {u’id’: u'123456'} 1 {u’about’: u’YESH’} {u’about’: u’YESH’} 2 {u’can_post’: True} {u’can_post’: True} 3 {u’category’: u’Community’} {u’category’: u’Community’}

Serge Ballesta · Accepted Answer · 2018-10-11 10:14:31Z

1

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 data
with open("input.json") as fd:
    data = json.load(fd)

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

# open the csv file as a DictWriter using those names
with open("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

answered Oct 11, 2018 at 10:14

Serge Ballesta

150k13 gold badges137 silver badges267 bronze badges

2 Comments

Guy Shoshan Over a year ago

I got the following error: "names.extend(d.key()) AttributeError: ‘dict’ object has no attribute ‘key’"

Serge Ballesta Over a year ago

@GuyShoshan: Unsure whether a typo, but I wrote keys, not key as you did in your comment

Collectives™ on Stack Overflow

Converting a JSON with a nested array to CSV

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related