Calculating project days worked per month

[slammie]

Hi all,

I am trying to analyze data where individuals are involved in projects over the course of various months and years, and I need to generate counts of how many days individuals are involved in projects each month. (In my actual project, I will need to do this for more than 2000 individuals over the course of 2 years.)

Take the examples below:

1. Person A – Works on project X April 27-June 5, 2019.
2. Person B – Works on project Y Jan 14-Apr 1, 2021.
3. Person C – Works on project Z Mar 21-July 4, 2020.

What code can I use to quickly generate these counts per month? Below is an example of how I need to determine project days per month using those same individuals I listed above.

library(tidyverse)

library(knitr)

data <- tibble::tribble(

  ~Project_Start, ~Project_End, ~`Apr-19`, ~`May-19`, ~`Jun-19`, ~`Jan-21`, ~`Feb-21`, ~`Mar-21`, ~`Apr-21`, ~`Mar-20`, ~`Apr-20`, ~`May-20`, ~`Jun-20`, ~`Jul-20`,

    "2019-04-27", "2019-06-05",        4L,       31L,        5L,        NA,        NA,        NA,        NA,        NA,        NA,        NA,        NA,        NA,

    "2021-01-14", "2021-04-01",        NA,        NA,        NA,       18L,       28L,       31L,        1L,        NA,        NA,        NA,        NA,        NA,

    "2020-03-21", "2020-07-04",        NA,        NA,        NA,        NA,        NA,        NA,        NA,       11L,       30L,       31L,       30L,        4L

  )

data %>% kable()

Thank you for the help!

Kind regards,

Sam

[FJCC]

Is this what you want?

library(tidyverse)
#> Warning: package 'tibble' was built under R version 4.1.2
data <- tibble::tribble(
  
  ~Project_Start, ~Project_End, ~`Apr-19`, ~`May-19`, ~`Jun-19`, ~`Jan-21`, ~`Feb-21`, ~`Mar-21`, ~`Apr-21`, ~`Mar-20`, ~`Apr-20`, ~`May-20`, ~`Jun-20`, ~`Jul-20`,
  
  "2019-04-27", "2019-06-05",        4L,       31L,        5L,        NA,        NA,        NA,        NA,        NA,        NA,        NA,        NA,        NA,
  
  "2021-01-14", "2021-04-01",        NA,        NA,        NA,       18L,       28L,       31L,        1L,        NA,        NA,        NA,        NA,        NA,
  
  "2020-03-21", "2020-07-04",        NA,        NA,        NA,        NA,        NA,        NA,        NA,       11L,       30L,       31L,       30L,        4L
  
)

data |> pivot_longer(cols = -c("Project_Start", "Project_End"), 
                     names_to = "Month") |> group_by(Month) |> 
  summarize(Total = sum(value, na.rm = TRUE))
#> # A tibble: 12 x 2
#>    Month  Total
#>    <chr>  <int>
#>  1 Apr-19     4
#>  2 Apr-20    30
#>  3 Apr-21     1
#>  4 Feb-21    28
#>  5 Jan-21    18
#>  6 Jul-20     4
#>  7 Jun-19     5
#>  8 Jun-20    30
#>  9 Mar-20    11
#> 10 Mar-21    31
#> 11 May-19    31
#> 12 May-20    31

^{Created on 2022-06-28 by the reprex package (v2.0.1)}