Tutorial to write SQL queries using Python and visualizing the results in Tableau - Part 1

This post walks you through the SQL queries in the Kaggle python notebook and shows you how to visualize them using Tableau.

We are taking the following dataset for our reference:

Dataset: Used Cars Dataset

How has the price of vehicles changed over the years?

Thinking about buying a used car? Then we need to analyze how the price of different cars has changed over the years and how that might affect future prices. In our previous SQL series, we discussed the "Craigslist used cars dataset" (link above) and discussed the various factors that affect the price of the car, be it the distance traveled, time of its purchase, or the brand. The price of a car reduces as it gets older and older.

In our previous SQL series, we discussed how to write queries. Today, we will talk about how to visualize those results in Tableau.

SQL Query in Kaggle

Following is the code to load necessary libraries in the Kaggle notebook to run your SQL queries.

import numpy as np # linear algebra
import pandas as pd # data processing
      
!pip install pandasql
pd.set_option('display.max_rows', 100)
pd.options.display.float_format = '{:.5f}'.format
import pandasql as ps
from IPython.display import HTML

Next, we have the SQL query to select vehicles at a particular price range and group them by the year of manufacturing to calculate whether a vehicle's price is affected by the year of its manufacturing.

SELECT Avg(price)AS average_price,
       year
FROM   vehicles
WHERE  price BETWEEN 1 AND 655000
GROUP  BY year; 

You can copy and paste the SQL query we shared in your notebook and see how the query works on data. After taking the data using queries you can create visualizations using tableau which we have discussed in the next section.

How to do it in Tableau?

"Data visualization is the presentation of abstract information in graphical form. Data visualization allows us to spot patterns, trends, and correlations that otherwise might go unnoticed in traditional reports, tables, or spreadsheets."

Thus to read the data better we'll be using Tableau for visualizing the out of our queries.

Following is the Tableau Window, with the data already loaded.  

In the above visualization, we have plotted "Year" against "Average Price".

X-axis/Columns : Year

Y-axis/Rows: AVG(Price)

We select the above specifications by dragging the Year from tables to columns and Avg(price) to the rows.

When we plot our data we see that the year '1960' and '2007' are outliers, which makes the rest of the data negligible and hard for us to observe.

Therefore, we will remove these outliers.

We click on the year '1960; and see some options in the pop-up box. Here, we can exclude the particular outliers to let data make sense using the Exclude option.

Now, we observe our graph. We have selected an Area Graph for this query.

Area Graphs are Line Graphs but with the area below the line filled in with a certain colour or texture. Area Graphs are drawn by first plotting data points on a Cartesian coordinate grid, joining a line between the points and finally filling in the space below the completed line.

Area graphs combine the line chart and bar chart to show how one or more groups’ numeric values change over the progression of a second variable, typically that of time, and is distinguished from a line chart by the addition of shading between lines and a baseline, like in a bar chart.

Let's see how the final plot looks like.

Interpretation

After removing the outliers we can see the data is following a trend that is quite interpretable and shows how the average price of used cars varies across years. Among all the years that were taken into consideration, the few such as the year 1935, 1955 that is touching the peak have the highest pricing of used cars. Also, a sudden dip at 1938 indicates that the used cars price was lowest for this particular year.

In the tableau, we can create many more visualizations for the same thing using the 'Show me' button at the top right corner. Also, in the marks field, we can choose the drop-down button and select from the different types of graphs available.

Conclusion

In the following series, we would be sharing different SQL queries in python and their visualization using Tableau. We're using Tableau because it is much easier and convenient than other python libraries that are being used for plotting various graphs and visualizing the dataset such as seaborn, matplotlib, etc.

We will publish many such articles in the days to come so stay tuned for more such queries and exciting graphs around the same.