CSV and JSON Formats

Difficulty: Beginner

CSV (Comma-Separated Values) and JSON (JavaScript Object Notation) are two of the most widely used data formats in the world of data analytics. Understanding how to read, write, and convert between these formats is a fundamental skill that you will use in virtually every data project. Both formats are plain text, human-readable, and supported by nearly every programming language and data tool.

CSV is the simplest tabular data format. Each line represents a row, and values within a row are separated by commas (or sometimes tabs, semicolons, or pipes). The first row typically contains column headers. CSV files are compact, easy to generate, and universally supported by spreadsheet applications like Excel and Google Sheets. However, CSV has limitations: it does not support nested or hierarchical data, it has no built-in way to specify data types, and handling commas within data values requires quoting rules that can become tricky.

JSON is a lightweight data interchange format that uses key-value pairs and arrays. It naturally represents hierarchical and nested data structures, making it the preferred format for web APIs, configuration files, and NoSQL databases. Each JSON object is enclosed in curly braces with key-value pairs separated by commas. JSON supports strings, numbers, booleans, null, arrays, and nested objects. Its main advantages over CSV are the ability to represent complex structures and the explicit typing of values.

Python provides built-in modules for both formats: the csv module for reading and writing CSV data, and the json module for working with JSON. The csv module provides csv.reader for reading rows as lists and csv.DictReader for reading rows as dictionaries (using the header row as keys). The json module provides json.loads() to parse a JSON string into Python objects and json.dumps() to convert Python objects into JSON strings. For file operations, json.load() and json.dump() work directly with file objects.

Choosing between CSV and JSON depends on your data structure and use case. Use CSV when your data is flat and tabular with consistent columns across all rows. Use JSON when your data has nested relationships, variable schemas, or when you need to preserve data types. In practice, you will often need to convert between the two formats, so fluency in both is essential for any data analyst.

Code examples

Parsing CSV Strings

import csv
import io

csv_data = """name,age,city,salary
Alice,28,Mumbai,75000
Bob,35,Delhi,82000
Charlie,42,Bangalore,95000"""

# Parse CSV using csv.reader
reader = csv.reader(io.StringIO(csv_data))
header = next(reader)
print("Columns: " + str(header))

rows = []
for row in reader:
    rows.append(row)
    print(row)

print("Total rows: " + str(len(rows)))

csv.reader parses each line into a list of strings. Note that all values are strings, even numbers. You need to explicitly convert types. The first call to next(reader) extracts the header row.

CSV with DictReader

import csv
import io

csv_data = """product,price,quantity
Laptop,999,45
Phone,699,120
Tablet,449,80"""

# DictReader uses first row as keys
reader = csv.DictReader(io.StringIO(csv_data))

total_revenue = 0
for row in reader:
    revenue = int(row["price"]) * int(row["quantity"])
    total_revenue += revenue
    print(row["product"] + ": 
quot; + str(revenue))

print("Total Revenue: 
quot; + str(total_revenue))

csv.DictReader automatically uses the header row to create dictionaries for each data row. This makes the code more readable since you access values by column name instead of index. Remember to convert numeric strings to int or float for calculations.

Working with JSON Strings

import json

# JSON string (like what you get from an API)
json_string = '{"name": "Alice", "age": 28, "skills": ["Python", "SQL", "Excel"], "address": {"city": "Mumbai", "state": "Maharashtra"}}'

# Parse JSON string into Python dict
data = json.loads(json_string)

print("Name: " + data["name"])
print("Age: " + str(data["age"]))
print("Skills: " + ", ".join(data["skills"]))
print("City: " + data["address"]["city"])

# Convert Python dict back to JSON string
output = json.dumps(data, indent=2)
print("\nFormatted JSON:")
print(output)

json.loads() converts a JSON string into native Python objects (dicts, lists, strings, numbers). json.dumps() converts back to a JSON string. The indent parameter makes the output human-readable. JSON naturally preserves data types unlike CSV.

Converting Between CSV and JSON

import csv
import json
import io

# Start with CSV data
csv_data = """name,age,department
Alice,28,Engineering
Bob,35,Marketing
Charlie,42,Sales"""

# CSV to list of dicts (JSON-ready)
reader = csv.DictReader(io.StringIO(csv_data))
records = []
for row in reader:
    row["age"] = int(row["age"])
    records.append(dict(row))

# Convert to JSON
json_output = json.dumps(records, indent=2)
print("JSON Output:")
print(json_output)

print("\nRecord count: " + str(len(records)))

A common task is converting CSV to JSON. We use DictReader to get rows as dictionaries, convert numeric fields from strings to integers, and then serialize the list of dictionaries as a JSON array. This is the exact workflow used when ingesting CSV data into APIs or document databases.

Key points

Concepts covered

CSV Format, JSON Format, Reading and Writing Files, Data Interchange