Search code examples
pythonpandasdataframeflatten

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