How to filter rows of Pandas dataframe by checking whether sub-level index value within a list?
pandas, python
Solution
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
Problem
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?