How do I create a column in a dataframe that is the sum of another column in another dataframe based on a common column?

  dataframe, pandas-groupby, python

I have a dataframe (df1) that looks like this:

Character       Word Count
Leslie Knope    58
Child           9
Leslie Knope    13
Child           63

and another dataframe (df2) that looks like this:

Character      Line Count
Leslie Knope   81
Child          1

I’m trying to create a new column in df2 that is the sum of the ‘Word Count’ column in df1 based on the common ‘Character Name’ column in both datasets. The output should look like this:

Character     Line Count   Word Count
Leslie Knope  81           71
Child         1            72

I’ve tried using groupby() but can’t figure out how to create the Word Count column in df2 that is based on the condition that the Character Name matches between the two dataframes.

Thanks in advance for any help!

Source: Python Questions

LEAVE A COMMENT