How to extrapolate data from data – NHS-R Community Quarto website

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)
library(kableExtra)

# Just show the top 20 results

kable(head(EMRdftbb, 20))

Age	Dx	TimeOfEndoscopy	TimeOfEndoscopy/60
57	LGD	40	0.6666667
11	NDBE	18	0.3000000
1	LGD	23	0.3833333
2	NDBE	50	0.8333333
100	LGD	44	0.7333333
57	NDBE	36	0.6000000
11	LGD	33	0.5500000
85	LGD	39	0.6500000
64	LGD	60	1.0000000
12	HGD	50	0.8333333
31	LGD	40	0.6666667
77	HGD	41	0.6833333
50	NDBE	14	0.2333333
47	LGD	37	0.6166667
53	IMC	58	0.9666667
5	NDBE	23	0.3833333
84	HGD	44	0.7333333
29	LGD	6	0.1000000
74	LGD	49	0.8166667
56	HGD	48	0.8000000

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
57	LGD	40	0.6666667	Too Long
11	NDBE	18	0.3000000	Too Short
1	LGD	23	0.3833333	Too Short
2	NDBE	50	0.8333333	Too Long
100	LGD	44	0.7333333	Too Long
57	NDBE	36	0.6000000	Too Long
11	LGD	33	0.5500000	Too Long
85	LGD	39	0.6500000	Too Long
64	LGD	60	1.0000000	Too Long
12	HGD	50	0.8333333	Too Long
31	LGD	40	0.6666667	Too Long
77	HGD	41	0.6833333	Too Long
50	NDBE	14	0.2333333	Too Short
47	LGD	37	0.6166667	Too Long
53	IMC	58	0.9666667	Too Long
5	NDBE	23	0.3833333	Too Short
84	HGD	44	0.7333333	Too Long
29	LGD	6	0.1000000	Too Short
74	LGD	49	0.8166667	Too Long
56	HGD	48	0.8000000	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
57	LGD	40	0.6666667	Just Right
11	NDBE	18	0.3000000	Too Short
1	LGD	23	0.3833333	Too Short
2	NDBE	50	0.8333333	Too Long
100	LGD	44	0.7333333	Just Right
57	NDBE	36	0.6000000	Just Right
11	LGD	33	0.5500000	Just Right
85	LGD	39	0.6500000	Just Right
64	LGD	60	1.0000000	Too Long
12	HGD	50	0.8333333	Too Long
31	LGD	40	0.6666667	Just Right
77	HGD	41	0.6833333	Just Right
50	NDBE	14	0.2333333	Too Short
47	LGD	37	0.6166667	Just Right
53	IMC	58	0.9666667	Too Long
5	NDBE	23	0.3833333	Too Short
84	HGD	44	0.7333333	Just Right
29	LGD	6	0.1000000	Too Short
74	LGD	49	0.8166667	Too Long
56	HGD	48	0.8000000	Just Right

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/ and has been edited to render in Quarto and had NHS-R styles applied.

Reuse

CC0