2
from pandas import Series, DataFrame
import pandas as pd
df1=pd.read_csv('/Users/nirmal/Desktop/Python/Assignments/Data/employee_compensation.csv', sep=',', skiprows=(1,1))
dfq2=DataFrame(df1.groupby(["Organization Group", "Department"])['Total Compensation'].mean())
dfq2

enter image description here

I need to sort the Total Compensation column descending order wise. and based on it Department should change within each Organization group. Organization group column should not get changed.

Harish
  • 565
  • 1
  • 12
  • 34
  • Possible duplicate of [In Pandas How to sort one level of a multi-index based on the values of a column, while maintaining the grouping of the other level](http://stackoverflow.com/questions/20413313/in-pandas-how-to-sort-one-level-of-a-multi-index-based-on-the-values-of-a-column) – Zeugma Apr 05 '17 at 03:39

1 Answers1

0

You can use sort_values with sort_index:

print (df.sort_values('Total Compensation', ascending=False)
         .sort_index(level=0, sort_remaining=False))
                                                                 Total Compensation
Organization Group               Department                                        
Community Health                 Academy of Sciences                  107319.727692
                                 Public Health                         96190.190140
                                 Arts Commission                       94339.597388
                                 Asian Art Museum                      71401.520060
Culture & Recreation             Law Library                          188424.362222
                                 City Attorney                        166082.677561
                                 Controller                           104515.234944
                                 Assessor/Recorder                     89994.260614
                                 City Planning                         89022.876966
                                 Board of Supervisors                  78801.347641
                                 War Memorial                          76250.068022
                                 Public Library                        70446.352147
                                 Civil Service Commission              67966.756559
                                 Fine Arts Museum                      44205.439895
                                 Recreation and Park Commission        38912.859465
                                 Elections                             20493.166618
General Administration & Finance Ethics Commission                     98631.380366

Another solution with reset_index, sort_values and set_index:

print (df.reset_index()
         .sort_values(['Organization Group','Total Compensation'], ascending=[True, False])
         .set_index(['Organization Group','Department']))

                                                                 Total Compensation
Organization Group               Department                                        
Community Health                 Academy of Sciences                  107319.727692
                                 Public Health                         96190.190140
                                 Arts Commission                       94339.597388
                                 Asian Art Museum                      71401.520060
Culture & Recreation             Law Library                          188424.362222
                                 City Attorney                        166082.677561
                                 Controller                           104515.234944
                                 Assessor/Recorder                     89994.260614
                                 City Planning                         89022.876966
                                 Board of Supervisors                  78801.347641
                                 War Memorial                          76250.068022
                                 Public Library                        70446.352147
                                 Civil Service Commission              67966.756559
                                 Fine Arts Museum                      44205.439895
                                 Recreation and Park Commission        38912.859465
                                 Elections                             20493.166618
General Administration & Finance Ethics Commission                     98631.380366
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • I have more more question with regard to extract function – Harish Apr 05 '17 at 16:35
  • df['Awards'].str.extract('Won (\d+) Oscar([s]+)', expand=True).fillna(0). Sometimes the data has only Oscar and some data has Oscars. How to match regex for both in the above expression. – Harish Apr 05 '17 at 16:35
  • Hmmm, I think the best is create new question - simply get data sample, desired output and your code (`df['Awards'].str.extract('Won (\d+) Oscar([s]+)', expand=True).fillna(0)`). I am not regex expert, but I think somebody help you. Because without data it is imposible answer. – jezrael Apr 05 '17 at 16:38