Pandas: create new (sub-level) columns in a multi-index dataframe and assign values

Question

Let's be given a data-frame like the following one:

import pandas as pd 
import numpy as np

a = ['a', 'b']
b = ['i', 'ii']
mi = pd.MultiIndex.from_product([a,b], names=['first', 'second'])
A = pd.DataFrame(np.zeros([3,4]), columns=mi)

first     a         b
second    i   ii    i   ii
0       0.0  0.0  0.0  0.0
1       0.0  0.0  0.0  0.0
2       0.0  0.0  0.0  0.0

I would like to create new columns iii for all first-level columns and assign the value of a new array (of matching size). I tried the following, to no avail.

A.loc[:,pd.IndexSlice[:,'iii']] = np.arange(6).reshape(3,-1)

The result should look like this:

     a              b
     i   ii  iii    i   ii  iii
0  0.0  0.0  0.0  0.0  0.0  1.0
1  0.0  0.0  2.0  0.0  0.0  3.0
2  0.0  0.0  4.0  0.0  0.0  5.0

BENY · Accepted Answer · 2019-03-08 02:39:51Z

Since you have multiple index in columns , I recommend create the additional append df , then concat it back

appenddf=pd.DataFrame(np.arange(6).reshape(3,-1),           
              index=A.index,
              columns=pd.MultiIndex.from_product([A.columns.levels[0],['iii']]))

appenddf
    a   b
  iii iii
0   0   1
1   2   3
2   4   5
A=pd.concat([A,appenddf],axis=1).sort_index(level=0,axis=1)
A
first     a             b         
second    i   ii iii    i   ii iii
0       0.0  0.0   0  0.0  0.0   1
1       0.0  0.0   2  0.0  0.0   3
2       0.0  0.0   4  0.0  0.0   5

Another workable solution

for i,x in enumerate(A.columns.levels[0]):
    A[x,'iii']=np.arange(6).reshape(3,-1)[:,i]
A
first     a         b        a   b
second    i   ii    i   ii iii iii
0       0.0  0.0  0.0  0.0   0   1
1       0.0  0.0  0.0  0.0   2   3
2       0.0  0.0  0.0  0.0   4   5
# here  I did not add `sort_index`

Collectives™ on Stack Overflow

Pandas: create new (sub-level) columns in a multi-index dataframe and assign values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related