1. Learn
    2. /
  2. Courses
    3. /
  3. Exploratory Data Analysis in SQL
Connected

Exercise

Group and recode values

There are almost 150 distinct values of evanston311.category. But some of these categories are similar, with the form "Main Category - Details". We can get a better sense of what requests are common if we aggregate by the main category.

To do this, create a temporary table recode mapping distinct category values to new, standardized values. Make the standardized values the part of the category before a dash ('-'). Extract this value with the split_part() function:

split_part(string text, delimiter text, field int)

You'll also need to do some additional cleanup of a few cases that don't fit this pattern.

Then the evanston311 table can be joined to recode to group requests by the new standardized category values.

Instructions 1/4

undefined XP
    1
    2
    3
    4
  • Create recode with a standardized column; use split_part() and then rtrim() to remove any remaining whitespace on the result of split_part().