case_when fails to mutate correct value in another column-CodePudding

I have a simple table as below.

tibble(
"KPI" =c("1 KPI","2 KPI","3 KPI","4 KPI","5 KPI"),
"VALUE" = c(1,500,1,0.20,7.88),
"BENCHMARK" = c(0,473,0,0.39,6.8),
"CRITERIA"= c(">=90%", "Lower than Benchmark", ">=90%","Lower than Benchmark","Higher than Benchmark"),
"APPROVAL" = c( case_when(
(`VALUE` >= 0.9) ~ 1,
             
             (`VALUE` < `BENCHMARK`) ~ 1,
              (`VALUE` >= 0.9) ~ 1,
             (`VALUE` < `BENCHMARK`) ~ 1,
             (`VALUE` > `BENCHMARK`) ~ 1,
             TRUE ~ 0))
)

Does anybody know why I do not have 0 for the first criteria "Lower than Benchmark"?

CodePudding user response：

I found a solution by creating a case_when condition for each criteria and giving indexing to value one by one like

case_when(VALUE[1] >=0.9)~1,T ~ 0),
case_when(VALUE[2] <BENCHMARK[2])~1,T ~ 0) ...so on...

If there is another solution I would like to hear.

CodePudding user response：

I believe that the issue is that you needed to specify what the expected criteria value should be, so that you're not getting multiple conditions registering as TRUE. In addition, I think you may have run into issues with using the backticks around the field names in your case_when code. I made the following edit and removed the redundant criteria from your case when and got the correct result:

tibble(
    "KPI" =c("1 KPI","2 KPI","3 KPI","4 KPI","5 KPI"),
    "VALUE" = c(1, 500, 1, 0.20, 7.88),
    "BENCHMARK" = c(0, 473, 0, 0.39, 6.8),
    "CRITERIA"= c(">=90%", "Lower than Benchmark", ">=90%","Lower than Benchmark","Higher than Benchmark"),
    "APPROVAL" = case_when(
        (CRITERIA == ">=90%") & (VALUE >= 0.9) ~ 1,
        (CRITERIA == "Lower than Benchmark") & (VALUE < BENCHMARK) ~ 1,
        (CRITERIA == "Higher than Benchmark")& (VALUE > BENCHMARK) ~ 1,
        TRUE ~ 0))

The result:

# A tibble: 5 x 5
  KPI    VALUE BENCHMARK CRITERIA              APPROVAL
  <chr>  <dbl>     <dbl> <chr>                    <dbl>
1 1 KPI   1         0    >=90%                        1
2 2 KPI 500       473    Lower than Benchmark         0
3 3 KPI   1         0    >=90%                        1
4 4 KPI   0.2       0.39 Lower than Benchmark         1
5 5 KPI   7.88      6.8  Higher than Benchmark        1