Compare two dataframes with same column names and flatten the resulting dataframe with suffix

I have two dataframes:

dfA = pd.DataFrame({
        "Run": (
            1, 1, 1, 2, 2, 2, 3, 3, 3
        ),
        "Point": (
            1, 2, 3, 1, 2, 3, 1, 2, 3
        ),
        "Val": (
            78, 79, 77, 78, 79, 77, 78, 79, 77
        )
    })

and

dfB = pd.DataFrame({
        "Run": (
            1, 1, 1, 2, 2, 2, 3, 3, 3,
        ),
        "Point": (
            1, 2, 3, 1, 2, 3, 1, 2, 3, 
        ),
        "Val": (
            68, 69, 67, 68, 69, 67, 68, 69, 67,
        ),
    })

with

res = dfA.compare(dfB, keep_equal=True, keep_shape=True).rename(columns={'self': 'A', 'other': 'B'}, level=-1)

I get a well aligned dataframe with the results I’m looking for, but it seems that Dash AG Grid can not handle Multiindex column dataframes, therefore I want to flatten the "res" df to get following structure

end_df = pd.DataFrame({
        "Run_A": (
            1, 1, 1, 2, 2, 2, 3, 3, 3,
        ),
        "Run_B": (
            1, 1, 1, 2, 2, 2, 3, 3, 3,
        ),
        "Point_A": (
            1, 2, 3, 1, 2, 3, 1, 2, 3, 
        ),
        "Point_B": (
            1, 2, 3, 1, 2, 3, 1, 2, 3, 
        ),
        "Val_A": (
            78, 79, 77, 78, 79, 77, 78, 79, 77
        ),
        "Val_B": (
            68, 69, 67, 68, 69, 67, 68, 69, 67,
        ),
    })

What would be the most efficient way to archieve the desired structure?

>Solution :

Simply join the column levels with str.join:

res.columns = map("_".join, res.columns)
print(res)

Prints:

   Run_A  Run_B  Point_A  Point_B  Val_A  Val_B
0      1      1        1        1     78     68
1      1      1        2        2     79     69
2      1      1        3        3     77     67
3      2      2        1        1     78     68
4      2      2        2        2     79     69
5      2      2        3        3     77     67
6      3      3        1        1     78     68
7      3      3        2        2     79     69
8      3      3        3        3     77     67

Leave a Reply