Wiki Python for data

CSV and JSON with the standard library

Read CSV with quotes and commas inside fields, convert types carefully and flatten nested JSON, with no dependencies at all.

CSV looks trivial until the first comma inside a field. Never split with split(","): use the csv module, which follows the quoting rules.

PythonOutput checked by running Python 3

import csv
import io

text = 'id,name,amount\n1,"Silva, Ana",230.50\n2,Bruno,\n'
for row in csv.DictReader(io.StringIO(text)):
    print(row)

Output (checked)

{'id': '1', 'name': 'Silva, Ana', 'amount': '230.50'}
{'id': '2', 'name': 'Bruno', 'amount': ''}

Everything arrives as text, and an empty field is '', not None. Converting types is your job, and it is where the data quality decision lives: what do you do with the empty value?

PythonOutput checked by running Python 3

def to_number(text):
    text = text.strip()
    if not text:
        return None
    if "," in text:
        text = text.replace(".", "").replace(",", ".")
    return float(text)

print([to_number(x) for x in ["230.50", "1.234,56", "", " 7 "]])

Output (checked)

[230.5, 1234.56, None, 7.0]

Nested JSON

json.loads returns dictionaries and lists. Flattening a nested record is what a select("customer.city") does in Spark.

PythonOutput checked by running Python 3

import json

raw = '{"id": 7, "customer": {"name": "Ana", "city": null}, "items": [{"sku": "A1", "qty": 2}, {"sku": "B2", "qty": 1}]}'
r = json.loads(raw)
flat = {"id": r["id"], "customer_name": r["customer"]["name"], "city": r["customer"]["city"],
        "total_qty": sum(i["qty"] for i in r["items"])}
print(flat)
print(json.dumps({"city": "São Paulo"}), json.dumps({"city": "São Paulo"}, ensure_ascii=False))

Output (checked)

{'id': 7, 'customer_name': 'Ana', 'city': None, 'total_qty': 3}
{"city": "S\u00e3o Paulo"} {"city": "São Paulo"}

null becomes None, true becomes True; a number without a decimal point becomes int.
ensure_ascii=False writes "São Paulo" as text; the default escapes the accents.

Sources

Shared knowledge from Genesis Innovation. Not a claim about the person behind this site.

PreviousGenerators and lazy evaluation NextDates, times and time zones