Pandas - Slice into DataFrame using indicies of MultiIndex DataFrame

Question

I have a DataFrame, for example, foo below. And a MultiIndex DataFrame, whose index levels correspond to the first two columns of foo, for example, foo_multi below. I want to slice into foo so that I only get the results with column values that match the indices of foo_multi.

foo = pd.DataFrame({'bar':[1,2,3,4,5,3,8,4,4,5],
                    'baz':['a','b','c','d','d','d','d','b','a','a'],
                    'qux':['z','x','c','x','z','z','x','c','c','c']})
foo_multi = foo.groupby(['bar', 'baz']).size().nlargest(3)

>>> foo
   bar baz qux
0    1   a   z
1    2   b   x
2    3   c   c
3    4   d   x
4    5   d   z
5    3   d   z
6    8   d   x
7    4   b   c
8    4   a   c
9    5   a   c
>>> foo_multi
bar  baz
  1    a    1
  2    b    1
  3    c    1
dtype: int64

So I'm looking for a method that, using only foo and foo_multi would return

   bar baz qux
0    1   a   z
1    2   b   x
2    3   c   c

I do not know why you come out this question. if any size is 2 , which row from foo, you want to select? — BENY
– BENY, Commented Apr 18, 2018 at 20:39

Scott Boston · Accepted Answer · 2018-04-18 20:26:00Z

2

Use merge to fitler records:

foo.merge(foo_multi.to_frame(), left_on=['bar','baz'], right_index=True).drop(0, axis=1)

Output:

   bar baz qux
0    1   a   z
1    2   b   x
2    3   c   c

answered Apr 18, 2018 at 20:26

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas - Slice into DataFrame using indicies of MultiIndex DataFrame

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related