pandas: loop over tables in excel sheet

Question

I'm trying to loop over a set of tables in a particular way but I'm stuck.

My tables are multiindex and look like this:

#read excel 
df = pd.read_excel(data_file,
                   header=[0,1],
                   index_col=[0,1])

                           T        Gender                  Age                         
                         Total   Male Female 16-24 25-34 35-44 45-54 55-75 
Q1. Are you?  Yes         17.5   26.8   23.4  13.7  20.7   100     -  17.6    
              No          17.5   26.8   23.4  13.7  20.7   100  11.5  22.6 
              Don’t know  17.5   26.8   23.4  13.7  20.7   100     -     -
Q2. Are you?  Yes         18.5   26.8   23.4  13.7  20.7   100     -  17.6    
              No          17.5   22.8   23.4  13.7  20.7   100  11.5  22.6 
              Don’t know  17.5   26.8   23.4  13.7  20.7   100     -     -

I would like to loop over these indexes and columns and print this:

                           T                             
                          Total   
Q1. Are you?  Yes         17.5    
              No          17.5  
              Don’t know  17.5 

                            Gender                                              
                          Male Female 
Q1. Are you?  Yes         26.8   23.4  
              No          26.8   23.4  
              Don’t know  26.8   23.4

and so forth....

My code so far groups the outter indexs together which allows me to loop downwards but I dont know how to work my way across horizontally..?

for outerside_grp, innerside_grp in df.groupby(level=0):
    print innerside_grp

UPDATE

Code below kinda of does what I want (thanks to Joshua Baboo) but now I'm wondering if it's the most effient method?

for key in df.index.levels[0]:
    for col in df.columns.levels[0]:
        print df.loc[row:row, col]

Joshua Baboo · Accepted Answer · 2016-04-23 19:48:57Z

1

as you've said:

'My tables are multiindex'

assuming the groupby(level=0) is not required, as the original dataframe is in 2 level MultiIndex structure on both row & column axes, see if the following sample servers your purpose:

import pandas as pd
print 'pandas-version: ', pd.__version__
import numpy a`enter code here`s np
l1 = ['r0_1', 'r0_2']
l2 = sorted(['r1_1','r1_2','r1_3'])
c1 = ['c0_1', 'c0_2', 'c0_3']
c2 = ['c1_1', 'c1_2', 'c1_3']
nrows = len(l1) * len(l2)
ncols = len(c1) * len(c2)
df = pd.DataFrame(np.random.random( nrows * ncols).reshape(nrows, ncols),
                 index=pd.MultiIndex.from_product([l1, l2],
                                                 names=['one','two']),
                 columns=pd.MultiIndex.from_product([c1, c2]))
l_all = slice(None)

# updated loop only over columns.level[0]
# to get all-rows for each column group
for col0 in df.columns.levels[0]:
    print df.loc(axis=1)[col0,:]

output

pandas-version:  0.15.2
               c0_1                    
               c1_1      c1_2      c1_3
one  two                               
r0_1 r1_1  0.177051  0.159676  0.677900
     r1_2  0.980404  0.441649  0.763252
     r1_3  0.631876  0.724937  0.158891
r0_2 r1_1  0.856933  0.432360  0.690534
     r1_2  0.568308  0.381117  0.430071
     r1_3  0.680781  0.795433  0.378414
               c0_2                    
               c1_1      c1_2      c1_3
one  two                               
r0_1 r1_1  0.275005  0.266315  0.326656
     r1_2  0.841370  0.197737  0.215751
     r1_3  0.511860  0.007003  0.509688
r0_2 r1_1  0.170542  0.577844  0.616402
     r1_2  0.440131  0.497631  0.628281
     r1_3  0.061970  0.192166  0.687346
               c0_3                    
               c1_1      c1_2      c1_3
one  two                               
r0_1 r1_1  0.308490  0.372552  0.275818
     r1_2  0.718901  0.784083  0.839253
     r1_3  0.357739  0.821503  0.336578
r0_2 r1_1  0.758157  0.248164  0.983741
     r1_2  0.498885  0.972781  0.922519
     r1_3  0.107162  0.364109  0.591648

ref for .loc(axis=0)

edited Apr 23, 2016 at 19:48

answered Apr 11, 2016 at 18:59

Joshua Baboo

5251 gold badge4 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Boosted_d16 Over a year ago

Good idea, i'll try that today :)

Boosted_d16 Over a year ago

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

Joshua Baboo Over a year ago

hi @Boosted_d16 did you get to resolve the error? which is the pandas version that you are using; I'm on pandas-version: 0.15.2

Boosted_d16 Over a year ago

I'm on 0.16.2 and I managed to edit your method but it doesnt seem ideal.

Joshua Baboo Over a year ago

@Boosted_d16 I've updated the code-snippet to loop-thru only the columns.levels[0] to get all rows for each column-group in source-data; I've also added documentation ref for further tweaking to get your exact format.

Collectives™ on Stack Overflow

pandas: loop over tables in excel sheet

1 Answer 1

output

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

output

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related