sort by values for each group in a MultiIndex dataframe

user_1021306 0
Viewed 108

I am trying to convert a MultiIndex dataframe (which was converted from a xarray Dataset with 3 dimensions being time, latitude and longitude and two variables "FFDI" and "REF_ID").

The dataframe looks like:

                                    FFDI    REF_ID
latitude    longitude   time        
-39.200001  140.800003  1972-01-20  1.2     0
                        1972-01-21  0.9     0
                        1972-01-22  1.5     0
                        1972-01-23  1.6     0
                        1972-01-24  1.1     0
                        1972-01-25  1.1     0
                        1972-01-26  1.0     0
                        1972-01-27  1.4     0
                        1972-01-28  1.4     0
                        1972-01-29  1.8     0
                        1972-01-30  1.7     0
                        1972-01-31  1.4     0
                        1972-02-01  0.9     0
                        1972-02-02  0.9     0
                        1972-02-03  1.4     0
            ...     ...     ...     ...     ...
-33.900002  150.000000  2020-06-17  0.7     36111
                        2020-06-18  0.9     36111
                        2020-06-19  1.6     36111
                        2020-06-20  1.8     36111
                        2020-06-21  0.6     36111
                        2020-06-22  0.4     36111
                        2020-06-23  0.6     36111
                        2020-06-24  0.6     36111
                        2020-06-25  1.2     36111
                        2020-06-26  1.3     36111
                        2020-06-27  1.1     36111
                        2020-06-28  1.7     36111
                        2020-06-29  0.9     36111
                        2020-06-30  1.8     36111
                        2020-07-01  0.8     36111
                        
639037952 rows × 2 columns

The original xarray Dataset three dimensions of time (17696, daily from 1972-01-20 to 2020-06-30) latitude (148) amd longitude (244).

​I would like to sort date/time by FFDI values for each (latitude, longitude) group.

                                    FFDI    REF_ID
latitude    longitude   time        
-39.200001  140.800003  2009-02-07  10.2    0
                        2009-01-30  10.1    0
                        1983-02-12  10.0    0
                        2003-01-13  9.8     0
                        2019-12-28  9.8     0
                        2000-01-17  9.7     0
            ...     ...     ...     ...     ...

-33.900002  150.000000  ... ...     ...     ...
                        1994-06-16  0.9     36111
                        1978-07-07  0.2     36111
                        2020-08-28  0.1     36111
                        2007-06-09  0.0     36111
                        1994-07-30  0.0     36111
                        1987-06-21  0.0     36111
                        
639037952 rows × 2 columns
python-xarray pandas python
2 Answers

I think you need DataFrame.sort_values by both levels and column FFDI:

df = df.sort_values(['latitude','longitude','FFDI'], ascending=[True, True, False])

Or:

df = df.sort_values('FFDI', ascending=False).sort_index(level = [0,1], sort_remaining=False)

If cannot be sorted by first 2 levels use:

f = lambda x: x.sort_values('FFDI', ascending=False)
df = df.groupby(level=[0, 1], group_keys=False, sort=False).apply(f)
user_29010020 
import pandas as pd
import pandasql as ps

df = pd.read_csv("URCSV.csv")
a = ps.sqldf('Select * from df Group by "latitude","longitude" order by "FFDI" ')
print(a)
Admin1
Related