Distance from a given variable

Question

I would like to return the position of an variable from a data frame. The data with has a serial (identification number), Day1 (week days from Mon.- Sun) that records the starting day of an observation and 7 variables day1 to day7 each representing a day of the week. Day1 is Mon and Day7 is Sun.

There is another WkDay2 variable that is used to record the position of an observation during the week.

I would like to identify the position of any observation. The starting day is recorded in WkDay1. For example in the case of id 12 the observation started on Monday and a new observation was recorded on Wednesday - on the Third day. In the case of id 123 the observation stared on Tuesday and a new observation was recorded on the same day - or first day. Finally, id 10, observation was made on Thursday and a new on the 4th day (after WDay1) or Sunday.

Input:

  serial  day1 day2 day3 day4 day5 day6 day7 WkDay1 WkDay2
    12    2    1    2    1    1    3    1   Monday   Wednesday
   123    0    3    0    3    3    0    3   Tuesday  Tuesday
    10    0    3    3    3    3    3    3   Thursday Sunday

Output:

serial  Position
    12      Third    
    123     First  
    10      Fourth

akrun akrun · Accepted Answer · 2020-04-17T21:53:00

We can create a mapping with the 'wkdays' vector using match, then with Map, get the sequence from 'WkDay1' to 'WkDay2', check the length and convert it to ordinal (from english)

library(english)
wkday <- c("Monday", "Tuesday", "Wednesday", "Thursday",
              "Friday", "Saturday", "Sunday")
m1 <- match(df1$WkDay1, wkday)
m2 <- match(df1$WkDay2, wkday)
Position <- unlist( Map(function(i, j, x) as.character(ordinal(length(i:j))),
             m1, m2, asplit(df1[startsWith(names(df1), "day")], 1)))
cbind(df1['serial'], Position = tools::toTitleCase(Position))
# serial Position
#1     12    Third
#2    123    First
#3     10   Fourth

Or with tidyverse

library(dplyr)
library(tidyr)
df1 %>% 
     pivot_longer(cols = starts_with('day'), names_to = 'day') %>% 
     filter(value  > 0) %>%
     group_by(serial) %>%
     summarise(Position = tools::toTitleCase(as.character(ordinal(length(
          match(first(WkDay1), 
          wkday):match(first(WkDay2), wkday))))))
# A tibble: 3 x 2
#   serial Position
#    <int> <chr>   
#1     10 Fourth  
#2     12 Third   
#3    123 First

data

df1 <- structure(list(serial = c(12L, 123L, 10L), day1 = c(2L, 0L, 0L
), day2 = c(1L, 3L, 3L), day3 = c(2L, 0L, 3L), day4 = c(1L, 3L, 
3L), day5 = c(1L, 3L, 3L), day6 = c(3L, 0L, 3L), day7 = c(1L, 
3L, 3L), WkDay1 = c("Monday", "Tuesday", "Thursday"),
WkDay2 = c("Wednesday", 
"Tuesday", "Sunday")), class = "data.frame", row.names = c(NA, 
-3L))

Distance from a given variable

1 Answers

data