Groupby columns and sort values descending by the count of rows for each group

user_13920180 0
Viewed 439

I have a dataframe (df) as follows:

 cluster    city    category    latitude    longitude   merchant
0   0   sanfran       10          39.072    -101.93253  merch2
1   0   sanfran       10          45.072    -110.93253  merch10
2   1   wichita       22          20.072    -100.93253  merch3
3   3   wichita        5          34.072    -99.93253   merch3
4   2   denver         1          40.072    -101.93253  merch1
5   1   longmont       4          30.072    -111.93253  merch2
6   1   longmont       4          30.072    -111.93253  merch2
7   3   topeka         5          20.072    -109.93253  merch1

And I want to get to this dataframe 'dfout'

  cluster   merchant    latitude    longitude   city    category
0   0        merch10    45.072  -110.93253      sanfran   10
1   0        merch2     39.072  -101.93253      sanfran   10
2   1        merch2     30.072  -111.93253      longmont    4
3   1        merch3     20.070  -100.93253       wichita    22
4   2        merch1     40.072  -101.93253       denver     1
5   3        merch1     20.072  -109.93253        topeka    5
6   3        merch3     34.072  -99.93253        wichita    5

Basically I want to group by the cluster and merchant within those clusters and count the number of rows each cluster-merchant combinations have and sort them by highest to lowest values i.e. the merchant who has highest number of rows in a specific cluster is on top and the rest of the merchants follows based on their row counts.

I can accomplish the following using the grouby statement, however thats where I get stuck.

df.groupby(['cluster','merchant']).count().sort_values(by='city',ascending=False)

                       city category    latitude    longitude
cluster merchant                
   1     merch2         2       2         2          2
   0    merch10         1       1         1          1
        merch2          1       1         1          1
   1    merch3          1       1         1          1
   2    merch1          1       1         1          1
   3    merch1          1       1        1           1
        merch3          1       1        1           1

Can somone shed some light on this issue please? how can I get from df to dfout?

Thanks

pandas-groupby pandas python
1 Answers

This will give you the desired output:

  1. You need to use .agg with your .groupby and use the first() function for all columns except for one where you need to take the count (for sorting purposes later. I used the cluster column).
  2. Since, I used the cluster column twice in the .groupby, I also had to rename it before .reset_index(), or an error would have been raised do to having two columns with the same name when resetting the index and bringing cluster into the columns of the dataframe.
  3. Sort, by the criteria cluster and cluster_count, and pass ascending=[True,False], so that you can sort ascending for the former and descending for the latter. Finally, dropt the cluster_count column.
df_out = df.copy()
df_out = (df_out.groupby(['cluster', 'merchant']).agg({'latitude' : 'first',
                                                  'longitude' : 'first',
                                                  'city' : 'first',
                                                  'category' : 'first',
                                                  'cluster' : 'count'})
          .rename({'cluster' : 'cluster_count'},axis=1).reset_index()
          .sort_values(['cluster', 'cluster_count'], ascending = [True, False])
          .drop('cluster_count', axis=1))
df_out
Out[1]: 
   cluster merchant  latitude  longitude      city  category
0        0  merch10    45.072 -110.93253   sanfran        10
1        0   merch2    39.072 -101.93253   sanfran        10
2        1   merch2    30.072 -111.93253  longmont         4
3        1   merch3    20.072 -100.93253   wichita        22
4        2   merch1    40.072 -101.93253    denver         1
5        3   merch1    20.072 -109.93253    topeka         5
user_63667700
Related