Some tips and tricks for working with date and time data

[ernestguevarra]

Using an example real world data on malaria containing data on rainfall (in mm) and the number of cases of malaria reported from health centres in an administrative district of Ethiopia between July 1997 and July 1999.

The dataset is available from the Oxford IHTM teaching datasets repository and can be read into R as follows:

malaria <- read.table("https://raw.githubusercontent.com/OxfordIHTM/teaching_datasets/main/malaria.dat", header = TRUE)

The dataset looks like this (all records):

     Time Cases  Rain
1  Jul-97   997  68.5
2  Aug-97   824 162.1
3  Sep-97   573 138.8
4  Oct-97   586 222.2
5  Nov-97   523 115.5
6  Dec-97   968  37.2
7  Jan-98   985  96.6
8  Feb-98   745  99.1
9  Mar-98   520  51.2
10 Apr-98   406  80.0
11 May-98   523 112.4
12 Jun-98   560 183.7
13 Jul-98   671 101.0
14 Aug-98   667 252.5
15 Sep-98   768  40.8
16 Oct-98   990 193.7
17 Nov-98   775  17.5
18 Dec-98   833   0.0
19 Jan-99   672  33.5
20 Feb-99   505   0.0
21 Mar-99   320 106.8
22 Apr-99   274 117.4
23 May-99   263 175.8
24 Jun-99   264 187.5
25 Jul-99   179 283.5

For this guide, we will focus on how we work with the date information found in the Time variable in the dataset.

In this dataset, the date information is recorded in character class. You can check this by using the class() function:

class(malaria$Time)
[1] "character"

As a general principle, we would like to keep date data in date class because this is the format in which R recognises how to handle this data appropriately. There are many implications of this but in this guide, we will show first what the implication of not having date information in date class when it comes to plotting.

The malaria dataset is a time series dataset and the most basic analysis we can perform on this dataset is to create a time series plot to show trend of cases and/or trend of rainfall over time (per month). If we create this plot using base R plotting functions without processing the Time variable (keeping it as is), we get the following:

plot(x = malaria$Time, y = malaria$Cases, type = "l")

we get the following error:

Error in plot.window(...) : need finite 'xlim' values
In addition: Warning messages:
1: In xy.coords(x, y, xlabel, ylabel, log) : NAs introduced by coercion
2: In min(x) : no non-missing arguments to min; returning Inf
3: In max(x) : no non-missing arguments to max; returning -Inf

This error indicates that R doesn't know how to plot/deal with the Time variable as it is hence it is complaining about the x values.

So, we need to sort of process that Time variable so that we can use it for plotting. The characteristics of what the Time variable should be is that it should be able to be recognised by R as data point that has a chronological order (i.e., months go from January to December and then years go from lowest year to highest year, and if the data has days, then lowest day to highest day). The data type that can be used that would give the Time variable these characteristics is the Date class. To read more about this data type, issue ?as.Date on your R console to read the help file.

So, we will now transform the Time variable into a date rather than a character class as follows:

malaria$Time <- as.Date(paste(01, malaria$Time, sep = "-"), format = "%d-%b-%y")

and checking the data type of Time variable, we get:

class(malaria$Time)
[1] "Date"

now, this should address the issue with the plotting. So, we try plotting again and we get:

plot(x = malaria$Time, y = malaria$Cases, type = "l")

This plot looks a lot more like what we expect.

[ernestguevarra]

Using dependencies to deal with date data types

This is where package dependencies, in my opinion, give a lot more utility for dealing with date data types.

Here is example code to deal with datasets with date data types using package dependencies:

library(dplyr)          ## for data wrangling
library(tidyr)          ## for data wrangling adjunct to dplyr
library(lubridate)      ## for workign with date data type
library(ggplot2)        ## for plotting

malaria <- read.table("https://raw.githubusercontent.com/OxfordIHTM/teaching_datasets/main/malaria.dat", header = TRUE)

malaria <- malaria %>%
  dplyr::mutate(Time = my(Time)) %>%
  tidyr::pivot_longer(Cases:Rain, names_to = "variable", values_to = "n")

malaria %>%
  dplyr::filter(variable == "Cases") %>%
  ggplot(mapping = aes(x = Time, y = n, group = variable)) +
  geom_line() +
  scale_x_date(
    breaks = seq(from = min(malaria$Time), to = max(malaria$Time), by = "2 month"),
    labels = paste(
      seq(from = min(malaria$Time), to = max(malaria$Time), by = "2 month") %>% 
        lubridate::month(label = TRUE) %>%
        as.character(), 
      seq(from = min(malaria$Time), to = max(malaria$Time), by = "2 month") %>% 
        lubridate::year() %>%
        as.character()
    )
  ) +
  scale_y_continuous(
    breaks = seq(
      from = 0, to = max(malaria$n[malaria$variable == "Cases"]), by = 100
    )
  ) +
  labs(
    title = "Malaria cases over time",
    subtitle = "July 1997 to July 1999",
    x = NULL, y = "n"
  ) +
  theme_bw() +
  theme(axis.text.x.bottom = element_text(angle = 90, vjust = 0.5, hjust = 1))

This gives the following plot:

[JosephineCarenNdawula]

Thanks Ernest.