R - add a column that counts sequentially within groups, but repeats for duplicates
I am looking for a solution to add a "wish_result" column, preferably using dplyr and / or ave (). See the data section here where the group is the "section" and the unique instances where I want the column of "desired results" to be counted sequentially are in the "exhibit":
structure(list(section = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L), exhibit = structure(c(1L,
2L, 3L, 3L, 1L, 2L, 2L, 3L), .Label = c("a", "b", "c"), class = "factor"),
desired_result = c(1L, 2L, 3L, 3L, 1L, 2L, 2L, 3L)), .Names = c("section",
"exhibit", "desired_result"), class = "data.frame", row.names = c(NA,
-8L))
source to share
I recently pushed a feature rleid()
on data.table
(development version is currently available, 1.9.5), which does exactly that. If you're curious, you can install it by running this .
require(data.table) # 1.9.5, for `rleid()`
require(dplyr)
DF %>%
group_by(section) %>%
mutate(desired_results=rleid(exhibit))
# section exhibit desired_result desired_results
# 1 1 a 1 1
# 2 1 b 2 2
# 3 1 c 3 3
# 4 1 c 3 3
# 5 2 a 1 1
# 6 2 b 2 2
# 7 2 b 2 2
# 8 2 c 3 3
source to share
If exact numbering is required and you need the desired result to be consistent (so that the same exhibit in the other section always has the same number), you can try:
library(dplyr)
df <- data.frame(section = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L),
exhibit = c('a', 'b', 'c', 'c', 'a', 'b', 'b', 'c'))
if (is.null(saveLevels <- levels(df$exhibit)))
saveLevels <- sort(unique(df$exhibit)) ## or levels(factor(df$exhibit))
df %>%
group_by(section) %>%
mutate(answer = as.integer(factor(exhibit, levels = saveLevels)))
## Source: local data frame [8 x 3]
## Groups: section
## section exhibit answer
## 1 1 a 1
## 2 1 b 2
## 3 1 c 3
## 4 1 c 3
## 5 2 a 1
## 6 2 b 2
## 7 2 b 2
## 8 2 c 3
If /, when section
a new one appears in subsequent s exhibit
, they should get new enumerable results. (Note that the latter exhibit
is different.)
df2 <- data.frame(section = c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L),
exhibit = c('a', 'b', 'c', 'c', 'a', 'b', 'b', 'd'))
if (is.null(saveLevels2 <- levels(df2$exhibit)))
saveLevels2 <- sort(unique(df2$exhibit))
df2 %>%
group_by(section) %>%
mutate(answer = as.integer(factor(exhibit, levels = saveLevels2)))
## Source: local data frame [8 x 3]
## Groups: section
## section exhibit answer
## 1 1 a 1
## 2 1 b 2
## 3 1 c 3
## 4 1 c 3
## 5 2 a 1
## 6 2 b 2
## 7 2 b 2
## 8 2 d 4
source to share