pivot_table of frequency

Asked

Viewed 14 times

0

I want to build a pivot_table on pandas that counts frequency. For example, I have a sex column, with 2 options, one of UF, with 5 options, and one of color, with 4 options, I wanted to make a table that shows how many people x sex and y UF have z color, but I find myself in a looping of testing color as values and as Columns but finding no answer. (aggfunc Count could not use this way)

python pandas date pivot

1 answer

0

I believe that groupby with the agg is the solution. See the example:

Loading libraries

import pandas as pd
import random

Auxiliary variables

sexos = ["M", "F"]
ufs = ["BA", "ES", "MT", "RJ", "SP"]
cores = ["amarelo", "azul", "vermelho", "roxo", "verde"]

tamanho = 1000

Creating Test Dataframe

df = pd.DataFrame({"sexo": [random.choice(sexos) for _ in range(tamanho)], "uf": [random.choice(ufs) for _ in range(tamanho)], "cor": [random.choice(cores) for _ in range(tamanho)]})

Creating "pivot_table"

print(df.groupby(["sexo", "uf", "cor"]).agg({"cor": 'count'}))

The result will be something like below:

                  cor
sexo uf cor
F    BA amarelo    21
        azul       20
        roxo       14
        verde      15
        vermelho   25
     ES amarelo    23
        azul       23
        roxo       22
        verde      24
        vermelho   25
     MT amarelo    24
        azul       19
        roxo       16
        verde      26
        vermelho   14
     RJ amarelo    14
        azul       20
        roxo       14
        verde      21
        vermelho   14
     SP amarelo    20
        azul       24
        roxo       17
        verde      18
        vermelho   14
M    BA amarelo    15
        azul       17
        roxo       24
        verde      22
        vermelho   30
     ES amarelo    14
        azul       19
        roxo       18
        verde      22
        vermelho   20
     MT amarelo    33
        azul       19
        roxo       15
        verde      18
        vermelho   22
     RJ amarelo    29
        azul       18
        roxo       20
        verde      19
        vermelho   19
     SP amarelo    17
        azul       22
        roxo       17
        verde      22
        vermelho   22

Browser other questions tagged python pandas date pivot

You are not signed in. Login or sign up in order to post.