Pandas: Assign MultiIndex Column from DataFrame

Question

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.

jezrael · Accepted Answer · 2018-09-03 14:59:09Z

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

edited Sep 3, 2018 at 14:59

answered Sep 3, 2018 at 11:24

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cameron Oliver Over a year ago

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)

Collectives™ on Stack Overflow

Pandas: Assign MultiIndex Column from DataFrame

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related