0

I have multi index data frame like below and I would like remove the row above 'A' (like shift the dataframe up)

metric data data
        F    K
        C    B
 A      2    3
 B      4    5
 C      6    7
 D      8    9

desired output

ALIAS  data data
metric  F    K
 A      2    3
 B      4    5
 C      6    7
 D      8    9

I looked multiple post but could not find anything closer to create desired outcome. How can I achive the desired output ?

https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html

Alexander
  • 4,527
  • 5
  • 51
  • 98

1 Answers1

1

Let's try DataFrame.droplevel to remove level 2 from the columns, and DataFrame.rename_axis to update column axis names:

df = df.droplevel(level=2, axis=1).rename_axis(['ALIAS', 'metric'], axis=1)

Or with the index equivalent methods Index.droplevel and Index.rename:

df.columns = df.columns.droplevel(2).rename(['ALIAS', 'metric'])

df:

ALIAS  data   
metric    F  K
A         2  3
B         4  5
C         6  7
D         8  9

Setup:

import numpy as np
import pandas as pd

df = pd.DataFrame(
    np.arange(2, 10).reshape(-1, 2),
    index=list('ABCD'),
    columns=pd.MultiIndex.from_arrays([
        ['data', 'data'],
        ['F', 'K'],
        ['C', 'B']
    ], names=['metric', None, None])
)

df:

metric data   
          F  K
          C  B
A         2  3
B         4  5
C         6  7
D         8  9
Henry Ecker
  • 34,399
  • 18
  • 41
  • 57
  • thank youu! one question, how would you know rename_axis modifys the 1st column ? – Alexander Sep 05 '21 at 08:10
  • You're welcome. It doesn't. Those are axis names for each column level. With a pivot_table typically the axis name is the old column header. It describes the contents of the new column headers. But those values are not data in the dataframe simply additional header annotations. – Henry Ecker Sep 05 '21 at 08:11
  • hımm how would you do if want to modify ‘data’ and ‘f’ column? ssorry asking this to learn more. your answer already elegant! – Alexander Sep 05 '21 at 08:16
  • What do you mean "modify ‘data’ and ‘f’ column"? Modify how? – Henry Ecker Sep 05 '21 at 08:17
  • Sorry modify those names for example. – Alexander Sep 05 '21 at 08:17
  • 1
    You'd need to `rename` them. Lots of good examples here -> [Rename MultiIndex columns in Pandas](https://stackoverflow.com/q/41221079/15497888) – Henry Ecker Sep 05 '21 at 08:20