Pandas: How To Sum Columns On Data Frame Based On Value Of Another Data Frame

I am new to Pandas and I am trying to do the following thing:: I have a dataframe called comms with columns articleID and commentScore (among others) I have another dataframe call

Solution 1:

I think you can use groupby followed by a merge on 'artID':

grpd = comms.groupby('artID')
to_merge = grpd.sum().divide(np.sqrt(grpd.count()+1)).reset_index().rename(columns={'commScore': 'artScore'})[['artID', 'artScore']]
arts.merge(to_merge, on='artID')

Solution 2:

You can use groupby with agg and a custom lambda function to apply to each group:

comms.groupby('artID').agg(
    {'commScore': lambda x: x.sum() / np.sqrt(len(x) + 1)}
).reset_index().rename(columns={'commScore': 'artScore'})

Result:

  artID  artScore
0  1x5w  2.886751
1  3612  3.535534
2  77k3  0.000000

Solution 3:

#article count and sumdf = df.groupby('artID').agg(['sum', 'count'])

#create new column and utilize your formuladf['artScore'] = df['commScore']['sum'] / math.sqrt(df['commScore']['count']+1)


    commScore   artScore
       sum  count   
artID           
1x5w    5   2   5.0
3612    5   1   5.0
77k3    0   2   0.0