R语言基于员工入职离职日期生成月度在职表及汇总数据集

前置依赖 #

首先加载用到的工具包：

library(tidyverse)
library(lubridate)

生成dataset3（组织月度在职人数统计） #

统计逻辑 #

员工满足以下两个条件即算当月在职，完全符合题目要求的「当月入职/离职均算全月在职」规则：

• 入职日期早于等于当月最后一天
• 未离职，或离职日期晚于当月最后一天

实现代码 #

# 预处理员工表：填充未离职员工的离职日期为统计周期结束后1天，方便条件判断
dataset1_processed <- dataset1 %>%
  mutate(Left_date_fill = if_else(is.na(Left_date), max(dataset2$Month) + days(1), Left_date))

# 关联月度基准表统计在职人数
dataset3 <- dataset2 %>%
  left_join(dataset1_processed, by = "Organisation") %>%
  filter(Joint_date <= Month & Left_date_fill > Month) %>%
  group_by(Organisation, Month) %>%
  summarise(Nr_employees = n_distinct(Employee), .groups = "drop") %>%
  # 补全无在职员工的月份，人数记为0
  right_join(dataset2, by = c("Organisation", "Month")) %>%
  mutate(Nr_employees = replace_na(Nr_employees, 0)) %>%
  arrange(Organisation, Month)

生成dataset4（组织全周期汇总指标） #

指标逻辑说明 #

• 平均在职人数：该组织所有月度在职人数的算术平均值，保留1位小数
• 周期内新入职人数：2018-01-01至2021-06-30期间入职的去重员工数
• 周期内离职人数：2018-01-01至2021-06-30期间离职的去重员工数
• 全周期均在职人数：2018-01-31前已入职，且2021-06-30仍未离职/离职日期晚于该日的去重员工数

实现代码 #

# 定义统计周期起止时间
start_period <- ymd("2018-01-31")
end_period <- ymd("2021-06-30")

dataset4 <- dataset3 %>%
  # 计算平均在职人数
  group_by(Organisation) %>%
  summarise(`Average Nr_employees` = mean(Nr_employees) %>% round(1), .groups = "drop") %>%
  # 关联新入职人数
  left_join(
    dataset1 %>%
      filter(Joint_date >= floor_date(start_period, "month"), Joint_date <= end_period) %>%
      group_by(Organisation) %>%
      summarise(`Nr_employees joined` = n_distinct(Employee), .groups = "drop"),
    by = "Organisation"
  ) %>%
  # 关联离职人数
  left_join(
    dataset1 %>%
      filter(Left_date >= floor_date(start_period, "month"), Left_date <= end_period) %>%
      group_by(Organisation) %>%
      summarise(`Nr_employees left` = n_distinct(Employee), .groups = "drop"),
    by = "Organisation"
  ) %>%
  # 关联全周期在职人数
  left_join(
    dataset1 %>%
      filter(Joint_date <= start_period, (is.na(Left_date) | Left_date >= end_period)) %>%
      group_by(Organisation) %>%
      summarise(`Nr_employees stayed the whole time` = n_distinct(Employee), .groups = "drop"),
    by = "Organisation"
  ) %>%
  # 空缺指标补0
  mutate(across(where(is.numeric), ~replace_na(.x, 0)))

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