CODEWITHSUNDEEP
pythonpandasdataframe
user3508140
user3508140

Reputation: 285

Sum column in one dataframe based on row value of another dataframe

Say, I have one data frame df:

   a  b   c  d   e
0  1  2  dd  5  Col1
1  2  3  ee  9  Col2
2  3  4  ff  1  Col4

There's another dataframe df2:

  Col1   Col2   Col3 
0  1      2       4      
1  2      3       5      
2  3      4       6

I need to add a column sum in the first dataframe, wherein it sums values of columns in the second dataframe df2, based on values of column e in df1.

Expected output

   a  b   c  d   e     Sum
0  1  2  dd  5  Col1    6
1  2  3  ee  9  Col2    9
2  3  4  ff  1  Col4    0

The Sum value in the last row is 0 because Col4 doesn't exist in df2.

What I tried: Writing some lamdas, apply function. Wasn't able to do it. I'd greatly appreciate the help. Thank you.

Upvotes: 1

Views: 2039

Answers (3)

Tyler Russin
Tyler Russin

Reputation: 106

Use .iterrows() to iterate through a data frame pulling out the values for each row as well as index.

A nest for loop style of iteration can be used to grab needed values from the second dataframe and apply them to the first

import pandas as pd

df1 = pd.DataFrame(data={'a': [1,2,3], 'b': [2,3,4], 'c': ['dd', 'ee', 'ff'], 'd': [5,9,1], 'e': ['Col1','Col2','Col3']})
df2 = pd.DataFrame(data={'Col1': [1,2,3], 'Col2': [2,3,4], 'Col3': [4,5,6]})
df1['Sum'] = df1['a'].apply(lambda x: None)


for index, value in df1.iterrows():
  sum = 0
  for index2, value2 in df2.iterrows():
    sum += value2[value['e']]
    
  df1['Sum'][index] = sum

Output:

    a   b   c   d   e       Sum
0   1   2   dd  5   Col1    6
1   2   3   ee  9   Col2    9
2   3   4   ff  1   Col3    15

Upvotes: 0

Anant Kumar
Anant Kumar

Reputation: 641

Try this. The following solution sums all values for a particular column if present in df2 using apply method and returns 0 if no such column exists in df2.

df1.loc[:,"sum"]=df1.loc[:,"e"].apply(lambda x: df2.loc[:,x].sum() if(x in df2.columns)  else 0)

Upvotes: 2

BENY
BENY

Reputation: 323226

Try

df['Sum']=df.e.map(df2.sum()).fillna(0)
df
Out[89]: 
   a  b   c  d     e  Sum
0  1  2  dd  5  Col1  6.0
1  2  3  ee  9  Col2  9.0
2  3  4  ff  1  Col4  0.0

Upvotes: 4

Related Questions