How to group

Its fairly common to need to group data by an aspect of the date. For example, how many patients underwent a colonoscopy this month, or week etc. To do this we need a date object as part of the data as usual. As long as we can extract the aspect we want to group by this should be a breeze with dplyr

The problem

How do I find out how many endoscopies were done by month for the past calendar year, by endoscopy type. We will use some data that is already created. If you want to know how it was created you can check out this page…..

#Create the data

EndoHistoMerge<-source('EndoPathMerged_ExternalCode.R')
EndoHistoMerge<-data.frame(EndoHistoMerge)
#Neaten up the names
names(EndoHistoMerge)<-gsub("value.","",names(EndoHistoMerge),fixed=T)
#Lets just select the columns relevant to this page
GroupDatesExample<-EndoHistoMerge%>%select(EndoHospNumId,Date.x)
###The resulting data looks like this:
kable(head(GroupDatesExample,5))
EndoHospNumId Date.x
Y763634 2014-03-12
P223311 2015-12-08
D0739033 2016-11-02
I927282 2015-05-16
I927282 2015-05-16

Using lubridate, we can extract the month very simply as follows: This can then be incorporated into dplyr

library(lubridate)

kable(GroupDatesExample %>% group_by(month=month(Date.x)) %>% summarise(Number=n()))%>%
  kable_styling(bootstrap_options = "striped", full_width = F)
month Number
1 1538
2 1308
3 1366
4 1662
5 1347
6 1160
7 1117
8 1463
9 1307
10 1161
11 1176
12 1379

Breaking this down, we are using select() to get the columns we are interested in, then we use group_by() to group according to the two aspects we are interested in. We then use summarise to count each of the groups. The output is then as expected.

Simple numbers per year

What if you simply want to plot out the number of procedures done by year. You don’t have to use lubrudate to do this, you can do this is base RThat needs you to extract the year from the date and then summarise as follows:

Tots<-GroupDatesExample %>%
  mutate(year = format(Date.x, "%Y")) %>%
  group_by(year)%>%
  summarise(n = n())

kable(Tots)
year n
2013 3642
2014 3555
2015 3657
2016 3930
2017 1200






Get difference between two dates in consecutive rows

Often you need to know the time between consecutive tests for a patient. This is done using the difftime() function. Not we use the following functions a lot in the surveillance page so these are worth understanding:

DateBetween<-GroupDatesExample %>% arrange(EndoHospNumId, Date.x) %>% group_by(EndoHospNumId) %>%
  mutate(diffDate = difftime(Date.x, lag(Date.x,1),units="weeks"))

kable(head(DateBetween,10))
EndoHospNumId Date.x diffDate
D0739033 2013-01-04 NA
D0739033 2013-01-09 0.7142857 weeks
D0739033 2013-01-11 0.2857143 weeks
D0739033 2013-01-11 0.0000000 weeks
D0739033 2013-01-12 0.1428571 weeks
D0739033 2013-01-12 0.0000000 weeks
D0739033 2013-01-12 0.0000000 weeks
D0739033 2013-01-20 1.1428571 weeks
D0739033 2013-01-20 0.0000000 weeks
D0739033 2013-01-24 0.5714286 weeks






Get the first date or the last date in a group

It may also be that you just need to know the first or last date in the tests for a patient, again using dplyr and the slice() function:

#To get the first
GroupDatesExample %>% arrange(Date.x) %>% group_by(EndoHospNumId) %>% slice(1)
#To get the last
GroupDatesExample %>% arrange(Date.x) %>% group_by(EndoHospNumId) %>% slice(n())
#To get the first and the last
kable(head(GroupDatesExample %>% arrange(Date.x) %>% group_by(EndoHospNumId) %>% slice(c(1,n())),10))
EndoHospNumId Date.x
D0739033 2013-01-04
D0739033 2017-04-24
F630230 2013-02-05
F630230 2017-04-25
G244224 2013-01-04
G244224 2017-04-25
I927282 2013-01-05
I927282 2017-04-28
J322909 2013-01-05
J322909 2017-04-27






Selecting rows by date position based on a conditional

There are many occasions when simply grouping by dates is not sufficient for what you need. Perhaps you want to order the number of investigations that a patient has had by date so that you are ordering the dates once the grouping by hospital number has already been done, or perhaps you need to know the time difference between one test and another for a particular patient

As always, dplyr has a solution for this: Let’s use a new data set just to make things more interesting:

#Generate some sample data:

proc<-sample(c("EMR","RFA","Biopsies"), 100, replace = TRUE)
#Sample dates
dat<-sample(seq(as.Date('2013/01/01'), as.Date('2017/05/01'), by="day"), 100)
#Generate 20 hospital numbers in no particular order:
HospNum_Id<-sample(c("P433224","P633443","K522332","G244224","S553322","D0739033","U873352","P223333","Y763634","I927282","P223311","P029834","U22415","U234252","S141141","O349253","T622722","J322909","F630230","T432452"), 100, replace = TRUE)
df<-data.frame(proc,dat,HospNum_Id)

So now we group the data according to patient number:

Upstage<-df %>%
  group_by(HospNum_Id) %>%
  arrange(HospNum_Id,dat)
#Only show the first 25 samples
kable(head(Upstage,25))
proc dat HospNum_Id
RFA 2014-01-03 D0739033
Biopsies 2014-12-17 D0739033
Biopsies 2014-12-27 D0739033
Biopsies 2015-06-16 D0739033
Biopsies 2016-04-08 D0739033
RFA 2016-10-25 D0739033
Biopsies 2017-04-10 D0739033
Biopsies 2013-06-23 F630230
Biopsies 2013-07-31 F630230
Biopsies 2013-11-28 F630230
Biopsies 2014-03-06 F630230
RFA 2015-01-12 F630230
EMR 2016-06-13 F630230
Biopsies 2013-04-08 G244224
EMR 2013-06-25 G244224
EMR 2017-03-22 G244224
EMR 2017-04-14 G244224
RFA 2017-04-30 G244224
Biopsies 2013-02-08 I927282
EMR 2013-07-03 I927282
Biopsies 2013-12-06 I927282
RFA 2015-08-25 I927282
RFA 2013-05-14 J322909
Biopsies 2017-03-28 J322909
EMR 2013-04-05 K522332

But actually we want only those patients who have had and EMR followed by RFA. lead() means the leading row ie the row that leads to the next row (which should contain RFA in the proc column).

Upstage<-df %>%
group_by(HospNum_Id)%>%
mutate(ind = proc=="RFA" & lead(proc)=="EMR") %>%
arrange(HospNum_Id,dat)

#Only show the first 25 samples
kable(head(Upstage,25))
proc dat HospNum_Id ind
RFA 2014-01-03 D0739033 NA
Biopsies 2014-12-17 D0739033 FALSE
Biopsies 2014-12-27 D0739033 FALSE
Biopsies 2015-06-16 D0739033 FALSE
Biopsies 2016-04-08 D0739033 FALSE
RFA 2016-10-25 D0739033 FALSE
Biopsies 2017-04-10 D0739033 FALSE
Biopsies 2013-06-23 F630230 FALSE
Biopsies 2013-07-31 F630230 FALSE
Biopsies 2013-11-28 F630230 FALSE
Biopsies 2014-03-06 F630230 FALSE
RFA 2015-01-12 F630230 FALSE
EMR 2016-06-13 F630230 FALSE
Biopsies 2013-04-08 G244224 FALSE
EMR 2013-06-25 G244224 FALSE
EMR 2017-03-22 G244224 FALSE
EMR 2017-04-14 G244224 FALSE
RFA 2017-04-30 G244224 TRUE
Biopsies 2013-02-08 I927282 FALSE
EMR 2013-07-03 I927282 FALSE
Biopsies 2013-12-06 I927282 FALSE
RFA 2015-08-25 I927282 FALSE
RFA 2013-05-14 J322909 FALSE
Biopsies 2017-03-28 J322909 FALSE
EMR 2013-04-05 K522332 FALSE

But that simply gives those values where consecutive rows show EMR and then RFA but we want it for patients so we have to do something a little more complex. We use the fact that the mutate column is boolean (so gives us a TRUE or FALSE return value) and we ask to return only those hospital numbers where all those values where this is true (and we also sort it).

Upstage<-df %>%
  group_by(HospNum_Id)%>%
 mutate(ind = proc=="EMR" & lead(proc)=="RFA") %>%
   slice(sort(c(which(ind),which(ind)+1)))%>%
  arrange(HospNum_Id,dat)
kable(Upstage)
proc dat HospNum_Id ind
EMR 2013-12-21 O349253 TRUE
RFA 2017-02-12 O349253 FALSE
EMR 2015-03-06 P029834 TRUE
RFA 2015-05-02 P029834 FALSE
EMR 2014-08-01 P223311 TRUE
RFA 2016-07-05 P223311 FALSE
EMR 2014-07-18 P223333 TRUE
RFA 2015-02-10 P223333 FALSE
EMR 2013-04-03 P433224 TRUE
RFA 2013-12-25 P433224 FALSE
RFA 2016-03-30 S141141 FALSE
EMR 2017-01-12 S141141 TRUE
RFA 2013-09-24 S553322 FALSE
EMR 2015-10-25 S553322 TRUE
EMR 2015-10-06 T432452 TRUE
RFA 2016-08-28 T432452 FALSE
RFA 2013-04-06 U234252 FALSE
EMR 2014-06-14 U234252 TRUE