How to assign a "reseting" group number by the second grouping variable in R?

Question

My data looks like this:

Measurement	Compound	Measure
1	A	111
1	A	222
1	B	333
1	B	444
2	C	555
2	C	666
2	D	777
2	D	888

And I'm trying to assign a "reseting" group number based on Compound:

Measurement	Compound	Measure	Compound_order
1	A	111	1
1	A	222	1
1	B	333	2
1	B	444	2
2	C	555	1
2	C	666	1
2	D	777	2
2	D	888	2

Didn't come up with the solution on dplyr group_by.

DATA

dat <- data.frame(
  Measurement = c(1, 1, 1, 1, 2, 2, 2, 2),
  Compound = c("A", "A", "B", "B", "C", "C", "D", 'D'),
  Measure = 111 * 1:8
)

Answer 1

You can do the following.

library(dplyr)

dat2 <- dat %>%
  group_by(Measurement) %>%
  mutate(Compound_order = as.integer(factor(Compound,
                                            levels = unique(Compound)))) %>%
  ungroup()

dat2
# # A tibble: 8 x 4
#   Measurement Compound Measure Compound_order
#         <dbl> <chr>      <dbl>          <int>
# 1           1 A            111              1
# 2           1 A            222              1
# 3           1 B            333              2
# 4           1 B            444              2
# 5           2 C            555              1
# 6           2 C            666              1
# 7           2 D            777              2
# 8           2 D            888              2

DATA

dat <- data.frame(
  Measurement = c(1, 1, 1, 1, 2, 2, 2, 2),
  Compound = c("A", "A", "B", "B", "C", "C", "D", 'D'),
  Measure = 111 * 1:8
)