1

I asked a similar question last week and now I have a similar issue, but I cannot convert the answer I received in this case.

Basically, I have a dataframe called comms which looks like this:

articleID   Material    commentScore
 1234         News          0.75      
 1234         News          -0.1      
 5678         Sport         1.33      
 5678         News          0.75      
 5678        Fashion        0.02 
 7412       Politics        -3.45

and another dataframe called arts and it looks like this:

articleID   wordCount      byLine
 1234         1524          John     
 5678         9824          Mary    
 7412         3713          Sam

I would like to simply count how many comms there are for each articleID, and store this number in a new column of the arts dataframe named commentNumber.

I think I have to use groupby, count() and maybe merge, but I can't figure out why.

Expected output

articleID   wordCount      byLine    commentNumber
 1234         1524          John         2
 5678         9824          Mary         3
 7412         3713          Sam          1

Thanks in advance! Andrea

Sala
  • 480
  • 4
  • 19

2 Answers2

3

Use groupby() then count() on one column. At last, map the result with articleID columns of arts.

arts['commentNumber'] = arts['articleID'].map(comms.groupby('articleID')['Material'].count())

print(arts)

   articleID  wordCount byLine  commentNumber
0       1234       1524   John              2
1       5678       9824   Mary              3
2       7412       3713    Sam              1
Ynjxsjmh
  • 28,441
  • 6
  • 34
  • 52
2

Use Series.map with Series.value_counts:

arts['commentNumber'] = arts['articleID'].map(comms['articleID'].value_counts())
print (arts)
   articleID  wordCount byLine  commentNumber
0       1234       1524   John              2
1       5678       9824   Mary              3
2       7412       3713    Sam              1

Alternative:

from collections import Counter
arts['commentNumber'] = arts['articleID'].map(Counter(comms['articleID']))
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252