samedi 9 janvier 2021

Matching Intervals with Using Lookup Table in R

I'm looking to use a lookup reference table that has Start and End intervals that align with a Standard (i.e. "Poor", "Average", etc.)

I have a data table where I'd like to create a new Standard column tagging the data value based on the interval it falls into on the lookup table. Below is a simplified example. My actual dataset is much larger and needs to be dynamic so I don't have to hard code or create many individual objects within the script.

lookup_df = data.frame("Standard" = c("Poor", "Below_Average", "Average", "Above_Average", "Good"),
                  "Start" = c(2,3,4,5,6), "End" = c(3,4,5,6,7))

col = c(1.5, 5.2, 4.1, 3.3, 9.6, 2.4)

I am attempting to use ifelse() and findInterval() to return the index from the lookup Standard column. I know the issue is the indexing part since findInterval returns 0s which can't be indexed. I tried to get around this by adding +1 to findInterval but that also didn't work. Here is what I've been trying:

ifelse(findInterval(col, lookup_df$End)+1 > 1, lookup_df$Standard[findInterval(col, lookup_df$End)+1], "Poor")

# [1] "Poor"          "Above_Average" "Average"       "Below_Average"
# [5] NA              "Poor"   

My desired result is:

# [1] "Poor"          "Above_Average" "Average" "Below_Average"      
# [5] "Good"             "Poor"

I have tried using transform() from this example Matching intervals with values in another table in R but also wasn't able to get it to function properly.

The ifelse() indexing issue seems to align with this one return value of ifelse from another vector via index

I'm guessing there is a simple solution here that I'm missing! Any help is appreciated.

Aucun commentaire:

Enregistrer un commentaire