Concatenating multiindex columns using pandas

Question

How can I concatenate two pandas dataframes, where one dataframe has multiindexed columns? I need to preserve the multiindex in the final dataframe.

import numpy as np
import pandas as pd

df1_cols = ["a", "b"]
df1_vals = np.random.randint(1, 10, [2, 2])
df1 = pd.DataFrame(data=df1_vals, columns=df1_cols)

df2_cols = pd.MultiIndex.from_tuples([("c", "1"), ("c", "2"), ("d", "1"), ("d", "2")])
df2_vals = np.random.randint(1, 10, [2, 4])
df2 = pd.DataFrame(data=df2_vals, columns=df2_cols)

df = pd.concat([df1, df2], axis=1)

Using pd.concat(), the multiindex will be squashed.

   a  b  (c, 1)  (c, 2)  (d, 1)  (d, 2)
0  3  7       1       6       1       3
1  6  1       2       7       6       3

jezrael · Accepted Answer · 2021-07-14 06:13:52Z

3

You need MultiIndex in both DataFrames for MultiIndex in final DataFrame:

df1.columns = pd.MultiIndex.from_product([df1.columns, ['']])
print (df1.columns)
MultiIndex([('a', ''),
            ('b', '')],
           )

df = pd.concat([df1, df2], axis=1)
print (df)
   a  b  c     d   
         1  2  1  2
0  5  6  6  9  7  7
1  1  7  7  7  2  6

answered Jul 14, 2021 at 6:13

jezrael

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

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

Collectives™ on Stack Overflow

Concatenating multiindex columns using pandas

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