CODEWITHSUNDEEP
pythonpandasfiltermulti-index
rhaskett
rhaskett

Reputation: 1962

Filtering on Column by a Value That Changes Depending on an MultiIndex Level

Complicated title but pretty simple problem. I have a DataFrame with a MultiIndex:

enter image description here

I'd like rows of the frame above but the 'Filter Column' must be greater than or equal to the values in the filter_value series below.

filter_value = Series([1, 3], ['red', 'blue'])

The correct solution for this toy problem would be the same dataframe but with only the (red, 2), (blue, 2) and (blue, 3) rows left.

To set up for the above Frame:

arrays = [['red', 'red', 'blue', 'blue', 'blue'], [1, 2, 1, 2, 3]]
idx = MultiIndex.from_arrays(arrays, names=['Color', 'Count'])

values = Series(2, idx, name='Value')
ratios = Series(range(5), idx, name='Filter Column')
df = concat([values, ratios], axis='columns')

Upvotes: 1

Views: 85

Answers (2)

Scott Boston
Scott Boston

Reputation: 153460

You can try this:

pd.concat(df.align(filter_value.rename('filter'), level=0, axis=0), axis=1)\
  .loc[lambda x: x['Filter Column']>=x['filter']]

Output:

             Value  Filter Column  filter
Color Count                              
red   2          2              1       1
blue  2          2              3       3
      3          2              4       3

Upvotes: 2

BENY
BENY

Reputation: 323276

Seem like you need get_level_values + map, then using the value comparison get the Boolean filter the df 

df[df['Filter Column'].values>=df.index.get_level_values(0).map(filter_value)]
Out[108]: 
             Value  Filter Column
Color Count                      
red   2          2              1
blue  2          2              3
      3          2              4

Upvotes: 2

Related Questions