Parse and convert JSON to CSV

Question

With the help of Parsing values from a JSON file using Python? I was able to read in the data using:

with open('data/data.json') as data_file:
    data_loaded = json.load(data_file)

However, I can't get to the next step where I want to convert this to a CSV file. Sample code:

import pandas as pd
import json 
from io import StringIO
import io
import csv
import sys

sample = {'Table': [{'AA12_Requestor_0': 'John Doe',
   'AA28_Requestor Business Unit_0': 'Manchester United',
   'AA29_Target Remediation Date_0': '2017-07-01',
   'AA29_Exception Lead_0': 'Ji Sung Park',
   'AA29_Level 1 Approver_0': 'Phil Knight',
   'AA29_Date of last WF change_0': '2017-02-12',
   'AA29_Approval Cycles_0': '1',
   'AA29_Level 2 Approver_0': 'Jae In Moon',
   'AA29_Exception Submitted Date_0': '2015-10-12',
   'AA29_Days in Awaiting Assignment_0': '0',
   'AA29_Days in Awaiting Info Sec Review_0': '0',
   'AA29_Days in Awaiting Governance Management Review_0': '2',
   'AA29_Days in Awaiting Level 1 Review - No Level 2 Rev_0': '',
   'AA29_Days from Submitted to Approved_0': '5',
   'AA29_Days from Submitted to Rejected_0': '',
   'AA29_Geography_0': None,
   'AA29_Blood Type': 'Love',
   'AA32_EX: Area_0': None,
   'AA32_Impact_0': None,
   'AA32_Rating_0': None,
   'AA32_Rating (Number)_0': '',
   'AA32_Likelihood_0': None,
   'AA32_Risk Sensitivity_0': None,
   'AA3_Description_0': 'Title: Get your job done | Norm: Be happy.\xa0 Love OS. | Blood type: Z',
   'AA39_Record ID_0': '12345',
   'AA39_Escalation Flag_0': 'No',
   'AA39_Submitted Date_0': '2015-10-15',
   'AA40_ID_0': '123',
   'AF13_Record Workflow State': 'Canceled',
   'FINDING_ID': 12345}]}

There are many more iterations of above dictionary in my JSON file

I've tried:

Referring to http://blog.appliedinformaticsinc.com/how-to-parse-and-convert-json-to-csv-using-python/
```
json_parsed = json.loads(sample)
```
Gives me a error saying JSON object must be string not dictionary.
```
TypeError: the JSON object must be str, bytes or bytearray, not 'dict'
```

Attempted to create csvwriter object

csvwriter = csv.writer(sample)

Gives me a error saying:

TypeError: argument 1 must have a "write" method

I've also read How can I convert JSON to CSV? but I cannot pass the first step, which is doing a json.loads(sample)
According to Python - JSON to CSV table? DictWriter sounds interesting. But don't know how I could apply this to my task.
pd.read_json(sample) is also expecting the string. Gives an error saying
```
TypeError: Expected String or Unicode
```

I want all the keys to be the headers (columns) and all the values of the keys in the rows.

Why do you need to `loads`/`read` it? It's *already loaded/read*. Those things are assuming you start with a JSON string, not the Python objects. — jonrsharpe, May 16 '17 at 22:29
@jonrsharpe I loaded the actual file I need to convert to csv. But when I try to go to the next step, which I used following: csvwriter = csv.writer(data_loaded) and it gives me an error saying TypeError: argument 1 must have a "write" method. My actual data has same (wrong) json format as the sample above, and I have no clue how I can fix the data structure within python... — Johnny, May 17 '17 at 21:27
`csv.writer` takes a *file*, why are you passing it the *data*?! — jonrsharpe, May 17 '17 at 21:34
@jonrsharpe Sorry newbie mistake. I was trying all the methods I could see in other questions, without reading the documentations for all of them. — Johnny, May 19 '17 at 15:55
Any reason for negative vote on my question? Just so I can make better formatted question next time — Johnny, May 19 '17 at 15:56

score 1 · Answer 1 · answered May 16 '17 at 22:33

sample is not a JSON object.

pd.read_json is going to fail.

pd.read_json takes a string that points to a JSON document. You're passing a dict object.

Try passing this:

sample = {'AA12_Requestor_0': 'John Doe',
   'AA28_Requestor Business Unit_0': 'Manchester United',
   'AA29_Target Remediation Date_0': '2017-07-01',
   'AA29_Exception Lead_0': 'Ji Sung Park',
   'AA29_Level 1 Approver_0': 'Phil Knight',
   'AA29_Date of last WF change_0': '2017-02-12',
   'AA29_Approval Cycles_0': '1',
   'AA29_Level 2 Approver_0': 'Jae In Moon',
   'AA29_Exception Submitted Date_0': '2015-10-12',
   'AA29_Days in Awaiting Assignment_0': '0',
   'AA29_Days in Awaiting Info Sec Review_0': '0',
   'AA29_Days in Awaiting Governance Management Review_0': '2',
   'AA29_Days in Awaiting Level 1 Review - No Level 2 Rev_0': '',
   'AA29_Days from Submitted to Approved_0': '5',
   'AA29_Days from Submitted to Rejected_0': '',
   'AA29_Geography_0': None,
   'AA29_Blood Type': 'Love',
   'AA32_EX: Area_0': None,
   'AA32_Impact_0': None,
   'AA32_Rating_0': None,
   'AA32_Rating (Number)_0': '',
   'AA32_Likelihood_0': None,
   'AA32_Risk Sensitivity_0': None,
   'AA3_Description_0': 'Title: Get your job done | Norm: Be happy.\xa0 Love OS. | Blood type: Z',
   'AA39_Record ID_0': '12345',
   'AA39_Escalation Flag_0': 'No',
   'AA39_Submitted Date_0': '2015-10-15',
   'AA40_ID_0': '123',
   'AF13_Record Workflow State': 'Canceled',
   'FINDING_ID': 12345}

data = pd.DataFrame(sample, index=[0]) # Only one row so index has a length of 1

From there you can do ```data.to_csv("path/to/out/file.csv")`` to pass out the JSON object to a csv.

The sample code works. But it looks like I need to change the data source, which starts with {'Table': [{'FA2891_Requestor_0': 'Michael Pollock', .... and ends with }]} This was easy to change in the small sample data, but I have a lot bigger file (what I thought was json but not a json) with the same issue. Is there a way I can slice the data so it will be a json file? {'Table': [{ and ends with below }]} — Johnny, May 16 '17 at 23:29

Parse and convert JSON to CSV

1 Answers1