Pandas Multiindex dataframe remove rows

Question

I have Multiiindex DF as follows:

tuples = list(zip(*[['a', 'a', 'b', 'b'], ['c', 'd', 'c', 'd']]))
index = pd.MultiIndex.from_tuples(tuples, names=['i1', 'i2'])
df = pd.DataFrame([5, 6, 7, 8], index=index[:4], columns=['col'])

       col
i1 i2     
a  c     5
   d     6
b  c     7
   d     8

Would like to keep rows whose index (level 0) is in

idx_to_keep = ['a']

Should be a straightforward task, but I can't think of any other way than

idx_to_drop = np.setdiff1d(pd.unique(df.index.levels[0]), idx_to_keep)
df.drop(idx_to_drop, inplace = True)

       col
i1 i2     
a  c     5
   d     6

Can I do better?

Possible duplicate of Select a multiple-key cross section from a DataFrame — FLab
– FLab, Commented Jul 26, 2017 at 17:48

Andrew L · Accepted Answer · 2017-07-26 17:42:44Z

4

One way is to use the index method get_level_values():

df
       col
i1 i2     
a  c     5
   d     6
b  c     7
   d     8

df[df.index.get_level_values(0).isin(idx_to_keep)]
       col
i1 i2     
a  c     5
   d     6

answered Jul 26, 2017 at 17:42

Andrew L

7,1083 gold badges28 silver badges30 bronze badges

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

1 Comment

James Kang Over a year ago

Found a cleaner solution, using 'level' parameter: df = df[df.index.isin(idx_to_keep, level=0)]

root · Accepted Answer · 2017-07-26 17:45:50Z

3

You can just use loc:

df.loc[['a']]

The resulting output:

answered Jul 26, 2017 at 17:45

root

34.1k6 gold badges77 silver badges89 bronze badges

Comments

FLab · Accepted Answer · 2017-07-26 17:45:14Z

2

You are looking for .xs:

df.xs('a', axis=0, level=0, drop_level=False)

Which gives:

edited Jul 26, 2017 at 17:45

answered Jul 26, 2017 at 17:42

FLab

7,5465 gold badges40 silver badges70 bronze badges

2 Comments

Andrew L Over a year ago

Also if looking to preserve index level 0, can specify drop_level=False

James Kang Over a year ago

what if I want to keep more than just 'a' (keep both 'a' and 'b' for example).

Collectives™ on Stack Overflow

Pandas Multiindex dataframe remove rows

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related