Return a string by reference from tbl_df of numerics in R

Question

I have a tbl_df (tibble) named 'control.scores' that has a column called "Overall", which is some value between 1.00 and 4.00.

# A tibble: 2 x 8
      group   GOV  CORC  TMSC     AUDIT   PPS     TRAIN  Overall
      <chr> <dbl> <dbl> <dbl>     <dbl> <dbl>     <dbl>    <dbl>
1 Group4  0.82   0.2 0.525 0.2833333   0.2 0.2333333 2.261667
2 Group5  0.82   0.0 0.525 0.2833333   0.2 0.2333333 2.061667

I also have a another tbl_df, called 'control.rating.tbl' constructed:

# create a reference table of control ratings and numeric ranges
control.ref.tbl <- tribble(
  ~RATING, ~MIN, ~MAX,
  "Ineffective", 3.500, 4.00,
  "Marginally Effective",2.500 ,3.499,
  "Generally Effective", 1.500 ,2.499,
  "Highly Effective", 1.00, 1.499
)

How can I append one more column to 'control.scores' that uses the value in Overall and checks its position between the MIN and MAX range of 'control.rating.tbl' and return the string that corresponds?

E.g., Group4_Overall == '2.261667, which corresponds to 'Generally Effective' in 'control.rating.tbl'. It would look like this:

# A tibble: 2 x 8
      group   GOV  CORC  TMSC     AUDIT   PPS     TRAIN  Overall  Rating
      <chr> <dbl> <dbl> <dbl>     <dbl> <dbl>     <dbl>    <dbl>
1 Group4  0.82   0.2 0.525 0.2833333   0.2 0.2333333 2.261667  Generally Effective
2 Group5  0.82   0.0 0.525 0.2833333   0.2 0.2333333 2.061667  Generally Effective

Good catch. I think that's just an error in how I typed the ranges. — Zach, Aug 23 '17 at 06:11
I don't understand why the updated table means "no gaps". 2.9995, for example, has no associated Rating. — www, Aug 23 '17 at 06:24

score 2 · Accepted Answer · answered Aug 23 '17 at 06:03

We can consider to use case_when from the dplyr. Notice that I changed the range of your classification a little bit because there are gaps in your original classification. For example, 3.505 will have no any associated classes based on your original classification. dt2 is the final output.

library(dplyr)

dt2 <- dt %>%
  mutate(Rating = case_when(
    Overall > 3.5 & Overall <= 4.00  ~ "Ineffective",
    Overall > 3 & Overall <= 3.5     ~ "Marginally Effective",
    Overall > 2.5 & Overall <= 3     ~ "Generally Effective",
    Overall >= 1 & Overall <= 2.5    ~ "Highly Effective"
  ))

Data:

dt <- read.table(text = "group   GOV  CORC  TMSC     AUDIT   PPS     TRAIN  Overall
1 Group4  0.82   0.2 0.525 0.2833333   0.2 0.2333333 2.261667
                 2 Group5  0.82   0.0 0.525 0.2833333   0.2 0.2333333 2.061667",
                 header = TRUE, stringsAsFactors = FALSE)

I was wondering if there was a more elegant way to approach it by referencing the table that was already created? Otherwise, this works quite nicely. — Zach, Aug 23 '17 at 06:33
@Zach I am sure there is way to reference the table, but I am not familiar with those approaches. By the way, since you changed your `control.rating.tbl`, the ratings for your examples both are now `Generally Effective`. — www, Aug 23 '17 at 06:38

Return a string by reference from tbl_df of numerics in R

1 Answers1