Sorting Pandas MultiIndex by the last value of level 0 index

user_13562864 0
Viewed 261

I have a df called df_world with the following shape:

                               Cases   Death  Delta_Cases  Delta_Death
Country/Region Date                                                       
Brazil         2020-01-22        0.0       0          NaN          NaN
               2020-01-23        0.0       0          0.0          0.0
               2020-01-24        0.0       0          0.0          0.0
               2020-01-25        0.0       0          0.0          0.0
               2020-01-26        0.0       0          0.0          0.0
                             ...     ...          ...          ...
World          2020-05-12  4261747.0  291942      84245.0       5612.0
               2020-05-13  4347018.0  297197      85271.0       5255.0
               2020-05-14  4442163.0  302418      95145.0       5221.0
               2020-05-15  4542347.0  307666     100184.0       5248.0
               2020-05-16  4634068.0  311781      91721.0       4115.0

I'de like to sort the country index by the value of the columns 'Cases' on the last recording i.e. comparing the cases values on 2020-05-16 for all countries and return the sorted country list

I thought about creating another df with only the 2020-05-16 values and then use the df.sort_values() method but I am sure there has to be a more efficient way.

While I'm at it, I've also tried to only select the countries that have a number of cases on 2020-05-16 above a certain value and the only way I found to do it was to iterate over the Country index:

for a_country in df_world.index.levels[0]:
        if df_world.loc[(a_country, last_date), 'Cases'] < cut_off_val:
            df_world = df_world.drop(index=a_country)

But it's quite a poor way to do it.

If anyone has an idea on how the improve the efficiency of this code I'de be very happy.

Thank you :)

multi-index pandas python
2 Answers

You can first group thee dataset by "Country/Region", then sort each group by "Date", take the last one, and sort again by "Cases".

Faking some data myself (data types are different but you see my point):

df = pd.DataFrame([['a', 1, 100],
                   ['a', 2, 10],
                   ['b', 2, 55],
                   ['b', 3, 15],
                   ['c', 1, 22],
                   ['c', 3, 80]])
df.columns = ['country', 'date', 'cases']
df = df.set_index(['country', 'date'])
print(df)
#               cases
# country date       
# a       1       100
#         2        10
# b       2        55
#         3        15
# c       1        22
#         3        80

Then,

# group them by country
grp_by_country = df.groupby(by='country')
# for each group, aggregate by sorting by data and taking the last row (latest date)
latest_per_grp = grp_by_country.agg(lambda x: x.sort_values(by='date').iloc[-1])
# sort again by cases
sorted_by_cases = latest_per_grp.sort_values(by='cases')

print(sorted_by_cases)
#          cases
# country       
# a           10
# b           15
# c           80

Stay safe!

user_135606230 
last_recs = df_world.reset_index().groupby('Country/Region').last()
sorted_countries = last_recs.sort_values('Cases')['Country/Region']

As I don't have your raw data, I can't test it but this should do what you need. All methods are self-explanatory I believe.

you may need to sort df_world by the dates in the first line if it isn't the case.

user_17733440
Related