0
$\begingroup$

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

$\endgroup$
1
$\begingroup$

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.

| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.