How to filter rows of Pandas dataframe by checking whether sub-level index value within a list?

Question

I have a sample Pandas dataframe df which has multi_level index:

>>> df
                STK_Name   ROIC   mg_r
STK_ID RPT_Date                       
002410 20111231      ???  0.401  0.956
300204 20111231      ???  0.375  0.881
300295 20111231     ????  2.370  0.867
300288 20111231     ????  1.195  0.861
600106 20111231     ????  1.214  0.857
300113 20111231     ????  0.837  0.852

and stk_list is defined as stk_list = ['600106','300204','300113']

I want to get the rows of df whose value of sub_level index STK_ID is within stk_list . The output is as below:

                STK_Name   ROIC   mg_r
STK_ID RPT_Date                       
300204 20111231      ???  0.375  0.881
600106 20111231     ????  1.214  0.857
300113 20111231     ????  0.837  0.852

Basically, I can achieve the target for this sample data by:

df = df.reset_index() ; df[df.STK_ID.isin(stk_list)]

But I already have columns 'STK_ID' & 'RPT_Date' in my application dataframe, so reset_index() will cause an error. Anyway, I want to directly filter against index instead of columns.

Learn from this : How to filter by sub-level index in Pandas

I try df[df.index.map(lambda x: x[0].isin(stk_list))] , and Pandas 0.8.1 gives AttributeError: 'unicode' object has no attribute 'isin',

My question: How should I filter rows of Pandas dataframe by checking whether sub-level index value within a list without using the reset_index() & set_index() methods?

Answer 1

You can try:

df[df.index.map(lambda x: x[0] in stk_list)]

Example:

In : stk_list
Out: ['600106', '300204', '300113']

In : df
Out:
                STK_Name   ROIC   mg_r
STK_ID RPT_Date
002410 20111231      ???  0.401  0.956
300204 20111231      ???  0.375  0.881
300295 20111231     ????  2.370  0.867
300288 20111231     ????  1.195  0.861
600106 20111231     ????  1.214  0.857
300113 20111231     ????  0.837  0.852

In : df[df.index.map(lambda x: x[0] in stk_list)]
Out:
                STK_Name   ROIC   mg_r
STK_ID RPT_Date
300204 20111231      ???  0.375  0.881
600106 20111231     ????  1.214  0.857
300113 20111231     ????  0.837  0.852

Answer 2

How about using the level parameter in DataFrame.reindex?

In [14]: df
Out[14]: 
            0         1
a 0  0.007288 -0.840392
  1  0.652740  0.597250
b 0 -1.197735  0.822150
  1 -0.242030 -0.655058

In [15]: stk_list = ['a']

In [16]: df.reindex(stk_list, level=0)
Out[16]: 
            0         1
a 0  0.007288 -0.840392
  1  0.652740  0.597250