Interval numbers in matrix

Question

I have a data frame with 3523 observation and 92 variables.

Below an example of a data frame with 6; the 24h recording of the observations starts at 4:00am and ends 4:00am .

  04:00   04:15   04:30 05:00  ...  04:35
1  -       -     -       -     ...  -
2  2       2     2       -     ...  -
3  2       -     -       2     ...  -
4  -       -     2       -     ...  -
5  -       -     -       -     ...  -
6  -       -     -       -     ...  2

Each row contain values '-' and '2'.

I want to extract the beginning and the ending of the intervals starting with: '2'

For example 2: 04:15-04:30; 
            3: 04:00 ; 05:00
            4: 04:30

Thank you

Please add a reproducible example. Take a look at other posts; we need to have a dataframe that we can reproduce ourselves (perhaps make a simple table and copy paste it here), and also clear output. — arg0naut91, Nov 11 '18 at 09:53

arg0naut91 · Accepted Answer · 2018-11-11T18:02:39.153

Let's expand a bit your example. In the expanded example, we can note that there is no 2 for the row number 1, and that there are also several trickier ones, like for example row 6 where we have 2, then a break (-), after that a sequence of two 2s, a -, and a 2 again.

    04:00 04:15 04:30 05:00 05:15 05:30
 1:     -     -     -     -     -     -
 2:     2     2     2     -     2     2
 3:     2     -     -     2     2     2
 4:     -     -     2     -     2     2
 5:     -     -     -     -     2     2
 6:     2     -     2     2     -     2
 7:     -     -     -     -     2     2
 8:     2     2     -     2     2     2
 9:     -     -     -     -     2     2
10:     2     2     -     2     2     2

You can reproduce it if you type in:

WorkSchedulesDay1 <- structure(list(`04:00` = c("-", "2", "2", "-", "-", "2", "-", 
"2", "-", "2"), `04:15` = c("-", "2", "-", "-", "-", "-", "-", 
"2", "-", "2"), `04:30` = c("-", "2", "-", "2", "-", "2", "-", 
"-", "-", "-"), `05:00` = c("-", "-", "2", "-", "-", "2", "-", 
"2", "-", "2"), `05:15` = c("-", "2", "2", "2", "2", "-", "2", 
"2", "2", "2"), `05:30` = c("-", "2", "2", "2", "2", "2", "2", 
"2", "2", "2")), row.names = c(NA, -10L), class = c("data.table", 
"data.frame"))

After that you apply the code:

WorkSchedulesDay1 <- WorkSchedulesDay1 %>% 
  group_by(rn = row_number()) %>% 
  gather(time, val, 1:6) %>%
  arrange(time) %>%
  mutate(tmp = cumsum(coalesce(val != lag(val), FALSE))) %>% arrange(rn) %>%
  filter(!val == "-") %>%
  group_by(rn, tmp) %>%
  mutate(
    time = case_when(
      n() > 1 ~ paste(min(time), max(time), sep = " - "),
      TRUE ~ time
    )
  ) %>%
  ungroup() %>% distinct(rn, tmp, time) %>%
  group_by(rn) %>%
  mutate(
    intervals = case_when(
      n() > 1 ~ paste(time, collapse = ", "),
      TRUE ~ time
    )
  ) %>% distinct(rn, intervals) %>%
  write_csv("WorkSchedulesDay1.csv")

You will see that what you get is:

     rn intervals                   
  <int> <chr>                       
     2 04:00 - 04:30, 05:15 - 05:30
     3 04:00, 05:00 - 05:30        
     4 04:30, 05:15 - 05:30        
     5 05:15 - 05:30               
     6 04:00, 04:30 - 05:00, 05:30 
     7 05:15 - 05:30               
     8 04:00 - 04:15, 05:00 - 05:30
     9 05:15 - 05:30               
    10 04:00 - 04:15, 05:00 - 05:30

There is no record for the row number 1, simply because there are only - in there.

Similarly, there is no record for 05:00 in row number 2, simply because there is a - in there.

In a similar fashion, there is 04:00, 04:30 - 05:00, 05:30 for row number 6, because there are - for 04:15 and 05:15.

Dear @arg0naut tahnk you fro your help and time; but when I am running the code I receive all intervals not just intervals defined by 2s. So for the above example I receive 1: 04:00-05:30; 2:04:00-04:30;05:00; 05:15-05:30; and so on. Could you help me please to receive just the '2s' intervals? Thank you – — RforDummies, Nov 12 '18 at 18:38

Interval numbers in matrix

1 Answers1