Hallo,
I am working with some data where I have summarized the number of livestock to some insecurity incidences and which were recovered during an operation. I have gone ahead and calculated a new variable (% percent recovery) based on the number lost & recovered variable as shown in the attached table output.
Now my challenge is I would like to compute the overall recovery percent in the grand summary row where I have summation of the lost & recovered livestock.
Could someone help on how to go about this?
my code is as follows:
aic %>%
select(ends_with(c("_theft","_recovered")),-insec_sos_cattle_theft,-experince_cattle_theft) %>%
pivot_longer(
cols = everything(),
names_to = "livestock",
values_to = "numb"
) %>%
filter(numb >= 0) %>%
mutate(
livestock_type = case_when(
str_detect(livestock,"cow") == TRUE ~ "Cow",
str_detect(livestock,"goat") == TRUE ~ "Goat",
str_detect(livestock,"sheep") == TRUE ~ "Sheep",
str_detect(livestock,"camel") == TRUE ~ "Camel",
),
category = case_when(str_detect(livestock,"theft") == TRUE ~ "Theft",
TRUE ~ "Recovered")
) %>%
group_by(livestock_type,category) %>%
summarise(
sum = sum(numb)
) %>%
ungroup() %>%
pivot_wider(
names_from = category,
values_from = sum,
values_fill = 0
) %>%
relocate(Theft,.before = Recovered) %>%
rowwise() %>%
mutate(
% Recovery = percent((Theft - Recovered)/Theft)
) %>%
gt(rowname_col = "livestock_type") %>%
tab_stubhead(label = "Livestock Type") %>%
grand_summary_rows(
columns = c("Theft","Recovered"),
fns = list(
Total = ~sum(.)
),
side = "top",
) %>%
fmt_number(
columns = c("Theft","Recovered"),
use_seps = T, decimals = 0
) %>%
tab_header(title = "No of Livestock Lost & Recovered",
subtitle = "One Year Recal Period")