Turning JSON Data into a CSV File Made Simple with Python

Working with data, you're bound to come across a JSON file. While this may be the preferred format for developers, this format can cause some confusion with clients and coworkers who prefer things in a CSV format. CSV Files can be opened in spreadsheet programs such as Microsoft Excel, Google Sheets, and Apple Numbers. Instead of googling a JSON to CSV convertor, we're going to go learn how to create a script to transform this data into a CSV file.

Lets get started

The Libraries

First, there's a few libraries we need to import 1) pandas for handling data 2) json for working with JSON files.

import pandas as pd
import json

Processing the JSON

JSON can be a bit tricky when converting. The process_json function will read the file and clean up the data. This will make it easier to convert. Each line in the file will be checked to see if it is a complete piece of data. Then it will add this data to a list.

def process_json(json_file_path):
    json_objects = []
    current_object = {}

    with open(json_file_path, 'r') as json_file:
        lines = json_file.readlines()

    for line in lines:
        line = line.strip()
        if line.startswith('{') and line.endswith('}'):
            try:
                json_objects.append(json.loads(line))
            except json.JSONDecodeError as e:
                print(f"Error decoding JSON on line: {line}")
                print(e)
        else:
            if line.endswith(','):
                line = line[:-1]
            if ':' in line:
                key_value = line.split(':', 1)
                key = key_value[0].strip().strip('"').strip("'")
                value = key_value[1].strip().strip('"').strip("'")
                try:
                    value = json.loads(value)
                except:
                    pass
                current_object[key] = value

            if line == '}' or line == '{' or line == '':
                if current_object:
                    json_objects.append(current_object)
                    current_object = {}

    return json_objects

Converting JSON to CSV:

The json_to_csv function takes the cleaned data and transforms it into a table using Pandas. Once its transformed, it will save as a CSV file.

def json_to_csv(json_file_path, csv_file_path):
    data_list = process_json(json_file_path)
    df = pd.json_normalize(data_list)
    df.to_csv(csv_file_path, index=False)
    print(f"CSV file saved to {csv_file_path}")

How to Use the Script:

Finally, just need to specify the paths to your JSON and CSV files. Replace '/path/your/file/json_file.json' and '/path/your/file/csv_file.csv' with the actual paths on your computer. And call the json_to_csv function

json_file_path = '/path/your/file/json_file.json'
csv_file_path = '/path/your/file/csv_file.csv'

json_to_csv(json_file_path, csv_file_path)

Summary

This script is will help you convert JSON data into a more readable CSV format, and avoid googling a converter to make your life a bit more automated. By breaking down the JSON file, cleaning it up, and converting it into a table, this script makes it easier to work with your data in spreadsheets. Whether you're dealing with data from a web application or any other source, this method can simplify your workflow.