CODEWITHSUNDEEP
rdata.table
psysky
psysky

Reputation: 3195

how delete rows which are not completely duplicated in R

i have data example

the first

resp=structure(list(person_number = c(914198L, 914198L, 914198L, 914198L, 
914198L, 957505L, 957505L, 957505L, 957505L, 957505L, 967216L, 
967216L, 967216L, 967216L, 967216L, 27771498L, 27771498L, 27771498L, 
27771498L, 27771498L, 957505L, 957505L, 957505L, 914198L, 967216L, 
967216L, 914198L, 967216L, 914198L), position_code = c(50000690L, 
50000690L, 50000690L, 50000690L, 50000690L, 50000690L, 50000690L, 
50000690L, 50000690L, 50000690L, 50000690L, 50000690L, 50000690L, 
50000690L, 50000690L, 801L, 801L, 801L, 801L, 801L, 50000690L, 
50000690L, 50000690L, 50000690L, 50000690L, 50000690L, 50000690L, 
50000690L, 50000690L), date = c(7L, 2L, 1L, 4L, 5L, 6L, 3L, 4L, 
5L, 2L, 3L, 5L, 1L, 6L, 7L, 7L, 2L, 6L, 4L, 1L, 6L, 3L, 4L, 1L, 
3L, 5L, 4L, 7L, 5L), start_hour = c(9L, 9L, 11L, 9L, 9L, 9L, 
9L, 11L, 9L, 9L, 9L, 11L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 9L, 12L, 
15L, 10L, 9L, 11L, 10L, 11L, 10L, 9L), end_hour = c(21L, 21L, 
21L, 15L, 15L, 21L, 21L, 21L, 21L, 21L, 21L, 21L, 21L, 21L, 21L, 
19L, 19L, 19L, 19L, 19L, 21L, 21L, 19L, 21L, 21L, 21L, 21L, 21L, 
21L)), class = "data.frame", row.names = c(NA, -29L))

let me show the clear example so that you can understand what i need help. Dataset reps and person_number = 957505

person_number   position_code   date    start_hour  end_hour
957505  50000690    6   9   21
957505  50000690    3   9   21
957505  50000690    4   11  21
957505  50000690    5   9   21
957505  50000690    2   9   21
957505  50000690    6   12  21
957505  50000690    3   15  21
957505  50000690    4   10  19

here we see that date = 6 occurs 2 times and the range is from 9-21 and from 12-21 also we see date = 4 occurs 2 times too, and the range start-end hours 11-21,11-19 it mean that i need to randomly delete an observation with a duplicate date but a different range. I.E I need to delete any one observation for date = 6 and any one for date = 4

like that

person_number   position_code   date    start_hour  end_hour

957505  50000690    3   9   21
957505  50000690    5   9   21
957505  50000690    2   9   21
957505  50000690    6   12  21
957505  50000690    3   15  21
957505  50000690    4   10  19

however, there are such cases

person_number   position_code   date    start_hour  end_hour
957505  50000690    6   9   21
957505  50000690    3   9   21
957505  50000690    4   11  21
957505  50000690    5   9   21
957505  50000690    2   9   21
957505  50000690    6   12  21
957505  50000690    3   15  21
957505  50000690    4   10  19

we see, for example, here date = 3 has duplicates 1 range start_hour end_hour from 9-21, and another 15-21 but the range from 15-21 for this person_number is no longer duplicated, but 9-21 is duplicated more than 2 times for this personal_number

957505  50000690    6   9   21
957505  50000690    3   9   21

957505  50000690    5   9   21
957505  50000690    2   9   21

it occurs here 4 times, so for date = 3 we delete 9-21. because the range from 15-21 is not duplicated 2 or more times. It must be left. for any other conditions not specified, this part of the code applies here we see that date = 6 occurs 2 times and the range is from 9-21 and from 12-21 also we see date = 4 occurs 2 times too, and the range start-end hours 11-21,11-19 it mean that i need to randomly delete an observation with a duplicate date but a different range. I.E I need to delete any one observation for date = 6 and any one for date = 4 How can i delete rows by such condtions? any help appreciated. Thank you.

Upvotes: 0

Views: 74

Answers (1)

Nechoj
Nechoj

Reputation: 1582

Here is an idea how to do such filtering using library dplyr:

library(dplyr)

# resp2 will contain all rows with at least double dates
multiple_date <- resp %>% count(person_number, date) %>% filter(n>1)
resp2 <- semi_join(resp, multiple_date)
# show all of resp2
resp2
# show difference between resp and resp2
anti_join(resp, resp2)

# compare resp with resp2 specifically for person 957505
resp %>% filter(person_number == 957505)
resp2 %>% filter(person_number == 957505)

# resp3 will contain all rows with at least double hour range
multiple_hour <- resp %>% count(person_number, start_hour, end_hour) %>% filter(n>1) 
resp3 <- semi_join(resp, multiple_hour)

# compare resp with resp3 specifically for person 957505
resp3 %>% filter(person_number == 957505)
resp %>% filter(person_number == 957505)

# resp4 will contain all rows that have at least double date and at least double hour range
resp4 <- semi_join(semi_join(resp, resp2), resp3)

# compare resp with resp4 specifically for person 957505
resp4 %>% filter(person_number == 957505)
resp %>% filter(person_number == 957505)

# remove rows that have at least double date and at least double hour range
final <- anti_join(resp, resp4)

# compare resp with final specifically for person 957505
final %>% filter(person_number == 957505)
resp %>% filter(person_number == 957505)

# check how many entries with double date have been left
final %>% count(person_number, date) %>% filter(n>1)

Upvotes: 1

Related Questions