5 Best Ways to Convert CSV to JSON with Specified Delimiters in Python

by

💡 Problem Formulation: Converting a CSV file to JSON format is a common task in data interchange and processing. A specific challenge arises when CSVs use unusual delimiters, needing customization in JSON conversion. For example, a CSV separated with semicolons (;) should be transformed into a neatly structured JSON file, adhering to the delimiter to accurately understand the data separation.

Method 1: Using Python’s Standard Library

This method involves Python’s built-in csv and json libraries to read a CSV file with a custom delimiter and write the data to a JSON file. By specifying the delimiter in the csv.reader function, we accurately parse the CSV data which is then serialized into JSON format using json.dump.

Here’s an example:

import csv
import json

# Read CSV file with specified delimiter
with open('data.csv', mode='r', newline='') as file:
    csv_reader = csv.DictReader(file, delimiter=';')
    data = list(csv_reader)

# Write to JSON file
with open('data.json', mode='w') as json_file:
    json.dump(data, json_file, indent=4)

Output: a JSON file with data structured as a list of dictionaries.

This code snippet reads from a CSV file named ‘data.csv’, specifying the semicolon as the delimiter. It then processes each row into a dictionary using DictReader, followed by writing the data into a ‘data.json’ file in a readable format with an indentation of 4 spaces.

Method 2: pandas Library Conversion

Using the popular pandas library, we can perform the conversion with more sophisticated data manipulation capabilities. The pandas.read_csv function takes a delimiter parameter, while pandas.DataFrame.to_json allows exporting DataFrame to JSON format effortlessly.

Here’s an example:

import pandas as pd

# Read CSV file with specified delimiter into DataFrame
df = pd.read_csv('data.csv', delimiter=';')

# Convert DataFrame to JSON and save to file
df.to_json('data.json', orient='records', lines=True, indent=4)

Output: a JSON file where each line corresponds to a row from the CSV, formatted as a JSON object.

With pandas, the process is succinct: load the CSV into a DataFrame, specifying the ‘;’, then save it as JSON where each record resides on a separate line. The output is not just converted but also formatted to be human-readable.

Method 3: Using List Comprehensions for Customized Conversion

This method leverages Python’s list comprehensions and involves reading the CSV file line by line, then applying custom transformation logic to convert it into a JSON-like dictionary before serializing it with json.

Here’s an example:

import json

# Custom parsing of CSV file with different delimiter
with open('data.csv', 'r') as file:
    header = file.readline().strip().split(';')
    json_data = [dict(zip(header, line.strip().split(';'))) for line in file]

# Write to JSON file
with open('data.json', 'w') as json_file:
    json.dump(json_data, json_file, indent=4)

Output: a JSON file generated from the custom parsing logic.

This code demonstrates a more manual approach: the first line is read as the header, while every subsequent line is split by the ‘;’, paired with the header, and turned into a dictionary. The list of dictionaries is then dumped to a JSON file.

Method 4: csvjson Command-line Tool

Not a pure Python solution, but this method includes using csvjson from the csvkit tool collection, which is installed via pip. This command-line utility can specify a delimiter and perform the conversion without writing any code.

Here’s an example:

csvjson --delimiter ";" data.csv > data.json

Output: ‘data.json’, containing the converted JSON data.

By simply typing this command into the terminal, `csvjson` reads ‘data.csv’ considering the ‘;’ as the delimiter and outputs the JSON representation to ‘data.json’. It is notably useful for quick conversions or when working directly on the command line.

Bonus One-Liner Method 5: Using Python’s json and CSV Snippet

A compact, yet powerful one-liner code that uses Python’s list comprehension, csv.reader, and json.dump for a quick-and-dirty CSV to JSON conversion.

Here’s an example:

import csv, json; json.dump(list(csv.DictReader(open('data.csv'), delimiter=';')), open('data.json', 'w'), indent=4)

Output: succinctly written ‘data.json’ from a one-liner that processes ‘data.csv’.

Concise and to the point, this one-liner contains the entire reading and writing process. However, its compact nature might detract from readability and maintainability for those reading the code later.

Summary/Discussion

  • Method 1: Standard Library. Straightforward implementation using Python’s built-in libraries. Might be less flexible with large or complex datasets.
  • Method 2: pandas Library. Robust and feature-rich approach suitable for complex data manipulation. Requires an additional library which might be an overkill for simple tasks.
  • Method 3: List Comprehensions. Offers fine control over the conversion process. Requires more boilerplate code and may be less efficient for large files.
  • Method 4: csvjson Command-line Tool. Streamlined and fast for those comfortable with command-line utilities. Not a Python solution, however, and requires csvkit installation.
  • Bonus Method 5: One-Liner. Fast and compact, great for quick tasks or scripting. Lacks readability and can be difficult to debug or extend.