Chapter 26, Intermediate

Read and write data

A result that only exists in memory disappears when the program ends. Here come the formats that matter (CSV, Parquet, Excel, SQL), and what each one loses along the way.

CSV, with the right parameters

The to_csv has the same adjustments as reading. For those who will open the file in Excel in Portuguese, sep=";" and decimal="," prevent numbers from turning into text, and the date_format fixes the writing of dates:

intermediario/cap26_ler_gravar.pylinhas 10 a 22

from pathlib import Path

import numpy as np
import pandas as pd

pd.set_option("display.width", 170)
pd.set_option("display.max_columns", 20)
Path("saida").mkdir(exist_ok=True)

pedidos = pd.read_csv("dados/pedidos.csv", parse_dates=["data_pedido"])
amostra = pedidos.head(3)
amostra.to_csv("saida/br.csv", index=False, sep=";", decimal=",", date_format="%d/%m/%Y")
print(Path("saida/br.csv").read_text(encoding="utf-8").splitlines()[:2])

Output

['id_pedido;id_cliente;id_produto;quantidade;data_pedido;desconto;canal', '1;57;17;6;01/01/2025;0,0;site']

The problem with CSV is that it is just text: everything that is not a simple number loses its type on the way back. You can see exactly what is lost:

intermediario/cap26_ler_gravar.pylinhas 24 a 31

tipado = pedidos.assign(
    canal=pedidos["canal"].astype("category"),
    id_cliente=pedidos["id_cliente"].astype("Int64"),
)
tipado.to_csv("saida/tipado.csv", index=False)
de_volta = pd.read_csv("saida/tipado.csv")
for coluna in ("data_pedido", "canal", "id_cliente"):
    print(coluna, str(tipado[coluna].dtype), "->", str(de_volta[coluna].dtype))

Output

data_pedido datetime64[us] -> str
canal category -> str
id_cliente Int64 -> int64

The date came back as text, the category came back as text, and the nullable integer came back as int64. Whoever reads the CSV needs to redo all the conversions (and needs to know which ones).

Parquet: the format that resembles types

The Parquet is a binary format, in columns, made for analysis. It keeps the types (dates, categories, nullable) and compresses. In small files, the metadata weighs and it doesn't gain in size, but in real data, the difference is significant:

intermediario/cap26_ler_gravar.pylinhas 36 a 45

tipado.to_parquet("saida/tipado.parquet", index=False)
voltou = pd.read_parquet("saida/tipado.parquet")
print(voltou.dtypes.astype(str).to_dict() == tipado.dtypes.astype(str).to_dict())

grande = pd.concat([tipado] * 100, ignore_index=True)
grande.to_csv("saida/grande.csv", index=False)
grande.to_parquet("saida/grande.parquet", index=False)
csv_kb = Path("saida/grande.csv").stat().st_size / 1024
parquet_kb = Path("saida/grande.parquet").stat().st_size / 1024
print(len(grande), "o parquet é menos de um quarto do csv:", parquet_kb < csv_kb / 4)

Output

True
80000 o parquet é menos de um quarto do csv: True

Since Parquet is in columns, it is possible to read only a few, which in a large file saves time and memory:

intermediario/cap26_ler_gravar.pylinha 47

print(pd.read_parquet("saida/grande.parquet", columns=["id_pedido", "quantidade"]).shape)

Output

(80000, 2)

Parquet needs the pyarrow, which is in the dependencies of this course. My rule: CSV to exchange data with people (spreadsheets, other systems), Parquet to store data between stages of an analysis.

Excel

Excel requires the openpyxl. Several tables can go to sheets of the same file, with the ExcelWriter, and reading all the sheets returns a dictionary:

intermediario/cap26_ler_gravar.pylinhas 52 a 57

with pd.ExcelWriter("saida/relatorio.xlsx") as escritor:
    pedidos.head(3).to_excel(escritor, sheet_name="primeiros", index=False)
    pedidos.tail(2).to_excel(escritor, sheet_name="ultimos", index=False)

abas = pd.read_excel("saida/relatorio.xlsx", sheet_name=None)
print({nome: tabela.shape for nome, tabela in abas.items()})

Output

{'primeiros': (3, 7), 'ultimos': (2, 7)}

JSON by lines

For records coming from APIs or logs, the format JSON Lines (one object per line) is the most practical, and it can be read gradually:

intermediario/cap26_ler_gravar.pylinhas 62 a 64

amostra.to_json("saida/pedidos.jsonl", orient="records", lines=True, date_format="iso")
print(Path("saida/pedidos.jsonl").read_text(encoding="utf-8").splitlines()[0][:60])
print(pd.read_json("saida/pedidos.jsonl", lines=True).shape)

Output

{"id_pedido":1,"id_cliente":57,"id_produto":17,"quantidade":
(3, 7)

SQL

Pandas interacts with databases. The sqlite3 comes with Python. The to_sql saves a table, and the read_sql reads the result of a query. To pass values, use parameters (?), and never construct SQL with text (the injection from chapter 15 of the FastAPI course applies here as well):

intermediario/cap26_ler_gravar.pylinhas 69 a 80

import sqlite3

conexao = sqlite3.connect(":memory:")
pedidos.to_sql("pedidos", conexao, index=False, if_exists="replace")

total = pd.read_sql("SELECT COUNT(*) AS n FROM pedidos", conexao)["n"].iloc[0]
caros = pd.read_sql(
    "SELECT id_pedido, quantidade FROM pedidos WHERE quantidade >= ? ORDER BY quantidade DESC LIMIT 3",
    conexao,
    params=(10,),
)
print(int(total), caros.shape)

Output

800 (3, 2)

A SQL query is often the right way to reduce the data before bringing it: filter and aggregate in the database, and only load the result, instead of bringing everything and filtering in Pandas.

Format	Good for	Loses
CSV	Exchanging with people and other systems	Types (dates, categories, nullable)
Parquet	Save between stages, large data	Does not open in a text editor
Excel	Who works with spreadsheets	Types and speed, and has a limit on rows
JSON Lines	API records and logs	Compact and weak types
SQL	Data that already resides in a database	Depends on a connection

Exercise 1

Round trip without losing anything

Write ida_e_volta_parquet(tabela, caminho), that writes a table in Parquet, reads it back and returns True if the types and values are identical. Test with the table tipado.

CSV, with the right parameters

Parquet: the format that resembles types

Excel

JSON by lines

SQL

View solution