Calculate a rate by category

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"
  )

Hi,

Here is a way to do that

#Count the number of cancellations
totalCancellations = sum(appts$AppointmentStatus == "Canceled")

appts %>% 
  filter(AppointmentStatus == "Canceled") %>% #Only look at cancellations
  group_by(ReasonAppointmentCanceled) %>% #Group per reason
  summarise(n = n(), rate = n() / totalCancellations) %>% #Calculate the sum and rate
  arrange(desc(rate)) #Sort from highest to lowest rate

# A tibble: 7 x 3
  ReasonAppointmentCanceled             n   rate
  <chr>                             <int>  <dbl>
1 Provider                             22 0.449 
2 Patient                              11 0.224 
3 Error                                 6 0.122 
4 Other                                 5 0.102 
5 Other: Public Health Emergency        3 0.0612
6 Alternative Appointment Scheduled     1 0.0204
7 Deceased                              1 0.0204

Hope this helps,
PJ

1 Like

Very helpful, thank you!

Great, that gives me rates within a particular appointment status, in this case "Canceled". How best to calculate the overall cancellation rate? Basically, how do I get a count of all cases/rows? Note that in my original dataset I have a field called CSN which is a unique identifier for each visit, i.e. row. When I read the Excel file into RStudio, the CSN column is numeric so sum doesn't work to give a count.

Hi,

Do you mean something like this?

sum(appts$AppointmentStatus == "Canceled") / nrow(appts)

PJ

Yes, nrow, that's what I was looking for!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.