使用R语言统计新老分组：将HAVE转换为WANT数据集

We can use the dplyr package to efficiently compute the required metrics step-by-step:

Step 1: Define the Input Dataset #

HAVE <- data.frame(
  TEACHER = c(1,1,1,1,1,2,2,2,2),
  STUDENT = c(1,1,1,2,2,3,3,3,4),
  TRIMESTER = c(1,2,3,2,3,3,4,5,4)
)

Step 2: Calculate Student Metrics #

First, identify the first trimester each student appears, then compute new and total student counts per trimester:

library(dplyr)

student_metrics <- HAVE %>%
  group_by(STUDENT) %>%
  summarise(first_trimester = min(TRIMESTER)) %>%
  count(first_trimester, name = "NEWSTUDENTS") %>%
  rename(TRIMESTER = first_trimester) %>%
  complete(TRIMESTER = 1:5, fill = list(NEWSTUDENTS = 0)) %>%
  arrange(TRIMESTER) %>%
  mutate(TOTALSTUDENTS = cumsum(NEWSTUDENTS))

Step 3: Calculate Teacher Metrics #

Repeat the same logic for teachers:

teacher_metrics <- HAVE %>%
  group_by(TEACHER) %>%
  summarise(first_trimester = min(TRIMESTER)) %>%
  count(first_trimester, name = "NEWTEACHER") %>%
  rename(TRIMESTER = first_trimester) %>%
  complete(TRIMESTER = 1:5, fill = list(NEWTEACHER = 0)) %>%
  arrange(TRIMESTER) %>%
  mutate(TOTALTEACHER = cumsum(NEWTEACHER))

Step 4: Combine Metrics into the WANT Dataset #

Merge the student and teacher metrics and reorder columns to match the desired output:

WANT <- student_metrics %>%
  left_join(teacher_metrics, by = "TRIMESTER") %>%
  select(TRIMESTER, NEWSTUDENTS, TOTALSTUDENTS, NEWTEACHER, TOTALTEACHER)

# View the result
print(WANT)

Output #

Running the code will produce exactly the WANT dataset:

TRIMESTER NEWSTUDENTS TOTALSTUDENTS NEWTEACHER TOTALTEACHER
1         1           1             1          1            1
2         2           1             2          0            1
3         3           1             3          1            2
4         4           1             4          0            2
5         5           0             4          0            2

内容的提问来源于stack exchange，提问作者bvowe