11

I have a df that looks like this

   a     b      c
              c1  c2
0  87    33   32  34
1  32    10   45  62
2  78    83   99  71

I'd like to drop the c level but keep all the other column names

   a     b    c1  c2
0  87    33   32  34
1  32    10   45  62
2  78    83   99  71

df.columns = df.columns.droplevel(0) works but the names of a and b disappear

              c1  c2
0  87    33   32  34
1  32    10   45  62
2  78    83   99  71
HappyPy
  • 9,839
  • 13
  • 46
  • 68

1 Answers1

8

You can use list comprehension to select second-level values if c:

df.columns = [col2 if col1 == 'c' else col1 for col1, col2 in df.columns]
print (df)
    a   b  c1  c2
0  87  33  32  34
1  32  10  45  62
2  78  83  99  71

Previous solutions for old pandas versions:

I think you can use set_index + droplevel + reset_index:

df = df.set_index(['a','b'])
df.columns = df.columns.droplevel(0)
df = df.reset_index()
print (df)
    a   b  c1  c2
0  87  33  32  34
1  32  10  45  62
2  78  83  99  71

Another solution with select columns by ['c']:

df = df.set_index(['a','b'])['c'].reset_index()
print (df)
    a   b  c1  c2
0  87  33  32  34
1  32  10  45  62
2  78  83  99  71

But if get it from pivot_table solution is remove [] or add parameter values='c' if missing.

jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • The second solution you posted worked for me. The output for your first solution formatted differently for me than the output shown in the answer. Thank you for the help! – BGG16 Jan 24 '23 at 23:16
  • @jezrael: what versions of pandas does this work on? can you confirm it still works on 1.4, 1.5, 2.0? – smci Apr 21 '23 at 02:22
  • @smci - You are right, it working for old pandas versions, so add to answer info about it with new solution working for all pandas versions – jezrael Apr 21 '23 at 05:27
  • 1
    Can you state which versions? 1.4? 1.5? The new list comprehension is a bit hard to follow since it mixes variable names that happen to be col names. I'd write it as `df.columns = [col2 if col1 == 'c' else col1 for col1, col2 in df.columns]` – smci Apr 21 '23 at 08:25
  • 1
    @smci - `Can you state which versions` - tested last 2 solutions in pandas 1.5 and failed, so create universal solution. – jezrael Apr 21 '23 at 09:04
  • @smci - `df.columns = [col2 if col1 == 'c' else col1 for col1, col2 in df.columns]` - Super idea, I can add to answer. – jezrael Apr 21 '23 at 09:04
  • 1
    Neat. You might also want to comment that `for clev1, clev2 in df.columns` is an iteration over the levels of a 2-level column multiindex. – smci Apr 22 '23 at 08:31