r - 根据联合/左日期创建数据集 - R

Question

我有以下数据集，其中包含有关员工加入和离开组织的信息:

dataset1 <- read.table(
  text = "
Employee       Organisation      Joint_date          Left_date
G223           A123              1993-05-15          2019-05-01
G223           A123              2020-04-11          NA
G233           A123              2018-02-20          NA
G234           A123              2015-09-04          NA
G111           A333              1980-10-03          2019-09-27
G122           A333              2000-11-16          NA
G177           A333              2005-01-19          NA
G330           A333              2002-12-24          NA
G556           A333              2018-05-01          2019-03-04
G555           A445              2015-11-18          NA
G556           A445              2005-09-01          2018-03-04
G557           A445              1989-04-05          NA",
  header = TRUE)

dataset1$Employee <- as.factor(dataset1$Employee)
dataset1$Organisation <- as.factor(dataset1$Organisation)
dataset1$Joint_date <- as.Date(dataset1$Joint_date, format="%Y-%m-%d")
dataset1$Left_date <- as.Date(dataset1$Left_date, format="%Y-%m-%d")

我创建了从 2018-01-31 到 2021-06-30 的数据集 2(每月数据集):

dataset2_dates=c("2018-01-31","2018-02-28","2018-03-31","2018-04-30","2018-05-31","2018-06-30","2018-07-31","2018-08-31","2018-09-30","2018-10-31","2018-11-30","2018-12-31","2019-01-31","2019-02-28","2019-03-31","2019-04-30","2019-05-31","2019-06-30","2019-07-31","2019-08-31","2019-09-30","2019-10-31","2019-11-30","2019-12-31","2020-01-31","2020-02-29","2020-03-31","2020-04-30","2020-05-31","2020-06-30","2020-07-31","2020-08-31","2020-09-30","2020-10-31","2020-11-30","2020-12-31","2021-01-31","2021-02-28","2021-03-31","2021-04-30","2021-05-31","2021-06-30")

# add dates
dataset2 <- expand.grid(Organisation = unique(dataset1$Organisation),
                       Month = dataset2_dates)
  
## sort
dataset2 <- dataset2[order(dataset2$Organisation, dataset2$Month),]
## reset id
rownames(dataset2) <- NULL

dataset2$Organisation <- as.factor(dataset2$Organisation)
dataset2$Month <- as.Date(dataset2$Month, format="%Y-%m-%d")

我希望得到以下 dataset3:

Organisation    | Month         | Nr_employees      
A123            | 2018-01-31    | 2
A123            | 2018-02-28    | 3
A123            | 2018-03-31    | 3
A123            | 2018-04-30    | 3
A123            | 2018-05-31    | 3
A123            | 2018-06-30    | 3
A123            | 2018-07-31    | 3
A123            | 2018-08-31    | 3
A123            | 2018-09-30    | 3
A123            | 2018-10-31    | 3
A123            | 2018-11-30    | 3
A123            | 2018-12-31    | 3
A123            | 2019-01-31    | 3
A123            | 2019-02-28    | 3
A123            | 2019-03-31    | 3
A123            | 2019-04-30    | 3
A123            | 2019-05-31    | 3
A123            | 2019-06-30    | 2
A123            | 2019-07-31    | 2
A123            | 2019-08-31    | 2
A123            | 2019-09-30    | 2
A123            | 2019-10-31    | 2
A123            | 2019-11-30    | 2
A123            | 2019-12-31    | 2
A123            | 2020-01-31    | 2
A123            | 2020-02-29    | 2
A123            | 2020-03-31    | 2
A123            | 2020-04-30    | 3
A123            | 2020-05-31    | 3
A123            | 2020-06-30    | 3
A123            | 2020-07-31    | 3
A123            | 2020-08-31    | 3
A123            | 2020-09-30    | 3
A123            | 2020-10-31    | 3
A123            | 2020-11-30    | 3
A123            | 2020-12-31    | 3
A123            | 2021-01-31    | 3
A123            | 2021-02-28    | 3
A123            | 2021-03-31    | 3
A123            | 2021-04-30    | 3
A123            | 2021-05-31    | 3
A123            | 2021-06-30    | 3
.....

注意:如果员工在当月的最后一天加入或在当月的第一天离开，仍然视为该员工整个月都在。

dataset4 汇总了 2018-01-31 到 2021-06-30 的数据:

Organisation  | Average Nr_employees  | Nr_employees joined | Nr_employess left | Nr_employess stayed the whole time
A123          | 115/42 = 2.7          | 2                   | 1                 | 1
....

关于如何生成 dataset3 和 dataset4 有什么想法吗？

Answer 1

我更喜欢使用 data.table 包 - 对于创建 dataset3 等问题，非等值连接功能非常适合。

library(data.table)
setDT(dataset1)    

dataset2 <- CJ(Organisation = dataset1[,unique(Organisation)],
               ## This is an option to generate the month sequence based on the first date in dataset1 to present
               # Month = seq.Date(from = as.Date(cut.Date(dataset1[,min(Joint_date)], breaks = "months")),
               #                  to = as.Date(cut.Date(Sys.Date(), breaks = "months")),
               #                  by = "month") - 1
               ## Otherwise you can still generate a full sequence of month-end dates with just a start and end
               Month = seq.Date(from = as.Date("2018-02-01"),
                                to = as.Date("2021-07-01"),
                                by = "month") - 1)

## Simpler to compare month start dates than end
dataset2[,MonthStart := as.Date(cut.Date(Month, breaks = "months"))]

## Fill NA's for Left_date with today's date to properly account for employees still present
dataset1[,Left_date_fill := data.table::fcoalesce(Left_date, Sys.Date())]

## Create columnns with the month start dates of arrivals/departures
dataset1[,Joint_date_month := as.Date(cut.Date(Joint_date, breaks = "months"))]
dataset1[,Left_date_fill_month := as.Date(cut.Date(Left_date_fill, breaks = "months"))]

## Use a non-equijoin to summarize the number of employees present by month
dataset2[dataset1, Nr_employees := .N, by = .(Organisation,
                                              Month), on = .(Organisation = Organisation,
                                                             MonthStart >= Joint_date_month,
                                                             MonthStart <= Left_date_fill_month)]

## Using this method, the information required for `dataset3` has been added to `dataset2` instead
print(dataset2[seq_len(5), .(Organisation, Month, Nr_employees)])
#    Organisation      Month Nr_employees
# 1:         A123 2018-01-31            2
# 2:         A123 2018-02-28            3
# 3:         A123 2018-03-31            3
# 4:         A123 2018-04-30            3
# 5:         A123 2018-05-31            3
# 6:         A123 2018-06-30            3

要创建像 dataset4 这样的汇总表，对我来说最有意义的做法是将每个步骤分解为一个单独的操作:

## Start with a table of organizations for dataset4
dataset4 <- data.table(Organisation = dataset1[,unique(Organisation)])

## Join on a summary of dataset2 to get the average over the window of interest
dataset4[dataset2[,.(Avg = mean(fcoalesce(Nr_employees),0.0)), by = .(Organisation)]
         ,Average_Nr_employees := Avg, on = .(Organisation)]

## Join a summary of dataset1 counting the number that joined in the window of interest
dataset4[dataset1[Joint_date_month >= dataset2[,min(MonthStart)]
                  & Joint_date_month <= dataset2[,max(MonthStart)]
                  , .(N = .N)
                  , by = .(Organisation)], Nr_employees_joined := N, on = .(Organisation)]

## Join a summary of dataset1 counting the number that left in the window of interest
dataset4[dataset1[Left_date_fill_month >= dataset2[,min(MonthStart)]
                  & Left_date_fill_month <= dataset2[,max(MonthStart)]
                  , .(N = .N)
                  , by = .(Organisation)], Nr_employees_left := N, on = .(Organisation)]

## Join a summary of dataset1 counting the number that joined before and left after window of interest
dataset4[dataset1[Joint_date_month <= dataset2[,min(MonthStart)]
                  & Left_date_fill_month >= dataset2[,max(MonthStart)]
                  , .(N = .N)
                  , by = .(Organisation)], Nr_employees_stayed := N, on = .(Organisation)]

print(dataset4)
#    Organisation Average_Nr_employees Nr_employees_joined Nr_employees_left Nr_employees_stayed
# 1:         A123             2.738095                   2                 1                   1
# 2:         A333             3.761905                   1                 2                   3
# 3:         A445             2.071429                  NA                 1                   2