How to find sum of values in a column after mapping from another dataframe?

user_2110417 0
Viewed 161

I have two dataframes as follows:

df1 = pd.DataFrame({'Name': ["cat", "dog", "fish"],
    'Set1': ["ad, cd, bd", "bd", "jk, md"],
    'Set2': ["kl, kd", "ad, kd", "kd"],
    'Set3': ["kd, ad", "jk", "bd"]})

df1

Name    set1        set2    set3
cat     ad, cd, bd  kl, kd  kd, ad
dog     bd          ad, kd  jk
fish    jk, md      kd      bd

and second dataframe:

df2 = pd.DataFrame({'Term': ["ad", "cd", "bd", "jk", "md", "kl", "kd", "mm", "nn"], 'Freq': [3,5,3,6,1,4,9,4,2]})

df2

Term    Freq
ad      3
cd      5
bd      3
jk      6
md      1
kl      4
kd      9
mm      4
nn      2

I want to map values Freq of df2 to df1 and find the sum of the value in that particular column. Expected output is:

Name    set1    set2    set3
cat     11      13      12
dog     3       12      6
fish    7       9       3
pandas dataframe python
2 Answers

Create a function that splits each cell, get rid of whitespace, convert the text to numbers via df2, and sum :

def func(val):
    # val = map(str.strip,  val.split(","))
    val = [ent.strip() for ent in val.split(",")]
    val = map(mapper.get, val)
    return sum(val)


 mapper = dict(zip(df2.Term, df2.Freq))

df1.set_index('Name').applymap(func)

      Set1  Set2  Set3
Name
cat     11    13    12
dog      3    12     6
fish     7     9     3

Of course the assumption here is that all entries are in df2

user_71757130

Here's a vectorized version capable of handling missing entries in df2 :

(df1.set_index('Name')
    .stack()
    .str.split(r',\s*')      # allow various number of whitespaces after comma
    .explode()
    .map(df2.set_index('Term')['Freq'])
    .fillna(0, downcast='infer')             # treat missing entry in df2 as 0
    .groupby(level=[0,1])                    # group on `Name` and `Set*`
    .sum()
    .unstack()
).reset_index()

Result

   Name  Set1  Set2  Set3
0   cat    11    13    12
1   dog     3    12     6
2  fish     7     9     3
user_150706970
Related