How To Sum Total That Refer To Other Dataframe

I would like to sum all combination from two DataFrames, DataFrame A ColA ColB Sales 1 A 10 1 B 20 1

Solution 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

Solution 2:

You can use pd.DataFrame.apply for a partially vectorised solution:

def summer(x):
    m1 = df_a['ColA'].eq(x['ColA'])
    m2 = df_a['ColB'].isin(x['ColB'].split(','))
    return df_a.loc[m1 & m2, 'Sales'].sum()

df_b['TotalSales'] = df_b.apply(summer, axis=1)

print(df_b)

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