Calculations using two pandas dataframes

Question

I have the following two (simplified) dataframes with me:

df1=
         origin destination  val1  val2
    0      1           A      0.8   0.9
    1      1           B      0.3   0.5
    2      1           c      0.4   0.2
    3      2           A      0.4   0.7
    4      2           B      0.2   0.1
    5      2           c      0.5   0.1
df2=
  org  price
0   1     50
1   2     45

what I need to do is to select the price from each origin from df2, multiply it by the sum of val1+val2 in df1 and write it to a csv file.

The calculation for A is as follows:

A => (0.8+0.9)* 50 + (0.4+ 0.7)* 45 = 134.5

here, the values 0.8, 0.9, 0.4 and 0.7 are coming from df1 and they correspond to val1 and val2 of A where as the values 50 and 45 come from df2 corresponding to origin 1 and 2 respectively. for B the calculation would be

B => (0.3+0.5)*50 + (0.2+0.1)*45 = 53.5

for C the calculation would be:

C => (0.4+0.2)*50 + (0.5+0.1)*45 = 57

The final CSV file should look like:

A,134.5

B,53.5

C,57 I've written the following python code for that:

# first convert the second table into a python dictionary so that I can refer price value at each origin
df2_dictionary = {}
for ind in df2.index:
    df2_dictionary[df2['org'][ind]] = float(df2['price'][ind])    

# now go through df1, add up val1 and val2 and add the result to the result dictionary. 
result = {}
for ind in df1.index:
    origin = df1['origin'][ind] 
    price = df2_dictionary[origin] # figure out the price from the dictionary.
    r = (df1['val1'][ind] + df1['val2'][ind])*price # this is the needed calculation 
    destination = df1['destination'][ind] # store the result in destination
    if(destination in result.keys()):
        result[destination] = result[destination]+r
    else:
        result[destination] = r
f = open("result.csv", "w")
for key in result:
    f.write(key+","+str(result[key])+"\n")
f.close()

This is lot of work and doesn't use the pandas inbuilt functions. How do I simplify this? I'm not that worried about efficiency.

Quang Hoang Quang Hoang · Accepted Answer · 2020-02-17T19:54:41

Your problem can be solved with map and then groupby:

df1['total'] = (df1[['val1','val2']].sum(1)
                   .mul(df1['origin']
                            .map(df2.set_index('org').price)
                       )
               )

summary = df1.groupby('destination')['total'].sum()

# save to csv
summary.to_csv('/path/to/file.csv')

Output (summary):

destination
A    134.5
B     53.5
c     57.0
Name: total, dtype: float64

Calculations using two pandas dataframes

1 Answers