Get the maximum value of each row in a grouped pandas dataframe

Question

I have a pandas dataframe with UF, Municipio, Classe_acidente, Total.

In this dataframe each municipality appears three times, one for each accident class (there are 3 classes) and I need to obtain the maximum value for each accident class. That is, I have to go through the entire dataframe and get the maximum of each class, by UF.

Fez

dfAcidentesPorMunicipiosPorUF = dfAcidentesPorMunicipiosPorUF.groupby(['uf','municipio','classificacao_acidente'])['classificacao_acidente'].count().reset_index(name="Total")

And returned grouped correctly, but, I can’t get the most.

    uf  municipio     classificacao_acidente       Total
0   AC  ACRELANDIA    Com Vítimas Feridas           10
1   AC  ASSIS BRASIL  Sem Vítimas                   6
2   AC  BRASILEIA     Com Vítimas Fatais            5
3   AC  BRASILEIA     Com Vítimas Feridas           8
4   AC  BRASILEIA     Sem Vítimas                   2
5   AC  BUJARI        Com Vítimas Fatais            5
6   AC  BUJARI        Com Vítimas Feridas           65
7   AC  BUJARI        Sem Vítimas                   26
47  TO  PARAISO DO    Sem Vítimas                   59
47  TO  PEDRO AFONSO  Com Vítimas Feridas           4
47  TO  PEIXE         Com Vítimas Fatais            18
47  TO  PEIXE         Com Vítimas Feridas           23
47  TO  PIRAQUE       Com Vítimas Feridas           5
47  TO  PIRAQUE       Sem Vítimas                   1
47  TO  KENNEDY       Com Vítimas Fatais            6
47  TO  KENNEDY       Com Vítimas Feridas           25
47  TO  KENNEDY       Sem Vítimas                   22

Any idea how to do that?

I broke my head, but I couldn’t.

Thank you.

Answer 1

With groupby and transform it is possible to select the highest value of each class per state

df = dfAcidentesPorMunicipiosPorUF.copy()
df.loc[df['Total'] == df.groupby(['uf','classificacao_acidente'])['Total'].transform('max')]

saida:
    uf  municipio   classificacao_acidente  Total
2   AC  BRASILEIA   Com Vítimas Fatais      5
5   AC  BUJARI      Com Vítimas Fatais      5
6   AC  BUJARI      Com Vítimas Feridas     65
7   AC  BUJARI      Sem Vítimas             26
8   TO  PARAISO DO  Sem Vítimas             59
10  TO  PEIXE       Com Vítimas Fatais      18
15  TO  KENNEDY     Com Vítimas Feridas     25

Answer 2

You said you wanted to group by state and by accident classification, so I don’t see the need for you to include the county in the code. Check that the line below solves your problem:

dfAcidentesPorMunicipiosPorUF = 
dfAcidentesPorMunicipiosPorUF.groupby(['uf','classificacao_acidente']) 
['classificacao_acidente'].count().reset_index(name="Total")

You can also try with the function max() instead of count().