I have this example of a dataset I'm working with:
ex <- structure(list(reg_desc = c("1-Northeast Region", "1-Northeast Region",
"1-Northeast Region", "1-Northeast Region", "1-Northeast Region"
), state = c("04-Connecticut", "05-Maine", "04-Connecticut",
"05-Maine", NA), trigger_city = c("14860-Bridgeport-Stamford-Norwalk",
"12620-Bangor", NA, NA, NA), Category = c("M", "M", "S", "S",
"R"), Cred_Fac = c(0, 0, 0.317804971641414, 0, 1), Mean = c(50323.3311111111,
48944.4266666667, 44220.8220792079, 43724.1495, 50492.0654351396
)), row.names = c(1L, 7L, 118L, 119L, 136L), class = "data.frame")
I have a category column where M is a row at a metropolitan level, S is state level, and R is region level. I want to create a new column based on an if-else statement I'd like to implement, but can't seem to get it right.
The code is:
ex %>% mutate(New_Mean = if(any(Cred_Fac == 1) Mean else if(any(Cred_Fac < 1) if(Category == 'S' & Cred_Fac == 1) M_Mean * M_Cred+R_Mean*(1-M_Cred_Fac) if(Category == 'R' & Cred_Fac == 1) M_Mean * M_Cred+R_Mean*(1-M_Cred_Fac))
My logic should be: if at the M level, Cred_Fac is 1, then keep Mean as is; if less than 1, move on to State level and if at State level Cred_Fac is 1, then do M_Mean * M_Cred+R_Mean*(1-M_Cred_Fac); repeat process if at State level Cred_Fac is not 1.
I guess one idea I have would be to create new columns where each row would also have the State and Region information, such as:
hi1 <- data.frame(reg_desc = c("1-Northeast Region", "1-Northeast Region",
"1-Northeast Region", "1-Northeast Region", "1-Northeast Region"
), state = c("04-Connecticut", "05-Maine", "04-Connecticut",
"05-Maine", NA), trigger_city = c("14860-Bridgeport-Stamford-Norwalk",
"12620-Bangor", NA, NA, NA), Category = c("M", "M", "S", "S",
"R"), Cred_Fac = c(0, 0, 0.317804971641414, 0, 1), Mean = c(50323.3311111111,
48944.4266666667, 44220.8220792079, 43724.1495, 50492.0654351396),
State_Cred_Fac = c(0.317805,0.000000,NA,NA,NA),Mean_State = c(44220.82,43724.15,NA,NA,NA),
Reg_Cred_Fac = c(1.000000,1.000000,1.000000,1.000000,NA),
Mean_Region = c(50492.07,50492.07,50492.07,50492.07,NA))
afterwards,I could just do
new <- hi1 %>% mutate(New_Mean = ifelse(Cred_Fac == 1,Mean,ifelse(Cred_Fac < 1 & (State_Cred_Fac == 1 & !is.na(State_Cred_Fac)), Mean*Cred_Fac+State_Mean*(1-Cred_Fac),
ifelse(Cred_Fac < 1 & Reg_Cred_Fac == 1, Mean*Cred_Fac+Mean_Region*(1-Cred_Fac),NA))))
which gives me the final result I'm looking for, but I was wondering if there was any way I could do this row-wise without having to insert the new columns? I did this on a small example, so I'm not sure how I would be able to create the columns State_Mean, State_Cred_Fac, etc on a larger scale without manually inserting the values. Any suggestions and advice would be greatly appreciated!
Aucun commentaire:
Enregistrer un commentaire