# Load required libraries
library(readxl)
## Warning: package 'readxl' was built under R version 4.4.3
library(dplyr)
## Warning: package 'dplyr' was built under R version 4.4.3
##
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
##
## filter, lag
## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union
library(purrr)
## Warning: package 'purrr' was built under R version 4.4.3
# List of Excel files
files <- c("C:/Users/xdf2/Desktop/2025Q1Dorgsheets.xlsx", "C:/Users/xdf2/Desktop/2025Q1Vorgsheets.xlsx", "C:/Users/xdf2/Desktop/2025Q1Torgsheets.xlsx", "C:/Users/xdf2/Desktop/2025Q1Korgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q4Dorgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q4Vorgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q4Torgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q4Korgsheets.xlsx")
# Function to read all sheets from a file as character columns
read_all_sheets <- function(file) {
sheet_names <- excel_sheets(file)
map_df(sheet_names, ~ read_excel(file, sheet = .x, col_types = "text") %>%
mutate(SourceFile = basename(file), Sheet = .x))
}
# Read and combine all sheets from all files
combined_data <- map_df(files, read_all_sheets)
## New names:
## • `%` -> `%...6`
## • `%` -> `%...8`
## • `%` -> `%...10`
## • `%` -> `%...12`
## • `%` -> `%...14`
## • `%` -> `%...16`
## • `%` -> `%...18`
## • `%` -> `%...20`
## • `%` -> `%...22`
## • `%` -> `%...24`
## • `%` -> `%...26`
## • `%` -> `%...28`
## • `%` -> `%...30`
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## • `%` -> `%...6`
## • `%` -> `%...8`
## • `%` -> `%...10`
## • `%` -> `%...12`
## • `%` -> `%...14`
## • `%` -> `%...16`
## • `%` -> `%...18`
## • `%` -> `%...20`
## • `%` -> `%...22`
## • `%` -> `%...24`
## • `%` -> `%...26`
## • `%` -> `%...28`
## • `%` -> `%...30`
# View structure and preview
str(combined_data)
## tibble [544 × 32] (S3: tbl_df/tbl/data.frame)
## $ Date : chr [1:544] "2025Q1" "2025Q1" "2025Q1" "2025Q1" ...
## $ Hospital : chr [1:544] "D" "D" "D" "D" ...
## $ Unit : chr [1:544] "Surgery" "Surgery" "Surgery" "Surgery" ...
## $ Organisms : chr [1:544] "GRAM NEGATIVE" "Klebsiella pneumoniae" "Інші Klebsiella\r\nOther Klebsiella" "E. coli" ...
## $ Усі рани
## All Wounds Total: chr [1:544] "37" "5" "0" "2" ...
## $ %...6 : chr [1:544] "14.015151515151516" "1.893939393939394" "0" "0.75757575757575757" ...
## $ WWTotal : chr [1:544] "31" "2" "0" "1" ...
## $ %...8 : chr [1:544] "12.449799196787151" "0.80321285140562237" "0" "0.40160642570281119" ...
## $ WWlt48 : chr [1:544] "31" "2" NA "1" ...
## $ %...10 : chr [1:544] "12.449799196787151" "0.80321285140562237" "0" "0.40160642570281119" ...
## $ WWgte48 : chr [1:544] "0" NA NA NA ...
## $ %...12 : chr [1:544] "0" NA NA NA ...
## $ NWTotal : chr [1:544] "6" "3" "0" "1" ...
## $ %...14 : chr [1:544] "40" "20" "0" "6.666666666666667" ...
## $ NWlt48 : chr [1:544] "3" "1" NA NA ...
## $ %...16 : chr [1:544] "33.333333333333329" "11.111111111111111" "0" "0" ...
## $ NWgte48 : chr [1:544] "3" "2" NA "1" ...
## $ %...18 : chr [1:544] "49.999999999999993" "33.333333333333329" "0" "16.666666666666664" ...
## $ BloodTotal : chr [1:544] "0" "0" "0" "0" ...
## $ %...20 : chr [1:544] "0" "0" "0" "0" ...
## $ Bloodlt48 : chr [1:544] "0" NA NA NA ...
## $ %...22 : chr [1:544] "0" "0" "0" "0" ...
## $ Bloodgte48 : chr [1:544] "0" NA NA NA ...
## $ %...24 : chr [1:544] "0" NA NA NA ...
## $ CSFTotal : chr [1:544] "0" "0" "0" "0" ...
## $ %...26 : chr [1:544] "0" NA NA NA ...
## $ CSFlt48 : chr [1:544] "0" NA NA NA ...
## $ %...28 : chr [1:544] "0" NA NA NA ...
## $ CSFgte48 : chr [1:544] "0" NA NA NA ...
## $ %...30 : chr [1:544] "0" NA NA NA ...
## $ SourceFile : chr [1:544] "2025Q1Dorgsheets.xlsx" "2025Q1Dorgsheets.xlsx" "2025Q1Dorgsheets.xlsx" "2025Q1Dorgsheets.xlsx" ...
## $ Sheet : chr [1:544] "Surgery" "Surgery" "Surgery" "Surgery" ...
head(combined_data)
## # A tibble: 6 × 32
## Date Hospital Unit Organisms Усі рани\r\nAll Woun…¹ `%...6` WWTotal `%...8`
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 2025Q1 D Surg… "GRAM NE… 37 14.015… 31 12.449…
## 2 2025Q1 D Surg… "Klebsie… 5 1.8939… 2 0.8032…
## 3 2025Q1 D Surg… "Інші Kl… 0 0 0 0
## 4 2025Q1 D Surg… "E. coli" 2 0.7575… 1 0.4016…
## 5 2025Q1 D Surg… "Інші En… 12 4.5454… 10 4.0160…
## 6 2025Q1 D Surg… "Acineto… 10 3.7878… 10 4.0160…
## # ℹ abbreviated name: ¹`Усі рани\r\nAll Wounds Total`
## # ℹ 24 more variables: WWlt48 <chr>, `%...10` <chr>, WWgte48 <chr>,
## # `%...12` <chr>, NWTotal <chr>, `%...14` <chr>, NWlt48 <chr>,
## # `%...16` <chr>, NWgte48 <chr>, `%...18` <chr>, BloodTotal <chr>,
## # `%...20` <chr>, Bloodlt48 <chr>, `%...22` <chr>, Bloodgte48 <chr>,
## # `%...24` <chr>, CSFTotal <chr>, `%...26` <chr>, CSFlt48 <chr>,
## # `%...28` <chr>, CSFgte48 <chr>, `%...30` <chr>, SourceFile <chr>, …
summary(combined_data)
## Date Hospital Unit Organisms
## Length:544 Length:544 Length:544 Length:544
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
## Усі рани\r\nAll Wounds Total %...6 WWTotal
## Length:544 Length:544 Length:544
## Class :character Class :character Class :character
## Mode :character Mode :character Mode :character
## %...8 WWlt48 %...10 WWgte48
## Length:544 Length:544 Length:544 Length:544
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
## %...12 NWTotal %...14 NWlt48
## Length:544 Length:544 Length:544 Length:544
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
## %...16 NWgte48 %...18 BloodTotal
## Length:544 Length:544 Length:544 Length:544
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
## %...20 Bloodlt48 %...22 Bloodgte48
## Length:544 Length:544 Length:544 Length:544
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
## %...24 CSFTotal %...26 CSFlt48
## Length:544 Length:544 Length:544 Length:544
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
## %...28 CSFgte48 %...30 SourceFile
## Length:544 Length:544 Length:544 Length:544
## Class :character Class :character Class :character Class :character
## Mode :character Mode :character Mode :character Mode :character
## Sheet
## Length:544
## Class :character
## Mode :character
#SUMMARIZE BY PATHOGEN WITHOUT TOTALS
# Organisms of interest
organisms_of_interest <- c(
"Інші Klebsiella Other Klebsiella", "E. coli",
"Інші Enterobacterales Other Enterobacterales", "Acinetobacter spp.",
"Pseudomonas aeruginosa", "Інші грам негативні Other gram negative",
"Staphylococcus aureus", "Enterococcus faecalis", "Enterococcus faecium",
"Інші грам позитивні Other gram positive")
# Specimen columns to sum
columns_to_sum <- c(
"Усі рани\r\nAll Wounds Total",
"WWTotal", "WWlt48", "WWgte48",
"NWTotal", "NWlt48", "NWgte48",
"BloodTotal", "Bloodlt48", "Bloodgte48",
"CSFTotal", "CSFlt48", "CSFgte48"
)
summary_by_org <- combined_data %>%
filter(Organisms %in% organisms_of_interest) %>%
group_by(Organism = Organisms, Date, Hospital, Unit) %>%
summarise(
total_count = rowSums(across(all_of(columns_to_sum), ~ as.numeric(.)), na.rm = TRUE) %>% sum(),
.groups = "drop"
)
library(scales)
## Warning: package 'scales' was built under R version 4.4.3
##
## Attaching package: 'scales'
## The following object is masked from 'package:purrr':
##
## discard
cb_palette_extended <- hue_pal()(length(unique(summary_by_org$Unit)))
# or use `viridis` for perceptual uniformity
library(viridis)
## Warning: package 'viridis' was built under R version 4.4.3
## Loading required package: viridisLite
## Warning: package 'viridisLite' was built under R version 4.4.3
##
## Attaching package: 'viridis'
## The following object is masked from 'package:scales':
##
## viridis_pal
cb_palette_extended <- viridis(length(unique(summary_by_org$Unit)), option = "C") # Originally had “D”, but try "C" or "E" for better contrast
library(ggplot2)
## Warning: package 'ggplot2' was built under R version 4.4.3
hospitals <- c("D", "V", "T", "K")
for (h in hospitals) {
plot <- summary_by_org %>%
filter(Hospital == h) %>%
ggplot(aes(x = Date, y = total_count, fill = Unit)) +
geom_col() +
facet_wrap(~ Organism, scales = "free_y") +
scale_fill_manual(values = cb_palette_extended) +
labs(
title = paste("Organism Counts at Hospital", h),
x = "Date", y = "Total Count", fill = "Unit"
) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
print(plot) # shows plot in RStudio
# Optional: save plot to file
ggsave(filename = paste0("organism_counts_hospital_", h, ".png"),
plot = plot, width = 12, height = 8, dpi = 300)
}
# List of Excel files
files <- c("C:/Users/xdf2/Desktop/2025Q1Dorgsheets.xlsx", "C:/Users/xdf2/Desktop/2025Q1Vorgsheets.xlsx", "C:/Users/xdf2/Desktop/2025Q1Torgsheets.xlsx", "C:/Users/xdf2/Desktop/2025Q1Korgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q4Dorgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q4Vorgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q4Torgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q4Korgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q3Dorgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q3Vorgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q3Torgsheets.xlsx", "C:/Users/xdf2/Desktop/2024Q3Korgsheets.xlsx")
# Function to read all sheets from a file as character columns
read_all_sheets <- function(file) {
sheet_names <- excel_sheets(file)
map_df(sheet_names, ~ read_excel(file, sheet = .x, col_types = "text") %>%
mutate(SourceFile = basename(file), Sheet = .x))
}
# Read and combine all sheets from all files
combined_data <- map_df(files, read_all_sheets)
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## New names:
## • `%` -> `%...6`
## • `%` -> `%...8`
## • `%` -> `%...10`
## • `%` -> `%...12`
## • `%` -> `%...14`
## • `%` -> `%...16`
## • `%` -> `%...18`
## • `%` -> `%...20`
## • `%` -> `%...22`
## • `%` -> `%...24`
## • `%` -> `%...26`
## • `%` -> `%...28`
## • `%` -> `%...30`
# View structure and preview
str(combined_data)
## tibble [816 × 32] (S3: tbl_df/tbl/data.frame)
## $ Date : chr [1:816] "2025Q1" "2025Q1" "2025Q1" "2025Q1" ...
## $ Hospital : chr [1:816] "D" "D" "D" "D" ...
## $ Unit : chr [1:816] "Surgery" "Surgery" "Surgery" "Surgery" ...
## $ Organisms : chr [1:816] "GRAM NEGATIVE" "Klebsiella pneumoniae" "Інші Klebsiella\r\nOther Klebsiella" "E. coli" ...
## $ Усі рани
## All Wounds Total: chr [1:816] "37" "5" "0" "2" ...
## $ %...6 : chr [1:816] "14.015151515151516" "1.893939393939394" "0" "0.75757575757575757" ...
## $ WWTotal : chr [1:816] "31" "2" "0" "1" ...
## $ %...8 : chr [1:816] "12.449799196787151" "0.80321285140562237" "0" "0.40160642570281119" ...
## $ WWlt48 : chr [1:816] "31" "2" NA "1" ...
## $ %...10 : chr [1:816] "12.449799196787151" "0.80321285140562237" "0" "0.40160642570281119" ...
## $ WWgte48 : chr [1:816] "0" NA NA NA ...
## $ %...12 : chr [1:816] "0" NA NA NA ...
## $ NWTotal : chr [1:816] "6" "3" "0" "1" ...
## $ %...14 : chr [1:816] "40" "20" "0" "6.666666666666667" ...
## $ NWlt48 : chr [1:816] "3" "1" NA NA ...
## $ %...16 : chr [1:816] "33.333333333333329" "11.111111111111111" "0" "0" ...
## $ NWgte48 : chr [1:816] "3" "2" NA "1" ...
## $ %...18 : chr [1:816] "49.999999999999993" "33.333333333333329" "0" "16.666666666666664" ...
## $ BloodTotal : chr [1:816] "0" "0" "0" "0" ...
## $ %...20 : chr [1:816] "0" "0" "0" "0" ...
## $ Bloodlt48 : chr [1:816] "0" NA NA NA ...
## $ %...22 : chr [1:816] "0" "0" "0" "0" ...
## $ Bloodgte48 : chr [1:816] "0" NA NA NA ...
## $ %...24 : chr [1:816] "0" NA NA NA ...
## $ CSFTotal : chr [1:816] "0" "0" "0" "0" ...
## $ %...26 : chr [1:816] "0" NA NA NA ...
## $ CSFlt48 : chr [1:816] "0" NA NA NA ...
## $ %...28 : chr [1:816] "0" NA NA NA ...
## $ CSFgte48 : chr [1:816] "0" NA NA NA ...
## $ %...30 : chr [1:816] "0" NA NA NA ...
## $ SourceFile : chr [1:816] "2025Q1Dorgsheets.xlsx" "2025Q1Dorgsheets.xlsx" "2025Q1Dorgsheets.xlsx" "2025Q1Dorgsheets.xlsx" ...
## $ Sheet : chr [1:816] "Surgery" "Surgery" "Surgery" "Surgery" ...
head(combined_data)
## # A tibble: 6 × 32
## Date Hospital Unit Organisms Усі рани\r\nAll Woun…¹ `%...6` WWTotal `%...8`
## <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
## 1 2025Q1 D Surg… "GRAM NE… 37 14.015… 31 12.449…
## 2 2025Q1 D Surg… "Klebsie… 5 1.8939… 2 0.8032…
## 3 2025Q1 D Surg… "Інші Kl… 0 0 0 0
## 4 2025Q1 D Surg… "E. coli" 2 0.7575… 1 0.4016…
## 5 2025Q1 D Surg… "Інші En… 12 4.5454… 10 4.0160…
## 6 2025Q1 D Surg… "Acineto… 10 3.7878… 10 4.0160…
## # ℹ abbreviated name: ¹`Усі рани\r\nAll Wounds Total`
## # ℹ 24 more variables: WWlt48 <chr>, `%...10` <chr>, WWgte48 <chr>,
## # `%...12` <chr>, NWTotal <chr>, `%...14` <chr>, NWlt48 <chr>,
## # `%...16` <chr>, NWgte48 <chr>, `%...18` <chr>, BloodTotal <chr>,
## # `%...20` <chr>, Bloodlt48 <chr>, `%...22` <chr>, Bloodgte48 <chr>,
## # `%...24` <chr>, CSFTotal <chr>, `%...26` <chr>, CSFlt48 <chr>,
## # `%...28` <chr>, CSFgte48 <chr>, `%...30` <chr>, SourceFile <chr>, …
#SUMMARIZE BY PATHOGEN WITHOUT TOTALS
# Organisms of interest
organisms_of_interest <- c(
"Інші Klebsiella Other Klebsiella", "E. coli",
"Інші Enterobacterales Other Enterobacterales", "Acinetobacter spp.",
"Pseudomonas aeruginosa", "Інші грам негативні Other gram negative",
"Staphylococcus aureus", "Enterococcus faecalis", "Enterococcus faecium",
"Інші грам позитивні Other gram positive")
# Specimen columns to sum
columns_to_sum <- c(
"Усі рани\r\nAll Wounds Total",
"WWTotal", "WWlt48", "WWgte48",
"NWTotal", "NWlt48", "NWgte48",
"BloodTotal", "Bloodlt48", "Bloodgte48",
"CSFTotal", "CSFlt48", "CSFgte48"
)
summary_by_org <- combined_data %>%
filter(Organisms %in% organisms_of_interest) %>%
group_by(Organism = Organisms, Date, Hospital, Unit) %>%
summarise(
total_count = rowSums(across(all_of(columns_to_sum), ~ as.numeric(.)), na.rm = TRUE) %>% sum(),
.groups = "drop"
)
summarize(combined_data)
## # A tibble: 1 × 0
library(scales)
cb_palette_extended <- hue_pal()(length(unique(summary_by_org$Unit)))
# or use `viridis` for perceptual uniformity
library(viridis)
cb_palette_extended <- viridis(length(unique(summary_by_org$Unit)), option = "C") # Originally had “D”, but try "C" or "E" for better contrast
library(ggplot2)
hospitals <- c("D", "V", "T", "K")
for (h in hospitals) {
plot <- summary_by_org %>%
filter(Hospital == h) %>%
ggplot(aes(x = Date, y = total_count, fill = Unit)) +
geom_col() +
facet_wrap(~ Organism, scales = "free_y") +
scale_fill_manual(values = cb_palette_extended) +
labs(
title = paste("Organism Counts at Hospital", h),
x = "Date", y = "Total Count", fill = "Unit"
) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
print(plot) # shows plot in RStudio
# Optional: save plot to file
ggsave(filename = paste0("organism_counts_hospital_", h, ".png"),
plot = plot, width = 12, height = 8, dpi = 300)
}
#SUMMARIZE BY PATHOGEN WITHOUT TOTALS
# Organisms of interest
organisms_of_interest <- c( "Klebsiella pneumoniae", "Інші Klebsiella Other Klebsiella", "E. coli",
"Інші Enterobacterales Other Enterobacterales", "Acinetobacter spp.",
"Pseudomonas aeruginosa", "Інші грам негативні Other gram negative",
"Staphylococcus aureus", "Enterococcus faecalis", "Enterococcus faecium",
"Інші грам позитивні Other gram positive")
# Specimen columns to sum
columns_to_sum <- c(
"Усі рани\r\nAll Wounds Total",
"WWTotal", "WWlt48", "WWgte48",
"NWTotal", "NWlt48", "NWgte48",
"BloodTotal", "Bloodlt48", "Bloodgte48",
"CSFTotal", "CSFlt48", "CSFgte48"
)
summary_by_org <- combined_data %>%
filter(Organisms %in% organisms_of_interest) %>%
group_by(Organism = Organisms, Date, Hospital, Unit) %>%
summarise(
total_count = rowSums(across(all_of(columns_to_sum), ~ as.numeric(.)), na.rm = TRUE) %>% sum(),
.groups = "drop"
)
summarize(combined_data)
## # A tibble: 1 × 0
library(scales)
cb_palette_extended <- hue_pal()(length(unique(summary_by_org$Unit)))
# or use `viridis` for perceptual uniformity
library(viridis)
cb_palette_extended <- viridis(length(unique(summary_by_org$Unit)), option = "C") # Originally had “D”, but try "C" or "E" for better contrast
library(ggplot2)
hospitals <- c("D", "V", "T", "K")
for (h in hospitals) {
plot <- summary_by_org %>%
filter(Hospital == h) %>%
ggplot(aes(x = Date, y = total_count, fill = Unit)) +
geom_col() +
facet_wrap(~ Organism, scales = "free_y") +
scale_fill_manual(values = cb_palette_extended) +
labs(
title = paste("Organism Counts at Hospital", h),
x = "Date", y = "Total Count", fill = "Unit"
) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
print(plot) # shows plot in RStudio
# Optional: save plot to file
ggsave(filename = paste0("organism_counts_hospital_", h, ".png"),
plot = plot, width = 12, height = 8, dpi = 300)
}
summarize(summary_by_org)
## # A tibble: 1 × 0