PERMIT SIR updated add conditional variables

memory.size(70000)
#READ CONDITION FILES- SUFFIX ALL NAMES WITH A 1 THEN YOU CAN IMPORT THEM ALL TOGETHER

library(zoo)
library(plyr)
library(tidyverse)
library(zoo)
library(data.table)
library(survival)
library(lubridate)

#The processed sir.data file will be used from this point onwards, not the long format.
####################################################

#######################################################
#######################################################
load("crea.rephf2tests.Rdata")
load("sirdatahfonly.rda")
sir.data<-sir.data[sir.data$PatientID %in% crea.rep$PatientID,]
sir.data$CodeValue<-as.numeric(as.character(sir.data$CodeValue))
sir.data<-sir.data[!is.na(sir.data$CodeValue),]
head(sir.data[sir.data$EntryDate<19800101,]) #Historical xray data  etc has been retrospectively added 
sir.data$EntryDate<-ifelse(sir.data$EntryDate>20170801|sir.data$EntryDate<19000101,NA,sir.data$EntryDate)

allfiles = list.files(pattern="*1.csv")
for (i in 1:length(allfiles)) assign(allfiles[i], read.csv(allfiles[i]))
#######################################################

#ADD IN VARIOUS CONDITIONS AND FLAGS FROM MAIN EHR FILES
#Narrow lookup table to the full list of codes of interest to speed up processing- use for CPRD
#merged <- Reduce(function(x, y) merge(x, y,all.x=TRUE,all.y=TRUE), 
#                 list(AF1.csv,BMI1.csv,BNP1.csv,BUN1.csv,cessation1.csv,DBP1.csv,Diabetes1.csv,Dialysis1.csv,Haemoglobin1.csv, HeartRate1.csv,IHD1.csv,MCV1.csv,Nephrectomy1.csv,NTPROBNP1.csv,PVD1.csv,RM1.csv,SBP1.csv,SerumAlbumin1.csv,SerumPotassium1.csv,SerumSodium1.csv,smoking1.csv,transplant1.csv,UACR1.csv,UAlbumin1.csv,UricAcid1.csv))
#sir.data<-sir.data[which(sir.data$ReadCode %in% merged$ReadCode),]


#######################################################

#CODE CONDITIONS REQUIRING VALUES TO BE ADDED, NEAREST IF WITHIN X TIME PERIOD
#NOTE THAT COERCION OF TIBBLES TO DATAFRAME FORMAT MAY NOT BE MAINTAINED IF YOU SAVE R OBJECTS
#FORMATTING CAN BE CLEARED BY SAVING MIDWAY POINTS AS CSV FILES
#################################################################

#NEAREST PRIOR MEAN DAILY SERUM SODIUM #DATE FLAG 30 DAYS
#OUT OF RANGE REMOVAL TBA
unique(sir.data$CodeUnits[sir.data$ReadCode %in% SerumSodium1.csv$ReadCode])
sir.data$temp<-ifelse(sir.data$ReadCode %in% SerumSodium1.csv$ReadCode & !sir.data$CodeUnits=="%" & sir.data$CodeValue>=20 & sir.data$CodeValue<=3000, paste(sir.data$CodeValue),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% SerumSodium1.csv$ReadCode])
smalltab<-sir.data[!is.na(sir.data$temp),c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(SerumSodium=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$SerumSodium<-first[indx1, "SerumSodium"]
crea.rep$SerSodDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >30, 1, NA)
table(crea.rep$SerSodDateFlag) #652
a<-crea.rep[!is.na(crea.rep$SerumSodium),]
length(a$PatientID) #284225
crea.rep$SerumSodium<-unlist(crea.rep$SerumSodium)
#######################################################

#CLOSEST DAILY BMI MEAN #1YR DATE FLAG
sir.data$temp<-as.numeric(ifelse(sir.data$ReadCode %in% BMI1.csv$ReadCode & sir.data$CodeValue>=10 & sir.data$CodeValue<=80, as.numeric(paste(sir.data$CodeValue)),NA))
sir.data$height<-as.numeric(ifelse(sir.data$ReadCode %in% Height1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA))
unique(sir.data$CodeUnits[sir.data$ReadCode %in% Height1.csv$ReadCode])
sir.data$height<-ifelse(!is.na(sir.data$height)&sir.data$CodeUnits=="cm",sir.data$height/100,sir.data$height)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% Weight1.csv$ReadCode])
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Weight1.csv$ReadCode & sir.data$CodeUnits=="kg/m2",sir.data$CodeValue,sir.data$temp)
#Apply max height across dataset

htab<-sir.data[!is.na(sir.data$height),c("PatientID","CodeValue","EntryDate")]
columns1=names(htab[c(1,3)])
dots<-lapply(columns1, as.symbol)
firstH <-htab %>% 
group_by_(.dots=dots) %>%
summarize(Height=max(CodeValue)) %>%
as.data.frame
sir.data<-merge(sir.data,firstH,all.x=TRUE)

sir.data$weight<-as.numeric(ifelse(sir.data$ReadCode %in% Weight1.csv$ReadCode & !sir.data$CodeUnits=="kg/m2",as.numeric(paste(sir.data$CodeValue)),NA))
sir.data$temp<-ifelse(is.na(sir.data$temp),(as.numeric(sir.data$weight)/(as.numeric(sir.data$height))^2),sir.data$temp)

BMItab<-sir.data[sir.data$temp>=10 & sir.data$temp<=80,c("PatientID","temp","EntryDate")]
columns1=names(BMItab[c(1,3)])
dots<-lapply(columns1, as.symbol)
firstA <-BMItab %>% 
group_by_(.dots=dots) %>%
summarize(BMI=mean(temp)) %>%
as.data.frame

indx1 <- neardate(crea.rep$PatientID, firstA$PatientID, crea.rep$EntryDate, firstA$EntryDate, 
                   best="prior")
crea.rep$BMI<-firstA[indx1, "BMI"]
crea.rep$BMIDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - firstA$EntryDate[indx1])) >365, 1, NA)
table(crea.rep$BMIDateFlag) #144139
a<-crea.rep[!is.na(crea.rep$BMI),]
length(a$PatientID) #278489
crea.rep$BMI<-unlist(crea.rep$BMI)
#######################################################
#MAX DAILY SERUM URIC ACID #1 YEAR
sir.data$temp<-ifelse(sir.data$ReadCode %in% UricAcid1.csv$ReadCode, as.numeric(paste(sir.data$CodeValue)),NA)
UA<-sir.data[!is.na(sir.data$temp) & !is.na(sir.data$PatientID),]
unique(UA$CodeUnits)
sir.data$temp<-ifelse((sir.data$CodeUnits=="umol/L"|sir.data$CodeUnits=="umol/l") & sir.data$ReadCode %in% UricAcid1.csv$ReadCode,sir.data$temp/1000,sir.data$temp)
sir.data$temp<-ifelse((sir.data$CodeUnits=="iu/L"|sir.data$CodeUnits=="IU/L"|sir.data$CodeUnits=="None") & sir.data$ReadCode %in% UricAcid1.csv$ReadCode,NA,sir.data$temp)
sir.data$temp<-ifelse(sir.data$CodeValue>=0.001 & sir.data$CodeValue<=1, sir.data$temp,NA)

smalltab<-sir.data[!is.na(sir.data$temp),c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
firstU <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(UricAcid=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, firstU$PatientID, crea.rep$EntryDate, firstU$EntryDate, 
                   best="prior")
crea.rep$UricAcid<-firstU[indx1, "UricAcid"]
crea.rep$UricAcidDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - firstU$EntryDate[indx1])) >365, 1, NA)
table(crea.rep$UricAcidDateFlag) #87055
a<-crea.rep[!is.na(crea.rep$UricAcid),]
length(a$PatientID) #101034
crea.rep$UricAcid<-unlist(crea.rep$UricAcid)

##################################################################
#MEAN DAILY BLOOD UREA NITROGEN #30 DAYS
sir.data$temp<-ifelse(sir.data$ReadCode %in% BUN1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
sir.data$temp<-ifelse(sir.data$ReadCode %in% BUN1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>=1,as.numeric(sir.data$CodeUnits),sir.data$temp)
sir.data$temp<-ifelse(sir.data$ReadCode %in% BUN1.csv$ReadCode & (sir.data$CodeUnits=="%"|sir.data$CodeUnits=="h"),NA,sir.data$temp)

smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=1&sir.data$temp<=50,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(BUN=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$BUN<-first[indx1, "BUN"]
crea.rep$BUNDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >30, 1, NA)
table(crea.rep$BUNDateFlag) #7458
a<-crea.rep[!is.na(crea.rep$BUN),]
length(a$PatientID) #7612
crea.rep$BUN<-unlist(crea.rep$BUN)
##############################################################
#MEAN DAILY SERUM POTASSIUM #1 MONTH
sir.data$temp<-ifelse(sir.data$ReadCode %in% SerumPotassium1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% SerumPotassium1.csv$ReadCode])
sir.data$temp<-ifelse(is.na(sir.data$ReadCode)&sir.data$ReadCode %in% SerumPotassium1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>0,as.numeric(sir.data$CodeUnits),sir.data$temp)
sir.data$temp<-ifelse(sir.data$ReadCode %in% SerumPotassium1.csv$ReadCode & (sir.data$CodeUnits=="mL/min"),NA,sir.data$temp)

smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=2&sir.data$temp<=10,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(SerumPotassium=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$SerPotassium<-first[indx1, "SerumPotassium"]
crea.rep$SerPotDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >30, 1, NA)
table(crea.rep$SerPotDateFlag) #4874
a<-crea.rep[!is.na(crea.rep$SerPotassium),]
length(a$PatientID) #284202
crea.rep$SerPotassium<-unlist(crea.rep$SerPotassium)
##############################################################
#MEAN DAILY HEART RATE #30 DAYS
sir.data$temp<-ifelse(sir.data$ReadCode %in% HeartRate1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% HeartRate1.csv$ReadCode])
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=20&sir.data$temp<=200,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(HeartRate=mean(CodeValue)) %>%
as.data.frame 
indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$HeartRate<-first[indx1, "HeartRate"]
crea.rep$HeartRateDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >30, 1, NA)
table(crea.rep$HeartRateDateFlag) #30167
a<-crea.rep[!is.na(crea.rep$HeartRate),]
length(a$PatientID) #35414
crea.rep$HeartRate<-unlist(crea.rep$HeartRate)

##############################################################
#MEAN BNP #1 YEAR
sir.data$temp<-ifelse(sir.data$ReadCode %in% BNP1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
#1ng/L=1pg/ml
unique(sir.data$CodeUnits[sir.data$ReadCode %in% BNP1.csv$ReadCode])
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=1&sir.data$temp<=1000,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(BNP=mean(as.numeric(CodeValue))) %>%
as.data.frame 
indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$BNP<-first[indx1, "BNP"]
crea.rep$BNPDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >365, 1, NA)
table(crea.rep$BNPDateFlag) #7
a<-crea.rep[!is.na(crea.rep$BNP),]
length(a$PatientID) #14
crea.rep$BNP<-unlist(crea.rep$BNP)

##############################################################
#MEAN NT-PRO BNP #1 YEAR
sir.data$temp<-ifelse(sir.data$ReadCode %in% NTPROBNP1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% NTPROBNP1.csv$ReadCode])
#1ng/L=1pg/ml
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=1&sir.data$temp<=6000,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(NTPROBNP=mean(as.numeric(CodeValue))) %>%
as.data.frame 
indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$NTPROBNP<-first[indx1, "NTPROBNP"]
crea.rep$NTPROBNPDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >365, 1, NA)
table(crea.rep$NTPROBNPDateFlag) #643
a<-crea.rep[!is.na(crea.rep$NTPROBNP),]
length(a$PatientID) #914
crea.rep$NTPROBNP<-unlist(crea.rep$NTPROBNP)


##############################################################
#MIN DAILY SYSTOLIC BP #30 DAYS
sir.data$SBP<-ifelse(sir.data$ReadCode %in% SBP1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
sir.data$temp<-sir.data$SBP
unique(sir.data$CodeUnits[sir.data$ReadCode %in% SBP1.csv$ReadCode])
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=30&sir.data$temp<=300,c("PatientID","SBP","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
slice(which.min(as.numeric(SBP))) %>%
ungroup()%>%
as.data.frame

indx2 <- neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$SBP<-first[indx2, "SBP"]
crea.rep$SBPDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx2])) >30, 1, NA)
table(crea.rep$SBPDateFlag) #159424
a<-crea.rep[!is.na(crea.rep$SBP),]
length(a$PatientID) #282541
crea.rep$SBP<-unlist(crea.rep$SBP)


##############################################################
#MIN DAILY DIASTOLIC BP #30 DAYS
sir.data$DBP<-ifelse(sir.data$ReadCode %in% DBP1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
sir.data$temp<-sir.data$DBP
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=20&sir.data$temp<=200,c("PatientID","DBP","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
slice(which.min(as.numeric(DBP))) %>%
ungroup()%>%
as.data.frame

indx2 <- neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")

crea.rep$DBP<-first[indx2, "DBP"]
crea.rep$DBPDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx2])) >30, 1, NA)
table(crea.rep$DBPDateFlag) #159537
a<-crea.rep[!is.na(crea.rep$DBP),]
length(a$PatientID) #282538
crea.rep$DBP<-unlist(crea.rep$DBP)
##############################################################
#MEAN DAILY SERUM ALBUMIN #`1 year
sir.data$temp<-ifelse(sir.data$ReadCode %in% SerumAlbumin1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% SerumAlbumin1.csv$ReadCode])
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=10&sir.data$temp<=60,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(SerumAlbumin=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$SerumAlbumin<-first[indx1, "SerumAlbumin"]
crea.rep$SerumAlbuminDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - firstU$EntryDate[indx1])) >365, 1, NA)
table(crea.rep$SerumAlbuminDateFlag) #9309
a<-crea.rep[!is.na(crea.rep$SerumAlbumin),]
length(a$PatientID) #284008
crea.rep$SerumAlbumin<-unlist(crea.rep$SerumAlbumin)
###################################################################
#MAX DAILY URINE ALBUMIN 1 YEAR
sir.data$temp<-ifelse(sir.data$ReadCode %in% UAlbumin1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% UAlbumin1.csv$ReadCode])
sir.data$temp<-ifelse(sir.data$ReadCode %in% UAlbumin1.csv$ReadCode&sir.data$CodeUnits=="g/L",sir.data$temp*1000,sir.data$temp)
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=0&sir.data$temp<=1000,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(UrineAlbumin=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$UrineAlbumin<-first[indx1, "UrineAlbumin"]
crea.rep$UrineAlbuminDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >365, 1, NA)
save(crea.rep,file="crea.repongoing.rda")
table(crea.rep$UrineAlbuminDateFlag) #117810
a<-crea.rep[!is.na(crea.rep$UrineAlbumin),]
length(a$PatientID) #152986
crea.rep$UrineAlbumin<-unlist(crea.rep$UrineAlbumin)
###################################################################
#MEAN DAILY URINE ALBUMIN CREATININE RATIO #1 YEAR
sir.data$temp<-ifelse(sir.data$ReadCode %in% UACR1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% UACR1.csv$ReadCode])
sir.data$temp<-ifelse(sir.data$ReadCode %in% UACR1.csv$ReadCode&sir.data$CodeUnits=="ratio"|sir.data$CodeUnits=="None"|sir.data$CodeUnits=="U/ml"|sir.data$CodeUnits=="GPL",NA,sir.data$temp)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% UACR1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>0,as.numeric(sir.data$CodeUnits),sir.data$temp)]

smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=0&sir.data$temp<=3000,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(UACratio=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$UACratio<-first[indx1, "UACratio"]
crea.rep$UACDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >365, 1, NA)
table(crea.rep$UACDateFlag) #122181
a<-crea.rep[!is.na(crea.rep$UACratio),]
length(a$PatientID) #159870
crea.rep$UACratio<-unlist(crea.rep$UACratio)
###################################################################
#MAX DAILY MEAN CORPUSCULAR VOLUME #120 DAYS
sir.data$temp<-ifelse(sir.data$ReadCode %in% MCV1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% MCV1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>0,as.numeric(sir.data$CodeUnits),sir.data$temp)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% MCV1.csv$ReadCode])
#1femtoliter=1 cubic micron
smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=50&sir.data$temp<=150,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(MCV=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
 crea.rep$MCV<-first[indx1, "MCV"]
crea.rep$MCVDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >120, 1, NA)

save(crea.rep,file="crea.repongoing.rda")
table(crea.rep$MCVDateFlag) #50532
a<-crea.rep[!is.na(crea.rep$MCV),]
length(a$PatientID) #283524
crea.rep$MCV<-unlist(crea.rep$MCV)
###################################################################
#MAX DAILY HAEMOGLOBIN #120 DAYS
sir.data$temp<-ifelse(sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode,as.numeric(paste(sir.data$CodeValue)),NA)
unique(sir.data$CodeUnits[sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode])
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode & as.numeric(sir.data$CodeUnits)>0,as.numeric(sir.data$CodeUnits),sir.data$temp)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode&sir.data$CodeUnits=="g/dl"|sir.data$CodeUnits=="g/dL",sir.data$temp*10,sir.data$temp)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode&sir.data$CodeUnits=="g/dl"|sir.data$CodeUnits=="g/dL",sir.data$temp*10,sir.data$temp)
sir.data$temp<-ifelse(is.na(sir.data$temp)&sir.data$ReadCode %in% Haemoglobin1.csv$ReadCode&sir.data$CodeUnits=="mmol/mol"|sir.data$CodeUnits=="None",NA,sir.data$temp)

smalltab<-sir.data[!is.na(sir.data$temp)&sir.data$temp>=30&sir.data$temp<=260,c("PatientID","CodeValue","EntryDate")]
columns=names(smalltab[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-smalltab %>% 
group_by_(.dots=dots) %>%
summarise(Haemoglobin=mean(CodeValue)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$EntryDate, first$EntryDate, 
                   best="prior")
crea.rep$Haemoglobin<-first[indx1, "Haemoglobin"]
crea.rep$HaemDateFlag<- ifelse(as.numeric(abs(crea.rep$EntryDate - first$EntryDate[indx1])) >120, 1, NA)
table(crea.rep$HaemDateFlag) #40204
a<-crea.rep[!is.na(crea.rep$Haemoglobin),]
length(a$PatientID) #293668
crea.rep$Haemoglobin<-unlist(crea.rep$Haemoglobin)
#########################################################
#VARIABLES WITH START AND ONGOING DATES- MATCH FIRST OCCURENCE
##########################################################
load("sirdatahfonly.rda")

head(sir.data[sir.data$ReadCode %in% Nephrectomy1.csv$ReadCode,]))# CHECK THERE ARE VALUES TO MATCH
#NEPHRECTOMY
sir.data$NephDate<-sir.data$EntryDate 
sir.data$NephDate<-ifelse(!sir.data$ReadCode  %in%  Nephrectomy1.csv$ReadCode,NA,sir.data$NephDate) 

smalltab<-sir.data[!is.na(sir.data$NephDate) & sir.data$PatientID %in% crea.rep$PatientID,c("PatientID","NephDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(NephDate) %>%
    slice(1L) %>%
as.data.frame
head(first)
first$NephDate<-as.Date(as.character(first$NephDate),format="%Y%m%d")
 
crea.rep<-merge(crea.rep,first,all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$NephDate<-as.Date(as.character(crea.rep$NephDate),format="%Y-%m-%d")
crea.rep$DaysSinceNephrectomy<-difftime(strptime(crea.rep$NephDate,format="%Y-%m-%d"),strptime(crea.rep$event.date,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceNephrectomy<-as.numeric(crea.rep$DaysSinceNephrectomy, units="days")
crea.rep$DaysSinceNephrectomy<-ifelse(crea.rep$DaysSinceNephrectomy<0,NA,crea.rep$DaysSinceNephrectomy)
#save(crea.rep, file = "crea.repongoing.rda")


#DIABETES
sir.data$DiabDate<-sir.data$EntryDate 
sir.data$DiabDate<-ifelse(!sir.data$ReadCode  %in%  Diabetes1.csv$ReadCode,NA,sir.data$DiabDate) 
smalltab<-sir.data[!is.na(sir.data$DiabDate),c("PatientID","DiabDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(DiabDate) %>%
    slice(which.min(as.numeric(DiabDate))) %>%
as.data.frame
    head(first)
    
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$DiabDate<-as.Date(as.character(crea.rep$DiabDate),format="%Y%m%d")
crea.rep$DaysSinceDiabetic<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$DiabDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceDiabetic<-ifelse(crea.rep$DaysSinceDiabetic<0,NA,crea.rep$DaysSinceDiabetic)
crea.rep$Diabetes<-ifelse(is.na(crea.rep$DaysSinceDiabetic),0,1)
save(crea.rep, file = "crea.repongoing.rda")

#ATRIAL FIBRILLATION
sir.data$AFDate<-sir.data$EntryDate 
sir.data$AFDate<-ifelse(!sir.data$ReadCode  %in%  AF1.csv$ReadCode,NA,sir.data$AFDate) 
smalltab<-sir.data[!is.na(sir.data$AFDate),c("PatientID","AFDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(AFDate) %>%
    slice(which.min(as.numeric(AFDate))) %>%
as.data.frame
    head(first)
    
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$AFDate<-as.Date(as.character(crea.rep$AFDate),format="%Y%m%d")
crea.rep$DaysSinceAF<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$AFDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceAF<-ifelse(crea.rep$DaysSinceAF<0,NA,crea.rep$DaysSinceAF)
crea.rep$AF<-ifelse(is.na(crea.rep$DaysSinceAF),0,1)
summary(crea.rep$DaysSinceAF,na.rm=TRUE)

#IHD
sir.data$IHDDate<-sir.data$EntryDate 
sir.data$IHDDate<-ifelse(!sir.data$ReadCode  %in%  IHD1.csv$ReadCode,NA,sir.data$IHDDate) 
smalltab<-sir.data[!is.na(sir.data$IHDDate),c("PatientID","IHDDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(IHDDate) %>%
    slice(which.min(as.numeric(IHDDate))) %>%
as.data.frame
    head(first)
   
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")FDate<-as.Date(as.character(crea.rep$IHDDate),format="%Y%m%d")
crea.rep$IHDDate<-as.Date(as.character(crea.rep$IHDDate),format="%Y%m%d")
crea.rep$DaysSinceIHD<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$IHDDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceIHD<-ifelse(crea.rep$DaysSinceIHD<0,NA,crea.rep$DaysSinceIHD)
crea.rep$IHD-ifelse(is.na(crea.rep$DaysSinceIHD),0,1)
summary(crea.rep$DaysSinceIHD,na.rm=TRUE)

#PERIPHERAL VASCULAR DISEASE
sir.data$PVDDate<-sir.data$EntryDate 
sir.data$PVDDate<-ifelse(!sir.data$ReadCode  %in%  PVD1.csv$ReadCode,NA,sir.data$PVDDate) 
smalltab<-sir.data[!is.na(sir.data$PVDDate),c("PatientID","PVDDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(PVDDate) %>%
    slice(which.min(as.numeric(PVDDate))) %>%
as.data.frame
    head(first)
    
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$PVDDate<-as.Date(as.character(crea.rep$PVDDate),format="%Y%m%d")
crea.rep$DaysSincePVD<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$PVDDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSincePVD<-ifelse(crea.rep$DaysSincePVD<0,NA,crea.rep$DaysSincePVD)
crea.rep$PVD<-ifelse(is.na(crea.rep$DaysSincePVD),0,1)
summary(crea.rep$DaysSincePVD,na.rm=TRUE)

save(crea.rep, file = "crea.repongoing.rda")

#RENAL MALIGNANCY
sir.data$RMALDate<-sir.data$EntryDate 
sir.data$RMALDate<-ifelse(!sir.data$ReadCode  %in%  RM1.csv$ReadCode,NA,sir.data$RMALDate) 
smalltab<-sir.data[!is.na(sir.data$RMALDate),c("PatientID","RMALDate")]
first<-smalltab %>%
    group_by(PatientID) %>%
    arrange(RMALDate) %>%
    slice(which.min(as.numeric(RMALDate))) %>%
as.data.frame
    head(first)
    
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$RMALDate<-as.Date(as.character(crea.rep$RMALDate),format="%Y%m%d")
crea.rep$DaysSinceRenMal<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$RMALDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceRenMal<-ifelse(crea.rep$DaysSinceRenMal<0,NA,crea.rep$DaysSinceRenMal)
crea.rep$RenalMalignancy<-ifelse(is.na(crea.rep$DaysSinceRenMal),0,1)
summary(crea.rep$DaysSinceRenMal,na.rm=TRUE)

save(crea.rep, file = "crea.repongoing.rda")

#RENAL TRANSPLANT
sir.data$RTDate<-sir.data$EntryDate 
sir.data$RTDate<-ifelse(!sir.data$ReadCode  %in%  transplant1.csv$ReadCode,NA,sir.data$RTDate) 
smalltab<-sir.data[!is.na(sir.data$RTDate),c("PatientID","RTDate")]
first<-smalltab %>%
   group_by(PatientID) %>%  
   arrange(RTDate) %>% 
   slice(which.min(as.numeric(RTDate))) %>%
as.data.frame    
head(first)
  
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)
crea.rep$event.date<-as.Date(as.character(crea.rep$EntryDate),format="%Y%m%d")
crea.rep$RTDate<-as.Date(as.character(crea.rep$RTDate),format="%Y%m%d")
crea.rep$DaysSinceRT<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$RTDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceRT<-ifelse(crea.rep$DaysSinceRT<0,NA,crea.rep$DaysSinceRT)
crea.rep$RenalTransplant<-ifelse(is.na(crea.rep$DaysSinceRT),0,1)
summary(crea.rep$DaysSinceRT,na.rm=TRUE)

save(crea.rep, file = "crea.repongoing.rda")
 write.csv(crea.rep,file="crea.repongoing.csv")
#########################################################
#CODE STOP CODED VARIABLES  
#smoking
#anaemia
#hypertension
#immunosuppresants
#antimicrobials
#NSAIDSs & STOP CODES/EXPOSURE TIME
#Nephrotoxins

#########################################################
PRESCRIBING DATA
###########################################
#PREP TAB DELIMITED FILES FOR PERL
#https://github.com/rw251/research-events-medication-htn/blob/master/resources/example-data2.txt
head(P2[P2$ReadCode=="b311.",])

crea.rep$LDdose<- #LOOP DIURETIC DOSE
crea.rep$TDdose<- #Thiazide DIURETIC DOSE
crea.rep$AAdose<- #ALDOSTERONE ANTAGONIST DOSE
crea.rep$ACEIdose<- #ACE INHIBITOR DOSE
crea.rep$ARBdose<- #Angiotensin receptor blocker DOSE

CHECK FOR SENSITIITY OF SPECULATIVE HEART FAILURE CODES
###################################################################
#HEART FAILURE ALL CODES ANY HF AT ANY POINT
data2<-sir.data[sir.data$ReadCode %in% HeartFailureAll1.csv$Code,]
crea.rep$HeartFailureAll<-ifelse(crea.rep$PatientID  %in%  data2$PatientID,1,0)
rm(data2)
###################################################################
#HEART FAILURE CONFIRMED CODES ANY HF AT ANY POINT
data2<-sir.data[sir.data$ReadCode %in% HeartFailureConfirmed1.csv$ReadCode,]
crea.rep$HeartFailureConf<-ifelse(crea.rep$PatientID  %in%  data2$PatientID,1,0) 
rm(data2)
specheartpatients<-crea.rep[crea.rep$HeartFailureConf==0&crea.rep$HeartFailureAll==1,]
levels(as.factor(specheartpatients$PatientID))
table(crea.rep$HeartFailureAll)
#CODE BINARY CONDITIONS BASED ON COMMENCEMENT DATE ONWARDS