Clever Geek Handbook

Pandas add new second level column to multiindex column based on other columns

I have a DataFrame with a multi-index column:

System   A                B
Trial    Exp1    Exp2     Exp1    Exp2
1        NaN     1        2       3
2        4       5        NaN     NaN
3        6       NaN      7       8

It turns out for each system ( A, B

), and each dimension ( 1, 2, 3

in the index), the result is Exp1

always superior Exp2

. So I want to create a third column for each system, name it Final

, which should be accepted Exp1

whenever available, and default Exp2

otherwise. Desired output:

System   A                       B
Trial    Exp1    Exp2    Final   Exp1    Exp2    Final
1        NaN     1       1       2       3       2
2        4       5       4       NaN     NaN     NaN
3        6       NaN     6       7       8       7

What's the best way to do this?

I tried using groupby

on columns:

grp = df.groupby(level=0, axis=1)

And thought about using transform

or apply

in combination with assign

to achieve it. But I cannot find any working or efficient way to do this. In particular, I avoid python inline loops for

for efficiency reasons (otherwise the problem is trivial).

+3


source to share


3 answers


Use stack

to modify, add column with fillna

and then backtrack unstack

with swaplevel

+ sort_index

:

df = df.stack(level=0)
df['Final'] = df['Exp1'].fillna(df['Exp1'])
df = df.unstack().swaplevel(0,1,axis=1).sort_index(axis=1)
print (df)
System    A               B           
Trial  Exp1 Exp2 Final Exp1 Exp2 Final
1       NaN  1.0   NaN  2.0  3.0   2.0
2       4.0  5.0   4.0  NaN  NaN   NaN
3       6.0  NaN   6.0  7.0  8.0   7.0

Another solution with xs

to choose DataFrames

, create a new one DataFrame

with combine_first

but the second level is missing - added MultiIndex.from_product

and the last concat

both DataFrames

together:



a = df.xs('Exp1', axis=1, level=1)
b = df.xs('Exp2', axis=1, level=1)
df1 =  a.combine_first(b)
df1.columns = pd.MultiIndex.from_product([df1.columns, ['Final']])
df = pd.concat([df, df1], axis=1).sort_index(axis=1)
print (df)
System    A               B           
Trial  Exp1 Exp2 Final Exp1 Exp2 Final
1       NaN  1.0   1.0  2.0  3.0   2.0
2       4.0  5.0   4.0  NaN  NaN   NaN
3       6.0  NaN   6.0  7.0  8.0   7.0

A similar solution with rename

:

a = df.xs('Exp1', axis=1, level=1, drop_level=False)
b = df.xs('Exp2', axis=1, level=1, drop_level=False)
df1 = a.rename(columns={'Exp1':'Final'}).combine_first(b.rename(columns={'Exp2':'Final'}))
df = pd.concat([df, df1], axis=1).sort_index(axis=1)
print (df)
System    A               B           
Trial  Exp1 Exp2 Final Exp1 Exp2 Final
1       NaN  1.0   1.0  2.0  3.0   2.0
2       4.0  5.0   4.0  NaN  NaN   NaN
3       6.0  NaN   6.0  7.0  8.0   7.0
+2


source


  • stack

    with the first level of the column index stack(0)

    , leaving ['Exp1', 'Exp2']

    in the column index
  • Use a function lambda

    that applies to the entire data frame in the call assign

    .
  • Finally, unstack

    , swaplevel

    , sort_index

    , to clean it and put everything where it belongs.
f = lambda x: x.Exp1.fillna(x.Exp2)
df.stack(0).assign(Final=f).unstack() \
    .swaplevel(0, 1, 1).sort_index(1)

     A               B           
  Exp1 Exp2 Final Exp1 Exp2 Final
1  NaN  1.0   1.0  2.0  3.0   2.0
2  4.0  5.0   4.0  NaN  NaN   NaN
3  6.0  NaN   6.0  7.0  8.0   7.0


Another concept using xs 

d1 = df.xs('Exp1', 1, 1).fillna(df.xs('Exp2', 1, 1))
d1.columns = [d1.columns, ['Final'] * len(d1.columns)]
pd.concat([df, d1], axis=1).sort_index(1)


     A               B           
  Exp1 Exp2 Final Exp1 Exp2 Final
1  NaN  1.0   1.0  2.0  3.0   2.0
2  4.0  5.0   4.0  NaN  NaN   NaN
3  6.0  NaN   6.0  7.0  8.0   7.0
+2


source


doesn't feel super optimal, but try this:

for system in df.columns.levels[0]:
    df[(system, 'final')] = df[(system, 'Exp1')].fillna(df[(system, 'Exp2')])
0


source






More articles:


All Articles