Create a new dataframe from the top values of another dataframe in Pandas

user_13917409 0
Viewed 125

I have made a ranking of the top 20 cities based on the sum of the daily Covid-19 cases in Brazil with the following code:

df_cidades.groupby('municipio')['obitosNovos'].sum().nlargest(20).sort_values(ascending=False)

from where I obtained the result:

municipio
São Paulo                  8696
Rio de Janeiro             7621
Fortaleza                  3582
Belém                      2026
Recife                     2024
Manaus                     1947
Salvador                   1521
Brasília                   1060
São Luís                   1036
Guarulhos                   906
Jaboatão dos Guararapes     667
Maceió                      663
Natal                       641
Teresina                    574
Osasco                      569
São Bernardo do Campo       543
São Gonçalo                 529
Campinas                    526
João Pessoa                 506
Duque de Caxias             492
Name: obitosNovos, dtype: int64

I would like to create a new dataframe considering only these cities. I tried:

df_selecao = df_cidades[df_cidades.groupby('municipio')['obitosNovos'].sum().nlargest(20).sort_values(ascending=False)]

But I get the error:

---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
<ipython-input-115-268c25478365> in <module>
----> 1 df_selecao = df_cidades[df_cidades.groupby('municipio')['obitosNovos'].sum().nlargest(20).sort_values(ascending=False)].copy()

~/opt/anaconda3/lib/python3.7/site-packages/pandas/core/frame.py in __getitem__(self, key)
   2804             if is_iterator(key):
   2805                 key = list(key)
-> 2806             indexer = self.loc._get_listlike_indexer(key, axis=1, raise_missing=True)[1]
   2807 
   2808         # take() does not accept boolean indexers

~/opt/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in _get_listlike_indexer(self, key, axis, raise_missing)
   1550 
   1551         self._validate_read_indexer(
-> 1552             keyarr, indexer, o._get_axis_number(axis), raise_missing=raise_missing
   1553         )
   1554         return keyarr, indexer

~/opt/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py in _validate_read_indexer(self, key, indexer, axis, raise_missing)
   1637             if missing == len(indexer):
   1638                 axis_name = self.obj._get_axis_name(axis)
-> 1639                 raise KeyError(f"None of [{key}] are in the [{axis_name}]")
   1640 
   1641             # We (temporarily) allow for some missing keys with .loc, except in

KeyError: "None of [Int64Index([8696, 7621, 3582, 2026, 2024, 1947, 1521, 1060, 1036,  906,  667,\n             663,  641,  574,  569,  543,  529,  526,  506,  492],\n           dtype='int64')] are in the [columns]"

Any tips on how to solve it? Thanks!

pandas python
2 Answers

Try this:

df_selecao = df_cidades.groupby('municipio')['obitosNovos'].sum().nlargest(20).to_frame().reset_index().sort_values(by='municipio', ascending=False)

There was also a missing argument in sort_values() as well. Set the sort_values column accordingly.

user_135917220

I think just like this should work:

df_selecao = df_cidades.groupby('municipio')['obitosNovos'].
                  sum().nlargest(20).sort_values(ascending=False)
df_selecao.head()
user_123746200
Related