Promoting the use of R in the NHS

Blog Article

There are many occasions when a column of data needs to be created from an already existing column for ease of data manipulation. For example, perhaps you have a body of text as a pathology report and you want to extract all the reports where the diagnosis is ‘dysplasia’. 

You could just subset the data using grepl so that you only get the reports that mention this word…but what if the data needs to be cleaned prior to subsetting like excluding reports where the diagnosis is normal but the phrase ‘No evidence of dysplasia’ is present. Or perhaps there are other manipulations needed prior to subsetting.

This is where data accordionisation is useful. This simply means the creation of data from (usually) a column into another column in the same dataframe.

The neatest way to do this is with the mutate function from the ‘dplyr’ package which is devoted to data cleaning. There are also other ways which I will demonstrate at the end.

The input data here will be an endoscopy data set:

Age<-sample(1:100, 130, replace=TRUE)
Dx<-sample(c("NDBE","LGD","HGD","IMC"), 130, replace = TRUE)
TimeOfEndoscopy<-sample(1:60, 130, replace=TRUE)
library(dplyr) 
EMRdf<-data.frame(Age,Dx,TimeOfEndoscopy,stringsAsFactors=F)

Perhaps you need to calculate the number of hours spent doing each endoscopy rather than the number of minutes

EMRdftbb<-EMRdf%>%mutate(TimeOfEndoscopy/60)
install.packages("knitr")
library(knitr)
#Just show the top 20 results
kable(head(EMRdftbb,20))
Age Dx TimeOfEndoscopy TimeOfEndoscopy/60
19 HGD 39 0.6500000
56 NDBE 14 0.2333333
16 HGD 10 0.1666667
17 HGD 38 0.6333333
59 HGD 23 0.3833333
79 HGD 19 0.3166667
72 NDBE 37 0.6166667
9 IMC 20 0.3333333
19 NDBE 39 0.6500000
89 LGD 26 0.4333333
32 NDBE 5 0.0833333
12 NDBE 34 0.5666667
42 NDBE 40 0.6666667
82 NDBE 40 0.6666667
4 NDBE 58 0.9666667
26 HGD 21 0.3500000
1 HGD 38 0.6333333
35 LGD 25 0.4166667
1 HGD 51 0.8500000
28 IMC 29 0.4833333

That is useful but what if you want to classify the amount of time spent doing each endoscopy as follows: <0.4 hours is too little time and >0.4 hours is too long.

Using ifelse() with mutate for conditional accordionisation.

For this we would use ifelse(). However this can be combined with mutate() so that the result gets put in another column as follows

EMRdf2<-EMRdf%>%mutate(TimeInHours=TimeOfEndoscopy/60)%>%mutate(TimeClassification = ifelse(TimeInHours>0.4, "Too Long", "Too Short"))

#Just show the top 20 results

kable(head(EMRdf2,20))

Age Dx TimeOfEndoscopy TimeInHours TimeClassification
19 HGD 39 0.6500000 Too Long
56 NDBE 14 0.2333333 Too Short
16 HGD 10 0.1666667 Too Short
17 HGD 38 0.6333333 Too Long
59 HGD 23 0.3833333 Too Short
79 HGD 19 0.3166667 Too Short
72 NDBE 37 0.6166667 Too Long
9 IMC 20 0.3333333 Too Short
19 NDBE 39 0.6500000 Too Long
89 LGD 26 0.4333333 Too Long
32 NDBE 5 0.0833333 Too Short
12 NDBE 34 0.5666667 Too Long
42 NDBE 40 0.6666667 Too Long
82 NDBE 40 0.6666667 Too Long
4 NDBE 58 0.9666667 Too Long
26 HGD 21 0.3500000 Too Short
1 HGD 38 0.6333333 Too Long
35 LGD 25 0.4166667 Too Long
1 HGD 51 0.8500000 Too Long
28 IMC 29 0.4833333 Too Long

Note how we can chain the mutate() function together.

Using multiple ifelse()

What if we want to get more complex and put several classifiers in? We just use more ifelse’s:

EMRdf2<-EMRdf%>%mutate(TimeInHours=TimeOfEndoscopy/60)%>%mutate(TimeClassification = ifelse(TimeInHours>0.8, "Too Long", ifelse(TimeInHours<0.5,"Too Short",ifelse(TimeInHours>=0.5&TimeInHours<=0.8,"Just Right","N"))))

#Just show the top 20 results

kable(head(EMRdf2,20))
Age Dx TimeOfEndoscopy TimeInHours TimeClassification
19 HGD 39 0.6500000 Just Right
56 NDBE 14 0.2333333 Too Short
16 HGD 10 0.1666667 Too Short
17 HGD 38 0.6333333 Just Right
59 HGD 23 0.3833333 Too Short
79 HGD 19 0.3166667 Too Short
72 NDBE 37 0.6166667 Just Right
9 IMC 20 0.3333333 Too Short
19 NDBE 39 0.6500000 Just Right
89 LGD 26 0.4333333 Too Short
32 NDBE 5 0.0833333 Too Short
12 NDBE 34 0.5666667 Just Right
42 NDBE 40 0.6666667 Just Right
82 NDBE 40 0.6666667 Just Right
4 NDBE 58 0.9666667 Too Long
26 HGD 21 0.3500000 Too Short
1 HGD 38 0.6333333 Just Right
35 LGD 25 0.4166667 Too Short
1 HGD 51 0.8500000 Too Long
28 IMC 29 0.4833333 Too Short

Using multiple ifelse() with grepl() or string_extract

Of course we need to extract information from text as well as numeric data. We can do this using grepl or string_extract from the library(stringr).

Let’s say we want to extract all the samples that had IMC. We don’t want to subset the data, just extract IMC into a column that says IMC and the rest say ’Non-IMC’

Using the dataset above:

library(stringr)

EMRdf$MyIMC_Column<-str_extract(EMRdf$Dx,"IMC") 

#to fill the NA's we would do:EMRdf$MyIMC_Column<-ifelse(grepl("IMC",EMRdf$Dx),"IMC","NoIMC")

#Another way to do this (really should be for more complex examples when you want to extract the entire contents of the cell that has the match) 

EMRdf$MyIMC_Column<-ifelse(grepl("IMC",EMRdf$Dx),str_extract(EMRdf$Dx,"IMC"),"NoIMC")

So data can be usefully created from data for further analysis.

Hopefully this way of extrapolating data and especially using conditional expressions to categorise data according to some rules is a helpful way to get more out   of your data.

Please follow @gastroDS on twitter

This article originally appeared on https://sebastiz.github.io/gastrodatascience/

 

 

 

 

Leave a Reply