Setting a row index on and querying a pandas dataframe with multi-index columns

Question

Starting from a pandas dataframe with a multi-dimensional column heading structure such as the following, is there a way I can transform the Area Names and Area Codes headings so they span each level (i.e. so single Area Names and Area Codes labels spanning the multiple column heading rows?

If so, how could I then run a query on the column to just return the row corresponding to a particular value (e.g. an Area Code of E06000047), or the Low and Very High values for ENGLAND in 2012/13?

I wonder if it would be easier to define a row index based on either Area Code or Area Names, or a two column row index ['*Area Code*', '*Area Names*']. And if so, how can I do this from the current table? set_index seems to balk at this using the current structure?

Code fragment to create the above:

import pandas as pd

df= pd.DataFrame({('2011/12*', 'High', '7-8'): {3: 49.83,
  5: 50.01,
  7: 48.09,
  8: 43.58,
  9: 44.19},
 ('2011/12*', 'Low', '0-4'): {3: 6.51, 5: 6.53, 7: 6.49, 8: 6.41, 9: 6.12},
 ('2011/12*', 'Medium', '5-6'): {3: 17.44,
  5: 17.59,
  7: 18.11,
  8: 19.23,
  9: 20.01},
 ('2011/12*', 'Very High', '9-10'): {3: 26.22,
  5: 25.87,
  7: 27.32,
  8: 30.78,
  9: 29.68},
 ('2012/13*', 'High', '7-8'): {3: 51.16,
  5: 51.35,
  7: 48.47,
  8: 44.67,
  9: 49.39},
 ('2012/13*', 'Low', '0-4'): {3: 5.71, 5: 5.74, 7: 6.73, 8: 8.42, 9: 6.51},
 ('2012/13*', 'Medium', '5-6'): {3: 17.1,
  5: 17.29,
  7: 18.46,
  8: 20.23,
  9: 15.81},
 ('2012/13*', 'Very High', '9-10'): {3: 26.03,
  5: 25.62,
  7: 26.34,
  8: 26.68,
  9: 28.3},
 ('Area Codes', 'Area Codes', 'Area Codes'): {3: 'K02000001',
  5: 'E92000001',
  7: 'E12000001',
  8: 'E06000047',
  9: 'E06000005'},
 ('Area Names', 'Area Names', 'Area Names'): {3: 'UNITED KINGDOM',
  5: 'ENGLAND',
  7: 'NORTH EAST',
  8: 'County Durham',
  9: 'Darlington'}})

jezrael · Accepted Answer · 2016-09-28 11:14:57Z

I think you need set_index with tuples for set by MultiIndex:

df.set_index([('Area Codes','Area Codes','Area Codes'),
              ('Area Names','Area Names','Area Names')], inplace=True)
df.index.names = ['Area Codes','Area Names']
print (df)
                          2011/12*                        2012/13*        \
                              High   Low Medium Very High     High   Low   
                               7-8   0-4    5-6      9-10      7-8   0-4   
Area Codes Area Names                                                      
K02000001  UNITED KINGDOM    49.83  6.51  17.44     26.22    51.16  5.71   
E92000001  ENGLAND           50.01  6.53  17.59     25.87    51.35  5.74   
E12000001  NORTH EAST        48.09  6.49  18.11     27.32    48.47  6.73   
E06000047  County Durham     43.58  6.41  19.23     30.78    44.67  8.42   
E06000005  Darlington        44.19  6.12  20.01     29.68    49.39  6.51   


                          Medium Very High  
                             5-6      9-10  
Area Codes Area Names                       
K02000001  UNITED KINGDOM  17.10     26.03  
E92000001  ENGLAND         17.29     25.62  
E12000001  NORTH EAST      18.46     26.34  
E06000047  County Durham   20.23     26.68  
E06000005  Darlington      15.81     28.30

Then need sort_index, because:

KeyError: 'MultiIndex Slicing requires the index to be fully lexsorted tuple len (2), lexsort depth (0)'

df.sort_index(inplace=True)

Last use selecting by slicers:

idx = pd.IndexSlice
print (df.loc[idx['E06000047',:], :])

                        2011/12*                        2012/13*        \
                             High   Low Medium Very High     High   Low   
                              7-8   0-4    5-6      9-10      7-8   0-4   
Area Codes Area Names                                                     
E06000047  County Durham    43.58  6.41  19.23     30.78    44.67  8.42   


                         Medium Very High  
                            5-6      9-10  
Area Codes Area Names                      
E06000047  County Durham  20.23     26.68

print (df.loc[idx[:,'ENGLAND'], idx['2012/13*',['Low','Very High']]])
                      2012/13*          
                           Low Very High
                           0-4      9-10
Area Codes Area Names                   
E92000001  ENGLAND        5.74     25.62

Collectives™ on Stack Overflow

Setting a row index on and querying a pandas dataframe with multi-index columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related