Exploratory Analysis with SQL – Data manipulation

There are several ways to explore new data. One effective one is putting together some pieces of information that may be related to each other. For example, in this table, there are almost 150 distinct values. But some of evanston311.category these categories are similar, with the form “Main Category – Details”. We can get a better sense of what requests are common if we aggregate them by the main category.

To do this, we can create a temporary table recode mapping distinct category values to new, standardized values. 

The result will be like this.

Ok, this seems a good result. But I have a doubt:

There are some categories related to trash, but they are not categorized as trash?

Let’s find out.

We can use the function ILIKE and NOT LIKE to do that. We filter the description that has ‘garbage’ and ‘trash’, but is not categorized with these words.

This will result.

Seems like categories suck as ‘rats’, ‘recycling’, ‘dead animals’, ‘ask a question’, and other categories, also can have a lot of information related to trash. Is better to keep our eyes on those categories, if we want to know more about this topic in the City of Evanston.

One thought on “Exploratory Analysis with SQL – Data manipulation

  1. Id like to thank you for the efforts you have put in penning this blog. Im hoping to view the same high-grade content by you in the future as well. In truth, your creative writing abilities has encouraged me to get my very own site now 😉

Leave a Reply

Your email address will not be published. Required fields are marked *