Skip to content

    Chapter 5, Basic

    DataFrame: create and load data

    The data comes from files. Here you learn to build a table by hand and, mainly, to read a CSV without falling into the three classic traps: the separator, the encoding, and the index column that appears out of nowhere.

    Create a DataFrame

    The two most common ways reflect how you already think about the data. A dictionary of lists has a list for each field. A list of dictionaries has a dictionary for each row. The result is the same:

    basico/cap05_dataframe_criar_carregar.pylinhas 10 a 23
    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)

por_coluna = pd.DataFrame({"nome": ["Ana", "Bruno"], "salario": [4500, 6500]})
por_linha = pd.DataFrame([{"nome": "Ana", "salario": 4500}, {"nome": "Bruno", "salario": 6500}])
print(por_coluna)
print(por_coluna.equals(por_linha))
print(pd.DataFrame(np.arange(6).reshape(3, 2), columns=["a", "b"]))
    Output
        nome  salario
0    Ana     4500
1  Bruno     6500
True
   a  b
0  0  1
1  2  3
2  4  5

    With a NumPy array, you give the column names separately, and the rest comes from the shape of the array.

    Read a CSV

    The read_csv is the function you will use all the time. The parameters that solve 90% of real cases:

    ParameterWhat it is for
    sepThe column separator (, by default, but Brazilian files usually use ;)
    decimalThe decimal separator (. by default, and , in Brazilian files)
    header and namesIn which line the column names are, or the names to use if the file does not have them
    usecolsRead only some columns, which saves memory
    dtypeForce the type of specific columns
    na_valuesWhich texts mean "missing" besides the defaults
    parse_datesConvert columns to dates already during reading
    encodingThe character encoding (utf-8 by default)

    A "Brazilian-style" file (semicolon, decimal comma) read the right way:

    basico/cap05_dataframe_criar_carregar.pylinhas 28 a 35
    import io

texto = "id;valor;data\n1;1,5;2025-01-10\n2;2,25;2025-02-11\n"
errado = pd.read_csv(io.StringIO(texto))
certo = pd.read_csv(io.StringIO(texto), sep=";", decimal=",", parse_dates=["data"])
print(errado.shape, certo.shape)
print(certo.dtypes)
print(certo["valor"].sum())
    Output
    (2, 1) (2, 3)
id                int64
valor           float64
data     datetime64[us]
dtype: object
3.75

    Without the sep, the entire file turns into a single column (the form (2, 1)), and Pandas doesn't complain. This is a sign that the separator is wrong. For a file without header lines, use header=None and give the names:

    basico/cap05_dataframe_criar_carregar.pylinhas 37 a 38
    sem_titulos = pd.read_csv(io.StringIO("1,Ana\n2,Bruno\n"), header=None, names=["id", "nome"])
print(sem_titulos)
    Output
       id   nome
0   1    Ana
1   2  Bruno

    The column `Unnamed: 0`

    A classic trap: the to_csv saves the index as a column by default. When rereading, it appears as an unnamed column:

    basico/cap05_dataframe_criar_carregar.pylinhas 43 a 47
    por_coluna.to_csv("saida/com_indice.csv")
print(pd.read_csv("saida/com_indice.csv").columns.tolist())

por_coluna.to_csv("saida/sem_indice.csv", index=False)
print(pd.read_csv("saida/sem_indice.csv").columns.tolist())
    Output
    ['Unnamed: 0', 'nome', 'salario']
['nome', 'salario']

    Use index=False, unless the index has information you want to keep.

    Character encoding

    A file saved in latin-1 (common in old exports) is not UTF-8, and the default reading fails on any accent. The fix is to specify the encoding:

    basico/cap05_dataframe_criar_carregar.pylinhas 52 a 57
    Path("saida/antigo.csv").write_bytes("nome\nJoão\n".encode("latin-1"))
try:
    pd.read_csv("saida/antigo.csv")
except UnicodeDecodeError as erro:
    print("UnicodeDecodeError:", erro.reason)
print(pd.read_csv("saida/antigo.csv", encoding="latin-1")["nome"].tolist())
    Output
    UnicodeDecodeError: invalid continuation byte
['João']

    Other formats

    The same DataFrame reads and writes various formats. CSV is universal, JSON is for APIs, Excel is for those who work with spreadsheets, and Parquet (chapter 26) is the binary format made for analysis:

    basico/cap05_dataframe_criar_carregar.pylinhas 62 a 67
    por_coluna.to_json("saida/tabela.json", orient="records")
print(Path("saida/tabela.json").read_text(encoding="utf-8"))
print(pd.read_json("saida/tabela.json").equals(por_coluna))

por_coluna.to_excel("saida/tabela.xlsx", index=False)
print(pd.read_excel("saida/tabela.xlsx").equals(por_coluna))
    Output
    [{"nome":"Ana","salario":4500},{"nome":"Bruno","salario":6500}]
True
True

    Excel requires the openpyxl, which is in the dependencies of this course.

    The file that does not exist

    The most common beginner mistake: the path relative does not match the folder from where you executed. Pandas tells you exactly what it looked for:

    basico/cap05_dataframe_criar_carregar.pylinhas 72 a 75
    try:
    pd.read_csv("dados/nao_existe.csv")
except FileNotFoundError as erro:
    print("FileNotFoundError:", erro)
    Output
    FileNotFoundError: [Errno 2] No such file or directory: 'dados/nao_existe.csv'

    Always run the files of this course from the root of the repository, where the folder dados/ is. If in doubt, Path.cwd() shows where you are.

    Exercise 1

    Read a Brazilian file

    Write ler_vendas(texto), which reads a CSV with ; and decimal comma, converts the column data to date and returns the total of the column valor. Test with a text of 3 lines.