Pandas: Acceptance and rejection percentage

Question

I have a dataset that contains some columns that inform me: user, situacao_requisicao, city. I would like to generate a csv that informs me the amount of requisicoes of each city, how many were accepted, and how many were rejected. Ex from the dataset:

+----------+-------------------+----------------+
|   nome   |situacao_requisicao|     cidade     |
|----------|-------------------|----------------|
|  felipe  |     aceita        |      belem     |
|    ana   |     aceita        |      belem     |
|   fabio  |    recusada       |      recife    |
| fabiane  |     aceita        |      belem     |
| leticia  |     aceita        | rio de janeiro |
|   luan   |    recusada       |      recife    |
+----------+-------------------+----------------+

currently I can only get the amount of accepted or denied, or the total. all this separately doing something like this:

df = pd.read_csv("../../data/bases/data.csv")
df["nome"].count()

If anyone can tell me how to gather all the above information, I would appreciate it.

Answer 1

You can do this by creating 2 temporary Dataframes using functions such as groupby, value_counts and pivot, one of them will have the total requisitions per city, and another will have the amount of requisitions accepted and refused.
In the end it is necessary to join the Dataframes with merge.

total = df.groupby('cidade')['cidade'].count().rename('Total de requisições').reset_index()

requisicoes = df.groupby('cidade') \
['situacao_requisicao'].value_counts().rename('countRequisicoes').reset_index()

requisicoes = pd.pivot(requisicoes, values='countRequisicoes', 
                                    index='cidade', 
                                    columns='situacao_requisicao').reset_index()

total.merge(requisicoes, on = 'cidade')
saida:
    cidade          Total de requisições    aceita  recusada
0   belem           3                       3.0     NaN
1   Rio de janeiro  1                       1.0     NaN
2   recife          2                       NaN     2.0