CODEWITHSUNDEEP
pythonpandas
Mike S
Mike S

Reputation: 1603

Pandas - Slice into DataFrame using indicies of MultiIndex DataFrame

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

Upvotes: 0

Views: 37

Answers (1)

Scott Boston
Scott Boston

Reputation: 153460

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

Upvotes: 2

Related Questions