How do I find the indices given value in base R by object?

March 7, 2023

This might seem whimsical to you, because a similar problem is easily solved in dplyr. But I still want to know how to do it.
To illustrate, imagine I am looking at employee data and the goal is to find how many records are there for a given employee-date pair.

# Mockup employee data
df <- data.frame(
  person_id = c(1, 2, 1),
  record_date = as.Date(c("2020-01-01", "2020-01-01", "2020-01-01")),
  salary = c(100, 110, 109)
)

# By object counts rows for each unique employee-date pair
out <- by(
    data = df,
    INDICES = df[, c("win", "record_date")],
    FUN = nrow
)

Now the task is to find all those employee-date pairs where the calculated number of rows is more than 1. I couldn’t find answers on the web yet, "by" makes a bad search word. What I can do is something like:

out>1
#          record_date
# person_id 2020-01-01
#         1       TRUE
#         2      FALSE

But I am not sure how to get (1, "2020-01-01").

>Solution :

You can use ave.

transform(df, flag=ave(person_id, person_id, record_date, FUN=\(x) length(x) > 1))
#   person_id record_date salary flag
# 1         1  2020-01-01    100    1
# 2         2  2020-01-01    110    0
# 3         1  2020-01-01    109    1

You can also use it in subset.

subset(df, ave(person_id, person_id, record_date, FUN=\(x) length(x) > 1) == 1)
#   person_id record_date salary
# 1         1  2020-01-01    100
# 3         1  2020-01-01    109

Note, that ave internally uses by.