Skip to content

    Chapter 20, Intermediate

    Combine tables: concat

    Data rarely arrives in a single file: one file per month, one spreadsheet per branch. The `concat` stacks tables (or places them side by side), and its errors are silent.

    Stack rows

    The concat joins tables one below the other. By default, it keeps the original index of each part, which generates repeated indices. The ignore_index=True renumbers, and the keys marks where each row came from:

    intermediario/cap20_concat.pylinhas 10 a 20
    import pandas as pd

pd.set_option("display.width", 170)
pd.set_option("display.max_columns", 20)

janeiro = pd.DataFrame({"produto": ["A", "B"], "quantidade": [10, 5]})
fevereiro = pd.DataFrame({"produto": ["A", "C"], "quantidade": [7, 3]})

print(pd.concat([janeiro, fevereiro]).index.tolist())
print(pd.concat([janeiro, fevereiro], ignore_index=True))
print(pd.concat([janeiro, fevereiro], keys=["jan", "fev"]).loc["fev"])
    Output
    [0, 1, 0, 1]
  produto  quantidade
0       A          10
1       B           5
2       A           7
3       C           3
  produto  quantidade
0       A           7
1       C           3

    For prevent accidental repeated indices, the verify_integrity=True raises an error:

    intermediario/cap20_concat.pylinhas 22 a 25
    try:
    pd.concat([janeiro, fevereiro], verify_integrity=True)
except ValueError as erro:
    print("ValueError:", str(erro)[:34])
    Output
    ValueError: Indexes have overlapping values: I

    Different columns

    When the tables have different columns, the concat uses the union of the columns and fills with NaN what was missing, without warning. Here, the March table does not have quantidade, and the integer column, upon gaining a missing value, becomes decimal (chapter 3):

    intermediario/cap20_concat.pylinhas 30 a 34
    marco = pd.DataFrame({"produto": ["D"], "desconto": [0.1]})
junto = pd.concat([janeiro, marco], ignore_index=True)
print(junto)
print(junto.dtypes.astype(str).to_dict())
print(pd.concat([janeiro, marco], join="inner", ignore_index=True).columns.tolist())
    Output
      produto  quantidade  desconto
0       A        10.0       NaN
1       B         5.0       NaN
2       D         NaN       0.1
{'produto': 'str', 'quantidade': 'float64', 'desconto': 'float64'}
['produto']

    This is convenient and dangerous: if a March file arrives with a column renamed (qtd instead of quantidade), the concat does not complain, and you end up with two columns halfway, filled with NaN. The join="inner" keeps only the common columns, and the best defense is to check the columns beforehand before joining:

    intermediario/cap20_concat.pylinhas 36 a 46
    def juntar_conferindo(partes):
    colunas = [set(p.columns) for p in partes]
    if any(c != colunas[0] for c in colunas):
        raise ValueError("as tabelas têm colunas diferentes")
    return pd.concat(partes, ignore_index=True)


try:
    juntar_conferindo([janeiro, marco])
except ValueError as erro:
    print("ValueError:", erro)
    Output
    ValueError: as tabelas têm colunas diferentes

    Join many files: all at once

    When there are many parts, the temptation is to join one at a time in a loop. Each concat copies everything that has already been accumulated, and the cost grows with the square of the number of parts. The correct approach is to collect in a list and join only once.:

    intermediario/cap20_concat.pylinhas 51 a 77
    import time

pedidos = pd.read_csv("dados/pedidos.csv", parse_dates=["data_pedido"])
meses = [parte for _, parte in pedidos.groupby(pedidos["data_pedido"].dt.month)]
print(len(meses), pd.concat(meses).equals(pedidos))

pequenas = [pedidos.iloc[i : i + 3] for i in range(0, 600, 3)]


def no_laco():
    acumulado = pequenas[0]
    for parte in pequenas[1:]:
        acumulado = pd.concat([acumulado, parte])
    return acumulado


def de_uma_vez():
    return pd.concat(pequenas)


def medir(funcao):
    inicio = time.perf_counter()
    funcao()
    return time.perf_counter() - inicio


print(no_laco().equals(de_uma_vez()), "uma vez só foi mais rápido:", medir(no_laco) > 2 * medir(de_uma_vez))
    Output
    12 True
True uma vez só foi mais rápido: True

    The result is identical, and the one-time version is much faster. The 12 at the beginning shows that the requests separated by month and joined again reconstruct the original table, exactly the same.

    Side by side: index alignment

    With axis=1, the concat puts the tables side by side, and aligns by index, not by position. If the indices differ, they appear NaN where there is no pair:

    intermediario/cap20_concat.pylinhas 82 a 84
    nomes = pd.Series(["Ana", "Bruno", "Carla"], index=[10, 11, 12], name="nome")
salarios = pd.Series([4500, 6500], index=[11, 12], name="salario")
print(pd.concat([nomes, salarios], axis=1))
    Output
         nome  salario
10    Ana      NaN
11  Bruno   4500.0
12  Carla   6500.0

    Ana was left without a salary because index 10 does not exist in the salaries. To join tables by a column (an id), the tool is the merge, from the next chapter.

    The `append` that no longer exists

    Much old material uses df.append(outra). This method was Filling in Pandas 2, and remains absent in Pandas 3:

    intermediario/cap20_concat.pylinha 89
    print(hasattr(pd.DataFrame, "append"))
    Output
    False

    The substitute is the pd.concat([df, outra]).

    Exercise 1

    Join safely

    Write empilhar(partes), which joins a list of tables with ignore_index=True, but refuses (with ValueError) if any part has columns different from the first. Test with two identical tables and one with a renamed column.