What is the best way to merge/consolidate multiple tables in pandas? For simplicity, lets say we have 3 simple tables as a dataframe like so:

Table1

AA_max    55
AA_min    40
BB_max    23
BB_min    10

Table2

AA_max    55
AA_min    40

Table3

AA_max    85
AA_min    10
BB_max    23
BB_min    10

How would you generate a consolidated table like so:

Item | Table1 min | Table1 max | Table2 min | Table2 max | Table3 min | Table3 max
AA     40           55           40           55           10           85          
BB     10           23                                     10           23

Is it doable using pandas? If not, is it better to just iterate each of the rows per table, insert to a dictionary and populate the columns accordingly as you iterate them?

Assume an item from another table may or may not exist from the other tables.

CodePudding user response：

Solution

• Concat the tables along column axis
• Split the index in order to convert to multiindex
• Unstack on level=1 to reshape
• Flatten the multilevel columns using map and join

tables = [df1, df2, df3]
out = pd.concat({f'Table_{i}': df.set_index('name')['val'] 
                 for i, df in enumerate(tables, 1)}, axis=1)

out.index = out.index.str.split('_', expand=True)
out = out.unstack()
out.columns = out.columns.map('_'.join)

Result

print(out)

    Table_1_max  Table_1_min  Table_2_max  Table_2_min  Table_3_max  Table_3_min
AA           55           40         55.0         40.0           85           10
BB           23           10          NaN          NaN           23           10