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 this. 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)


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)
#Just show the top 20 results
kable(head(EMRdftbb,20))
Age Dx TimeOfEndoscopy TimeOfEndoscopy/60
75 IMC 25 0.4166667
54 HGD 57 0.9500000
4 IMC 45 0.7500000
66 HGD 25 0.4166667
91 NDBE 37 0.6166667
19 LGD 29 0.4833333
13 LGD 40 0.6666667
32 HGD 47 0.7833333
51 NDBE 2 0.0333333
68 HGD 48 0.8000000
36 IMC 28 0.4666667
85 IMC 35 0.5833333
100 LGD 40 0.6666667
74 IMC 54 0.9000000
63 NDBE 12 0.2000000
49 LGD 26 0.4333333
95 NDBE 16 0.2666667
34 LGD 50 0.8333333
24 NDBE 56 0.9333333
39 LGD 24 0.4000000

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
75 IMC 25 0.4166667 Too Long
54 HGD 57 0.9500000 Too Long
4 IMC 45 0.7500000 Too Long
66 HGD 25 0.4166667 Too Long
91 NDBE 37 0.6166667 Too Long
19 LGD 29 0.4833333 Too Long
13 LGD 40 0.6666667 Too Long
32 HGD 47 0.7833333 Too Long
51 NDBE 2 0.0333333 Too Short
68 HGD 48 0.8000000 Too Long
36 IMC 28 0.4666667 Too Long
85 IMC 35 0.5833333 Too Long
100 LGD 40 0.6666667 Too Long
74 IMC 54 0.9000000 Too Long
63 NDBE 12 0.2000000 Too Short
49 LGD 26 0.4333333 Too Long
95 NDBE 16 0.2666667 Too Short
34 LGD 50 0.8333333 Too Long
24 NDBE 56 0.9333333 Too Long
39 LGD 24 0.4000000 Too Short

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
75 IMC 25 0.4166667 Too Short
54 HGD 57 0.9500000 Too Long
4 IMC 45 0.7500000 Just Right
66 HGD 25 0.4166667 Too Short
91 NDBE 37 0.6166667 Just Right
19 LGD 29 0.4833333 Too Short
13 LGD 40 0.6666667 Just Right
32 HGD 47 0.7833333 Just Right
51 NDBE 2 0.0333333 Too Short
68 HGD 48 0.8000000 Just Right
36 IMC 28 0.4666667 Too Short
85 IMC 35 0.5833333 Just Right
100 LGD 40 0.6666667 Just Right
74 IMC 54 0.9000000 Too Long
63 NDBE 12 0.2000000 Too Short
49 LGD 26 0.4333333 Too Short
95 NDBE 16 0.2666667 Too Short
34 LGD 50 0.8333333 Too Long
24 NDBE 56 0.9333333 Too Long
39 LGD 24 0.4000000 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). We have used this before here so you may want to refresh yourself.

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