I Have two data frames for is sales data with many different columns and another is incentive scale data .
I am trying to map scale against goals in sales data , then I am trying to recalculate Target columns against incentive scale . but unable to create the perfect logic to do this .
df <- data.frame(Name = c("ABC", "DCA", "GOL", "LAM", "MNA", "BVA", "VAN"),
Goal =c("published", "pending", "not designed",NA, "pending", "pending", "not designed"),
Target_1 = c(3734, 2639, 2604, NA, 2793, 2688, 2403),
Target_2 = c(3322, 2016, 2310, NA, 3236, 3898, 2309),
Target_3 = c(3785, 2585, 3750, NA, 2781, 3589, 2830))
df1 <- data.frame(Goals = c("published", "pending", "not designed"),
Incentive =c(1.4,1.1,0.5))
df <- left_join(df,df1,by=c("Goal"="Goals")) %>% relocate(Incentive,.after = Goal) %>%
mutate(colnames(contains("Target")) = colnames(contains("Target"))*Incentive)
the output should be like
| Name |
Goal |
Incentive |
Target_1 |
Target_2 |
Target_3 |
| ABC |
published |
1.4 |
5227.6 |
4650.8 |
5299 |
| DCA |
pending |
1.1 |
2902.9 |
2217.6 |
2843.5 |
| GOL |
not designed |
0.5 |
1302 |
1155 |
1875 |
| LAM |
|
|
0 |
0 |
0 |
| MNA |
pending |
1.1 |
3072.3 |
3559.6 |
3059.1 |
| BVA |
pending |
1.1 |
2956.8 |
4287.8 |
3947.9 |
| VAN |
not designed |
0.5 |
1201.5 |
1154.5 |
1415 |