Identify consecutive occurances and merge two data farmes

Question

This question relates to one posted earlier.

I have 2 data.frames that I would like to merge. The two data.frames have different sizes (eg. dim (df1) =16533, 580 and dim(df2) = 2820 , 675`).

The records were made on different days by different person/group of persons.

Variables from df1

• Index = the group of person who made the record (eg. it can represent 1 person or 2 or more)

• id1 = the person from the group who made the recording (eg. 12 1 =group 12 person 1; 12 2 = group 12 person 2, etc. )

• id2 = the first or the second day when the record was made (eg. 12 1 1 = group 12, person 1, 1 day; 12 1 2 = group 12, person 1, 2 day;)

• Day = the weekday when the diary record was made (eg. 12 1 1 Wednesday =group 12, person 1, day 1, weekday Wednesday; 12 1 2 Sunday = group 12, person 1, day 1 , weekday Sunday)

  These variables are followed by 24h observations

obs1_1-obs1_144=primary observation obs2_1-obs2_144=secondary observations obs3_1-obs3_144=tertiary observations obs4_1-obs4_144=quarterly observations

Example of

df1

index id1 id2  Day           obs1_1...obs1_144....obs2_1...obs2_144...obs3_1...obs3_144...obs4_1...obs4_144
 12    1   1   Wednesday    1      11          12
 12    1   2   Sunday       2      0           0
123    1   1   Tuesday      1      0           1
123    1   2   Saturday     3      0           3
123    2   1   Monday       2      2           4
123    2   2   Saturday     1      0           8

In df2 observations were recorded just based on index and id1. There is just one observation per person. Similarly here there is also a Day variable that records when the recordings started (eg. not the day of the recordings). For example here id 12 1 Tuesday would suggest that group 12 person 1 started to record observations from Tuesday.

The week is divided as:

           Monday = 95 variables starting from day11-day196 
(in the actual data t0400_0415_d1-t0345_0400_d1)
            Tuesday = 95 variables starting day21-day296 
    (in the actual data t0400_0415_d2-t0345_0400_d2)
            Wednesday = 95 variables starting day31-day396
    (in the actual data t0400_0415_d3-t0345_0400_d3)
            Thursday = 95 variables starting day41-day496
    (in the actual data t0400_0415_d4-t0345_0400_d4)
            Friday = 95 variables starting day51-day596
    (in the actual data t0400_0415_d5-t0345_0400_d5)
            Saturday = 95 variables starting day61-day696
    (in the actual data t0400_0415_d6-t0345_0400_d6)
            Sunday = 95 variables starting day71-day796
    (in the actual data t0400_0415_d7-t0345_0400_d7)

Example of df2

index   id1  Day       day11 day12 day13 day14 day15 day16  day17  .....day196......day796
 12      1    Tuesday     2    1    2    1    1    3    1    
123      1    Friday      0    3    0    3    3    0    3  

I would like to identify the observations from df2 that were recorded on the same day as in df1.

What I aim for:

1. df2 to identify consecutive records (there is no gap between the daily records). For example a consecutive record would be : Recording started on Tuesday and there is records on Wednesday, Thursday Friday. This is call as three consecutive record. A non-consecutive record would be if a record started on Tuesday and there is a record on Wednesday and Friday. As there is a gap day this is a non-consecutive recording.

2. df1 I would like to identify the index and id1 of the person who made the consecutive records as well the position of the record in the consecutive observation (eg. in a 3 consecutive observation the observation could fall on the 1,2 or 3 day)Post related to one of my question

Outcome:

 index id1 id2   obs1 obs2 obs3 
 12      1   1     1   11    12   
 12      1   2     2    0     0
 123     1   2     3    0     3        
 123     2   2     1    0     8

Sample data

df1:

structure(list(index = c(12, 12, 123, 123, 123, 123), id1 = c(1, 
1, 1, 1, 2, 2), id2 = c(1, 2, 1, 2, 1, 2), Day = structure(c(5L, 
3L, 4L, 2L, 1L, 2L), .Label = c("Monday", "Saturday", "Sunday", 
"Tuesday", "Wednesday"), class = "factor"), obs1 = c(1, 2, 1, 
3, 2, 1), obs2 = c(11, 0, 0, 0, 2, 0), obs3 = c(12, 0, 1, 3, 
4, 8)), class = "data.frame", row.names = c(NA, -6L))

df2:

  structure(list(index = c(12, 123), id1 = c(1, 1), Day = structure(2:1, .Label = c("Friday", 
    "Tuesday"), class = "factor"), day1 = c(2, 0), day2 = c(1, 3), 
        day3 = c(2, 0), day4 = c(1, 3), day5 = c(1, 3), day6 = c(3, 
        0), day7 = c(1, 3)), class = "data.frame", row.names = c(NA, 
    -2L))

Answer 1

We can do this with Map to create a key/value named vector and then do the matching with the column names

lst1 <- Map(`:`, seq(11, 71, by = 10), seq(196, 796, by = 100))
names(lst1) <- c('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')
out <- stack(lst1)[2:1]
out$values <- paste0('day', out$values)

-checking

setNames(as.character(out$ind), out$values)[c('day41', 'day182', 'day242', 'day724')]
#   day41    day182    day242    day724 
# "Monday"  "Monday" "Tuesday"  "Sunday"