R/dashboard_london.R

In caldwellst/fightr: Tools for Fight for Peace Data

#' Create files necessary for FFP London's Power BI dashboard
#'
#' `get_pbi_dashboard_london()` takes in the parsed databases coming from
#' [parse_session_database()] or [parse_multiple_session_databases()] and produces the
#' data frames necessary for use in FFP London's Power BI dashboards.
#' The function exports a list of data frames that will go into the dashboards in R,
#' and also exports them in Excel to a specified file path.
#'
#' The data frames filter data based on a specific set of attributes. Specifically:
#'
#' * Open Access filters data for sessions where `project == "Open Access"`
#' * Employability filters data for sessions where `project == "Employment"`
#' * Pathways filters data for sessions where `activity_group == "Pathways"`
#' * Special Project filters data for sessions where `project == "Special Project"`
#' * Youth leadership filters data for sessions where `project == "Youth Leadership"`
#'
#' Relevant columns are then selected for each of these activity groups, written
#' to Excel, and exported as a list.
#'
#' @param pd Parsed database list coming out of [parse_session_database()] or
#'     [parse_multiple_session_databases()] with `sessions`, `attendance`,  and `attendees`
#'     data frames.
#' @param file If not `NULL`, file name and path for writing, must end in `".xlsx"`.
#'     If `NULL`, no Excel file is generated.
#' @param ... Additional arguments passed to [openxlsx::write.xlsx()].
#'
#' @return A list of data frames, and Excel file printed to `file`.
#'
#' @export
get_pbi_dashboard_london <- function(pd,
                                     file = NULL,
                                     ...) {
  # Join all data to attendance data frames
  df <- get_full_session_df(pd)

  # Dashboard data frames
  dfs <- list("Open Access" = open_access_df(df),
              "Employability" = employability_df(df),
              "Pathways" = pathways_df(df),
              "Special Project" = special_project_df(df),
              "Youth leadership" = youth_leadership_df(df))

  # Export to Excel
  if (!is.null(file)) {
    openxlsx::write.xlsx(dfs,
                         file,
                         ...)
  }

  dfs
}

#' Get data for the Open Access dashboard
#'
#' `open_access_df()` creates a data frame with the columns and values necessary
#' for providing to the Open Access dashboard in Power BI. This is generated by
#' filtering for `project == "Open Access"` and selecting relevant columns.
#'
#' @param df Full data frame of attendance and session data produced by [get_full_session_df()].
#'
#' @return A data frame.
#'
#' @export
open_access_df <- function(df) {
  nms <- c("Activity Group" = "activity_group",
           "Activity" = "activity",
           "Date" = "date",
           "Duration (hrs)" = "duration_hrs",
           "Participants" = "total_participants",
           "Gender" = "gender",
           "DOB" = "dob",
           "Postcode" = "postcode",
           "Authority" = "authority",
           "District" = "district",
           "Constituency" = "constituency",
           "Ward" = "ward",
           "Disability" = "disability",
           "Ethnicity" = "ethnicity",
           "Quarter" = "quarter",
           "Activity - Recoded" = "activity")

  assert_df(df, nms)

  df %>%
    dplyr::filter(.data[["project"]] == "Open Access") %>%
    dplyr::select(dplyr::any_of(nms))
}

#' Get data for the Employability dashboard
#'
#' `employability_df()` creates a data frame with the columns and values necessary
#' for providing to the Employability dashboard in Power BI. This is generated by
#' filtering for `project == "Employment"` and selecting relevant columns.
#'
#' @inherit open_access_df params return
#'
#' @export
employability_df <- function(df) {
  nms <- c("Attendee ID" = "attendee_id",
           "Session ID" = "session_id",
           "Activity group" = "activity_group",
           "Activity" = "activity",
           "Date" = "date",
           "Duration (hrs)" = "duration_hrs",
           "Participants" = "total_participants",
           "Gender" = "gender",
           "DOB" = "dob",
           "Quarter" = "quarter")
  assert_df(df, nms)

  emp_df <- df %>%
    dplyr::filter(.data[["project"]] == "Employment") %>%
    dplyr::select(dplyr::any_of(nms)) %>%
    tibble::add_column("Value" = "participant", .after = "Session ID")
}

#' Get data for the Pathways dashboard
#'
#' `pathways_df()` creates a data frame with the columns and values necessary
#' for providing to the Pathways dashboard in Power BI. This is generated by
#' filtering for `activity_group == "Pathways"` and selecting relevant columns.
#'
#' @inherit open_access_df params return
#'
#' @export
pathways_df <- function(df) {
  nms <- c("Attendee ID" = "attendee_id",
           "Session ID" = "session_id",
           "Activity group" = "activity_group",
           "Activity" = "activity",
           "Date" = "date",
           "Participants" = "total_participants",
           "Total (sessions attended)" = "total_sessions",
           "Quarter" = "quarter")
  assert_df(df, nms[nms != "value"])

  df %>%
    dplyr::filter(.data[["project"]] == "Open Access") %>%
    dplyr::select(dplyr::any_of(nms)) %>%
    tibble::add_column("Value" = "participant", .after = "Session ID")
}

#' Get data for the Special Project dashboard
#'
#' `special_project_df()` creates a data frame with the columns and values necessary
#' for providing to the Special Project dashboard in Power BI. This is generated by
#' filtering for `project == "Special Project"` and selecting relevant columns.
#'
#' @inherit open_access_df params return
#'
#' @export
special_project_df <- function(df) {
  nms <- c("Attendee ID" = "attendee_id",
           "Session ID" = "session_id",
           "Activity group" = "activity_group",
           "Activity" = "activity",
           "Date" = "date",
           "Participants" = "total_participants",
           "Total (sessions attended)" = "total_sessions",
           "Total (mins)" = "total_mins",
           "Total (hrs)" = "total_hrs",
           "Quarter" = "quarter")
  assert_df(df, nms)

  df %>%
    dplyr::filter(.data[["project"]] == "Special Project") %>%
    dplyr::select(dplyr::any_of(nms)) %>%
    tibble::add_column("Value" = "participant", .after = "Session ID")
}

#' Get data for the Youth Leadership dashboard
#'
#' `youth_leadership_df()` creates a data frame with the columns and values necessary
#' for providing to the Youth Leadership dashboard in Power BI. This is generated by
#' filtering for `project == "Youth Leadership"` and selecting relevant columns.
#'
#' @inherit open_access_df params return
#'
#' @export
youth_leadership_df <- function(df) {
  nms <- c("Attendee ID" = "attendee_id",
           "Session ID" = "session_id",
           "Activity group" = "activity_group",
           "Activity" = "activity",
           "Date" = "date",
           "Quarter" = "quarter")
  assert_df(df, nms)

  df %>%
    dplyr::filter(.data[["project"]] == "Youth Leadership") %>%
    dplyr::select(dplyr::any_of(nms)) %>%
    tibble::add_column("Value" = "participant", .after = "Session ID")
}