0

I have a large dataframe in R in which users were tasked to describe objects in a scene. I needed unique 3 users per scene, however some scenes got described more than 3 times. I'm attempting to keep the first 3 unique users and remove the rest.

Toy data (the real dataset has many more rows and columns)

user <- c("A", "A", "A", "B", "B", "C", "C", "D", "E", "E", "F", "F", "F")
scene <- c("library", "library", "library", "park", "park", "library", "library", "park", "library", "library", "library", "library", "library")
object <- c("book", "book", "lamp", "dog", "cat", "book", "lamp", "dog", "desk", "desk", "book", "lamp", "lamp")
index <- c(1, 2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1, 2)
dat <- data.frame(user, scene, object, index)

user     scene      object      index
A        library    book        1
A        library    book        2
A        library    lamp        1
B        park       dog         1
B        park       cat         1
C        library    book        1
C        library    lamp        1
D        park       dog         1
E        library    desk        1
E        library    desk        2
F        library    book        1
F        library    lamp        1
F        library    lamp        2
...      ...        ...         ...

For example, here A, B, and C were the first users to describe the scene library. So now F's description is not needed. My main problem is that while I can get overall count of unique users, I don't know how to label them as 1, 2, 3, etc in order to chop off the values past 3.

Desired output

user     scene      object      index   count
A        library    book        1       1
A        library    book        2       1
A        library    lamp        1       1
B        park       dog         1       1
B        park       cat         1       1
C        library    book        1       2
C        library    lamp        1       2
D        park       dog         1       2
E        library    desk        1       3
E        library    desk        2       3

This was helpful but only groups by one column, so I've not been able to apply it here: R - Group by variable and then assign a unique ID

psychcoder
  • 543
  • 3
  • 14

1 Answers1

1

For each user you can create a count variable using match and then filter out the values only till count <= 3 :

library(dplyr)

dat %>%
  group_by(scene) %>%
  mutate(count = match(user, unique(user))) %>%
  filter(count <= 3)

#   user  scene   object index count
#   <chr> <chr>   <chr>  <dbl> <int>
# 1 A     library book       1     1
# 2 A     library book       2     1
# 3 A     library lamp       1     1
# 4 B     park    dog        1     1
# 5 B     park    cat        1     1
# 6 C     library book       1     2
# 7 C     library lamp       1     2
# 8 D     park    dog        1     2
# 9 E     library desk       1     3
#10 E     library desk       2     3

The same in data.table would be :

library(data.table)
setDT(dat)[, count := match(user, unique(user)), scene]
dat[count <= 3]

and base R :

dat$count <- with(dat, ave(user, scene, FUN = function(x) match(x, unique(x))))
subset(dat, count <= 3)
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213