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:
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')
python pandas filter multi-index
asked Nov 13 '18 at 20:31
rhaskettrhaskett
4801622
add a comment |
Complicated title but pretty simple problem. I have a DataFrame with a MultiIndex:
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')
python pandas filter multi-index
asked Nov 13 '18 at 20:31
rhaskettrhaskett
4801622
add a comment |
Complicated title but pretty simple problem. I have a DataFrame with a MultiIndex:
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')
python pandas filter multi-index
asked Nov 13 '18 at 20:31
rhaskettrhaskett
4801622
Complicated title but pretty simple problem. I have a DataFrame with a MultiIndex:
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')
python pandas filter multi-index
python pandas filter multi-index
asked Nov 13 '18 at 20:31
rhaskettrhaskett
4801622
asked Nov 13 '18 at 20:31
rhaskettrhaskett
4801622
asked Nov 13 '18 at 20:31
rhaskettrhaskett
4801622
asked Nov 13 '18 at 20:31
rhaskettrhaskett
4801622
asked Nov 13 '18 at 20:31
rhaskettrhaskett
4801622
4801622
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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
answered Nov 13 '18 at 20:46
W-BW-B
104k73165
1
This is nice and readable. Is the.valuesnecessary here?
– rhaskett
Nov 13 '18 at 21:39
@rhaskett yep, since you have multiple index and pandas is index sensitive, I have the habit using the values for safety since I usually do assign as well :-)
– W-B
Nov 13 '18 at 21:46
add a comment |
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
answered Nov 13 '18 at 20:46
Scott BostonScott Boston
52.7k72955
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289044%2ffiltering-on-column-by-a-value-that-changes-depending-on-an-multiindex-level%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 13 '18 at 20:46
W-BW-B
104k73165
1
This is nice and readable. Is the.valuesnecessary here?
– rhaskett
Nov 13 '18 at 21:39
@rhaskett yep, since you have multiple index and pandas is index sensitive, I have the habit using the values for safety since I usually do assign as well :-)
– W-B
Nov 13 '18 at 21:46
add a comment |
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
answered Nov 13 '18 at 20:46
W-BW-B
104k73165
1
This is nice and readable. Is the.valuesnecessary here?
– rhaskett
Nov 13 '18 at 21:39
@rhaskett yep, since you have multiple index and pandas is index sensitive, I have the habit using the values for safety since I usually do assign as well :-)
– W-B
Nov 13 '18 at 21:46
add a comment |
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
answered Nov 13 '18 at 20:46
W-BW-B
104k73165
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
answered Nov 13 '18 at 20:46
W-BW-B
104k73165
answered Nov 13 '18 at 20:46
W-BW-B
104k73165
answered Nov 13 '18 at 20:46
W-BW-B
104k73165
answered Nov 13 '18 at 20:46
W-BW-B
104k73165
104k73165
1
This is nice and readable. Is the.valuesnecessary here?
– rhaskett
Nov 13 '18 at 21:39
@rhaskett yep, since you have multiple index and pandas is index sensitive, I have the habit using the values for safety since I usually do assign as well :-)
– W-B
Nov 13 '18 at 21:46
add a comment |
1
This is nice and readable. Is the.valuesnecessary here?
– rhaskett
Nov 13 '18 at 21:39
@rhaskett yep, since you have multiple index and pandas is index sensitive, I have the habit using the values for safety since I usually do assign as well :-)
– W-B
Nov 13 '18 at 21:46
1
1
This is nice and readable. Is the
.values necessary here?– rhaskett
Nov 13 '18 at 21:39
This is nice and readable. Is the
.values necessary here?– rhaskett
Nov 13 '18 at 21:39
@rhaskett yep, since you have multiple index and pandas is index sensitive, I have the habit using the values for safety since I usually do assign as well :-)
– W-B
Nov 13 '18 at 21:46
@rhaskett yep, since you have multiple index and pandas is index sensitive, I have the habit using the values for safety since I usually do assign as well :-)
– W-B
Nov 13 '18 at 21:46
add a comment |
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
answered Nov 13 '18 at 20:46
Scott BostonScott Boston
52.7k72955
add a comment |
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
answered Nov 13 '18 at 20:46
Scott BostonScott Boston
52.7k72955
add a comment |
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
answered Nov 13 '18 at 20:46
Scott BostonScott Boston
52.7k72955
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
answered Nov 13 '18 at 20:46
Scott BostonScott Boston
52.7k72955
answered Nov 13 '18 at 20:46
Scott BostonScott Boston
52.7k72955
answered Nov 13 '18 at 20:46
Scott BostonScott Boston
52.7k72955
answered Nov 13 '18 at 20:46
Scott BostonScott Boston
52.7k72955
52.7k72955
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53289044%2ffiltering-on-column-by-a-value-that-changes-depending-on-an-multiindex-level%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
