business rules from multiple tables

[jak123]

Hi Rcommunity

i have 3 tables of business rules (lets start with 1), and i need to run them through the df LL.

[jak123]

have tried this, but then the first rule gets overwritten when i run the next rule... (join5 df is = LL df)

BOG

join6<- join5 %>% mutate(
rule = ifelse(ID %in% BOGA$ID, "BOGA",0),
lead = ifelse(ID %in% BOGA$ID,BOGA$meanLead, "ML"))

RF

join6<- join5 %>% mutate(
rule = ifelse(ID %in% Ring_Fence$ID, "RF",0),
lead = ifelse(ID %in% Ring_Fence$ID,Ring_Fence$Trader Name`, "ML"))

[JackDavison]

I don't think you've provided Ring_Fence?

[jak123]

ring_fence<- structure(list(customer = c("Rankers International Private Limited",
""
))

sry

[jak123]

the main problem is the overwrite, i need all the rules to be matched and added to LL df. So if boga rule have alrdy been applied and we go to ringfence then ringfence does not overwrite the coumn in LL "trader" and "rule" that have been added by the boga_rule

[nirgrahamuk]

from my perspective you haven't provided a reprex, there aren't common ID's to do a join between LL you shared and boga, but assuming that you do actually have that and succeeded, and you want to have your join and subsequent mutate of rule not overwriting a previous rule if there isnt a match, then rather than 0 as the else condition use the existing rule value.

rule = ifelse(ID %in% BOGA$ID, "BOGA",0)

rule = ifelse(ID %in% BOGA$ID, "BOGA",rule)

to set that up, add a rule column to LL where the starting values are all zero under rule, then you can layer rules on top.

[JackDavison]

@nirgrahamuk is right - there's nothing in common between your three tables. If I had to guess, this would work, but its hard to say when there's nothing to succesfully join by:

library(tidyverse)

LL |>
  tibble() |> 
  rename(ID = customerID) |>
  mutate(ID = as.character(ID)) |> 
  left_join(select(boga, ID, meanLead), by = "ID") |> 
  left_join(select(ring_fence, ID, rf_trader = trader) |> mutate(ID = as.character(ID)), by = "ID") |> 
  mutate(
    rule = case_when(
      !is.na(meanLead) ~ "BOGA",
      !is.na(rf_trader) ~ "RF",
      TRUE ~ "0"
    ),
    lead = case_when(
      !is.na(meanLead) ~ meanLead,
      !is.na(rf_trader) ~ rf_trader,
      TRUE ~ "ML"
    )
  ) |> 
  select(-meanLead, -rf_trader)

[jak123]

Hi both of u and thx ill try it out. the 3 df (LL, boga and ringfence all have ID in common)

how does " |> " work?

[jak123]

Hi JackDavison

thanks a lot!

I am still not sure about the code, can u maybe explain a little of the code in words "|> " and why do u use tible?

[jak123]

why not a %>% operator (i know it doesn't work but what is the difference?)

[JackDavison]

This will run based on what you've provided (there is no ID column in LL so it needs renaming:

LL |>
  tibble() |>
  rename(ID = customerID) |> 
  mutate(ID = as.character(ID)) |>
  left_join(select(boga, ID, meanLead), by = "ID") |>
  left_join(select(ring_fence, ID, rf_trader = trader) |> mutate(ID = as.character(ID)), by = "ID") |>
  left_join(select(SAM, ID, sam_trader = trader) |> mutate(ID = as.character(ID)), by = "ID") |>
  mutate(
    rule = case_when(
      !is.na(meanLead) ~ "BOGA",
      !is.na(rf_trader) ~ "RF",
      !is.na(sam_trader) ~ "SAM",
      TRUE ~ "0"
    ),
    lead = case_when(
      !is.na(meanLead) ~ meanLead,
      !is.na(rf_trader) ~ rf_trader,
      !is.na(sam_trader) ~ sam_trader,
      TRUE ~ "ML"
    )
  ) |>
  select(-meanLead, -rf_trader, -sam_trader)

To unpack:

• tibble() just makes the dataframe easier to look at as the printing method is better than the default data.frame. You can drop it if you'd like.

• |> is the base pipe. You can use %>% instead, in this case they're interchangeable.

• Everything untill the last left_join() is joining on the other tables by the "ID" column. It is important that they're all the same data type, so I set them all to be characters.

• Inside the mutate() I use case_when(), which is a vectorised if_else(). The first one says "if there is a meanLead, set rule to BOGA. Else, if there is an rf_trader, set ruleto RF. Else, if there is a sam_trader, set the rule to SAM. Otherwise just set it to 0.

• Then I drop the meanLead, rf_trader and sam_trader columns as they are no longer needed.