converting sql to width bins in r

Question

I want to create a histogram out of the range of a column named surfacearea with country data frame. I made an sql file

# In R
range <- max(country$surface) - min(country$surfacearea)

#in SQL
# part B Divide the range into 5 equal width bins
#SELECT CASE 
#WHEN surfacearea  <  3415080 THEN "LOW"
#WHEN surfacearea <= 6830160 and surfacearea > 3415081 THEN "MEDIUM-LOW"
#WHEN surfacearea <= 10245240 and surfacearea > 6830161 THEN "MEDIUM"
#WHEN surfacearea <= 13660320 and surfacearea > 10245240 THEN "MEDIUM-HIGH"
#ELSE "HIGH" END AS `type`, COUNT(surfacearea)
#GROUP BY `type`
#ORDER BY `type` ASC;

I'm working on putting mySQL into r

Answer 1

You could use the dplyr package and its case_when function. Note the spacing is just for readability:

library(dplyr)

country %>%
  mutate(
    type = case_when(
      surfacearea <  3415080                           ~ "LOW",
      surfacearea >  3415081 & surfacearea <=  6830160 ~ "MEDIUM-LOW",
      surfacearea >  6830160 & surfacearea <= 10245240 ~ "MEDIUM",
      surfacearea > 10245240 & surfacearea <= 13660320 ~ "MEDIUM-HIGH",
      TRUE                                             ~ "HIGH"
    )
  )

The last line is the catch-all case. For more see ?case_when. To complete your pipeline, you could do:

country %>%
  group_by(type) %>%
  mutate(
    type = case_when(
      surfacearea <  3415080                           ~ "LOW",
      surfacearea >  3415081 & surfacearea <=  6830160 ~ "MEDIUM-LOW",
      surfacearea >  6830160 & surfacearea <= 10245240 ~ "MEDIUM",
      surfacearea > 10245240 & surfacearea <= 13660320 ~ "MEDIUM-HIGH",
      TRUE                                             ~ "HIGH"
    )
  ) %>%
  arrange(type)

There is also the between function which could further help the readability, but that function is a shortcut for x >= left & x <= right so it might not work since your first bucket is strictly less-than.