HKC Database

This file processes raw data from the HKC database into dataframes for export as csv files.

#Load data frames from MYSQL This puts the data from patients,consultations,level_2_treatments,level_3_treatmetns and sites into data frams for transformation and analysis.

Column headings for the data frames.

Site
Fields
site
code
organisation
province
school_registration_date
Children
Fields
patient_id
school_id
date_birth
gender
student_registration_date
Screening
Fields
patient_id
date
infection
posterior
anterior
Level 2
Fields
id
patient_id
quadrant
tooth_number
surface
treatment
treatment_date
Level 3
Fields
patient_id
date
infection
posterior
anterior 
## [1] TRUE

#Load Data The raw data from the HKC database are loaded and will now be cleaned, transformed, visualised and analysed.

#Clean the data for df_level_1 This will clean level_1 or records where infection, post, anterior indicators are all blank. Will also filter for age = “NA” or gender = “NA”

Add province and school name and organisation to df_patients

This is for the 1st table in the Tida - methods section

columns for df_patients_sites
x
patient_id
school_id
date_birth
gender
student_registration_date
site
organisation
province
school_registration_date

#Add date for first contact This is the date of the first consultation for each child

Table_1 STatistics
Mean_Age SD_Age Count Gender_Pct_F Gender_Pct_M
9.003853 2.997412 10899 49.99771 NA
9.003853 2.997412 10900 NA 50.00229

Stats Age

summary statistics and validity checks on age.

Table 1_stats by schools
school_id n_patients mean_age sd_age min_age max_age
ACPS 470 7.82 2.11 3 14
AGS 605 8.44 2.50 3 16
AK 246 10.56 2.46 6 17
AMT 238 8.87 2.64 4 17
ANG 444 7.93 2.34 3 15
ANGPS1 936 9.10 2.36 5 21
APM1 316 6.73 3.69 0 16
APPS 413 8.87 2.28 3 15
APS1 295 8.02 2.27 4 18
BKM1 195 7.59 2.37 3 13
BS1 159 13.64 5.67 2 25
CCCKC 647 8.40 2.68 3 21
CCCKS 565 8.14 2.80 0 20
CCCPC 466 8.30 2.27 4 17
CCCPP 482 7.92 2.82 0 16
CCFSMC 2402 10.66 3.30 2 20
CPS 1559 8.45 2.48 3 16
GG1 100 8.75 2.93 0 17
HMPS1 95 7.71 1.23 4 13
HNBTKT1 1211 9.09 2.00 5 16
HNTTP2-1 954 8.97 2.29 0 19
HPS1 853 8.37 2.44 4 17
KAD 646 6.99 2.01 3 15
KCS1 58 8.88 2.57 4 13
KS1 8 9.50 3.59 4 14
KSPS1 633 8.43 2.10 4 16
KT-BB 161 12.37 3.99 3 22
KT-BM 97 8.79 3.38 4 25
KT-CP 112 12.83 3.43 5 22
KT-KC 258 11.10 3.50 3 20
KT-PP 391 11.28 3.82 0 23
KT-SR 230 11.68 3.71 4 24
MPS1 301 8.17 1.96 4 14
MT1 939 9.29 3.18 3 22
NKT1 252 8.92 2.45 3 13
NR1 388 8.72 2.27 5 14
OCTO1 51 10.80 4.06 3 21
OP1 412 9.40 2.53 0 16
PCC! 91 11.31 3.72 4 20
RAB1 199 11.00 4.17 3 22
SMC 2264 8.73 2.87 2 23
SOL1 26 10.27 2.75 5 19
SPS1 511 7.70 2.31 4 15
TTPS 120 8.57 2.76 3 19
Table 1_stats_all
n_patients mean_age sd_age min_age max_age
21799 9 3 0 25

Working with screenings pre COVID

Want to make a dataframe from df_level which has the latest screening prior to 2020-03-31 and after 2019-06-01

Columns Table 2
x
patient_id
date_pre
gender
date_birth
age_date_pre
infection
posterior
anterior
site
province
Table_2 STatistics
Mean_Age SD_Age Count Gender_Pct_F Gender_Pct_M
9.003853 2.997412 10899 49.99771 NA
9.003853 2.997412 10900 NA 50.00229

Get postCOVID dates

Looking for the 1st treatment after 2022-09-01 when schools re-opened

Table_3
x
patient_id
date_post
gender
date_birth
age_date_post
infection
posterior
anterior
site
province

##Common records sample 1 and sample 2

Table_5
x
patient_id
site
province
gender
date_birth
date_pre
age_date_pre
infection_pre
posterior_pre
anterior_pre
date_post
age_date_post
infection_post
posterior_post
anterior_post

#Charts and Tables - Table 1 These stats are from the table_1 set. AA children in the database with whom there has has been at least one screening.

##  [1] "patient_id"                "school_id"                
##  [3] "date_birth"                "gender"                   
##  [5] "student_registration_date" "site"                     
##  [7] "organisation"              "province"                 
##  [9] "school_registration_date"  "first_contact_date"       
## [11] "age_first_date"
## Proportion of males to females
## 
##        F        M 
## 49.99771 50.00229
## Mean =  9 std dev =  3
##    Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
##   0.000   7.000   9.000   9.004  11.000  25.000
##                                        site count
## 1                                  All Kids   246
## 2                   Ang Chum Primary School   470
## 3               Ang Punereay Primary School   413
## 4                 Angmuntrey Primary School   238
## 5                  Angrun Primary School TK   444
## 6                Angserey primary school KS   936
## 7                   Angsiema Primary School   605
## 8                   Angsleng Primary School   295
## 9                       Ark Piswat Meanchey   316
## 10                                   Baknim   195
## 11                             Borei School   159
## 12                         CCC Kampong Speu   565
## 13          CCC PC Prey Chum Primary School   466
## 14                            CCCPP Champov   482
## 15                  Cambodia Childrens Fund  2402
## 16                 Chaktomuk primary school  1559
## 17                              Green Gecko   100
## 18                    Haggov Primary School   853
## 19 Hun Neang Boeng Travel Khan Thong School  1211
## 20         Hun Neang Tuol Tom Pong 2 School   954
## 21           Hun Sen Monorom Primary School    95
## 22     Kampuchea Associaton for Development   646
## 23                   Kirirom Coc0nut School    58
## 24                              Kraing Srey     8
## 25                Krang Chek Primary School   647
## 26                               Krang Snai   633
## 27               Krousar Thmey - Battambang   161
## 28                  Krousar Thmey - Champov   112
## 29             Krousar Thmey - Kampong Cham   258
## 30                Krousar Thmey - Siem Reap   230
## 31                 Krousar Thmey Phnom Penh   391
## 32                     Krousar Thmey Poipet    97
## 33                              MLop Tapang   939
## 34               Monirangsey primary school   301
## 35                                Near Reay   388
## 36                         Nikroat Thereaum   252
## 37                              Ong Punreay   412
## 38                              Orphan Care    51
## 39                               PIO School  2264
## 40                        Ptea Clara Center    91
## 41                            Rabbit School   199
## 42                Santepheap Primary School   511
## 43                           School of Life    26
## 44                    Ta Ten Primary School   120
Schools
site province
All Kids Kampong Saom
Ang Chum Primary School Kampot
Ang Punereay Primary School Takeo
Angmuntrey Primary School Takeo
Angrun Primary School TK Takeo
Angserey primary school KS Kampong Speu
Angsiema Primary School Takeo
Angsleng Primary School Takeo
Ark Piswat Meanchey Phnom Penh
Baknim Kampot
Borei School Phnom Penh
CCC Kampong Speu Kampong Speu
CCC PC Prey Chum Primary School Kampong Speu
CCCPP Champov Phnom Penh
Cambodia Childrens Fund Phnom Penh
Chaktomuk primary school Phnom Penh
Green Gecko Siem Reap
Haggov Primary School Phnom Penh
Hun Neang Boeng Travel Khan Thong School Phnom Penh
Hun Neang Tuol Tom Pong 2 School Phnom Penh
Hun Sen Monorom Primary School Mondulkiri
Kampuchea Associaton for Development Kampong Speu
Kirirom Coc0nut School Kampong Spue
Kraing Srey Kampot
Krang Chek Primary School Kampong Speu
Krang Snai Kampot
Krousar Thmey - Battambang Battambang
Krousar Thmey - Champov Phnom Penh
Krousar Thmey - Kampong Cham Kampong Cham
Krousar Thmey - Siem Reap Siem Reap
Krousar Thmey Phnom Penh Phnom Penh
Krousar Thmey Poipet Banteay Meanchey
MLop Tapang Sihanoukville
Monirangsey primary school Kampot
Near Reay Kampot
Nikroat Thereaum Takeo
Ong Punreay Takeo
Orphan Care Kampong Spue
PIO School Phnom Penh
Ptea Clara Center Kandal
Rabbit School Phnom Penh
Santepheap Primary School Kampong Speu
School of Life Kandal
Ta Ten Primary School Kampot 
##  [1] "patient_id"   "date_pre"     "gender"       "date_birth"   "age_date_pre"
##  [6] "infection"    "posterior"    "anterior"     "site"         "province"
## Proportion of males to females
## 
##        F        M 
## 49.99771 50.00229
## Warning in mean.default(df_table_1$age_date_pre): argument is not numeric or
## logical: returning NA
## Mean =  NA std dev =  NA
## Length  Class   Mode 
##      0   NULL   NULL
##                                        site count
## 1                   Angsleng Primary School   122
## 2                       Ark Piswat Meanchey    44
## 3                          CCC Kampong Speu   323
## 4           CCC PC Prey Chum Primary School   309
## 5                             CCCPP Champov   135
## 6                   Cambodia Childrens Fund  1280
## 7                  Chaktomuk primary school    16
## 8                               Green Gecko   100
## 9                     Haggov Primary School   187
## 10 Hun Neang Boeng Travel Khan Thong School    52
## 11         Hun Neang Tuol Tom Pong 2 School   212
## 12                Krang Chek Primary School   285
## 13               Krousar Thmey - Battambang    39
## 14             Krousar Thmey - Kampong Cham   126
## 15                Krousar Thmey - Siem Reap    75
## 16                 Krousar Thmey Phnom Penh   160
## 17               Monirangsey primary school   108
## 18                              Ong Punreay   412
## 19                               PIO School  1044
Baseline schools
site province
Angsleng Primary School Takeo
Ark Piswat Meanchey Phnom Penh
CCC Kampong Speu Kampong Speu
CCC PC Prey Chum Primary School Kampong Speu
CCCPP Champov Phnom Penh
Cambodia Childrens Fund Phnom Penh
Chaktomuk primary school Phnom Penh
Green Gecko Siem Reap
Haggov Primary School Phnom Penh
Hun Neang Boeng Travel Khan Thong School Phnom Penh
Hun Neang Tuol Tom Pong 2 School Phnom Penh
Krang Chek Primary School Kampong Speu
Krousar Thmey - Battambang Battambang
Krousar Thmey - Kampong Cham Kampong Cham
Krousar Thmey - Siem Reap Siem Reap
Krousar Thmey Phnom Penh Phnom Penh
Monirangsey primary school Kampot
Ong Punreay Takeo
PIO School Phnom Penh
Table_2 STatistics
Mean_Age SD_Age Count Gender_Pct_F Gender_Pct_M
10.11772 3.052521 2643 52.55518 NA
10.11772 3.052521 2386 NA 47.44482

#Follow up group treat post COVID From among those seen in the baseline window, Schools that had a contact between 2022 and June 2023 extended to include Sep - Dec 2022.

Baseline schools
x
patient_id
date_post
gender
date_birth
age_date_post
infection
posterior
anterior
site
province
Table_2 STatistics
site province
Angsleng Primary School Takeo
Ark Piswat Meanchey Phnom Penh
CCC Kampong Speu Kampong Speu
CCC PC Prey Chum Primary School Kampong Speu
CCCPP Champov Phnom Penh
Cambodia Childrens Fund Phnom Penh
Chaktomuk primary school Phnom Penh
Green Gecko Siem Reap
Haggov Primary School Phnom Penh
Hun Neang Boeng Travel Khan Thong School Phnom Penh
Hun Neang Tuol Tom Pong 2 School Phnom Penh
Krang Chek Primary School Kampong Speu
Krousar Thmey - Battambang Battambang
Krousar Thmey - Kampong Cham Kampong Cham
Krousar Thmey - Siem Reap Siem Reap
Krousar Thmey Phnom Penh Phnom Penh
Monirangsey primary school Kampot
Ong Punreay Takeo
PIO School Phnom Penh
Mean_Age SD_Age Count Gender_Pct_F Gender_Pct_M
8.918537 3.157444 1960 47.80488 NA
8.918537 3.157444 2140 NA 52.19512
eligible schools
x
patient_id
site
province
gender
date_birth
date_pre
age_date_pre
infection_pre
posterior_pre
anterior_pre
date_post
age_date_post
infection_post
posterior_post
anterior_post
Table_5 STatistics_pre
site province
PIO School Phnom Penh
Monirangsey primary school Kampot
Krousar Thmey - Battambang Battambang
Krousar Thmey - Kampong Cham Kampong Cham
Krousar Thmey Phnom Penh Phnom Penh
Haggov Primary School Phnom Penh
Chaktomuk primary school Phnom Penh
Table_5 STatistics_post
Mean_Age SD_Age Count Gender_Pct_F Gender_Pct_M
8.966746 2.874988 193 45.84323 NA
8.966746 2.874988 228 NA 54.15677
Mean_Age SD_Age Count Gender_Pct_F Gender_Pct_M
8.966746 2.874988 193 45.84323 NA
8.966746 2.874988 228 NA 54.15677

##Statiscal Analysis Infection Test for differences between pre and post COVID infection.

Table 5
x
patient_id
site
province
gender
date_birth
date_pre
age_date_pre
infection_pre
posterior_pre
anterior_pre
date_post
age_date_post
infection_post
posterior_post
anterior_post 
## 
##  Pearson's Chi-squared test with Yates' continuity correction
## 
## data:  contingency_table
## X-squared = 6.8084, df = 1, p-value = 0.009073

##Statistical posterior Testing for a significant increase in posterior cavities

## 
##  Pearson's Chi-squared test with Yates' continuity correction
## 
## data:  contingency_table
## X-squared = 2.9027, df = 1, p-value = 0.08843

##Statistical posterior Testing for a significant increase in antterior cavities

## 
##  Pearson's Chi-squared test with Yates' continuity correction
## 
## data:  contingency_table
## X-squared = 21.53, df = 1, p-value = 3.484e-06