Unfilter data frame with conditional statement in R

user_14475827 0
Viewed 92

I Have two different data frames DF1 and DF2. I want to exclude the rows of DF1 which matches with the data frame DF2 and my resulting data frame would look like DF3. Additionally I want Pass the condition as If my Room number is All Rooms then I would be able to match columns Code, Description and Company from DF2 to DF1, if My room number column does not contains All Rooms then it should match the columns Code, Description, Company and Room number.

Code=c("A","B","C","E","D")
Desciption=c("Color is not Good","Odour is not good","Astetic Issue","Odour is not good","Lighting issue")
Company=c("Asian Paints","Burger","Asian Paints","Burger","Burger")
`Room number`=c("Room_1","Room_1","Room_2","Room_3","Room_2")
Rating=c("2","3","5","4","3")

DF1=data.frame(Code,Desciption,Company,`Room number`,Rating)

  Code        Desciption      Company Room.number Rating
1    A Color is not Good Asian Paints      Room_1      2
2    B Odour is not good       Burger      Room_1      3
3    C     Astetic Issue Asian Paints      Room_2      5
4    E Odour is not good       Burger      Room_3      4
5    D    Lighting issue       Burger      Room_2      3

Code=c("A","B")
Desciption=c("Color is not Good","Odour is not good")
Company=c("Asian Paints","Burger")
`Room number`=c("Room_1","All Rooms")

DF2=data.frame(Code,Desciption,Company,`Room number`)


> DF2
  Code        Desciption      Company Room.number
1    A Color is not Good Asian Paints      Room_1
2    B Odour is not good       Burger   All Rooms


Code=c("C","D")
Desciption=c("Astetic Issue","Lighting issue")
Company=c("Asian Paints","Burger")
`Room number`=c("Room_2","Room_2")
Rating=c("5","3")

DF3=data.frame(Code,Desciption,Company,`Room number`,Rating)

> DF3
  Code     Desciption      Company Room.number Rating
1    C  Astetic Issue Asian Paints      Room_2      5
2    D Lighting issue       Burger      Room_2      3

Can someone help me to solve this

dplyr subset dataframe filter r
2 Answers

You have mentioned

Additionally I want Pass the condition as If my Room number is All Rooms then I would be able to match columns Code, Description and Company from DF2 to DF1,..

It is not clear whether in this specific case (all rooms) whether you want to check that description & company across all codes in DF1? If yes, the below syntax will do..

Otherwise, If all combinations have to checked in DF1 across all possible combinations all columns (i.e. code, description & company), the filtered rows will be 0. Please re-check your logic and revise the question accrodingly

DF1 %>% anti_join(DF2, by = c("Code", "Desciption", "Company", "Room.number")) %>%
  anti_join(DF2 %>% filter(Room.number == "All Rooms") %>% 
              mutate(Code = list(unique(DF1$Code))) %>% 
              unnest_longer(Code) , 
            by = c("Code", "Desciption", "Company"))

  Code     Desciption      Company Room.number Rating
1    C  Astetic Issue Asian Paints      Room_2      5
2    D Lighting issue       Burger      Room_2      3
user_28848590

Here is a base R, vectorized way of filtering out the rows matching multiple criteria. It creates logical indices and then subsets DF1 based on those indices. The only difference between DF3b and the expected result DF3 is in the row names, so I set them to consecutive values.

i_all_rooms <- DF1[["Room.number"]] == "All Rooms"
i1 <- !DF1[["Code"]] %in% DF2[["Code"]]
i2 <- !DF1[["Desciption"]] %in% DF2[["Desciption"]]
i3 <- !DF1[["Company"]] %in% DF2[["Company"]]
i4 <- !DF1[["Room.number"]] %in% DF2[["Room.number"]]

j1 <- i_all_rooms & i1 & (i2 | i3)
j2 <- !i_all_rooms & i1 & (i2 | i3) & i4

DF3b <- DF1[j1 | j2, ]
row.names(DF3b) <- NULL

identical(DF3, DF3b)
#[1] TRUE
user_82454060
Related