CODEWITHSUNDEEP
rdategroup-bycountdplyr
Boudu
Boudu

Reputation: 87

Counting Records that are Active between two dates in R by Fiscal Quarter

head(df, 6) 

       PositionName       Hire.Date     Termination.Date      
1      CBM                4/22/2002         9/14/2007  
2      CBM                10/5/2005         5/5/2008   
3      SBM                10/31/2005        6/25/2007  
4      CBM                12/1/2005         5/5/2008   
5      SBM                7/6/2006          6/20/2008  
6      CBM                10/6/2006         6/30/2008  



df$Hire.Date <- format(as.Date(sm.SL$Hire.Date, format = "%m/%d/%Y"), "%m/%Y")
df$Termination.Date <- format(as.Date(sm.SL$Termination.Date, format = "%m/%d/%Y"), "%m/%Y")

    #Stuckhere
      WorkedDuring <- df %>% 
      filter(PositionName == "CBM")%>%
      mutate(quarter = quarter(Hire.Date >, with_year = TRUE))%>% 
      group_by(quarter) %>%
      summarise(total = n())

Trying to get a df:

Year.Quarter     n
2005.1           1
2005.2           1
2005.3           1
2005.4           4
2006.1           4
2006.2           4
2006.3           5
2006.4           6

Not a great example as it just goes up, but I would like to take away people when they are terminated.

Upvotes: 4

Views: 677

Answers (3)

jmurph25
jmurph25

Reputation: 11

You can think of this problem as adding a +1 to your workforce for every hire date, and a -1 to your work force for every termination date. 

library(tidyverse)
library(lubridate)

# create date variables using the lubridate package  
df <- df %>% 
        mutate(Hire.Date = mdy(Hire.Date),
               Termination.Date = mdy(Termination.Date))

# create an arrival counter
arrive <- df %>% 
           mutate(time = floor_date(Hire.Date, 'quarter'),
                  type = 1) %>% 
           select(time, type)

# create a departure counter
depart <- df %>% 
           mutate(time = floor_date(Termination.Date,'quarter'),
                  type = -1) %>% 
           select(time, type)

# bind the two and take cumulative sum
arrive_depart <- arrive %>% 
                    bind_rows(depart) %>% 
                    arrange(time) %>% 
                    mutate(volume = cumsum(type))

# fill in the missing dates
start_time <- min(arrive_depart$time)
end_time <- max(arrive_depart$time)
full_time <- tibble(time = seq(start_time, end_time, 'quarter'))

arrive_depart <- full_time %>% 
  left_join(arrive_depart, by = 'time') %>% 
  fill(volume)

Upvotes: 1

Ben G
Ben G

Reputation: 4328

Here's how you would add the fiscal quarter column based on the government fiscal year (the US federal government fiscal year is from October 1 to September 30). You could adjust for whatever your fiscal year is. Shout out to my colleague Ron Graff for the code:

library(zoo) # good library for dealing with dates, also check out lubridate
library(dplyr)
library(lubridate) # good library for dealing with dates
library(stringr) # for dealing with characters

data_with_quarter <- data %>%
  mutate(fiscal_year = as.numeric(as.yearmon(Hire.Date) - 9/12 +1) %/% 1) %>%
  mutate(month = month(Hire.Date)) %>%
  mutate(quarter = if_else(month > 9, 1,
                       if_else(month > 6, 4,
                           if_else(month > 3, 3, 3)))) %>%
  mutate(fiscal_qurater = str_c(as.character(fiscal_year), ".", as.character(quarter)))

Then if you wanted to plot this, you'd probably want to convert your fiscal quarter column to a factor

Upvotes: 1

r2evans
r2evans

Reputation: 160407

Try this:

# quick helper function
date2qtr <- function(d) 1 + (as.integer(format(d, "%m")) - 1) %/% 3L

library(dplyr)
# library(tidyr)
# library(purrr)
tbl_df(df) %>%
  mutate(
    alldates = purrr::map2(Hire.Date, Termination.Date,
                           ~ seq.Date(..1, ..2, by = "3 months"))
  ) %>%
  tidyr::unnest() %>%
  mutate(
    Year = as.integer(format(alldates, "%Y")),
    Quarter = date2qtr(alldates)
  ) %>%
  group_by(Year, Quarter) %>%
  tally()
# # A tibble: 25 x 3
# # Groups:   Year [?]
#     Year Quarter     n
#    <int>   <dbl> <int>
#  1  2002       2     1
#  2  2002       3     1
#  3  2002       4     1
#  4  2003       1     1
#  5  2003       2     1
#  6  2003       3     1
#  7  2003       4     1
#  8  2004       1     1
#  9  2004       2     1
# 10  2004       3     1
# # ... with 15 more rows

Data:

df <- structure(list(PositionName = c("CBM", "CBM", "SBM", "CBM", "SBM", 
"CBM"), Hire.Date = c("4/22/2002", "10/5/2005", "10/31/2005", 
"12/1/2005", "7/6/2006", "10/6/2006"), Termination.Date = c("9/14/2007", 
"5/5/2008", "6/25/2007", "5/5/2008", "6/20/2008", "6/30/2008"
)), class = "data.frame", row.names = c("1", "2", "3", "4", "5", 
"6"))

df[c("Hire.Date","Termination.Date")] <-
  lapply(df[c("Hire.Date","Termination.Date")], as.Date, format = "%m/%d/%Y")

Upvotes: 2

Related Questions