##Merge PCR and culture_2022

library(readxl)
pcr.22 <- read_xlsx("D:\\2.RESEARCH\\0. Projects\\Sepsis_@Quick\\Data\\PCR\\PCR_2022.xlsx")
cul.22 <- read_xlsx("D:\\2.RESEARCH\\0. Projects\\Sepsis_@Quick\\Data\\culture\\Culture_2022.xlsx")

#Conver Name to lowercase

library(dplyr) # for mutating
## 
## 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
pcr.22 <- pcr.22 %>%
       mutate(NAME = tolower(NAME))

cul.22 <- cul.22 %>%
      mutate(FULL_NAME = tolower(FULL_NAME))

sq.22  <- merge(pcr.22, cul.22, by.x = "NAME", by.y = "FULL_NAME")

#Export dataframe to excel

#install.packages('writexl')

library(writexl)
write_xlsx(sq.22, "D:/2.RESEARCH/0. Projects/Sepsis_@Quick/sq.22.xlsx")

##Merge PCR and culture_2023

library(readxl)
pcr.23 <- read_xlsx("D:\\2.RESEARCH\\0. Projects\\Sepsis_@Quick\\Data\\PCR\\PCR_2023.xlsx")
cul.23 <- read_xlsx("D:\\2.RESEARCH\\0. Projects\\Sepsis_@Quick\\Data\\culture\\Culture_2023.xlsx")
library(dplyr)
library(tidyr)
library(magrittr)
## 
## Attaching package: 'magrittr'
## The following object is masked from 'package:tidyr':
## 
##     extract
cul.23 %>% count(SPEC_TYPE)
## # A tibble: 46 × 2
##    SPEC_TYPE     n
##    <chr>     <int>
##  1 NA         7508
##  2 ab           41
##  3 ad           17
##  4 ak           15
##  5 as          180
##  6 ba           45
##  7 bi           55
##  8 bl          721
##  9 bo            1
## 10 bx            4
## # ℹ 36 more rows
library(dplyr) # for mutating
pcr.23 <- pcr.23 %>%
       mutate(NAME = tolower(NAME))

cul.23 <- cul.23 %>%
      mutate(FULL_NAME = tolower(FULL_NAME))

sq.23  <- merge(pcr.23, cul.23, by.x = "NAME", by.y = "FULL_NAME")

#Export dataframe to excel

#install.packages('writexl')

library(writexl)
write_xlsx(sq.23, "D:/2.RESEARCH/0. Projects/Sepsis_@Quick/sq.23.xlsx")

##Merge PCR and culture_2024_Culture data until 9.2024

library(readxl)
pcr.24 <- read_xlsx("D:\\2.RESEARCH\\0. Projects\\Sepsis_@Quick\\Data\\PCR\\PCR_2024.xlsx")
cul.24 <- read_xlsx("D:\\2.RESEARCH\\0. Projects\\Sepsis_@Quick\\Data\\culture\\Culture_2024.xlsx")
library(dplyr) # for mutating
pcr.24 <- pcr.24 %>%
       mutate(NAME = tolower(NAME))

cul.24 <- cul.24 %>%
      mutate(FULL_NAME = tolower(FULL_NAME))

sq.24  <- merge(pcr.24, cul.24, by.x = "NAME", by.y = "FULL_NAME")

#Export dataframe to excel

#install.packages('writexl')

library(writexl)
write_xlsx(sq.24, "D:/2.RESEARCH/0. Projects/Sepsis_@Quick/sq.24.xlsx")