Solving Puzzles with R: Improving Work Logs and Calculating Types of Leave

#151–152

Puzzles

Author: ExcelBI

All files (xlsx with puzzle and R with solution) for each and every puzzle are available on my Github. Enjoy.

Puzzle #151

Today we simplified log of people work. They just written down date and time of start and end. Not really helpful, but fortunatelly their manager gave us certain rules: there are no overtime or weekend work, and prepare timetable when exactly each day they were suppose to work.
Lets utilize hms package and sequences to look for how many hours should be paid.

Loading libraries and data

library(tidyverse)
library(readxl)
library(hms)

test  = read_excel("Power Query/PQ_Challenge_151.xlsx", range = "G1:H6") %>%
  janitor::clean_names()


read_excel_range <- function(file, range) {
  read_excel(file, range = range) %>%
    mutate(across(c(starts_with("Start Time"), starts_with("End Time")), as_hms),
           across(c(starts_with("Start Date"), starts_with("End Date")), as_date)) %>%
    janitor::clean_names()
}

input1 <- read_excel_range("Power Query/PQ_Challenge_151.xlsx", "A1:E6")
input2 <- read_excel_range("Power Query/PQ_Challenge_151.xlsx", "A9:D14")

Transformation

result <- input1 %>%
  mutate(
    start = as_datetime(start_date) + start_time,
    end = as_datetime(end_date) + end_time,
    datetime = map2(start, end, seq, by = "hour")
  ) %>%
  unnest(datetime) %>%
  mutate(
    weekday = wday(datetime, week_start = 1),
    time = as_hms(datetime)
  ) %>%
  left_join(input2, by = "weekday") %>%
  filter(datetime >= start & datetime <= end,
         time >= start_time.y & time < end_time.y) %>%
  group_by(employee) %>%
  summarise(total_hours = n() %>% as.numeric())

Validation

identical(result, test)
#> [1] TRUE

Puzzle #152

Another they, another HR issue. Now we have to calculate how many different types of leave and how many days certain workers take. Some conditional expressions and we will have it covered. Let’s go.

Loading libraries and data

library(tidyverse)
library(readxl)

input = read_excel("Power Query/PQ_Challenge_152.xlsx", range = "A1:D17") %>%
  janitor::clean_names()
test  = read_excel("Power Query/PQ_Challenge_152.xlsx", range = "F1:I5") %>%
  janitor::clean_names()

Transformation

result = input %>%
  mutate(seq = map2(from_date, to_date, seq, by = "day")) %>%
  unnest_longer(seq) %>%
  select(-c(from_date, to_date)) %>%
  mutate(value = 1) %>%
  pivot_wider(names_from = type_of_leave, values_from = value, values_fill = 0) %>%
  select(name, seq, ML, PL, CL) %>%
  mutate(sum = ML + PL + CL,
         concat = paste0(ML, PL, CL) %>% as.numeric(),
         main_leave = case_when(sum == 1 & ML == 1 ~ "ML",
                                sum == 1 & PL == 1 ~ "PL",
                                sum == 1 & CL == 1 ~ "CL",
                                sum == 2 & concat >= 100 ~ "ML",
                                sum == 2 & concat < 100 ~ "PL",
                                sum == 3 ~ "ML",
                                TRUE ~ "NA"),
         wday = wday(seq, week_start = 1)) %>%
  filter(!wday %in% c(6, 7)) %>%
  select(name, seq, main_leave) %>%
  mutate(main_leave = str_to_lower(main_leave)) %>%
  group_by(name, main_leave) %>%
  summarise(days = n() %>% as.numeric()) %>%
  ungroup() %>%
  pivot_wider(names_from = main_leave, values_from = days, values_fill = 0)

Validation

identical(result, test)
#> [1] TRUE

Feel free to comment, share and contact me with advices, questions and your ideas how to improve anything. Contact me on Linkedin if you wish as well.

PowerQuery Puzzle solved with R was originally published in Numbers around us on Medium, where people are continuing the conversation by highlighting and responding to this story.

Continue reading: PowerQuery Puzzle solved with R

Implications and Future Developments

The text provides a fascinating exploration of how R programming can be used in two different puzzles to simplify the calculation processes. The first puzzle revolves around improving log work, while the second puzzle tackles the issue of calculating different types of leave. Both cases demonstrate that R’s potential extends beyond its typical use cases, offering a promising outlook for future developments.

Long-Term Implications

These exercises reflect the evolving role of R in streamlining operational procedures, implying a future where R could be widely used to automate a larger portion of data processing tasks across various industries. This result will significantly decrease the time and effort invested in manual calculations, thereby improving operational efficiency. The demonstrated applications also suggest possible integration opportunities for other programming languages and platforms, further expanding R’s role.

Possible Future Developments

Given these applications’ efficiency and scalability, we can expect more refined puzzle solutions powered by R programming language. It is likely to see R being used even more extensively to build models that predict trends, perform sentiment analysis, automate tasks, and make sense of big data. Furthermore, as more businesses recognize the value of R programming in solving complex problems, we may witness a surge in the demand for advanced R training and professional certification courses.

Actionable Advice

1. Explore R Programming: Given its demonstrated efficiency in solving complex problems, exploring R programming becomes essential for businesses and individuals dealing with large datasets regularly.
2. Invest in Training: To maximise efficiency gains from using R programming, investing in comprehensive training programs is advised. This could involve attending workshops, enrolling in online courses or certificate programs, or hiring an expert for personalized training.
3. Stay updated: The world of R programming is continually evolving. Keep an eye on new developments, applications, and updates. Attending seminars, participating in online communities, and subscribing to relevant newsletters are useful ways to stay updated.
4. Seek Expert Assistance: While exploring R programming, it’s beneficial to collaborate with experienced developers or consult a programming expert. This can help accelerate understanding and application within your specific context.

Conclusion

In conclusion, the information in the text underscores the diverse applications and potential of R programming. It highlights the need for continued exploration and training in R for businesses and professionals working with large streams of data. By capitalizing on its applicability, professionals can drive increased operational efficiency and problem-solving capabilities.

Read the original article