How to Convert a JSON file to CSV file in Python

When you are working with data in python, you will most frequently see JSON files and CSV files. Data available for processing are most often in the form of a CSV file. But what if that data is in the form of JSON and you want to convert it to a CSV file in Python?

In this article, We will discuss differnt methods of conversion JSON file into CSV file in Python.

What is JSON?

JSON stands for JavaScript Object Notation, is a standard format for data representation that is based on JavaSript object Syntax.Example of JSON data:

[
	{
		"color": "red",
		"value": "#f00"
	},
	{
		"color": "green",
		"value": "#0f0"
	},
	{
		"color": "blue",
		"value": "#00f"
	},
	{
		"color": "cyan",
		"value": "#0ff"
	},
	{
		"color": "magenta",
		"value": "#f0f"
	},
	{
		"color": "yellow",
		"value": "#ff0"
	},
	{
		"color": "black",
		"value": "#000"
	}
]

Why do we use data in JSON Format?

It is one of the standards in, Reading data from a server through API or sending data to the server. We use JSON format because it is easy to work with and is simple to deal with JSON data.

Alternative to the JSON format is XML, which was also used in the past and is still in use.

What is a CSV?

CSV stands for Comma-separated values and is one of the most popular formats for representing structured data. In most data processing applications, we need CSV format. Large data sets are also often in a CSV format.

Why Convert a JSON file into a CSV file?

There are many reasons you might need the conversion of a JSON file into a CSV fileThe reason why you would convert a JSON file into a CSV file

Every file Format has its own speciality, In some cases you would prefer using JSON instead of CSV. e.g; A good use case for JSON format is using it in API response.

Convert a JSON file into CSV File in Python

In Python, there are multiple ways you can convert a JSON file into a CSV file. While there are many ways you can convert a JSON to CSV, see which one of the following works for you.

Convert JSON file into CSV file using the Pandas module

Pandas module is a python third-party module, which you need to install before using it. Python pandas module provides different utility functions for working with CSV and JSON files.To convert JSON to CSV file in Python

  • install pandas using pip or conda pip install pandas
  • import pandas module import pandas as pd
  • open the JSON file with open() function
  • use pandas pd.read_json() function
  • Now convert pandas dataframe to csv df.to_csv()

Below is the python program that converts a JSON file into CSV file.

import pandas as pd

input_json_file='json_file.json'
output_csv_file='csv_file.csv'
with open(input_json_file) as inputfile:
    df = pd.read_json(inputfile)

df.to_csv(output_csv_file, index=False) 

Input file was a ‘json_file.json’ which contains the follwoing json data:

[
	{
		"color": "red",
		"value": "#f00"
	},
	{
		"color": "green",
		"value": "#0f0"
	},
	{
		"color": "blue",
		"value": "#00f"
	},
	{
		"color": "cyan",
		"value": "#0ff"
	},
	{
		"color": "magenta",
		"value": "#f0f"
	},
	{
		"color": "yellow",
		"value": "#ff0"
	},
	{
		"color": "black",
		"value": "#000"
	}
]

Output of the code is a CSV file that contains the following data:

color,value
red,#f00
green,#0f0
blue,#00f
cyan,#0ff
magenta,#f0f
yellow,#ff0
black,#000

This is how we can use a pandas module in python to convert a JSON file into a CSV file in python.

Convert JSON file into CSV file using JSON and CSV module in python

Python also provides a JSON module for working with JSON data. With the help of JSON module in Python, we can read and write JSON data.

Another way to Convert json data into csv data is to use CSV and JSON modules in python.To convert JSON to CSV in Python :

  • import json and csv modules
  • open the JSON file with open() function
  • load data as a JSON object with json.load() function
  • write the data into a CSV file using csv.writerow() function

Following is a Python code Example that converts JSON data into csv data using python CSV and JSON modules.

'''
@author : alixaprodev.com
'''
import csv, json
input_json_file='json_file.json'
output_csv_file='csv_file.csv'
input = open(input_json_file)
data = json.load(input)
input.close()
output = csv.writer(open(output_csv_file,'w'))
output.writerow(data[0].keys())  # header row
for row in data:
    output.writerow(row.values())

 

Input JSON file Contains the following JSON data:

[
	{
		"color": "red",
		"value": "#f00"
	},
	{
		"color": "green",
		"value": "#0f0"
	},
	{
		"color": "blue",
		"value": "#00f"
	},
	{
		"color": "cyan",
		"value": "#0ff"
	},
	{
		"color": "magenta",
		"value": "#f0f"
	},
	{
		"color": "yellow",
		"value": "#ff0"
	},
	{
		"color": "black",
		"value": "#000"
	}
]

Output CSV file after execution of the above code is following:

color,value

red,#f00

green,#0f0

blue,#00f

cyan,#0ff

magenta,#f0f

yellow,#ff0

black,#000

Convert a JSON file into csv using pandas.json_normalize() function

We have seen that with pandas we can easily convert a JSON file into a CSV file, but with the help of using pandas.json_normailze() we can go one step furhter to normalize the JSON data and then write it to a CSV file.Below is the python code that first normalizes the json and then writes the json to a CSV file.

import pandas as pd
from pathlib import Path
import json

# file is in current dir
path = Path(r'json_file.json')

# read json
with path.open('r', encoding='utf-8') as f:
    data = json.loads(f.read())

# create dataframe
df = pd.json_normalize(data)

# save to csv
df.to_csv('output.csv', index=False, encoding='utf-8')  

Output is just same is of the above other funcitons. No differnce just an error free output.

Convert JSON file to CSV file using csv.DictWriter()

You can use the csv.DictWriter() and json.read() functions to convert a json file to a csv file in python.To convert a JSON file into a csv file :

  1.  read the JSON file with read_json() function
  2.  write this JSON to CSV file using write_csv() function

Check out the following code that use csv.DictWriter() funciton to convert json into a csv file.

import json,csv
def read_json(filename):
    return json.loads(open(filename).read())
def write_csv(data,filename):
    with open(filename, 'w+') as outf:
        writer = csv.DictWriter(outf, data[0].keys())
        writer.writeheader()
        for row in data:
            writer.writerow(row)

write_csv(read_json('json_file.json'), 'output.csv') 

The Easiest way to convert a JSON file to a csv file in python

Well, you have seen different ways of converting to a CSV file. the most easiest way to convert a json file into a CSV file is to use JSON and csv modules.

Check the below code that converts a json file into a csv file using the python csv and json modules.

import csv, json


fileInput = 'json_file.json'
fileOutput = 'output.csv'

inputFile = open(fileInput)
outputFile = open(fileOutput, 'w')
data = json.load(inputFile)
inputFile.close()

output = csv.writer(outputFile)

output.writerow(data[0].keys()) 
for row in data:
    output.writerow(row.values())
  

Important Note on Conversion of JSON File to a CSV file

It is not always possiblle to apply the same method to convert any json file inot a csv file in python.Some reasons are

  • JSON can represent a wide variety of data structures
  • JSON “object” is roughly like a Python dictionary
  • JSON “array” is like a Python list
  • CSV can essentially represent only a 2-D table

So, in the general case, you can’t translate an arbitrary JSON structure to a CSV. In a few special cases you can (array of arrays with no further nesting; arrays of objects which all have exactly the same keys).

Leave a Comment

Scroll to Top