vlookup equivalent to fill existing table using pandas-CodePudding

Scenario:

there are more than three pivot table in python
one existing table in which i want to fill value as per row and and as per column from different pivot table

I made sample in excel ,want to automate in python. (In python using pivot After making pivot column name changes)

Pivot1          Pivot2  
            
Name Sum of English Average     Name    Sum of Maths Average
Ni   7.25                         Ni    7.25
Pi  11.25                         Pi    11.25
Si  12.25                         Si    12.25
Ti  17.25                         Ti    17.25
Vi  9.75                          Vi    9.75
Grand Total 57.75       Grand Total 57.75

Reqired table to fill

English Avg       Maths Avg     
Ni      -----          ------
Pi      ------         ------
Si      ------         -------

enter image description here

CodePudding user response：

Use reduce and pd.merge:

import pandas as pd
from functools import reduce

names = ['Ni', 'Pi', 'Si']

df = reduce(lambda piv1, piv2: pd.merge(piv1.loc[piv1['Name'].isin(names)],
                                        piv2, on='Name', how='left'),
            [pivot1, pivot2, pivot3])

>>> df
  Name  Sum of English Average  Sum of Maths Average  Sum of History Average
0   Ni                    7.25                  7.25                    7.25
1   Pi                   11.25                 11.25                   11.25
2   Si                   12.25                 12.25                   12.25