I'm fairly new to R and have been busy with other projects so I've forgotten some of what I previously learned. I have an appointment dataset and I want to calculate overall cancellation rates, provider initiated cancellation rates, and average cancellation rate, all grouped by provider, patient sex, and race. I'd also like to see if there is a correlation between provider cancellation rate and the number of "no shows".
So, I have calculated new columns for when an appointment is cancelled and provider cancelled, and I tried creating a provider cancelled rate but that doesn't seem to be working.
How to I calculate rates grouped by different categories?
#load libraries
library(dplyr)
#create binary cancellation columns and rate column
df1 <- df %>%
dplyr::mutate(
Cancelled = ifelse(AppointmentStatus == "Canceled", 1, 0)
)
df1 <- df1 %>%
dplyr::mutate(
ProviderCancelled = ifelse(ReasonAppointmentCanceled == "Provider", 1, 0)
)
df1 <- df1 %>%
dplyr::mutate(
ProviderCNCLRate = sum(ProviderCancelled)/sum(Cancelled)
)
sample dataset
appts <- tibble::tribble(
~Sex, ~FirstRace, ~AppointmentStatus, ~ReasonAppointmentCanceled, ~VisitType, ~ProviderName, ~Type,
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "A", "Clinical Nurse Specialist",
"Female", "Hispanic or Latino", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "C", "Physician",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "D", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "D", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "G", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "G", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Male", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "H", "Physician",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Female", "Asian", "Canceled", "Other", "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "J", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Canceled", "Other", "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Male", "White", "Canceled", "Other: Public Health Emergency", "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Completed", NA, "TELEPHONE VISIT", "B", "Physician",
"Male", "White", "Canceled", "Error", "GROUP THERAPY FOLLOW-UP", "K", "Psychologist",
"Male", "White", "Canceled", "Error", "GROUP THERAPY FOLLOW-UP", "K", "Psychologist",
"Male", "White", "Canceled", "Error", "GROUP THERAPY FOLLOW-UP", "K", "Psychologist",
"Male", "White", "Canceled", "Error", "GROUP THERAPY FOLLOW-UP", "K", "Psychologist",
"Male", "White", "Canceled", "Error", "GROUP THERAPY FOLLOW-UP", "K", "Psychologist",
"Female", "White", "Completed", NA, "TELEPHONE VISIT", "B", "Physician",
"Female", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Female", "Hispanic or Latino", "Canceled", "Other", "OFFICE VISIT EXTENDED", "L", "Psychologist",
"Female", "Hispanic or Latino", "Canceled", "Other", "OFFICE VISIT EXTENDED", "L", "Psychologist",
"Female", "Hispanic or Latino", "Canceled", "Other", "OFFICE VISIT EXTENDED", "L", "Psychologist",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "H", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "H", "Physician",
"Female", "White", "Canceled", "Other: Public Health Emergency", "TELEPHONE VISIT", "F", "Physician",
"Female", "White", "Canceled", "Other: Public Health Emergency", "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Male", "White", "Completed", NA, "VIDEO VISIT", "B", "Physician",
"Male", "White", "Completed", NA, "VIDEO VISIT", "B", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "G", "Physician",
"Male", "White", "No Show", NA, "OFFICE VISIT INTERMEDIATE", "M", "Physician",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "G", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT BRIEF", "N", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT EXTENDED", "O", "Psychologist",
"Male", "White", "Completed", NA, "TELEPHONE VISIT", "B", "Physician",
"Female", "White", "Completed", NA, "VIDEO VISIT", "B", "Physician",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT EXTENDED", "K", "Psychologist",
"Female", "White", "Completed", NA, "VIDEO VISIT", "B", "Physician",
"Male", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "H", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT EXTENDED", "P", "Psychologist",
"Female", "White", "Canceled", "Provider", "GROUP THERAPY FOLLOW-UP", "K", "Psychologist",
"Male", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "G", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Male", "White", "Completed", NA, "TELEPHONE VISIT", "Q", "Nurse Practitioner",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "S", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Female", "White", "No Show", NA, "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Female", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT EXTENDED", "L", "Psychologist",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Male", "White", "Completed", NA, "TELEPHONE VISIT", "R", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Female", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "B", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "C", "Physician",
"Female", "White", "Canceled", "Patient", "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "H", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "G", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Female", "White", "Canceled", "Deceased", "OFFICE VISIT INTERMEDIATE", "A", "Clinical Nurse Specialist",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "G", "Physician",
"Male", "White", "Canceled", "Error", "OFFICE VISIT EXTENDED", "C", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "C", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Male", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Female", "White", "No Show", NA, "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "S", "Physician",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Canceled", "Alternative Appointment Scheduled", "OFFICE VISIT INTERMEDIATE", "R", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "E", "Physician",
"Female", "White", "Canceled", "Provider", "OFFICE VISIT INTERMEDIATE", "F", "Physician",
"Female", "White", "Completed", NA, "OFFICE VISIT EXTENDED", "T", "Psychologist",
"Male", "White", "Completed", NA, "OFFICE VISIT INTERMEDIATE", "M", "Physician"
)