Organizing strings contained in df.

General
#1

Hello I am having issues by separating the strings contained in a df

Having the following df (HOUSE)

rooms_and_items <- c("bed", "bed", "carpet", "chair", "BEDROOM", "table", "oven", "KITCHEN", "GARAGE", "keys", "GARDEN")
surface <- c(1,1,2,2,6,1,1,2,10,1,15)

HOUSE<- data.frame(rooms_and_items, surface)
View(HOUSE)

and knowing that the items contained in "rooms_and_items" are defied by the following vectors of strings

BEDROOM <- c("bed", "carpet", "chair")
## Corrected Error in the line Below error, *KITCHEN <- c("fork", "knive")*
KITCHEN  <- c("table", "oven")
GARAGE <- "GARAGE"
GARDEN <- "GARDEN"

I want to make separate columns for "rooms" and "items" so:

  • Check one by one the values in rooms_and_things and if they are contained in the vectors, give back the names of the vectors in a new column called "rooms"
  • If there is a value that is not contained in the defined vector, put a "unknown" in the column "rooms"
  • NOTE THAT the values of the rooms contain the total values of the items in them; BEDROOM = 6 (bed + bed + 2 carpet + 2 chair); KITCHEN = 2 (table + oven )
  • GARAGE and GARDEN should last as it only has one data of surface.

The final result i am looking should look like this:

rooms<- c("BEDROOM", "BEDROOM", "BEDROOM", "BEDROOM", "KITCHEN", "KITCHEN", "GARAGE", "unkwnown", "GARDEN")
items<- c("bed", "bed", "carpet", "chair", "table", "oven", "GARAGE", "keys", "GARDEN") 
surface_2 <- c(1,1,2,2,1,1,10,1,15)
HOUSE_fin <- data.frame(rooms,items,surface_2)
View(HOUSE_fin)

Thank you in advance

#2

The reason that the results are not what is being sought is that chair and table are not in KITCHEN

rooms_and_items <- c("bed", "bed", "carpet", "chair", "BEDROOM", "table", "oven", "KITCHEN", "GARAGE", "keys", "GARDEN")
surface <- c(1,1,2,2,6,1,1,2,10,1,15)

HOUSE<- data.frame(rooms_and_items, surface)
BEDROOM <- c("bed", "carpet", "chair")
KITCHEN <- c("fork", "knive")
GARAGE <- "GARAGE"
GARDEN <- "GARDEN"
G <- c(GARAGE,GARDEN)
rooms<- c("BEDROOM", "BEDROOM", "BEDROOM", "BEDROOM", "KITCHEN", "KITCHEN", "GARAGE", "unkwnown", "GARDEN")
items<- c("bed", "bed", "carpet", "chair", "table", "oven", "GARAGE", "keys", "GARDEN") 
surface_2 <- c(1,1,2,2,1,1,10,1,15)
target <- data.frame(rooms,items,surface_2)

HOUSE_fin <- dplyr::anti_join(HOUSE,HOUSE[HOUSE[,1] %in% c("BEDROOM","KITCHEN"),])
#> Joining with `by = join_by(rooms_and_items, surface)`

rooms <- rep("unkown",dim(HOUSE_fin)[1])
colnames(HOUSE_fin)[1] <- "items"
HOUSE_fin <- cbind(rooms,HOUSE_fin)
HOUSE_fin[HOUSE_fin$items %in% BEDROOM,1] <- "BEDROOM"
HOUSE_fin[HOUSE_fin$items %in% KITCHEN,1] <- "KITCHEN"
HOUSE_fin[HOUSE_fin$items %in% G,1] <- HOUSE_fin[HOUSE_fin$items %in% G,2]

identical(HOUSE_fin,target)
#> [1] FALSE

Created on 2023-09-17 with reprex v2.0.2