PERMIT CPRD data reshaping

OOpen MobaXTerm
ssh mqbpjhr4@incline256.itservices.manchester.ac.uk
(enter password this does not print on screen!)
module load apps/binapps/stata/14-iph
stata

module load apps/gcc/R/3.3.3
module load apps/binapps/rstudio/0.98.983-any-r
rstudio

require(haven)
require(gtools)
library(zoo)
library(plyr)
library(tidyverse)
library(data.table)
library(survival)
library(lubridate)
CPRD<-read_dta("hf_cases_therapy.dta") #Medications
CPRD2<-read_dta("hf_cases_clinical.dta") 
CPRD3<-read_dta("hf_cases_test.dta") 
CPRD5<-read_dta("hf_cases_additional.dta") 
#head(CPRD[CPRD$medcode=="5",]) #Only 2312 #References some dates of creatinine measures but no additional data
#crea<-CPRD3[CPRD3$medcode=="5",] #1579351 #This is the file with the creatinine values in
load("creaongoing.rda")
#this is the file of Creatinine entries to be used as a reference 
#####################################################################################################
#SELECTING FOR HEART FAILURE
#SELECT PATIENTS WHO WERE 18 OR ABOVE AT THE TIME OF HEART FAILURE DIAGNOSIS

#Add hfage for selection

CPRD4<-read_dta("hf_cases_patient_practice_incidentHFdate.dta")  
CPRD4$hfage<-(as.numeric(year(strptime(CPRD4$eventdate, format="%Y-%m-%d"))))-CPRD4$yob
CPRD4$hfage<-ifelse(as.numeric(month(strptime(CPRD4$eventdate, format="%Y-%m-%d")))>CPRD4$mob&CPRD4$mob>0,CPRD4$hfage-1,CPRD4$hfage)
CPRD4<-CPRD4[,c(1:8,18,19)]
head(CPRD4[duplicated(CPRD4$patid),]) 
#None of the records are duplicated- each gives the hfdate based on 97 hf codes

CPRD4<-CPRD4[CPRD4$hfage>=18,]
save(CPRD4,file="CPRD4.rda")
hfnames<-unique(CPRD4$patid)
length(hfnames) 
#282219

crea<-crea[crea$patid %in% hfnames,] 
CPRD4<-CPRD4[,c(1,2,6:10)]
CPRD4$hfdate<-CPRD4$eventdate
CPRD4<-CPRD4[,c(1,3:8)]
CPRD4<-as.data.frame(CPRD4)
crea<-as.data.frame(crea)
crea<-merge(crea,CPRD4,all.x=TRUE,all.y=FALSE)

save(crea, file = "crea.Rdata") 

##################################################################################################### 
#START WITH A SKELETON OF CREATININE VALUES THEN ADD ON TO IT.

#Remove outliers then order and select the highest value per day
crea<-crea[!is.na(crea$data2),]
crea<-crea[order(-crea$data2),]

smalltab<-crea %>%
group_by_(.dots=c("patid","eventdate")) %>% 
summarize(Creatinine = mean(data2)) %>%
ungroup()%>%
as.data.frame
crea<-merge(crea,as.data.frame(smalltab),all.x=TRUE)

save(crea, file = "crea2.rda") 

#######################################################################################################up to here
#CHECK UNITS IN CASE VALUES NEED CONVERTING

#C<-unique(crea$data3) 
#Should be 142(umol/L). Lookups for units are in the text file 'SUM.txt'
#write.csv(C,file="SUMcrea.csv")
#HAND ANNOTATE WITH CONVERSION FACTORS

C<-read.csv("SUMcrea.csv")
colnames (C)[1]<-"data3"
crea<-merge(crea,C[,c(1,3,4)],all.x=TRUE,all.y=FALSE)
save(crea, file = "crea2.rda") 
#BIND ON UNITS OF MEASUREMENT

#CONVERT PROBLEMATIC UNITS
#crea$data2<-ifelse(!crea$CodeUnits=="umol/L",(crea$data2*crea$Times),crea$data2)
#1477308 values at this stage
#ALTHOUGH I TESTED THIS THE MMOL/L UNITS JUST SEEM TO BE MISNAMED-ALL ARE IN RANGE AND WE LOSE TOO MUCH DATA IF WE CONVERT THEM THEN CUT THEM OUT.

#CUTOFF IMPLAUSIBLE VALUES
crea<-crea[crea$data2>=20&crea$data2<=3000,]
#1422167 rows remaining

save(crea, file = "crea2.rda")
length(levels(as.factor(crea$patid)))
#120731 patients

########################################################################################################
#Add birth and death and basic formulaic variables
crea$Dead<-ifelse(!is.na(crea$deathdate),1,0)
#The death dates are not reliable, many of them are before 1950 and 100 are before 1980.

crea$Age<-(as.numeric(year(strptime(crea$eventdate, format="%Y-%m-%d"))))-crea$yob
crea$Age<-ifelse(as.numeric(month(strptime(crea$eventdate, format="%Y-%m-%d")))>crea$mob&crea$mob>0,crea$Age-1,crea$Age)
save(crea, file = "crea2.rda")
crea$log_CREA<-log(crea$Creatinine)

colnames(crea)[2] <- "PatientID"
colnames(crea)[3] <- "event.date"
colnames(crea)[18] <- "Gender"
crea<-crea[,c(2,3,18,21:24,26:28)]
save(crea, file = "crea2.rda")
######################################################################################################## CHECKED

#Add demographic variables from lookup tables
CPRD2<-read_dta("hf_cases_clinical.dta")  


#CODE ETHNICITY
eth<-read.table("ethnicitycprd.csv",header=TRUE,sep=",")
eth<-eth[,c(1,5)]
WHICH TABLE ARE THE ETH MEDCODES IN?
CPRD2<-merge(CPRD2,eth,by.x="medcode",by.y="medcode",all=TRUE)
CPRD2<-CPRD2[!is.na(CPRD2$Category),]

table(CPRD2$Category)
CPRD2<-CPRD2[,c(2,12)]
CPRD2<-unique(CPRD2)
head(CPRD2[duplicated(CPRD2),])
#THERE ARE NO PATIENTS WITH CONFLICTING ETHNICITY CODES

colnames(CPRD2)[1] <- "PatientID"
colnames(CPRD2)[2] <- "Ethnicity"
crea<-merge(crea,CPRD2,all.x=TRUE)

save(crea,file="crea2.rda")

#CHECK ALSO FOR 23955 generic code x1
#DRAW INFORMATION ON 23955 FROM LOOKUP TABLE
CPRD2<-read_dta("hf_cases_clinical.dta")  
C2<-CPRD2[CPRD2$medcode=="23955",]
table(C2$adid) #Not linked to text

#################################################################################################################
#COHORT SELECTION
#LIMIT TO PATIENTS WITH AT LEAST 2 (POST 2008?) CREATININE TEST VALUES
#crea<-crea[as.numeric(year(strptime(crea$event.date, format="%Y-%m-%d")))>=2008,]
table(crea$PatientID) < 2 -> rare  
rownames(as.matrix(rare)) -> ids
table(rare)
crea[!(crea$PatientID %in% ids[rare]),] -> crea.rep	
levels(unique(as.factor(crea.rep$PatientID))) # 198543 adult hf patients have 2 or more creatinine tests
length(crea.rep$PatientID) #1579775 rows remaining

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

############################################################################################################ONGOING
#Add LSOA

IMD<-read.csv("patient_imd2010_16_241RMnA.txt",sep="\t")
IMD<-IMD[,c(1,3)]
colnames(IMD)<-c("PatientID","IMD_Decile2010")
crea<-merge(crea,IMD,all.x=TRUE)
save(crea.rep,file="crea.rep.rda")
#Breakpoint
#################################################################################################################
load("crea.rep.rda")








#################################################################################################################
#ADD CONDITIONAL VARIABLES
library(dplyr)
#ADD BMI
#Call the data fields that go with the clinical table from the 'additional' table using enttype and adid.

#There are some in test, also some in clinical, 
BMI<-read.csv("BMI1.csv")
C<-(CPRD3[CPRD3$medcode %in% BMI$Medcode,c(1,2,11)])
C<-as.data.frame(C)
colnames(C)[1] <- "PatientID"
colnames(C)[3] <- "BMI"

C2<-(CPRD2[CPRD2$medcode %in% BMI$Medcode | CPRD2$enttype==13,])
C2<-as.data.frame(C2)
C2<-merge(C2,CPRD5,all.x=TRUE,all.y=FALSE) 
colnames(C2)[1] <- "PatientID"
C2<-C2[!is.na(C2$data3),]
C2<-C2[,c(1,4,14)]
colnames(C2)[3] <- "BMI"
C<-rbind(C,C2)

#CLOSEST DAILY BMI MEAN #1YR DATE FLAG
BMItab<-C[C$BMI>=10 & C$BMI<=70,]
firstA <-BMItab %>% 
group_by(PatientID,eventdate) %>%
summarize(BMI=mean(BMI)) %>%
as.data.frame

indx1 <- neardate(crea$PatientID, firstA$PatientID, crea$event.date, firstA$eventdate, best="prior")
crea$BMI<-firstA[indx1, "BMI"]
crea$BMIDateFlag<- ifelse(as.numeric(abs(crea$event.date - firstA$eventdate[indx1])) >365, 1, 0)
table(crea$BMIDateFlag) 
a<-crea[!is.na(crea$BMI),]
length(a$PatientID) 
crea$BMI<-unlist(crea$BMI)
crea$BMIDateFlag<-ifelse(is.na(crea$BMIDateFlag),0,crea$BMIDateFlag)
save(crea,file="creaongoing.rda")
rm(a)
#IF BMI IS MISSING WE CAN CALCULATE IT.

Height<-read.csv("Height1.csv")
H<-(CPRD2[CPRD2$medcode %in% Height$Medcode,])
H<-as.data.frame(H)
H<-merge(H,CPRD5,all.x=TRUE,all.y=FALSE) 
H<-H[,c(1,4,12)]
colnames(H)[3] <- "Height"
colnames(H)[1] <- "PatientID"

#Near data match each creatinine record to the last (tallest) recorded height and the most recent weight
firstH<-H %>% 
group_by(PatientID) %>%
summarize(Height=max(Height)) %>%
as.data.frame


crea<-merge(crea,firstH,all.x=TRUE)
crea$Height<-unlist(crea$Height)
save(crea,file="creaongoing.rda")
rm(firstH)

Weight<-read.csv("Weight1.csv")
W<-(CPRD2[CPRD2$medcode %in% Weight$Medcode,])
W<-as.data.frame(W)
CPRD5<-read_dta("hf_cases_additional.dta") 
W<-merge(W,CPRD5,all.x=TRUE,all.y=FALSE) 
#Call the data fields that go with the clinical table from the 'additional' table using enttype and adid.
W<-W[,c(1,4,12)]
colnames(W)<- c("PatientID","event.date","Weight")

indx1 <- neardate(crea$PatientID, W$PatientID, crea$event.date, W$event.date, best="prior")
crea$Weight<-W[indx1, "Weight"]
crea$Weightdate<-W[indx1,"entry.date"]
crea$Weight<-unlist(crea$Weight)
crea$Weightdate<-unlist(crea$Weightdate)

crea$BMIDateFlag<-ifelse(is.na(crea$BMIDateFlag)&!is.na(crea$Height)&!is.na(crea$Weight)&(as.numeric(abs(crea$event.date - crea$Weightdate))>365),1,crea$BMIDateFlag)
crea$BMI<-ifelse(is.na(crea$BMI)&!is.na(crea$Height)&!is.na(crea$Weight),(crea$Weight/(crea$Height^2)),crea$BMI)
crea$BMI<-ifelse(crea$BMI>=10&crea$BMI<=70,crea$BMI,NA)
#no additional BMIs added this way

save(crea,file="creaongoing.rda")
##########################################################################

#SELECT PATIENTS WHICH HAVE AT LEAST 2 TESTS AND AT LEAST 2 YEARS DATA
#FIND MIN AND MAX DATES PER PATIENT
x<-CPRD2[,c(1,2)]
x1<-x %>% 
group_by(patid) %>%
summarize(start=min(eventdate),end=max(eventdate)) %>%
as.data.frame
x1$range<-as.numeric(as.character(x1$end-x1$start))

x1[(which(x1$range<2)),1] -> range_short_ids    # define exclusion range as 2 years
crea[-which(crea$PatientID %in% range_short_ids),]->crea.rep 
length(unique(crea.rep$PatientID))
save(crea.rep,file="crea.rep.rda")

###########################################################################
#SERUM SODIUM
SerumSodium1<-read.csv("SerumSodium1.csv")
C<-(CPRD3[CPRD3$medcode %in% SerumSodium1$Medcode,c(1,2,11,12)])
table(C$data3)
#CHECK FOR UNIQUE UNITS USED (mmol/L OR NONE)

C<-as.data.frame(C)
colnames(C)<- c("PatientID","event.date","Value")
C<-C[C$Value>=80&C$Value<=200,]

columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(SerumSodium=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$SerumSodium<-first[indx1, "SerumSodium"]
crea.rep$SerSodDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >30, 1, 0)
table(crea.rep$SerSodDateFlag) #652
crea.rep$SerumSodium<-unlist(crea.rep$SerumSodium)

###########################################################################
#SERUM POTASSIUM
SerumPotassium<-read.csv("SerumPotassium1.csv")
C<-(CPRD3[CPRD3$medcode %in% SerumPotassium$Medcode,c(1,2,11,12)])
table(C$data3)
#CHECK FOR UNIQUE UNITS USED (mmol/L OR NONE)

C<-as.data.frame(C)
colnames(C)<- c("PatientID","event.date","Value")
C<-C[C$Value>=2 & C$Value<=10,]

columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(SerumPotassium=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$SerumPotassium<-first[indx1, "SerumPotassium"]
crea.rep$SerPotDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >30, 1, 0)
table(crea.rep$SerPotDateFlag) #652
crea.rep$SerumPotassium<-unlist(crea.rep$SerumPotassium)

###############################################################################
#URIC ACID

UA<-read.csv("UricAcid1.csv")
C<-(CPRD3[CPRD3$medcode %in% UA$Medcode,c(1,2,11,12)])
table(C$data3)
#CHECK FOR UNIQUE UNITS USED (mmol/L OR umol)
C$data2<-ifelse(C$data3==142,C$data2/1000,C$data2)

C<-as.data.frame(C)
colnames(C)<- c("PatientID","event.date","Value")
C<-C[C$Value>=0.1 & C$Value<=1,]

columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(UricAcid=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$UricAcid<-first[indx1, "UricAcid"]
crea.rep$UricAcidDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >365, 1, 0)
table(crea.rep$UricAcidDateFlag) #652
crea.rep$UricAcid<-unlist(crea.rep$UricAcid)
crea.rep$UricAcid_DF<-ifelse(crea.rep$UricAcidDateFlag==1,NA,crea.rep$UricAcid)

save(crea,file="creaongoing.rda")


###############################################################################
#UACR
UACR<-read.csv("UACR1.csv")
C<-(CPRD3[CPRD3$medcode %in% UA$Medcode,c(1,2,11,12)])
table(C$data3)
#CHECK FOR UNIQUE UNITS USED (mmol/L OR umol)
C$data2<-ifelse(C$data3==142,C$data2/1000,C$data2)
C$data2<-ifelse(C$data3==99,C$data2*1000,C$data2)

C<-as.data.frame(C[C$data2<=3000,])
colnames(C)<- c("PatientID","event.date","Value")

columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(UACratio=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$UACratio<-first[indx1, "UACratio"]
crea.rep$UACDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >365, 1, 0)
table(crea.rep$UACDateFlag) #652
crea.rep$UACratio<-unlist(crea.rep$UACratio)

save(crea,file="creaongoing.rda")

###############################################################################
#BUN
BUN<-read.csv("BUN1.csv")
C<-(CPRD3[CPRD3$medcode %in% BUN$Medcode &CPRD3$enttype==204,c(1,2,11,12)])
table(C$data3)
#CHECK FOR UNIQUE UNITS USED (mmol/L OR NA)

C<-as.data.frame(C[C$data2>=1&C$data2<=50,])
colnames(C)<- c("PatientID","event.date","Value")

columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(BUN=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$BUN<-first[indx1, "BUN"]
crea.rep$BUNDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >365, 1, 0)
table(crea.rep$BUNDateFlag) #652
crea.rep$BUN<-unlist(crea.rep$BUN)

save(crea,file="creaongoing.rda")


###############################################################################
#DBP
DBP<-read.csv("DBP1.csv")
C<-(CPRD2[CPRD2$medcode  %in% DBP$Medcode,])
C<-merge(C,CPRD5,all.x=TRUE,all.y=FALSE) 
C2<-CPRD3[CPRD3$medcode %in% DBP$Medcode,]

CA<-C[C$enttype==1,c(1,4,12)]#data1
CB<-C[C$enttype==288,c(1,4,13)]#data2- THERE ARE NONE OF THESE
colnames(CA)<- c("PatientID","event.date","Value")

CB<-C2[C2$enttype==288,c(1,2,11)]#data2
CC<-C2[C2$enttype==1,c(1,2,10)]#data1- NONE OF THESE
colnames(CB)<- c("PatientID","event.date","Value")
C<-rbind(CA,CB)

C<-as.data.frame(C[C$Value>=20&C$Value<=200,])
columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(DBP=min(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$DBP<-first[indx1, "DBP"]
crea.rep$DBPDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >30, 1, 0)
table(crea.rep$DBPDateFlag) #652
crea.rep$DBP<-unlist(crea.rep$DBP)

save(crea,file="creaongoing.rda")


###############################################################################
#SBP
SBP<-read.csv("SBP1.csv")
C<-(CPRD2[CPRD2$medcode  %in% SBP$Medcode,])
C<-merge(C,CPRD5,all.x=TRUE,all.y=FALSE) 
C2<-CPRD3[CPRD3$medcode %in% SBP$Medcode,]

CA<-C[C$enttype==1,c(1,4,13)]#data2
colnames(CA)<- c("PatientID","event.date","Value")
CB<-C2[,c(1,2,11)]#data2
colnames(CB)<- c("PatientID","event.date","Value")
C<-rbind(CA,CB)

C<-as.data.frame(C[C$Value>=30&C$Value<=300,])
columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(SBP=min(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$SBP<-first[indx1, "SBP"]
crea.rep$SBPDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >30, 1, 0)
table(crea.rep$SBPDateFlag) #652
crea.rep$SBP<-unlist(crea.rep$SBP)

save(crea.rep,file="crea.rep.rda")
###############################################################################
#MEAN DAILY MEAN CORPUSCULAR VOLUME #120 DAYS
MCV<-read.csv("MCV1.csv")
C<-(CPRD3[CPRD3$medcode  %in% MCV$Medcode,])
C<-merge(C,CPRD5,all.x=TRUE,all.y=FALSE) 
table(C$data3)

C<-C[,c(1,4,10)]#data2
colnames(C)<- c("PatientID","Value","event.date")

C<-as.data.frame(C[C$Value>=50&C$Value<=150,])
columns=names(C[c(1,3)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(MCV=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$MCV<-first[indx1, "MCV"]
crea.rep$MCVDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >120, 1, 0)
table(crea.rep$MCVDateFlag) #652
crea.rep$MCV<-unlist(crea.rep$MCV)

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

###########################################################################
#SERUM ALBUMIN
SA<-read.csv("SerumAlbumin1.csv")
C<-(CPRD3[CPRD3$medcode %in% SA$Medcode & !CPRD3$enttype=="291",])
table(C$data3)
C$data2<-ifelse(C$data3==83,C$data2/1000, C$data2)
#CHECK FOR UNIQUE UNITS USED (mmol/L OR NONE)

C<-as.data.frame(C[,c(1,2,11)])
colnames(C)<- c("PatientID","event.date","Value")
C<-C[C$Value>=10 & C$Value<=60,]

columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(SerumAlbumin=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$SerumAlbumin<-first[indx1, "SerumAlbumin"]
crea.rep$SADateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >365, 1, 0)
table(crea.rep$SADateFlag) #652
crea.rep$SerumAlbumin<-unlist(crea.rep$SerumAlbumin)

###########################################################################
#URINE ALBUMIN
UA<-read.csv("UAlbumin1.csv")
C<-(CPRD3[CPRD3$medcode %in% UA$Medcode,])
C2<-(CPRD2[CPRD2$medcode %in% UA$Medcode,])
C2<-merge(C2,CPRD5,all.x=TRUE,all.y=FALSE) 
summary(C2)#NO numeric data

#CHECK UNITS!
unique(C$data3)
C$data2<-ifelse(C$data3==57,C$data2*1000,C$data2)
C<-C[,c(1,2,11)]
colnames(C)<- c("PatientID","event.date","Value")
C<-C[C$Value>=0 & C$Value<=1000,]

columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(UrineAlbumin=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$UrineAlbumin<-first[indx1, "UrineAlbumin"]
crea.rep$UADateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >365, 1, 0)
table(crea.rep$UADateFlag) #652
crea.rep$UrineAlbumin<-unlist(crea.rep$UrineAlbumin)

################################################################################
#ATRIAL FIBRILLATION
AF1<-read.csv("AF1.csv")
CPRD2$tempDate<-CPRD2$eventdate 
CPRD2$tempDate<-ifelse(!CPRD2$medcode  %in%  AF1$Medcode,NA,CPRD2$tempDate) 
CPRD3$tempDate<-CPRD3$eventdate 
CPRD3$tempDate<-ifelse(!CPRD3$medcode  %in%  AF1$Medcode,NA,CPRD3$tempDate) 

smalltab<-rbind(CPRD2[,c(1,12)],CPRD3[,c(1,18)])
smalltab<-smalltab[!is.na(smalltab$tempDate),]
first<-smalltab %>%
    group_by(patid) %>%
    arrange(tempDate) %>%
    slice(which.min(as.numeric(tempDate))) %>%
as.data.frame
    head(first)
    
names(first)<-c("PatientID","AFDate")
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)

crea.rep$AFDate<-as.Date(crea.rep$AFDate,origin="1970/01/01")
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)

################################################################################
#CHRONIC LIVER DISEASE
CLD1<-read.csv("Liver1.csv")
CPRD2$tempDate<-CPRD2$eventdate 
CPRD2$tempDate<-ifelse(!CPRD2$medcode  %in%  CLD1$Medcode,NA,CPRD2$tempDate) 
CPRD3$tempDate<-CPRD3$eventdate 
CPRD3$tempDate<-ifelse(!CPRD3$medcode  %in%  CLD1$Medcode,NA,CPRD3$tempDate) 

smalltab<-rbind(CPRD2[,c(1,12)],CPRD3[,c(1,18)])
smalltab<-smalltab[!is.na(smalltab$tempDate),]
first<-smalltab %>%
    group_by(patid) %>%
    arrange(tempDate) %>%
    slice(which.min(as.numeric(tempDate))) %>%
as.data.frame
    head(first)
    
names(first)<-c("PatientID","CLD1Date")
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)

crea.rep$CLDDate<-as.Date(crea.rep$CLDDate,origin="1970/01/01")
crea.rep$DaysSinceCLD<-difftime(strptime(crea.rep$event.date,format="%Y-%m-%d"),strptime(crea.rep$CLDDate,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceCLD<-ifelse(crea.rep$DaysSinceCLD<0,NA,crea.rep$DaysSinceCLD)
crea.rep$CLD<-ifelse(is.na(crea.rep$DaysSinceCLD),0,1)
summary(crea.rep$DaysSinceCLD,na.rm=TRUE)

#################################################################################
#HEART RATE
#data1 enttype 131
HR<-read.csv("HeartRate1.csv")
C<-(CPRD2[CPRD2$medcode %in% HR$Medcode & CPRD2$enttype=="131",])
C<-merge(C,CPRD5,all.x=TRUE,all.y=FALSE) l

C<-as.data.frame(C[,c(1,4,13)])
colnames(C)<- c("PatientID","event.date","Value")
C<-C[C$Value>=20 & C$Value<=200,]

columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(HeartRate=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$HeartRate<-first[indx1, "HeartRate"]
crea.rep$HeartRateDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >30, 1, 0)
table(crea.rep$HeartRateDateFlag) #652
crea.rep$HeartRate<-unlist(crea.rep$HeartRate)

#####################################################################################
#BNP
BNP<-read.csv("BNP1.csv")
C<-CPRD3[CPRD3$medcode %in% BNP$Medcode,]
C<-merge(C,CPRD5,all.x=TRUE,all.y=FALSE) 
unique(C$data3)
#MANY UNITS
C$data2<-ifelse(C$data3==120,C$data2/1000,C$data2)
C<-C[C$data2>=1&C$data2<=1000,]

C<-as.data.frame(C[,c(1,10,4)])
colnames(C)<- c("PatientID","event.date","Value")
C<-na.omit(C)
columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(BNP=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$BNP<-first[indx1, "BNP"]
crea.rep$BNPDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >365, 1, 0)
table(crea.rep$BNPDateFlag) #652
crea.rep$BNP<-unlist(crea.rep$BNP)


######################################################################################
#NTPROBNP 
NTBNP<-read.csv("NTPROBNP1.csv")
C<-CPRD3[CPRD3$medcode %in% NTBNP$Medcode,]
C<-merge(C,CPRD5,all.x=TRUE,all.y=FALSE) 
unique(C$data3)
#MANY UNITS
C$data2<-ifelse(C$data3==120,C$data2/1000,C$data2)
C$data2<-ifelse(C$data3==96,C$data2*1000000000*0.289,C$data2)
C<-C[C$data2>=1&C$data2<=6000,]

C<-as.data.frame(C[,c(1,10,4)])
colnames(C)<- c("PatientID","event.date","Value")
C<-na.omit(C)
columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(NTPROBNP=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$NTPROBNP<-first[indx1, "NTPROBNP"]
crea.rep$NTPROBNPDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >365, 1, 0)
table(crea.rep$NTPROBNPDateFlag) #652
crea.rep$NTPROBNP<-unlist(crea.rep$NTPROBNP)

#CHECK UNITS!
#####################################################################################
#Haemoglobin
HB<-read.csv("Haemoglobin1.csv")
C<-(CPRD3[CPRD3$medcode %in% HB$Medcode & CPRD3$enttype=="173",])
C<-merge(C,CPRD5,all.x=TRUE,all.y=FALSE) 
unique(C$data3)
#MANY UNITS
C<-C[!C$data3==97&!C$data3==1,]
#AVOID UNITS INDICATING PROPORTIONS OF Hba

#0=NA,56,57,,97,26
C$data2<-ifelse(C$data3==56,C$data2*10,C$data2)

C<-as.data.frame(C[,c(1,2,11)])
colnames(C)<- c("PatientID","event.date","Value")
C<-C[C$Value>=30 & C$Value<=260,]

columns=names(C[c(1,2)])
dots<-lapply(columns, as.symbol)
first <-C %>% 
group_by_(.dots=dots) %>%
summarise(Haemoglobin=mean(Value)) %>%
as.data.frame

indx1 <-neardate(crea.rep$PatientID, first$PatientID, crea.rep$event.date, first$event.date, 
                   best="prior")
crea.rep$Haemoglobin<-first[indx1, "Haemoglobin"]
crea.rep$HaemDateFlag<- ifelse(as.numeric(abs(crea.rep$event.date - first$event.date[indx1])) >30, 1, 0)
table(crea.rep$HaemDateFlag) #652
crea.rep$Haemoglobin<-unlist(crea.rep$Haemoglobin)

##############################################################################
#IHD
IHD1<-read.csv("IHD1.csv")
CPRD2$tempDate<-CPRD2$eventdate 
CPRD2$tempDate<-ifelse(!CPRD2$medcode  %in%  IHD1$Medcode,NA,CPRD2$tempDate) 
CPRD3$tempDate<-CPRD3$eventdate 
CPRD3$tempDate<-ifelse(!CPRD3$medcode  %in%  IHD1$Medcode,NA,CPRD3$tempDate) 

smalltab<-rbind(CPRD2[,c(1,12)],CPRD3[,c(1,18)])
smalltab<-smalltab[!is.na(smalltab$tempDate),]
first<-smalltab %>%
    group_by(patid) %>%
    arrange(tempDate) %>%
    slice(which.min(as.numeric(tempDate))) %>%
as.data.frame
    head(first)
    
names(first)<-c("PatientID","IHDDate")
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)

crea.rep$IHDDate<-as.Date(crea.rep$IHDDate,origin="1970/01/01")
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)


##################################################################################
#PVD
PVD1<-read.csv("PVD1.csv")
CPRD2$tempDate<-CPRD2$eventdate 
CPRD2$tempDate<-ifelse(!CPRD2$medcode  %in%  PVD1$Medcode,NA,CPRD2$tempDate) 
CPRD3$tempDate<-CPRD3$eventdate 
CPRD3$tempDate<-ifelse(!CPRD3$medcode  %in%  PVD1$Medcode,NA,CPRD3$tempDate) 

smalltab<-rbind(CPRD2[,c(1,12)],CPRD3[,c(1,18)])
smalltab<-smalltab[!is.na(smalltab$tempDate),]
first<-smalltab %>%
    group_by(patid) %>%
    arrange(tempDate) %>%
    slice(which.min(as.numeric(tempDate))) %>%
as.data.frame
    head(first)
    
names(first)<-c("PatientID","PVDDate")
crea.rep<-merge(crea.rep,as.data.frame(first),all.x=TRUE)

crea.rep$PVDDate<-as.Date(crea.rep$PVDDate,origin="1970/01/01")
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")


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

#NEPHRECTOMY
Nep<-read.csv("Nephrectomy1.csv")
head(CPRD2[CPRD2$medcode %in% Nep$Medcode,])# CHECK THERE ARE VALUES TO MATCH
CPRD2$NephDate<-CPRD2$eventdate 
CPRD2$NephDate<-ifelse(!CPRD2$medcode  %in%  Nep$Medcode,NA,paste(CPRD2$NephDate)) 

smalltab<-CPRD2[!is.na(CPRD2$NephDate) & CPRD2$patid %in% crea.rep$PatientID,c("patid","NephDate")]
first<-smalltab %>%
    group_by(patid) %>%
    arrange(NephDate) %>%
    slice(1L) %>%
as.data.frame
head(first)
names(first)[1]<-"PatientID"

crea.rep<-merge(crea.rep,first,all.x=TRUE)
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")
######################################################################################
#RRT
Dialysis<-read.csv("Dialysis1.csv")
Transplant<-read.csv("transplant1.csv")

head(CPRD2[CPRD2$medcode %in% Transplant$Medcode| CPRD2$medcode %in% Dialysis$Medcode,])# CHECK THERE ARE VALUES TO MATCH
CPRD2$RRTDate<-CPRD2$eventdate 
CPRD2$RRTDate<-ifelse(!CPRD2$medcode  %in%  Transplant$Medcode & !CPRD2$medcode  %in%  Dialysis$Medcode,NA,paste(CPRD2$RRTDate)) 

smalltab<-CPRD2[!is.na(CPRD2$RRTDate) & CPRD2$patid %in% crea.rep$PatientID,c("patid","RRTDate")]
first<-smalltab %>%
    group_by(patid) %>%
    arrange(RRTDate) %>%
    slice(1L) %>%
as.data.frame
head(first)
names(first)[1]<-"PatientID"

crea.rep<-merge(crea.rep,first,all.x=TRUE)
crea.rep$DaysSinceRRT<-difftime(strptime(crea.rep$RRTDate,format="%Y-%m-%d"),strptime(crea.rep$event.date,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceRRT<-as.numeric(crea.rep$DaysSinceRRT, units="days")
crea.rep$DaysSinceRRT<-ifelse(crea.rep$DaysSinceRRT<0,NA,crea.rep$DaysSinceRRT)
save(crea.rep, file = "crea.repongoing.rda")

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

#DIABETES
Diabetes<-read.csv("Diabetes1.csv")
head(CPRD2[CPRD2$medcode %in% Diabetes$Medcode,])# CHECK THERE ARE VALUES TO MATCH
CPRD2$DiabDate<-CPRD2$eventdate 
CPRD2$DiabDate<-ifelse(!CPRD2$medcode  %in%  Diabetes$Medcode ,NA,paste(CPRD2$DiabDate)) 

smalltab<-CPRD2[!is.na(CPRD2$DiabDate) & CPRD2$patid %in% crea.rep$PatientID,c("patid","DiabDate")]
first<-smalltab %>%
    group_by(patid) %>%
    arrange(DiabDate) %>%
    slice(1L) %>%
as.data.frame
head(first)
names(first)[1]<-"PatientID"

crea.rep<-merge(crea.rep,first,all.x=TRUE)
crea.rep$DaysSinceDiabetic<-difftime(strptime(crea.rep$DiabDate,format="%Y-%m-%d"),strptime(crea.rep$event.date,format="%Y-%m-%d"),unit="days")
crea.rep$DaysSinceDiabetic<-as.numeric(crea.rep$DaysSinceDiabetic, units="days")
crea.rep$DaysSinceDiabetic<-ifelse(crea.rep$DaysSinceDiabetic<0,NA,crea.rep$DaysSinceDiabetic)
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")

########################################################### 
#MATERNITY 
Maternity1.csv<-read.csv("Maternity1.csv")
CPRD2$Maternity<-ifelse(CPRD2$medcode  %in%  Maternity1.csv$Medcode,1,0) 
CPRD3$Maternity<-ifelse(CPRD3$medcode  %in%  Maternity1.csv$Medcode,1,0) 
tab1<-CPRD2[CPRD2$Maternity==1 & CPRD2$patid %in% crea.rep$PatientID,c("patid","Maternity","eventdate")]
tab2<-CPRD3[CPRD3$Maternity==1 & CPRD3$patid %in% crea.rep$PatientID,c("patid","Maternity","eventdate")]
smalltab<-rbind(tab1,tab2)

names(smalltab)[3]<-"MatDate1"
smalltab<-smalltab[smalltab$MatDate1>="2007-01-01",]
first<-smalltab %>%
   group_by(patid) %>%  
      slice(which.min(as.numeric(MatDate1))) %>%
as.data.frame
head(first)

#NB THIS IS THE FIRST RECORDED PREGANANCY DATE FOR THE PATIENT'S FIRST POST-2007 BABY, NOT NECESSARILY THE FIRST CHILD.

names(smalltab)[3]<-"MatDate2"
smalltab<-merge(smalltab[,c(1,3)],first[,c(1,3)],all.x=TRUE)

smalltab$MatDate2<-as.Date(as.character(smalltab$MatDate2),"%Y-%m-%d")
smalltab$MatDate1<-as.Date(as.character(smalltab$MatDate1),"%Y-%m-%d")
smalltab$d<-as.numeric(smalltab$MatDate2-smalltab$MatDate1)
second<-smalltab[smalltab$d>=310,]

s<-second %>%
   group_by(patid) %>%  
      slice(which.min(as.numeric(MatDate2))) %>%
      as.data.frame
        head(s)
        
smalltab2<-rbind(tab1,tab2)
names(smalltab2)[3]<-"MatDate3"   
smalltab2$MatDate3<-as.Date(as.character(smalltab2$MatDate3),"%Y-%m-%d")
smalltab2<-smalltab2[smalltab2$MatDate3>="2007-01-01",]
smalltab2<-merge(smalltab2[,c(1,3)],s,all.x=TRUE)     
smalltab2<-unique(smalltab2)
smalltab2$MatDate2<-as.Date(as.character(smalltab$MatDate2),"%Y-%m-%d")
smalltab2$MatDate3<-as.Date(as.character(smalltab2$MatDate3),"%Y-%m-%d")
smalltab2$d2<-as.numeric(smalltab2$MatDate3-smalltab2$MatDate2)
third<-smalltab2[smalltab2$d2>=310,]

t<-third %>%
   group_by(patid) %>%  
      slice(which.min(as.numeric(MatDate3))) %>%
      as.data.frame
        head(t)
        
        length(t$patid)
#ONLY 27 HF PATIENTS HAD 3 OR MORE CHILDREN POST 2007
        
smalltab3<-merge(unique(smalltab2[,c(1,3,4)]),t[,c(1,2)],all.x=TRUE)     
smalltab3<-unique(smalltab3)

smalltab4<-rbind(tab1,tab2)
names(smalltab4)[3]<-"MatDate4"   
smalltab4<-smalltab4[smalltab4$MatDate4>="2007-01-01",]
smalltab4<-merge(smalltab4[,c(1,3)],t,all.x=TRUE)     
smalltab4<-unique(smalltab4)
smalltab4$MatDate4<-as.Date(as.character(smalltab4$MatDate4),"%Y-%m-%d")
smalltab4$d3<-as.numeric(smalltab4$MatDate4-smalltab4$MatDate3)
fourth<-smalltab4[smalltab4$d3>=310,]
length(unique(fourth$patid))

#7 PATIENTS HAD 4 OR MORE CHILDREN

f<-fourth %>%
   group_by(patid) %>%  
      slice(which.min(as.numeric(MatDate4))) %>%
      as.data.frame
        head(f)

smalltab5<-merge(unique(smalltab4[,c(1,3:5)]),f[,c(1,2)],all.x=TRUE) 
crea.rep<-merge(crea.rep,smalltab5,all.x=TRUE)

crea.rep$EstPregnant<-ifelse(crea.rep$event.date-crea.rep$MatDate1<310,1,0)
crea.rep$EstPregnant<-ifelse(crea.rep$event.date-crea.rep$MatDate2<310,1,crea.rep$EstPregnant)
crea.rep$EstPregnant<-ifelse(crea.rep$event.date-crea.rep$MatDate3<310,1,crea.rep$EstPregnant)
crea.rep$EstPregnant<-ifelse(crea.rep$event.date-crea.rep$MatDate4<310,1,crea.rep$EstPregnant)
save(crea.rep, file = "crea.rep18.rda")