preppin_data_wk_49.R

###################################################################

# Input data
# Create the Employment Range field which captures the employees full tenure at the company in the MMM yyyy to MMM yyyy format. 
# Work out for each year employed per person:
# Number of months they worked
# Their salary they will have received 
# Their sales total for the year
# For each Reporting Year (the individual year someone worked for us), calculate their cumulative months (called Tenure)
# Determine the bonus payments the person will have received each year
# It's 5% of their sales total
# Round Salary Paid and Yearly Bonus to two decimal places 
# Add Salary Paid and Yearly Bonus together to form Total Paid

###################################################################


library(tidyverse)
library(lubridate)


import <- 
  read_csv("PD 2021 Wk 49 Input - Input.csv") %>% 
  janitor::clean_names() %>% 
  mutate(date = dmy(date))




# Create the Employment Range field which captures the employees full tenure at the company in the MMM yyyy to MMM yyyy format. 


employment_range <- 
  import %>% 
  group_by(name) %>% 
  summarise(min_date = min(date),
            max_date = max(date)) %>% 
  mutate(across(c(min_date, max_date), list(tidy = ~.x %>% format("%b %Y"))),
         employment_range = glue::glue("{min_date_tidy} to {max_date_tidy}"))




# Work out for each year employed per person:
# Number of months they worked
# Their salary they will have received 
# Their sales total for the year


yearly_worker_summary_stats <- 
  import %>% 
  group_by(name, year_of_employment = year(date)) %>% 
  summarise(
    total_months_worked = n(),
    min_month = min(date),
    max_month = max(date),
    # unique_annual_salaries = n_distinct(annual_salary),
    annual_salary = min(annual_salary),
    total_sales = sum(sales)) %>% 
  mutate(yearly_bonus = 0.05*total_sales)




month_to_month <- 
  import %>% 
  group_by(name) %>% 
  summarise(
    min_month = min(date),
    max_month = max(date)) %>% 
  mutate(tenure_months = map2(min_month, max_month, ~seq.Date(.x, .y, by = "month"))) %>% 
  select(name, tenure_months) %>% 
  unnest(tenure_months) %>% 
  mutate(year_of_employment = year(tenure_months)) %>% 
  inner_join(
    yearly_worker_summary_stats %>% select(name, year_of_employment, annual_salary)
    , by = c("name", "year_of_employment")) %>% 
  mutate(monthly_salary = annual_salary/12, .keep = "unused") %>% 
  arrange(tenure_months) %>% 
  group_by(name) %>% 
  mutate(cumulative_salary = cumsum(monthly_salary),
         cumulative_months = row_number())





# For each Reporting Year (the individual year someone worked for us), calculate their cumulative months (called Tenure)
# Determine the bonus payments the person will have received each year
# It's 5% of their sales total
# Round Salary Paid and Yearly Bonus to two decimal places 
# Add Salary Paid and Yearly Bonus together to form Total Paid


output_as_requested <- 
  month_to_month %>% 
  group_by(name, year_of_employment) %>% 
  summarise(cumulative_salary = max(cumulative_salary),
            cumulative_months = max(cumulative_months)) %>% 
  inner_join(
    yearly_worker_summary_stats %>% select(name, year_of_employment, yearly_bonus)
    , by = c("name", "year_of_employment")) %>% 
  mutate(`Total Paid` = cumulative_salary + yearly_bonus) %>%
  inner_join(
    employment_range %>% select(name, employment_range)
    , by = "name") %>% 
  select(Name = name, 
         `Employment Range` = employment_range,
         `Reporting Year` = year_of_employment,
         `Tenue by End of Re` = cumulative_months,
         `Salary Paid` = cumulative_salary,
         `Yearly Bonus` = yearly_bonus,
         `Total Paid`)




output_as_requested %>% 
  write_csv("output.csv")