AppDividend
Latest Code Tutorials

JSON To CSV in Python: How to Convert JSON to CSV

The full form of JSON is JavaScript Object Notation. JSON is a script file, which is made of text in a programming language, is used to store and transfer the data. When you are working with servers, then JSON data is beneficial. Python supports JSON through a built-in package called json.

Python JSON to CSV

To convert JSON to CSV in Python, use the pandas to_csv() function. The to_csv() is a Pandas library function you can use in Python that writes objects to a comma-separated values (csv) file. To convert Python JSON to CSV, we first need to read json data using the Pandas read_json() function and then convert it to csv.

To use json in Python, we have to import the json package in Python script. The JSON text is done through quoted-string, which contains the value in key-value mapping within { }. It is similar to the Dictionary in Python.

CSV (Comma Separated Values) is the simple file format used to store tabular data, such as databases or spreadsheets.

CSV file stores tabular data (numbers and text) in plain text. Each line of the file is the data record. Each record consists of one or more fields, separated by commas. The comma as the field separator is the source of the name for this file format.

Now, we need to convert Python JSON String to CSV format. So, we need to deal with the external json file. This scenario is often used in web development in which the data from a server is always sent in JSON format, and then we need to convert that data in CSV format so that users can quickly analyze the data.

In our case, there are no servers. So we have to create a static json file with some data in it. Then we read that file and get the data and convert it into CSV format using the following steps.

Steps to Convert a Python JSON to CSV

Follow the below steps one by one to convert JSON to CSV in Python.

  1. Get the JSON Data.
  2. Read the data and transform it into a Pandas object.
  3. Convert Pandas object to CSV

Step 1: Get JSON Data

Let’s say and we have a file called export.json.

The contents of the file are following. You can copy the following content and create your own json file. Let’s name it the export.json file.

{
  "0": {
    "Netflix": "Stranger Things",
    "Quibi": "Most Dangerous Game"
  },
  "1": {
    "Netflix": "Money Heist",
    "Quibi": "The Stranger"
  },
  "2": {
    "Netflix": "House of Cards",
    "Quibi": "50 States of Fright"
  },
  "3": {
    "Netflix": "Rick and Morty",
    "Quibi": "Flipped"
  },
  "4": {
    "Netflix": "Better Call Saul",
    "Quibi": "Survival"
  }
}

Now, this is the content of the file, and we will convert it into CSV.

In the above file, you can see that the keys are index. That means, when we convert it into a pandas object, the index would be 0, 1, 2, 3, 4, and header columns will be Netflix and Quibi.

Step 2: Read json and transform it into Pandas object

Pandas read_json() is an inbuilt function that converts a JSON string to a pandas object. Parsing of the JSON Dataset using pandas is much more convenient. Pandas allow you to convert the list of lists to DataFrame and specify the column names separately.

A JSON parser transforms a JSON text into another representation must accept all texts that conform to the JSON grammar.

I am assuming that the export.json file is in the same directory as your coding file. So your path is just a filename. Now, let’s transform the JSON string to Pandas object using the following code.

# app.py

import pandas as pd

pdObj = pd.read_json('export.json', orient='index')
print(pdObj)

Output

python3 app.py
            Netflix                Quibi
0   Stranger Things  Most Dangerous Game
1       Money Heist         The Stranger
2    House of Cards  50 States of Fright
3    Rick and Morty              Flipped
4  Better Call Saul             Survival

In the above code, we have passed orient=’index’.

Orient is an indication of the expected JSON string format. Compatible JSON strings can be produced by to_json() with a corresponding orient value. The set of possible orients is:

  1. split‘: dict like {index -> [index], columns ->
    , data -> [values]}
  2. records‘: list like [{column -> value}, … , {column -> value}]
  3. index‘: dict like {index -> {column -> value}}
  4. columns‘: dict like {column -> {index -> value}}
  5. values‘: just the values array

From the output, we can say that we have transformed the json string into Pandas object.

Step 3: Convert Pandas Object to CSV

Let’s convert Pandas object to CSV data and print it in the console.

# app.py

import pandas as pd

pdObj = pd.read_json('export.json', orient='index')
csvData = pdObj.to_csv(index=False)
print(csvData)

Output

python3 app.py
Netflix,Quibi
Stranger Things,Most Dangerous Game
Money Heist,The Stranger
House of Cards,50 States of Fright
Rick and Morty,Flipped
Better Call Saul,Survival

We have disabled the index because we don’t need an index in CSV data.

Here, we have printed the CSV data. We can also export CSV data here. To do that, we need to provide the export path to create a CSV file. Let’s say we are exporting in the same directory as the export.json file.

# app.py

import pandas as pd

pdObj = pd.read_json('export.json', orient='index')
pdObj.to_csv('streaming.csv', index=False)

In this case, we don’t need to return any data because we are exporting the file. So that is why the returning value here is None.

The exported file’s name is the streaming.csv file, in the same directory as the export.json file. The file’s content is the following.

# streaming.csv

Netflix,Quibi
Stranger Things,Most Dangerous Game
Money Heist,The Stranger
House of Cards,50 States of Fright
Rick and Morty,Flipped
Better Call Saul,Survival

Let’s recall everything here.

In the first step, we have prepared the JSON file.

In the second step, we have used the read_json() function to convert it into a Pandas object.

In the final step, we need to use the Pandas to_csv() function to convert Pandas object to CSV data or export it into a file.

Conclusion

Working with large JSON datasets can be deteriorating, mainly when they are too large to fit into memory. In cases like this, the Pandas library can make for an efficient way to explore and analyze the data. Furthermore, we can convert almost any standard data type to any other data type using the Pandas library.

Finally, converting json to csv file is over.

See also

Pandas DataFrame to List

Pandas to_json()

Pandas filter()

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.