2

I have a DataFrame with multiIndex columns. Suppose it is this:

index = pd.MultiIndex.from_tuples([('one', 'a'), ('one', 'b'),
                                   ('two', 'a'), ('two', 'b')])
df = pd.DataFrame({'col': np.arange(1.0, 5.0)}, index=index)

df = df.unstack(1)

(I know this definition could be more direct). I now want to set a new level 0 column based on a DataFrame. For example

df['col2'] = df['col'].applymap(lambda x: int(x < 3))

This does not work. The only method I have found so far is to add each column seperately: Pandas: add a column to a multiindex column dataframe , or some sort of convoluted joining process.

The desired result is a new level 0 column 'col2' with two level 1 subcolumns: 'a' and 'b'

Any help would be much appreciated, Thank you.

Cameron Oliver
  • 91
  • 5

1 Answers1

0

I believe need solution with no unstack and stack - filter by boolean indexing, rename values for avoid duplicates and last use DataFrame.append:

df2 = df[df['col'] < 3].rename({'one':'one1', 'two':'two1'}, level=0)
print (df2)
        col
one1 a  1.0
     b  2.0

df = df.append(df2)
print (df)
        col
one  a  1.0
     b  2.0
two  a  3.0
     b  4.0
one1 a  1.0
     b  2.0
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 1
    Thank you for your answer, in reality I have a very large data frame with a multiindex on the columns, I want to avoid constantly stacking and unstacking as it will be computationally heavy for what is just an asignment. The unstack in my original post was just for the purposes of defining the data frame in the example (I should probably change that) – Cameron Oliver Sep 03 '18 at 12:35