Count the rows by group and get the proportion of different columns

I am trying to find the ratio of CGM prescribed at each location over number of diabetes patients. my actual data looks like this

Desired output:

We may get the sum of the numeric by 'Location' and then create the proportion column by dividing the Total columns

library(dplyr) # version >= 1.1.0
library(stringr)
df1 %>%
   reframe(across(everything(), ~ sum(.x, na.rm = TRUE),
  .names = "Total_{str_remove(.col, ' .*')}"), .by = "Location") %>%
   mutate(proportion = round(Total_CGM/Total_Diabetes, 2))

-output

  Location Total_Diabetes Total_CGM proportion
1       CA              2         1       0.50
2       TX              3         1       0.33
3       AZ              3         2       0.67

Or with base R

transform(aggregate(.~ Location, df1, sum), 
  proportion = round(`CGM prescribed`/`Diabetes present`, 2), 
    check.names = FALSE)

-output

   Location Diabetes present CGM prescribed proportion
1       AZ                3              2       0.67
2       CA                2              1       0.50
3       TX                3              1       0.33

data

df1 <- structure(list(Location = c("CA", "TX", "TX", "CA", "AZ", "AZ", 
"AZ", "TX"), `Diabetes present` = c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L), `CGM prescribed` = c(1L, 0L, 1L, 0L, 1L, 0L, 1L, 0L)),
 class = "data.frame", row.names = c(NA, 
-8L))

Here's a solution in data.table.

setnames(setDT(df1)[, lapply(.SD, sum), .(Location), .SDcols = -1][, 
            proportion := do.call(`/`, .SD), .(Location), .SDcols = 3:2],
         names(df1)[-1], paste0("Total ", sub(" .*", "", names(df1)[-1])))[]

#    Location Total Diabetes Total CGM proportion
# 1:       CA              2         1  0.5000000
# 2:       TX              3         1  0.3333333
# 3:       AZ              3         2  0.6666667

Another dplyr way:

library(dplyr)

df %>% 
  mutate(Location = factor(Location, levels = c("CA", "TX", "AZ"))) %>% 
  group_by(Location) %>% 
  summarise(TotalDiabetes = sum(Diabetes_present),
            Total_CGM = sum(CGM_prescribed),
            Proportion = Total_CGM/TotalDiabetes) 

  Location TotalDiabetes Total_CGM Proportion
  <fct>            <int>     <int>      <dbl>
1 CA                   2         1      0.5  
2 TX                   3         1      0.333
3 AZ                   3         2      0.667