Vlookup value from master data frame and summarize it

I have a unique data frame having a list of seller names. Below is the sample.

DF_Master_seller

I have multiple data frames like -

DF1

DF2

DF3

Search each data frame in DF_Master_seller, for each match get the value and calculate the total at last.

Final DF

Note - Column should be ordered by date

Same approach as @Corralien's but with margins and sorted columns :

out = (pd.concat([df1, df2, df3])
        .pivot_table(index="Seller", values="Vaule", columns="Date",
                     aggfunc="sum", margins=True, margins_name="Total", sort=False)
        .drop(columns="Total").reindex(list(df["Seller"]) + ["Total"])
        .sort_index(key= lambda x: pd.to_datetime(x, format="%b-%d"), axis=1)
        .add_prefix("Value").reset_index().rename_axis(columns=None)
)

Output :
​

print(out)

     Seller  Value_Jan-02  Value_Feb-02  Value_Oct-02
0      Hari          2.00           NaN        222.00
1        Om         12.00           NaN        120.00
2       Cat          0.00           NaN         10.00
3       Mat         14.00        144.00        143.00
4      John           NaN         10.00         30.00
5     Messi           NaN           NaN           NaN
6      John           NaN         10.00         30.00
7   Ronaldo           NaN           NaN           NaN
8   Shannon           NaN           NaN           NaN
9    Catthy           NaN           NaN           NaN
10      Ray           NaN         22.00           NaN
11    Total         28.00        188.00        525.00

You can use pd.concat to merge df1, df2 and df3 then reindex by the Seller column of master dataframe (dfM):

# 1st pass: aggregate all dataframes whatever the dates
out = (pd.concat([df1, df2, df3])
         .pivot_table(index='Seller', columns='Date', values='Vaule', 
                      sort=False, fill_value=0)
         .reindex(dfM['Seller'], fill_value=0))

# 2nd pass: reorganize the output
out = (pd.concat([out, out.sum().to_frame('Total').T])
         .sort_index(axis=1, key=lambda x: pd.to_datetime(x, format='%b-%y'))
         .add_prefix('Value_').rename_axis(index='Seller', columns=None).reset_index())

Output:

>>> out
     Seller  Value_Jan-02  Value_Feb-02  Value_Oct-02
0      Hari             2             0           222
1        Om            12             0           120
2       Cat             0             0            10
3       Mat            14           144           143
4      John             0            10            30
5     Messi             0             0             0
6      John             0            10            30
7   Ronaldo             0             0             0
8   Shannon             0             0             0
9    Catthy             0             0             0
10      Ray             0            22             0
11    Total            28           186           555