Multiple Assignment in Multindex Pandas Dataframe [duplicate]

Question

Let's say that I have a multindex dataframe like the one below

In [221]: df
Out[221]:
first        bar                 baz
second       one       two       one       two
A      -1.089798  2.053026  0.470218  1.440740
B       0.488875  0.428836  1.413451 -0.683677
C      -0.243064 -0.069446 -0.911166  0.47837

I would like to add a third and fourth column to each first level columns,'bar' and 'baz'.

I have been trying to use:

df[['bar','baz'],['third','forth']]=prices_df.apply(
    lambda row: pd.Series(get_bond_metrics(row))
    , axis=1)

But that is not the right way to make multiple assignments in a multi index dataframe.

Thank you

Bharath M Shetty · Accepted Answer · 2018-09-09 14:56:27Z

1

One approach is via pd.concat, concatenate existing dataframe with new dataframe of desired columns( created by MultiIndex.from_product which gives the combinations of two lists) and your values i.e

df
first        bar                 baz          
second       one       two       one       two
0      -0.122485  0.943154  1.253930 -0.955231
1      -0.293157 -1.167648 -0.864481  1.251452

values = np.random.randn(2,4) # here goes your values

df2 = pd.DataFrame(values, columns=pd.MultiIndex.from_product([['bar','baz'],['third','forth']]))

# Column wise concatenation followed by sorting of index for better view
ndf = pd.concat([df,df2],axis = 1).sort_index(level='first',axis=1,sort_remaining=False)

Output :

first        bar                                     baz                      \
second       one       two     third     forth       one       two     third   
0      -0.122485  0.943154 -0.419076  0.667690  1.253930 -0.955231 -0.858656   
1      -0.293157 -1.167648  0.516346 -0.907558 -0.864481  1.251452  0.429894   

first             
second     forth  
0       0.237544  
1      -0.521049

edited Sep 9, 2018 at 14:56

answered Sep 9, 2018 at 14:45

Bharath M Shetty

30.6k6 gold badges65 silver badges111 bronze badges

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

1 Comment

ruben Over a year ago

I like your answer but I would like the new values to be a function of the dataframe old columns. Is there any way to do that? Thank you.

Collectives™ on Stack Overflow

Multiple Assignment in Multindex Pandas Dataframe [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related