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      101       B      201       C      1002       D      10002       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
01A,B3012   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
01  A,B          3012    E        2000