pandas, how to add columns to a multiindex column DataFrame

Question

This is my original DataFrame (with multiindex column):

In [72]:df
Out[72]: 
          a                   b          
          x         y         x         y
0  1.545293 -0.459270  0.899254 -1.010453
1  0.458760  0.275400 -0.190951  0.169195
2 -0.941817  1.109823  0.077953 -0.247074
3  1.790101 -1.643470  0.979625 -1.704657
4 -2.044814 -0.243726 -0.039724  0.600066

and I have another DataFrame:

In [77]:df2
Out[77]: 
          x         y
0 -1.085869 -0.952949
1  0.601585  0.570050
2  0.328601  0.802610
3 -0.415952 -0.090088
4  0.757545 -0.736933

how can I add df2's columns to df to get a new DataFrame like this:

In [83]:df3
Out[83]: 
          a                   b                   c          
          x         y         x         y         x         y
0  1.545293 -0.459270  0.899254 -1.010453 -1.085869 -0.952949
1  0.458760  0.275400 -0.190951  0.169195  0.601585  0.570050
2 -0.941817  1.109823  0.077953 -0.247074  0.328601  0.802610
3  1.790101 -1.643470  0.979625 -1.704657 -0.415952 -0.090088
4 -2.044814 -0.243726 -0.039724  0.600066  0.757545 -0.736933

My current approach is to use a for loop:

for col in df2.columns:
    df['c', col] = df2[col]

is there any method to avoid the loop?

Kartik · Accepted Answer · 2016-01-27 03:27:59Z

1

Try pd.concat:

pieces = {'a' : df1['a'],
          'b' : df1['b'],
          'c' : df2}
df3 = pd.concat(pieces, axis=1)

answered Jan 27, 2016 at 3:27

Kartik

8,73345 silver badges78 bronze badges

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

2 Comments

Luke Davis Over a year ago

Do you know if there's any way to do the above without deconstructing the inner columns of df1? Seems like there must be something.

Kartik Over a year ago

You could use answers to this question stackoverflow.com/questions/14744068/… to prepend a level to your other df, then use pd.concat as demonstrated in this answer

Luke Davis · Accepted Answer · 2017-03-07 20:35:46Z

I discovered another way to do this in the general case (running Python 3.6), without having to explicitly deconstruct the DataFrame. You can use pd.concat with the dictionary argument,

df3 = pd.concat({**df1, **{('c',nm):val for nm,val in df2.items()})

** expansion on DataFrame objects seems to return a dictionary of Series objects with "names" equal to the column name string/value, or if the columns are MultiIndexed, the tuple containing the hieararchy of column string/values. Then, when read back into pd.concat as a dictionary, Pandas re-constructs the MultiIndexed columns from the tuples.

Note this is much less efficient than the direct assignment you were doing! Since it has to deconstruct each column and MultiIndex of the dataframe, then re-combine.

Andi · Accepted Answer · 2021-11-17 15:39:12Z

1

First, add a column level ("c") to df2. Then, concatenate both dataframes.

df2.columns = pd.MultiIndex.from_product(["c"], df.columns])
df = pd.concat([df, df2], axis=1)

answered Nov 17, 2021 at 15:39

Andi

5,1775 gold badges37 silver badges65 bronze badges

Comments

Adriel M. Vieira · Accepted Answer · 2021-03-01 15:33:08Z

0

I think stacking is the best solution here:

# First stack df and df2
df, df2 = df.stack(), df2.stack()

# Then add df2 to df as a column
df2['c'] = df2

# Now unstack
df2 = df2.unstack()

answered Mar 1, 2021 at 15:33

Adriel M. Vieira

1311 silver badge4 bronze badges

Collectives™ on Stack Overflow

pandas, how to add columns to a multiindex column DataFrame

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related