CODEWITHSUNDEEP
pandasmulti-index
Sara
Sara

Reputation: 245

How to set multiindex columns at once

I am working with MultiIndex of pandas dataframes. My columns have two levels. I want to set the values for a new first level. For example, I want to set type3 below

index = pd.MultiIndex.from_product(
    [('type1', 'type2'), ('a', 'b', 'c')], 
    names=['type', 'alphabet']
    )
df = pd.DataFrame(
    np.random.random((2, 6)),
    index=['X', 'Y'], columns=index
    )

df_type3 = pd.DataFrame(
    np.random.random((2, 3)),
    index=['X', 'Y'], columns=['a', 'b', 'c']
    )

I looked up some post and tried different ways, here are two failed attemps and two successful ones.

# Try 1: doesn't work
df['type3'] = df_type3

# Try 2: doesn't work
df_[('type3', :)] = df_type3

# Try 3: this works, but takes two steps
df_type3_.columns = pd.MultiIndex.from_product(
        [('type3',), ('a', 'b', 'c')], 
        names=['type', 'alphabet']
    )
df = pd.concat(
    [df, df_type3_], axis=1
)

# Try 4: this also works, but uses a for loop
for alphabet in ('a', 'b', 'c'):
    df[('type3', alphabet)] = df_type3[alphabet]

As you can see, the ones that worked used more than one step or a for loop. I wonder if there is an easier way to do this, like how we can set new columns in a single level dataframes (df['new_col'] = some_array)

Upvotes: 1

Views: 40

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150805

This looks fancy, but might not be as fast as your loop:

df.stack(level=1).assign(type3=df_type3.stack()).unstack(level=-1)

Upvotes: 1

Related Questions