How to sum total that refer to other dataframe

Question

I would like to sum all combination from two DataFrames,

DataFrame A

         ColA    ColB   Sales
           1       A      10
           1       B      20
           1       C      100
           2       D      1000
           2       E      2000

DataFrame B
         ColA    ColB   
          1      A,B
          2      E

My Expect result for a DataFrame is

        ColA     ColB   TotalSales
         1       A,B     30
         2       E       2000

Answer 1

You can try of selecting dataframe A rows, which are in dataframe B ColB column and add the sales column of selected

#df
 ColA    ColB   Sales
   1       A      10
   1       B      20
   1       C      100
   2       D      1000
   2       E      2000


df.set_index('ColB',inplace=True)
#df
#       ColA    Sales
# ColB      
# A        1    10
# B        1    20
# C        1    100
# D        2    1000
# E        2    2000

#df1

 #ColA    ColB   
 # 1      A,B
 # 2      E

df1['TotalSales'] = df1.ColB.str.split(',').apply(lambda x: df.loc[x]['Sales'].sum() )

Out:

ColA    ColB    TotalSales
0   1   A,B 30
1   2   E   2000