Reformat: pivot, melt and crosstab

The starting point: sales, in a table

From here until the end of the level, the sales model appears already along: orders, plus products (for the price), plus customers (for the segment). The function combines what chapter 21 showed, and never discards orders:

import numpy as np
import pandas as pd

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


def carregar_vendas() -> pd.DataFrame:
    clientes = pd.read_csv("dados/clientes.csv")
    produtos = pd.read_csv("dados/produtos.csv")
    pedidos = pd.read_csv("dados/pedidos.csv", parse_dates=["data_pedido"])
    v = pedidos.merge(produtos, on="id_produto", how="left", validate="m:1")
    v = v.merge(clientes[["id_cliente", "segmento"]], on="id_cliente", how="left", validate="m:1")
    v["segmento"] = v["segmento"].fillna("Sem cadastro")
    v["receita"] = v["quantidade"] * v["preco_unitario"] * (1 - v["desconto"].fillna(0))
    v["mes"] = v["data_pedido"].dt.month
    return v


vendas = carregar_vendas()
print(vendas.shape, round(float(vendas["receita"].sum()), 2))

(800, 13) 970851.1

`pivot_table`: aggregate and open in columns

The pivot_table does what a pivot table in Excel does: groups by one or more keys in the rows, opens another key in columns and aggregates the values. The margins=True adds the totals:

tabela = vendas.pivot_table(
    index="categoria", columns="canal", values="receita",
    aggfunc="sum", margins=True, margins_name="Total",
)
print(tabela.round(0))
print(bool(np.isclose(tabela.loc["Total", "Total"], vendas["receita"].sum())))

canal             app      loja      site     Total
categoria                                          
Casa          53988.0   77803.0  105830.0  237621.0
Esporte       51362.0   95554.0  124000.0  270915.0
Informática   43765.0   79425.0  124154.0  247345.0
Papelaria     43095.0   69274.0  102601.0  214970.0
Total        192210.0  322056.0  456585.0  970851.0
True

The total in the corner closes with the sum of all revenue (the True), which is the usual check. For more than one aggregation, pass a list, and the columns gain an extra level:

print(vendas.pivot_table(index="categoria", values="receita", aggfunc=["sum", "count"]).round(0))

                  sum   count
              receita receita
categoria                    
Casa         237621.0     205
Esporte      270915.0     206
Informática  247345.0     196
Papelaria    214970.0     193

`pivot`: reorganize, without aggregating

The pivot just reorganizes: does not aggregate anything, and therefore requires that each pair (linha, coluna) appears once. Before it, I aggregate with the groupby to ensure this:

por_mes_e_canal = vendas.groupby(["mes", "canal"])["receita"].sum().reset_index()
largo = por_mes_e_canal.pivot(index="mes", columns="canal", values="receita")
print(largo.round(0).head(3))

canal      app     loja     site
mes                             
1      16034.0  38612.0  27511.0
2      11320.0  23562.0  28582.0
3      13786.0  22658.0  56152.0

Without the prior aggregation, the pivot fails, because there are many orders for the same pair of category and channel:

try:
    vendas.pivot(index="categoria", columns="canal", values="quantidade")
except ValueError as erro:
    print("ValueError:", erro)

ValueError: Index contains duplicate entries, cannot reshape

The rule for choosing: if there are repetitions to combine, use pivot_table (which aggregates). If each pair is already unique, the pivot is enough.

`melt`: from wide to long

The melt does the reverse: transforms columns into rows. It is the format that graph libraries and the groupby prefer:

longo = largo.reset_index().melt(id_vars="mes", var_name="canal", value_name="receita")
print(longo.shape, round(float(longo["receita"].sum()), 2) == round(float(largo.sum().sum()), 2))
print(longo.head(3).round(1))

(36, 3) True
   mes canal  receita
0    1   app  16033.6
1    2   app  11320.5
2    3   app  13786.3

There are 12 months for 3 channels, 36 rows, and the total revenue has not changed: reformatting does not create or lose information.

`crosstab`: count combinations

The crosstab counts how many times each combination occurs, without needing a column of values. The normalize converts the count into proportion, by row, by column, or of the total:

print(pd.crosstab(vendas["segmento"], vendas["canal"]))
print(pd.crosstab(vendas["segmento"], vendas["canal"], normalize="index").round(2))

canal         app  loja  site
segmento                     
Atacado        30    43    69
Online         71   103   137
Sem cadastro    2     4     6
Varejo         64   108   163
canal          app  loja  site
segmento                      
Atacado       0.21  0.30  0.49
Online        0.23  0.33  0.44
Sem cadastro  0.17  0.33  0.50
Varejo        0.19  0.32  0.49

In the second table, each row sums to 1: it indicates, for each segment, the proportion of orders by channel. It answers the question "does the channel profile change from one segment to another?", which the gross count hides when the segments have different sizes.

`unstack` and `stack`

The unstack takes one level of the index to the columns, and the stack does the opposite. After a groupby with two keys, the unstack is the shortcut to the wide table:

agrupado = vendas.groupby(["segmento", "canal"])["receita"].sum()
largo2 = agrupado.unstack("canal")
print(largo2.round(0))
print(largo2.stack().equals(agrupado))

canal             app      loja      site
segmento                                 
Atacado       34940.0   49348.0   74926.0
Online        83440.0  128267.0  170309.0
Sem cadastro   3771.0    5218.0    8071.0
Varejo        70059.0  139222.0  203279.0
True

Percentage of each row

A wide table of values becomes more useful as participation. The div with axis=0 divides each row by its total, aligning by the index (the / simply dividing would align by the columns, and would be incorrect):

por_categoria = vendas.pivot_table(index="categoria", columns="canal", values="receita", aggfunc="sum")
participacao = por_categoria.div(por_categoria.sum(axis=1), axis=0).round(2)
print(participacao)
print(participacao.sum(axis=1).round(2).tolist())

canal         app  loja  site
categoria                    
Casa         0.23  0.33  0.45
Esporte      0.19  0.35  0.46
Informática  0.18  0.32  0.50
Papelaria    0.20  0.32  0.48
[1.01, 1.0, 1.0, 1.0]

Each row sums to 1 You will see, in much material, of rounding. The 1.01 of the first category is just the effect of rounding each cell to two decimal places: there is no error in the calculation.