key error and MultiIndex lexsort depth

Question

I have a set of tab delimited files that I have to go through read them, use them as pandas dataframe, do a whole bunch of operations on them and then merge them back to one excel file, the code is too long so I am going to go through the problematic part of it

The tab files that I am parsing contains all the same number of rows 2177

When I read these files I am indexing by the first 2 columns of type (string, int)

df = df.set_index(['id', 'coord'])
data = OrderedDict()
#data will contain all the information I am writing to excel
data[filename_id] = df

one of the procedures I am doing needs access to each row of data[sample_id] which contains dataframe of mixed types indexed with the columns 'id' and 'coord', like this

sample_row = data[sample].ix[index]

my index being ('id','coord')

If I am treating a subset of the file everything works great, but If I read the entire files with 2177 lines I end up having this error message

KeyError: 'Key length (2) was greater than MultiIndex lexsort depth (0)'

I searched over SO and everywhere and it seems that this is an issue of sorting the index, but I dont understand why using an unsorted subset do not cause the problem

Any idea on how I can get this sorted out ?

Thanks

Answer 1

Docs are quite good. If you work with multi-indexes it pays to read them thru (several times!), see here

In [9]: df = DataFrame(np.arange(9).reshape(-1,1),columns=['value'],index=pd.MultiIndex.from_product([[1,2,3],['a','b','c']],names=['one','two']))

In [10]: df
Out[10]: 
         value
one two       
1   a        0
    b        1
    c        2
2   a        3
    b        4
    c        5
3   a        6
    b        7
    c        8

In [11]: df.index.lexsort_depth
Out[11]: 2

In [12]: df.sortlevel(level=1)
Out[12]: 
         value
one two       
1   a        0
2   a        3
3   a        6
1   b        1
2   b        4
3   b        7
1   c        2
2   c        5
3   c        8

In [13]: df.sortlevel(level=1).index.lexsort_depth
Out[13]: 0

In [9]: df = DataFrame(np.arange(9).reshape(-1,1),columns=['value'],index=pd.MultiIndex.from_product([[1,2,3],['a','b','c']],names=['one','two']))

In [10]: df
Out[10]: 
         value
one two       
1   a        0
    b        1
    c        2
2   a        3
    b        4
    c        5
3   a        6
    b        7
    c        8

In [11]: df.index.lexsort_depth
Out[11]: 2

In [12]: df.sortlevel(level=1)
Out[12]: 
         value
one two       
1   a        0
2   a        3
3   a        6
1   b        1
2   b        4
3   b        7
1   c        2
2   c        5
3   c        8

In [13]: df.sortlevel(level=1).index.lexsort_depth
Out[13]: 0

Update:

sortlevel will be deprecated so use sort_index i.e

df.sort_index(level=1)