How To Parse and Convert JSON to CSV using Python

by Shahid Ayoub

JSON is an acronym standing for JavaScript Object NotationThe json library in python can parse JSON from strings or files. The library parses JSON into a Python dictionary or list. We come across various circumstances where we receive data in json format and we need to send or store it in csv format. Since the csv files can easily be opened using LibreOffice Calc in ubuntu or Microsoft Excel in windows the need for json to csv conversion usually increases.

Parsing JSON

Let us take an example of  JSON in string form as shown below :

{

json_data = ‘{“name”: “smith”, “email”: “smithjack@gmail.com”}’

The json_data will be parsed like this in python:

import json

json_parsed = json.loads(json_data)

Now json.loads() function parses the json string data and it can be used as a normal dictionary in python. And we can access the values using keys. For example, here we can access email Id using key ‘email’ as shown below:

print json_parsed[‘email’]

smithjack@gmail.com

This is a simple example of parsing JSON, but we can have JSON file where data is much more complex . For example JSON data where values are lists and are accessed using list index as shown below :

Data = '{"employee_name": "James", "email": "james@gmail.com", "job_profile": ["Team Lead", "Sr. Developer"]}'

It can be parsed like this :

Parsed_data = json.loads(Data)

print Parsed_data[‘job_profile’][0]

"Team Lead"

The complexity increases further for JSON data where values are lists having dictionaries within. Again values will be accessed using list index and key value pair as shown below:

 

Data = '{"employee_name": "James", "email": "james@gmail.com", "job_profile": [{"title1":"Team Lead", "title2":"Sr. Developer"}]}'

It can be parsed like this:

Parsed_data = json.loads(Data)

print Parsed_data[‘job_profile’][0][‘title1’]

"Team Lead"

Similarly other fields can be accessed from the parsed data.

Converting JSON to CSV using Python:

CSV (Comma Separated Values) format is the most common import and export format for spreadsheets and databases. CSV file format separates values using commas as delimiters . For simple JSON data, keys will be headers for the CSV file and values the descriptive data. For example we have thousands of records about employees working in an organisation as shown below:

employee_data = '{"employee_details":[{"employee_name": "James", "email": "james@gmail.com", "job_profile": "Sr. Developer"},{"employee_name": "Smith", "email": "Smith@gmail.com", "job_profile": "Project Lead"},.....]}'

This can be parsed and converted to CSV using python as shown below:

import json

import csv

employee_parsed = json.loads(employee_data)

emp_data = employee_parsed['employee_details']

# open a file for writing

employ_data = open('/tmp/EmployData.csv', 'w')

# create the csv writer object

csvwriter = csv.writer(employ_data)

count = 0

for emp in emp_data:

      if count == 0:

             header = emp.keys()

             csvwriter.writerow(header)

             count += 1

      csvwriter.writerow(emp.values())

employ_data.close()


The CSV file will look like this for few records  :

employee_name email job_profile
James james@gmail.com Sr. Developer
Smith Smith@gmail.com Project Lead

 

The python program written above will open a csv file in tmp folder and write the content of JSON file into it and close it at the end. Make sure to close the file at the end in order to save the contents.

JSON files can have much more complex structures than CSV files, so a direct conversion is not always possible. The above mentioned examples will lay the foundation for converting JSON data with high complexity to CSV. 

12 thoughts on “How To Parse and Convert JSON to CSV using Python”

    1. It seems you are using the employee JSON data as it is. You have to strip dots(…) at the end it is an example or you can add more instances there.

  1. I keep getting an error: Input string must be text, not bytes.
    Any hints?
    Here is my JSON data, as provided to me by a third-party:
    [

    {

    “publishDate”: “2017-01-01T05:00:00Z”,

    “pnodeId”: 1123175420,

    “versionNum”: 0,

    “priceType”: “CongLMP”,

    “prices”: [

    {

    “utchour”: “2017-01-01T05:00:00Z”,

    “price”: -0.18

    },

    {

    “utchour”: “2017-01-01T06:00:00Z”,

    “price”: -0.41

    },

    {

    “utchour”: “2017-01-01T07:00:00Z”,

    “price”: -0.41

    },

    {

    “utchour”: “2017-01-01T08:00:00Z”,

    “price”: -0.44

    },

    {

    “utchour”: “2017-01-01T09:00:00Z”,

    “price”: -0.38

    },

    {

    “utchour”: “2017-01-01T10:00:00Z”,

    “price”: -0.27

    },

    {

    “utchour”: “2017-01-01T11:00:00Z”,

    “price”: -0.39

    },

    {

    “utchour”: “2017-01-01T12:00:00Z”,

    “price”: -0.21

    },

    {

    “utchour”: “2017-01-01T13:00:00Z”,

    “price”: 0.08

    },

    {

    “utchour”: “2017-01-01T14:00:00Z”,

    “price”: 0.25

    },

    {

    “utchour”: “2017-01-01T15:00:00Z”,

    “price”: 0.34

    },

    {

    “utchour”: “2017-01-01T16:00:00Z”,

    “price”: 0.59

    },

    {

    “utchour”: “2017-01-01T17:00:00Z”,

    “price”: 0.43

    },

    {

    “utchour”: “2017-01-01T18:00:00Z”,

    “price”: 0.42

    },

    {

    “utchour”: “2017-01-01T19:00:00Z”,

    “price”: 0.43

    },

    {

    “utchour”: “2017-01-01T20:00:00Z”,

    “price”: 0.04

    },

    {

    “utchour”: “2017-01-01T21:00:00Z”,

    “price”: -0.03

    },

    {

    “utchour”: “2017-01-01T22:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-01T23:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-02T00:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-02T01:00:00Z”,

    “price”: -0.03

    },

    {

    “utchour”: “2017-01-02T02:00:00Z”,

    “price”: -0.05

    },

    {

    “utchour”: “2017-01-02T03:00:00Z”,

    “price”: -0.1

    },

    {

    “utchour”: “2017-01-02T04:00:00Z”,

    “price”: -0.13

    }

    ]

    },

    {

    “publishDate”: “2017-01-01T05:00:00Z”,

    “pnodeId”: 1123175420,

    “versionNum”: 0,

    “priceType”: “LossLMP”,

    “prices”: [

    {

    “utchour”: “2017-01-01T05:00:00Z”,

    “price”: -0.71

    },

    {

    “utchour”: “2017-01-01T06:00:00Z”,

    “price”: -0.69

    },

    {

    “utchour”: “2017-01-01T07:00:00Z”,

    “price”: -0.56

    },

    {

    “utchour”: “2017-01-01T08:00:00Z”,

    “price”: -0.41

    },

    {

    “utchour”: “2017-01-01T09:00:00Z”,

    “price”: -0.43

    },

    {

    “utchour”: “2017-01-01T10:00:00Z”,

    “price”: -0.51

    },

    {

    “utchour”: “2017-01-01T11:00:00Z”,

    “price”: -0.52

    },

    {

    “utchour”: “2017-01-01T12:00:00Z”,

    “price”: -0.62

    },

    {

    “utchour”: “2017-01-01T13:00:00Z”,

    “price”: -0.63

    },

    {

    “utchour”: “2017-01-01T14:00:00Z”,

    “price”: -0.66

    },

    {

    “utchour”: “2017-01-01T15:00:00Z”,

    “price”: -0.62

    },

    {

    “utchour”: “2017-01-01T16:00:00Z”,

    “price”: -0.47

    },

    {

    “utchour”: “2017-01-01T17:00:00Z”,

    “price”: -0.52

    },

    {

    “utchour”: “2017-01-01T18:00:00Z”,

    “price”: -0.54

    },

    {

    “utchour”: “2017-01-01T19:00:00Z”,

    “price”: -0.55

    },

    {

    “utchour”: “2017-01-01T20:00:00Z”,

    “price”: -0.54

    },

    {

    “utchour”: “2017-01-01T21:00:00Z”,

    “price”: -0.63

    },

    {

    “utchour”: “2017-01-01T22:00:00Z”,

    “price”: -1.38

    },

    {

    “utchour”: “2017-01-01T23:00:00Z”,

    “price”: -1.45

    },

    {

    “utchour”: “2017-01-02T00:00:00Z”,

    “price”: -1.47

    },

    {

    “utchour”: “2017-01-02T01:00:00Z”,

    “price”: -1.4

    },

    {

    “utchour”: “2017-01-02T02:00:00Z”,

    “price”: -1.33

    },

    {

    “utchour”: “2017-01-02T03:00:00Z”,

    “price”: -1.23

    },

    {

    “utchour”: “2017-01-02T04:00:00Z”,

    “price”: -1.13

    }

    ]

    },

    {

    “publishDate”: “2017-01-01T05:00:00Z”,

    “pnodeId”: 1123175420,

    “versionNum”: 0,

    “priceType”: “TotalLMP”,

    “prices”: [

    {

    “utchour”: “2017-01-01T05:00:00Z”,

    “price”: 24.54

    },

    {

    “utchour”: “2017-01-01T06:00:00Z”,

    “price”: 24.66

    },

    {

    “utchour”: “2017-01-01T07:00:00Z”,

    “price”: 23.32

    },

    {

    “utchour”: “2017-01-01T08:00:00Z”,

    “price”: 22.89

    },

    {

    “utchour”: “2017-01-01T09:00:00Z”,

    “price”: 22.52

    },

    {

    “utchour”: “2017-01-01T10:00:00Z”,

    “price”: 22.94

    },

    {

    “utchour”: “2017-01-01T11:00:00Z”,

    “price”: 22.38

    },

    {

    “utchour”: “2017-01-01T12:00:00Z”,

    “price”: 23.56

    },

    {

    “utchour”: “2017-01-01T13:00:00Z”,

    “price”: 24.39

    },

    {

    “utchour”: “2017-01-01T14:00:00Z”,

    “price”: 24.49

    },

    {

    “utchour”: “2017-01-01T15:00:00Z”,

    “price”: 23.88

    },

    {

    “utchour”: “2017-01-01T16:00:00Z”,

    “price”: 23.32

    },

    {

    “utchour”: “2017-01-01T17:00:00Z”,

    “price”: 23.21

    },

    {

    “utchour”: “2017-01-01T18:00:00Z”,

    “price”: 22.88

    },

    {

    “utchour”: “2017-01-01T19:00:00Z”,

    “price”: 22.54

    },

    {

    “utchour”: “2017-01-01T20:00:00Z”,

    “price”: 22.26

    },

    {

    “utchour”: “2017-01-01T21:00:00Z”,

    “price”: 22.96

    },

    {

    “utchour”: “2017-01-01T22:00:00Z”,

    “price”: 28.93

    },

    {

    “utchour”: “2017-01-01T23:00:00Z”,

    “price”: 27.72

    },

    {

    “utchour”: “2017-01-02T00:00:00Z”,

    “price”: 25.25

    },

    {

    “utchour”: “2017-01-02T01:00:00Z”,

    “price”: 23.6

    },

    {

    “utchour”: “2017-01-02T02:00:00Z”,

    “price”: 23.61

    },

    {

    “utchour”: “2017-01-02T03:00:00Z”,

    “price”: 23.35

    },

    {

    “utchour”: “2017-01-02T04:00:00Z”,

    “price”: 21.65

    }

    ]

    },

    {

    “publishDate”: “2017-01-02T05:00:00Z”,

    “pnodeId”: 1123175420,

    “versionNum”: 0,

    “priceType”: “CongLMP”,

    “prices”: [

    {

    “utchour”: “2017-01-02T05:00:00Z”,

    “price”: -0.01

    },

    {

    “utchour”: “2017-01-02T06:00:00Z”,

    “price”: -0.03

    },

    {

    “utchour”: “2017-01-02T07:00:00Z”,

    “price”: -0.04

    },

    {

    “utchour”: “2017-01-02T08:00:00Z”,

    “price”: -0.11

    },

    {

    “utchour”: “2017-01-02T09:00:00Z”,

    “price”: -0.04

    },

    {

    “utchour”: “2017-01-02T10:00:00Z”,

    “price”: -0.08

    },

    {

    “utchour”: “2017-01-02T11:00:00Z”,

    “price”: -0.05

    },

    {

    “utchour”: “2017-01-02T12:00:00Z”,

    “price”: -0.04

    },

    {

    “utchour”: “2017-01-02T13:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-02T14:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-02T15:00:00Z”,

    “price”: 0.01

    },

    {

    “utchour”: “2017-01-02T16:00:00Z”,

    “price”: -0.07

    },

    {

    “utchour”: “2017-01-02T17:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-02T18:00:00Z”,

    “price”: -0.03

    },

    {

    “utchour”: “2017-01-02T19:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-02T20:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-02T21:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-02T22:00:00Z”,

    “price”: 0.01

    },

    {

    “utchour”: “2017-01-02T23:00:00Z”,

    “price”: 0.01

    },

    {

    “utchour”: “2017-01-03T00:00:00Z”,

    “price”: 0.01

    },

    {

    “utchour”: “2017-01-03T01:00:00Z”,

    “price”: 0.01

    },

    {

    “utchour”: “2017-01-03T02:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-03T03:00:00Z”,

    “price”: 0.0

    },

    {

    “utchour”: “2017-01-03T04:00:00Z”,

    “price”: 0.0

    }

    ]

    },

    {

    “publishDate”: “2017-01-02T05:00:00Z”,

    “pnodeId”: 1123175420,

    “versionNum”: 0,

    “priceType”: “LossLMP”,

    “prices”: [

    {

    “utchour”: “2017-01-02T05:00:00Z”,

    “price”: -0.95

    },

    {

    “utchour”: “2017-01-02T06:00:00Z”,

    “price”: -0.76

    },

    {

    “utchour”: “2017-01-02T07:00:00Z”,

    “price”: -0.73

    },

    {

    “utchour”: “2017-01-02T08:00:00Z”,

    “price”: -0.74

    },

    {

    “utchour”: “2017-01-02T09:00:00Z”,

    “price”: -0.74

    },

    {

    “utchour”: “2017-01-02T10:00:00Z”,

    “price”: -0.8

    },

    {

    “utchour”: “2017-01-02T11:00:00Z”,

    “price”: -0.99

    },

    {

    “utchour”: “2017-01-02T12:00:00Z”,

    “price”: -1.22

    },

    {

    “utchour”: “2017-01-02T13:00:00Z”,

    “price”: -1.45

    },

    {

    “utchour”: “2017-01-02T14:00:00Z”,

    “price”: -1.82

    },

    {

    “utchour”: “2017-01-02T15:00:00Z”,

    “price”: -2.31

    },

    {

    “utchour”: “2017-01-02T16:00:00Z”,

    “price”: -2.69

    },

    {

    “utchour”: “2017-01-02T17:00:00Z”,

    “price”: -2.73

    },

    {

    “utchour”: “2017-01-02T18:00:00Z”,

    “price”: -2.41

    },

    {

    “utchour”: “2017-01-02T19:00:00Z”,

    “price”: -2.44

    },

    {

    “utchour”: “2017-01-02T20:00:00Z”,

    “price”: -2.2

    },

    {

    “utchour”: “2017-01-02T21:00:00Z”,

    “price”: -2.61

    },

    {

    “utchour”: “2017-01-02T22:00:00Z”,

    “price”: -3.04

    },

    {

    “utchour”: “2017-01-02T23:00:00Z”,

    “price”: -2.94

    },

    {

    “utchour”: “2017-01-03T00:00:00Z”,

    “price”: -3.17

    },

    {

    “utchour”: “2017-01-03T01:00:00Z”,

    “price”: -2.42

    },

    {

    “utchour”: “2017-01-03T02:00:00Z”,

    “price”: -1.94

    },

    {

    “utchour”: “2017-01-03T03:00:00Z”,

    “price”: -1.39

    },

    {

    “utchour”: “2017-01-03T04:00:00Z”,

    “price”: -1.19

    }

    ]

    },

    {

    “publishDate”: “2017-01-02T05:00:00Z”,

    “pnodeId”: 1123175420,

    “versionNum”: 0,

    “priceType”: “TotalLMP”,

    “prices”: [

    {

    “utchour”: “2017-01-02T05:00:00Z”,

    “price”: 21.48

    },

    {

    “utchour”: “2017-01-02T06:00:00Z”,

    “price”: 21.48

    },

    {

    “utchour”: “2017-01-02T07:00:00Z”,

    “price”: 21.3

    },

    {

    “utchour”: “2017-01-02T08:00:00Z”,

    “price”: 21.16

    },

    {

    “utchour”: “2017-01-02T09:00:00Z”,

    “price”: 22.05

    },

    {

    “utchour”: “2017-01-02T10:00:00Z”,

    “price”: 22.34

    },

    {

    “utchour”: “2017-01-02T11:00:00Z”,

    “price”: 22.79

    },

    {

    “utchour”: “2017-01-02T12:00:00Z”,

    “price”: 24.17

    },

    {

    “utchour”: “2017-01-02T13:00:00Z”,

    “price”: 24.75

    },

    {

    “utchour”: “2017-01-02T14:00:00Z”,

    “price”: 28.42

    },

    {

    “utchour”: “2017-01-02T15:00:00Z”,

    “price”: 32.76

    },

    {

    “utchour”: “2017-01-02T16:00:00Z”,

    “price”: 35.26

    },

    {

    “utchour”: “2017-01-02T17:00:00Z”,

    “price”: 35.07

    },

    {

    “utchour”: “2017-01-02T18:00:00Z”,

    “price”: 30.7

    },

    {

    “utchour”: “2017-01-02T19:00:00Z”,

    “price”: 29.8

    },

    {

    “utchour”: “2017-01-02T20:00:00Z”,

    “price”: 26.72

    },

    {

    “utchour”: “2017-01-02T21:00:00Z”,

    “price”: 29.8

    },

    {

    “utchour”: “2017-01-02T22:00:00Z”,

    “price”: 32.59

    },

    {

    “utchour”: “2017-01-02T23:00:00Z”,

    “price”: 35.75

    },

    {

    “utchour”: “2017-01-03T00:00:00Z”,

    “price”: 38.93

    },

    {

    “utchour”: “2017-01-03T01:00:00Z”,

    “price”: 34.22

    },

    {

    “utchour”: “2017-01-03T02:00:00Z”,

    “price”: 32.16

    },

    {

    “utchour”: “2017-01-03T03:00:00Z”,

    “price”: 24.32

    },

    {

    “utchour”: “2017-01-03T04:00:00Z”,

    “price”: 22.06

    }

    ]

    }]

    1. There seems an encoding issue, use proper quotes for the the key and string values. I was able to parse the data after replacing the existing quotes with normal double quotes or use proper encoding.

  2. Hi,

    I have a variable called user_info where profile data of users in stored. The output is supposed to in json.
    When I run the program (it’s a bot), I’d like to generate a json file to store all the data as json, and later export the data as CSV. I’ve been looking for a solution for hours now and I’ve found nothing. I’m new to Python. Do you know how to do that?

    Thanks

  3. Which one is easier to parse and convert into CSV: JSON or XML?
    I can get my data in either of them and would like to make a decision which one to proceed with.

  4. Hello Shahid pretty well your post, actually that helps me a lot.
    You can check my JSON example here https://secure.bixi.com/data/stations.json following your approach I have already created the CSV file. however and that could be a very stupid question I have tried to do two things:
    1. just get a couple of values from ‘stations’ (id,n,da,dx,ba,bx)
    1.1 rename the output with a different name header
    2. I don’t know why my CSV file has an enter after each row 🙁

    Probably you are busy with another stuff but my friend I would really appreciate if you can guide me

    thanks so much
    Andres

    1. Buddy i played a little bit and i found that:

      Script
      ***************************
      import json
      import csv

      #read the json file
      with open(‘stations.json’,’r’) as bixi_stations:
      data = json.load(bixi_stations)

      data_csv = data[‘stations’]

      file_data= open( ‘C:/Users/axu30/PycharmProjects/First/stations.csv’,’w’)
      csvwriter = csv.writer(file_data)

      liste = {}
      i =0
      for count in data_csv:
      if i == 0:
      header = ‘Id’,’Station Name’,’Station Id’,’Empty places’,’Qty Avail Bikes’
      csvwriter.writerow(header)
      i += 1

      liste[‘id’]=count[‘id’]
      liste[‘s’] = count[‘s’]
      liste[‘n’] = count[‘n’]
      liste[‘da’] = count[‘da’]
      liste[‘ba’] = count[‘ba’]

      csvwriter.writerow(liste.values())

      file_data.close()

Leave a Reply

Your email address will not be published. Required fields are marked *

Tools & Practices

Tools and Technologies we use at Applied

Contact us now

Popular Posts