I have a database of users, who all need a particular user role, in the example below it's "User Role - 3", for each of the firms they represent.
ex <- data.frame('Firm' = c("Firm 1", "Firm 2", "Firm 2", "Firm 2", "Firm 3", "Firm 3", "Firm 4", "Firm 5"),
'User' = c("Person 1", "Person 2", "Person 2","Person 2","Person 3","Person 4", "Person 5", "Person 5"),
'Role' = c("User Role - 1", "User Role - 1","User Role - 2", "User Role - 3", "User Role - 1", "User Role - 3", "User Role - 3", "User Role - 2"))
Each user must have User Role - 3 for each firm the are assigned to. As you can see in the before table some people have multiple roles for single firms, or individuals are assigned to multiple firms. I am trying to filter out all the users who have User Role - 3 for a particular firm, so I am left with those who do not.
As you can see Firm 2 /Person 2 has gone completely as they were assigned multiple roles to the firm , one of which was 3. Where as although person 5 had user role 3, it was only for one of the two firms so they are still on the list for the firm they don't have it for.
I'm kind of stumped where even to start to be honest.
ex %>% gather(Firm)
seems to bring things to key value pairs which isn't applicable here.
ex %>% subset(Role != "User Role - 3")
doesn't take into account that Firm 2 can be removed completely.
I can extract a list of users and firms who have the role ( ex %>% subset(Role == "User Role - 3")
) and can therefore be removed from the original list, which feels like the right place to start, but I don't know how to filter the original list by this new information. When I try and apply the subset to a command then I get an error.
toremove <- subset(ex$Role == "User Role - 3")
Error in subset.default(ex$Role == "User Role - 3") :
argument "subset" is missing, with no default
Try
ex %>%
group_by(Firm, User) %>%
filter(!("User Role - 3" %in% Role))
with output
> ex %>% group_by(Firm, User) %>% filter(!("User Role - 3" %in% Role))
# A tibble: 3 x 3
# Groups: Firm, User [3]
Firm User Role
<chr> <chr> <chr>
1 Firm 1 Person 1 User Role - 1
2 Firm 3 Person 3 User Role - 1
3 Firm 5 Person 5 User Role - 2