43

I have a multi level column table like this:

    a
   ---+---+---
    b | c | f
--+---+---+---
0 | 1 | 2 | 7
1 | 3 | 4 | 9

How can I drop column "c" by name? to look like this:

    a
   ---+---
    b | f
--+---+---
0 | 1 | 7
1 | 3 | 9

I tried this:

del df['c']

but I get the following error, which makes sense:

KeyError: 'Key length (1) was greater than MultiIndex lexsort depth (0)'

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Boosted_d16
  • 13,340
  • 35
  • 98
  • 158

3 Answers3

55

With a multi-index we have to specify the column using a tuple in order to drop a specific column, or specify the level to drop all columns with that key on that index level.

Instead of saying drop column 'c' say drop ('a','c') as shown below:

df.drop(('a', 'c'), axis = 1, inplace = True)

Or specify the level as shown below

df.drop('c', axis = 1, level = 1)

Let's make a simple df to demonstrate on:

>>> cols = pd.MultiIndex.from_tuples([("a", "b"), ("a", "c"),
...                                   ("a", "f"), ('x', 'c'),('x', 'f')])
>>> df = pd.DataFrame([[1,3, 7, 21, 8], [2, 4, 9, 21, 8]], columns=cols)
>>> df
   a         x   
   b  c  f   c  f
0  1  3  7  21  8
1  2  4  9  21  8

Now here's how to drop 'c' from 'a'

>>> df.drop(('a', 'c'), axis = 1)
   a      x   
   b  f   c  f
0  1  7  21  8
1  2  9  21  8

With a three level index then include that key in the tuple to drop from the bottom level e.g. ('a','c','k')

With a single value as the index, like you did, it searches the top level index for a match by default and drops a match on that index or throws an error if the key is not in the index, like you found.

So in my example it would be fine to tell it to drop just 'x'

>>> df.drop('x', axis = 1)
   a      
   b  c  f
0  1  3  7
1  2  4  9

To drop all columns with the second index 'c', then specify the level

>>> df.drop('c', axis = 1, level = 1)
   a     x
   b  f  f
0  1  7  8
1  2  9  8
Mint
  • 1,928
  • 1
  • 13
  • 12
49

Solved:

df.drop('c', axis=1, level=1)
Boosted_d16
  • 13,340
  • 35
  • 98
  • 158
  • 7
    Even though the DataFrame does not seem to contain the `c` column anymore, when I query `df.columns` the deleted columns indexes show up. – Makis Tsantekidis Sep 12 '17 at 13:01
  • I have the same problem, did you find any solution? – Kevin Wittek Jan 26 '21 at 10:40
  • 3
    I think I found the culprit. The levels are a `FrozenList` and therefore not touched by this operation. You can get the up to date index by using `df.columns.remove_unused_levels()`. – Kevin Wittek Jan 26 '21 at 10:51
  • Tnx, @KevinWittek. Here is a snippet to show how it works:```cols = pd.MultiIndex.from_tuples( [("a", "b"), ("a", "c"), ("a", "f"), ("x", "c"), ("x", "f")] ) df = pd.DataFrame([[1, 3, 7, 21, 8], [2, 4, 9, 21, 8]], columns=cols) df.drop(["x"], inplace=True, axis=1, level=0) df.columns.remove_unused_levels().levels[0]``` – Vadym Tyemirov Jul 15 '21 at 07:33
  • 1
    You should use `inplace = True`, but it looks like that in general pandas if getting rid of the `inplace` option, so perhaps it is better to do `df = df.drop('c', axis=1, level=1)`. – Barzi2001 Oct 20 '22 at 14:36
-1

This doen't work for me. It works when 'inplace=True' is added. The correct statement is df.drop('c', axis = 1, level = 1, inplace=True)

user21244696
  • 1
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/33879656) – tturbo Feb 24 '23 at 15:01