Search code examples
rggplot2time-seriesboxplottimeserieschart

How to assign category to specific rows in R data frame, in a new column?

In a time series dataset, what would be a good way to group sets of rows such that they have a unique identifier in a new column? For instance (in a very abridged manner), taking this:

library(tidyverse)

data <- read_csv("snippet.csv")

print(data,n=29)

# A tibble: 29 x 5
   Port        Timestamp             MultiPort dev_value dev_unit
   <chr>       <chr>                 <chr>         <dbl> <chr>   
 1 PortConRef1 2/26/2020 12:39:40 PM n            -38.1  ‰       
 2 PortConRef1 2/26/2020 12:39:41 PM n            -38.0  ‰       
 3 PortConRef1 2/26/2020 12:39:42 PM n            -38.2  ‰       
 4 PortConRef1 2/26/2020 12:39:43 PM n            -38.1  ‰       
 5 PortConRef1 2/26/2020 12:39:44 PM n            -38.3  ‰       
 6 PortConRef1 2/26/2020 12:39:45 PM n            -37.9  ‰       
 7 PortConRef1 2/26/2020 12:39:46 PM n            -38.3  ‰       
 8 PortRef1    2/26/2020 12:40:48 PM n             -9.82 ‰       
 9 PortRef1    2/26/2020 12:40:49 PM n            -10.2  ‰       
10 PortRef1    2/26/2020 12:40:50 PM n             -9.75 ‰       
11 PortRef1    2/26/2020 12:40:51 PM n             -9.89 ‰       
12 PortRef1    2/26/2020 12:40:52 PM n            -10.1  ‰       
13 PortRef1    2/26/2020 12:40:53 PM n            -10.1  ‰       
14 PortRef1    2/26/2020 12:40:54 PM n            -10.3  ‰       
15 PortSampleB 2/26/2020 12:51:14 PM n             -5.13 ‰       
16 PortSampleB 2/26/2020 12:51:15 PM n             -4.70 ‰       
17 PortSampleB 2/26/2020 12:51:16 PM n             -4.90 ‰       
18 PortSampleB 2/26/2020 12:51:17 PM n             -5.03 ‰       
19 PortSampleB 2/26/2020 12:51:18 PM n             -4.76 ‰       
20 PortSampleB 2/26/2020 12:52:50 PM y             -5.15 ‰       
21 PortSampleB 2/26/2020 12:52:51 PM y             -4.97 ‰       
22 PortSampleB 2/26/2020 12:52:52 PM y             -5.11 ‰       
23 PortSampleB 2/26/2020 12:52:53 PM y             -4.71 ‰       
24 PortSampleB 2/26/2020 12:58:49 PM y             -5.19 ‰       
25 PortSampleB 2/26/2020 1:00:21 PM  n             -4.75 ‰       
26 PortSampleB 2/26/2020 1:00:22 PM  n             -5.20 ‰       
27 PortSampleB 2/26/2020 1:00:23 PM  n             -4.95 ‰       
28 PortSampleB 2/26/2020 1:00:24 PM  n             -5.06 ‰       
29 PortSampleB 2/26/2020 1:00:25 PM  n             -4.81 ‰ 

# Remove reference gas rows
data2 <- data %>% 
  filter(`Port`=="PortSampleB")

# Convert timestamp column to useable time
library(lubridate)
data2 <- data2 %>% 
  mutate(
    time=mdy_hms(`Timestamp`))

> print(data2,n=15)
# A tibble: 15 x 6
   Port        Timestamp             MultiPort dev_value dev_unit time               
   <chr>       <chr>                 <chr>         <dbl> <chr>    <dttm>             
 1 PortSampleB 2/26/2020 12:51:14 PM n             -5.13 ‰        2020-02-26 12:51:14
 2 PortSampleB 2/26/2020 12:51:15 PM n             -4.70 ‰        2020-02-26 12:51:15
 3 PortSampleB 2/26/2020 12:51:16 PM n             -4.90 ‰        2020-02-26 12:51:16
 4 PortSampleB 2/26/2020 12:51:17 PM n             -5.03 ‰        2020-02-26 12:51:17
 5 PortSampleB 2/26/2020 12:51:18 PM n             -4.76 ‰        2020-02-26 12:51:18
 6 PortSampleB 2/26/2020 12:52:50 PM y             -5.15 ‰        2020-02-26 12:52:50
 7 PortSampleB 2/26/2020 12:52:51 PM y             -4.97 ‰        2020-02-26 12:52:51
 8 PortSampleB 2/26/2020 12:52:52 PM y             -5.11 ‰        2020-02-26 12:52:52
 9 PortSampleB 2/26/2020 12:52:53 PM y             -4.71 ‰        2020-02-26 12:52:53
#...
10 PortSampleB 2/26/2020 12:58:49 PM y             -5.19 ‰        2020-02-26 12:58:49
11 PortSampleB 2/26/2020 1:00:21 PM  n             -4.75 ‰        2020-02-26 13:00:21
12 PortSampleB 2/26/2020 1:00:22 PM  n             -5.20 ‰        2020-02-26 13:00:22
13 PortSampleB 2/26/2020 1:00:23 PM  n             -4.95 ‰        2020-02-26 13:00:23
14 PortSampleB 2/26/2020 1:00:24 PM  n             -5.06 ‰        2020-02-26 13:00:24
15 PortSampleB 2/26/2020 1:00:25 PM  n             -4.81 ‰        2020-02-26 13:00:25    

#note that data from the original dataset has been removed between rows 9 and 10 to ease reproducibility

and giving each section (defined by row number or 'time') a unique letter category. In the abridged example above, there is a gap in 1 hz data collection between rows 5 and 6 corresponding to multiport switch from "n" to "y". This pattern repeats every six minutes such that in the full dataset there are eight alternating 6 minute groups of "n" and "y" with 90 seconds between. As it is 1 hz data, each 6 minute group has 360 rows.

I'd like each six minute y and n period to have a different letter category, such as "a" through "h".

The goal is to have a separate boxplot for the data from each time period to plot on top of the raw data, which as it stands looks like this:

time series

Solution

  • We can use rleid from data.table to get a unique number every time MultiPort changes and use it to index predefined letters vector. 

    library(dplyr)
df %>% mutate(cat = letters[data.table::rleid(MultiPort)])