Remove observation based a given condition

Question

I would like to remove specific observation from a data set. Each observation has a serial (identification number), Day (week days from Mon.- Sun) 7 variables day1 to day7 each representing a day of the week. Day1 is Mon and Day7 is Sun.

I would like delete those observations (serials) where the day1...day7 equals zero. This is the case of id 12 where during Monday no observation was made (eg. Day matched with day1 returns zero). In the case of 123 there was 3 observation recorded on Tuesday, I would like to keep this serial.

I tried to convert the data to long and based on this to do a matching without success.

Input:

  serial  day1 day2 day3 day4 day5 day6 day7 Day 
    12    0    1    2    1    1    3    1   Monday   
   123    0    3    0    3    3    0    3   Tuesday  
    10    0    3    0    3    3    3    3   Thursday

Output:

serial  day1 day2 day3 day4 day5 day6 day7 Day 
  123    0    3    0    3    3    0    3   Tuesday

Sample data

structure(list(serial = c(12, 123, 10), day1 = c(0, 0, 0), day2 = c(1, 
3, 3), day3 = c(2, 0, 0), day4 = c(1, 3, 3), day5 = c(1, 3, 3
), day6 = c(3, 0, 3), day7 = c(1, 3, 3), Day = structure(c(1L, 
3L, 2L), .Label = c("Monday", "Thursday", "Tuesday"), class = "factor")), row.names = c(NA, 
3L), class = "data.frame")

@NelsonGon many thanks I tried to mutate the data and to match Days., — user10974052
you said delete observations where day1 or day 7 equal 0. serial 123 has a condition where day1 is 0 why didn't it get deleted — Golden Lion
create a second dataframe where matching conditions are add into it. — Golden Lion

Rui Barradas Rui Barradas · Accepted Answer · 2020-04-18T16:10:02

The following code does what the question asks for.
It uses package DescTools to have a vector of weekday names and matches the values in column Day to that vector. Then pastes the string "day" with the day numbers and uses these strings to get the observations and keep the rows where they are not zero.

j <- match(df1$Day, DescTools::day.name)
j <- paste0("day", j)
df1[diag(as.matrix(df1[, j])) != 0, ]
#  serial day1 day2 day3 day4 day5 day6 day7      Day
#2    123    0    3    0    3    3    0    3  Tuesday
#3     10    0    3    0    3    3    3    3 Thursday

Remove observation based a given condition

1 Answers