0

I am trying to aggregate my data so that each entity that I have has a total amount for its accompanying observations.

I will provide a few rows of my data, with col names and row names changed for privacy: My df is in 3 columns ( A, B, C). Column A contains unique ID of a person, (example: 001,002,003), column B contains the organization each ID from A belongs to (example: EntityA, EntityB, EntityC).

Column C contains how many convictions were given to each person(ID), if a ID is repeated in Col A means the person was given two separate convictions, with the total amount of convictions given represented in Col C. In my df, some of these ID's and entities repeat, for example; there may be 5 IDs associated to Entity A, and then 2 for B, 1 for C. Some may repeat in Col A which means it is the same person, or may repeat in Column B but not A, which means two different people but same organization etc as shown below in the picture of data:

Screen grab of Data being used

#

#

I want to be able to count the convictions in Col C per both Entity (Col B) and ID (Col A) in order to see which Entity has the most convictions, and if that is a result of one ID or multiple ID's. This will also help clean my data up by shortening the number of rows as ID and Entity repeats. Thanks in advance.

I have tried sum and count but neither have worked. All columns are character variables but could be converted to numeric or factor.

thegoodjerms
  • 3
  • 2
  • Try `library(dplyr); df1 %>% group_by(A, B) %>% summarise(SumConvictions = sum(C))` – akrun Apr 04 '19 at 14:48
  • You'll certainly want to convert column C to numeric. Can't sum characters or factors. – Gregor Thomas Apr 04 '19 at 14:50
  • Seems like a duplicate of the FAQ [How to sum a variable by group](https://stackoverflow.com/q/1660124/903061). If you've tried some of those answers and they haven't worked, please show what you've tried, and we can re-open the question. If you haven't tried those answers, then please do. – Gregor Thomas Apr 04 '19 at 14:51
  • Thank you, will try the above as well as the FAQ link provided. If neither work I will circle back. – thegoodjerms Apr 04 '19 at 15:04

1 Answers1

0

I would try

library(dplyr)

df %>% 
  count(A, B) # counts uniques combos of A and B

This should give you a new column called "n" that has number of unique instances of A and B.

MDEWITT
  • 2,338
  • 2
  • 12
  • 23