R count rows within multi-level group data frame

Question

I am using R and have a data frame as such

Record_Num    STR
1             ABC
1             ABC
1             EFG
2             HIJ
2             HIJ
2             KLM
3             KLM
3             KLM
3             KLM

I am attempting to get a count for each STR for each record number, so something like this:

Record_Num    STR    Count
1             ABC    2
1             EFG    1
2             HIJ    2
2             KLM    1
3             KLM    3

If I can get that I can take the steps to get what I need, but my next step is to simply weed out the max count for each STR (which I will then use to create a loop - I'm trying to tidy up some very messy data). If I can incorporate that into a single step, all the better, but I'm not concerned about that. I just can't seem to solve the above problem. Given that I'm an extreme novice. I've toyed around with table and tapply, but haven't been able to get it right. Can anybody help?

I realize this is very basic for most of you, so sorry for such a novice question.

This question has been asked numerous times [here](http://stackoverflow.com/questions/7450600/count-number-of-observations-rows-per-group-and-add-result-to-data-frame) and [here](http://stackoverflow.com/questions/25293045/count-number-of-rows-in-a-data-frame-in-r-based-on-group), for instance. — Uwe, Nov 30 '16 at 22:03

score 2 · Answer 1 · answered Nov 30 '16 at 21:43

2

Or even simpler:

library(dplyr)

count(dat, Record_Num, STR)

answered Nov 30 '16 at 21:43

denrou

630
3
12

This also works perfectly, thanks for the correct & fast response! – fleetmack Nov 30 '16 at 21:56
Nice solution. Even simpler without the grouping – Rentrop Nov 30 '16 at 21:57

score 1 · Answer 2 · answered Nov 30 '16 at 21:39

1

You can do the following:

require(dplyr)
dat %>% 
  group_by(Record_Num, STR) %>% 
  summarise(Count = n())

Which gives you:

  Record_Num    STR Count
1          1    ABC     2
2          1    EFG     1
3          2    HIJ     2
4          2    KLM     1
5          3    KLM     3

answered Nov 30 '16 at 21:39

Rentrop

20,979
10
72
100

This works perfectly, thank you for the correct & fast response! – fleetmack Nov 30 '16 at 21:56

Kristofersen · Answer 3 · 2016-11-30T21:56:04.983

1

If you use a data.table this is very easy.

setDT(dat) 
dat[,.(Count =.N), by=.(STR, Record_Num)]

edited Nov 30 '16 at 21:56

answered Nov 30 '16 at 21:46

Kristofersen

2,736
1
15
31

1

This also works perfectly (if I add the record_num to the group by clause), thanks for the correct & fast response! – fleetmack Nov 30 '16 at 21:56

R count rows within multi-level group data frame

3 Answers3