Search code examples
pythonpandasdataframejoinhierarchy

Python: Join two DataFrames with same column prefix on same indices

I have two dataframes that look like this:

df1 = pd.DataFrame(
{
    "A_price": [10, 12],
    "B_price": [20, 21],
},
index = ['01-01-2020', '01-02-2021']
)
df1:
            A_price B_price
01-01-2020  10      20
01-02-2021  12      21

df2 = pd.DataFrame(
{
    "A_weight": [0.1, 0.12],
    "B_weight": [0.2, 0.21],
},
index = ['01-01-2020', '01-02-2021']
)
df2:
            A_weight B_weight
01-01-2020  0.1      0.2
01-02-2021  0.12     0.21

How can I join the two dataframes on the same indices and then have the columns in a hierarchy? i.e. I want the following:

df:
            A              B
            price weight   price weight      
01-01-2020  10    0.1      20    0.2
01-02-2021  12    0.12     21    0.21
Solution

  • Use join (or merge) and explode your column names.

    # out = pd.merge(df1, df2, left_index=True, right_index=True)
out = out.join(df2)
out.columns = out.columns.str.split('_', expand=True)
out = out.sort_index(axis=1)
print(out)

# Output:
               A            B       
           price weight price weight
01-01-2020    10   0.10    20   0.20
01-02-2021    12   0.12    21   0.21