CODEWITHSUNDEEP
jsonpython-3.xcsvdata-conversion
Johnny
Johnny

Reputation: 181

Parse and convert JSON to CSV

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:

  1. 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'

  2. Attempted to create csvwriter object 

    csvwriter = csv.writer(sample)

    Gives me a error saying:

    TypeError: argument 1 must have a "write" method

  3. 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)

  4. According to Python - JSON to CSV table? DictWriter sounds interesting. But don't know how I could apply this to my task.

  5. 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.

Upvotes: 0

Views: 884

Answers (1)

Quentin
Quentin

Reputation: 700

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.

Upvotes: 1

Related Questions