count number of observations between two overlapping dates r

user_5052482 0
Viewed 443

Let's say we have a data frame defined as follows:

mydata <- data.frame(id = c('A', 'B', 'C', 'D'),
                     start_date = as.Date(c('2012-08-05',
                                            '2013-05-04',
                                            '2012-02-01',
                                            '2015-03-02')),
                     end_date = as.Date(c('2014-01-12',
                                          '2015-06-05',
                                          '2016-05-06',
                                          '2017-09-12')))

Where start_date talks about the day an employee joined and end_date talks about the day he left and id is the unique employee id.

For each month from 5 August 2012 (the earliest start_date) to 12 September 2017 (the latest end_date) I would like the employee count month wise. The final output should be in a format similar to the one below: (doesn't matter if its in wide format or long format) Final Output Table

In the table above the columns denote the months (1 to 12), the rows the year and the cells in the table the number of employees in that month.

Any help will be highly appreciated.

dataframe r date
4 Answers

You can use also a data.table package or combo of data.table and dplyr.

I will show the dplyr and data.table version (The only reason I use dplyr is the %>% operator. You could also do the whole thing in one line without using %>%).

# load data.table
library(data.table)
# load dplyr
library(dplyr)

#for each employee id, list first days of months during which employee was working, then transform from long to wide format using dcast function
dt <- setDT(mydata)[, list(date = seq(as.Date(format(min(start_date), "%Y-%m-01")),
 as.Date(format(max(end_date), "%Y-%m-01")),
 by = "month")), by = id] %>% dcast(year(date) ~ month(date))

What exactly the code above does?

the by = id tells you that the operation (the calculation of dates in this case) in data.table will be performed for each employee id.

format(max(end_date), "%Y-%m-01") gives you the start of the last month during which each of the employee worked.

format(min(start_date), "%Y-%m-01)" start of the month in which the employee started working.

seq(..., ...., by = "month") gives you first day of all the months in which each of the employees worked.

%>% is the piping operator, it means "then". It is the same as using the result of setDT(mydata)[..., ..., ...] as the first parameter of dcast function.

dcast function transforms the long format (in this case, the result of setDT[...]) to wide format.

End that is the end :)

user_70646280
Related