CODEWITHSUNDEEP
pythonpandasdataframemulti-index
NicoH
NicoH

Reputation: 1415

Pandas multindex DataFrame: replace certain values by values from other column on 2nd level

I've got the following multiindex DataFrame:

    0   M1        M2     
  bla    C   LQ    C   LQ
0   1  1.6  0.1  2.7  0.5
1   2  <LQ  0.2  4.1  0.3
2   3  NaN  NaN  NaN  NaN
3   4  3.2  0.1  <LQ  0.4

Furthermore I created a mask of where 'C' is "<LQ", dropping the 2nd level

mask = df.xs('C',level=1, axis=1) == "<LQ"

I'd like to

  1. replace '<LQ' by the value in the respective 'LQ' column
  2. drop the 'LQ' colums and reduce the columns to one level, keeping the name of the first level where the second level is ['C', 'LQ'] and keeping the name of the second level for the rest like this:

:

   bla     M1    M2
0    1    1.6   2.7
1    2    0.2   4.1
2    3    NaN   NaN
3    4    3.2   0.4

while keeping track of the replaced values using the mask (eg. for later plotting)

Code to generate the df:

df = pd.DataFrame({(0,'bla'): [1,2,3,4],
                   ('M1', 'C'): [1.6,'<LQ',np.nan,3.2],
                   ('M1', 'LQ'): [0.1,0.2,np.nan,0.1],
                   ('M2', 'C'): [2.7,4.1,np.nan,'<LQ'],
                   ('M2', 'LQ'): [0.5,0.3,np.nan,0.4]})

Upvotes: 1

Views: 172

Answers (1)

jezrael
jezrael

Reputation: 863166

Usemask for replace values with <LQ by LQ DataFrame, then remove this levels by drop with removing first level of MultiIndex by droplevel and last join together:

df1 = df.xs('C',level=1, axis=1)
df1 = df1.mask(df1 == "<LQ", df.xs('LQ',level=1, axis=1))

df2 = df.drop(['C','LQ'], axis=1, level=1)
df2.columns = df2.columns.droplevel(0)

df = df2.join(df1)
print (df)
   bla   M1   M2
0    1  1.6  2.7
1    2  0.2  4.1
2    3  NaN  NaN
3    4  3.2  0.4

If only one 0, bla column then use set_index with rename_axis and last reset_index:

df = df.set_index((0,'bla'))
df1 = df.xs('C',level=1, axis=1)
df = (df1.mask(df1 == "<LQ", df.xs('LQ',level=1, axis=1))
         .rename_axis('bla')
         .reset_index())

print (df)
   bla   M1   M2
0    1  1.6  2.7
1    2  0.2  4.1
2    3  NaN  NaN
3    4  3.2  0.4

Upvotes: 1

Related Questions