A Complete Data Analysis Workflow

Companies are always finding new ways to leverage their data for valuable insights. The landscape of Big Data is fast changing and always quick to embrace new trends and techniques. the bedrock of analytics will always remain relevant.

The purpose of this report is to provide an example of a data analysis workflow from start to finish. In doing so, I will highlight each individual aspect of the process and demonstrate how every step works in tandem with each other to create a complete report. When finished, this project will utilize the strengths of a variety of software: R, MySQL, Microsoft Excel, Tableau, and even Blogger to write this very report. To frame the steps of this analysis I will be utilizing a graphic created by Ketaki Kekatpure. Although every project is unique, this image provides an intuitive look into the lifecycle of a data analysis project.

 

Credit: Ketaki Kekatpure

 

Step -1: Problem Identification

While not explicitly listed on Kekatpure's graphic, identifying a problem to explore is essential to analysis. In the case of this project, I wondered to myself, "What is the difference between the Scion xB and Scion tC?" Asking simple, realistic questions often leads to good answers. With a question this broad, there are many ways to interpret it; the most obvious answer is that an xB is a 5-door hatchback and a tC is a 3-door hatchback. However, there are many similarities than differences between the two models: identical materials used in their interiors, first generation tC's use the same engine as both generations of xB's, and both models were developed on the same "Toyota New MC Platform".

These observations formed the basis for this report, exploratory data analysis to explain potential differences in valuation between these models. In addition, I added the Toyota Avalon to this comparison because the Avalon represents a different market segment, being the flagship Toyota sedan and sharing a platform with the Lexus ES series. One would expect a premium model like an Avalon to be better maintained and hold more value on the used car market. Scion was Toyota's attempt to entice a younger audience by offering barebones, economy vehicles. If Toyota's marketing held true, it would be reasonable to suggest that Scions were typically driven harder and less maintained than the more luxurious Avalon.    

Step 0: Data Gathering

With an established plan, the next step was to collect data to explore the various hypotheses. I targeted three of the largest used car marketplaces to collect information from: Autotrader, Cars.com, and Truecar. To extract this information, I used web scraping packages in R to pull the name, price, mileage, and year of the car from each individual listing. Then, I added columns to indicate the date this information was compiled and the website the listing originated from. This was done in 9 individual scripts, one for each combination of model and website. 

The characteristics of this data set is also important to touch on. I filtered listings to a 500-mile radius around the ZIP code 92845, centered in West Garden Grove. This radius captures the metropolitan areas of Northern California, Las Vegas, Reno, Phoenix, and Tucson. The value and condition of used cars varies based on geographic location. For example, the combination of snow and salt in the northern regions of the United States is a potent recipe for rust on the undercarriage of vehicles. This radius of search results sidesteps this factor that would be difficult to account for based on listing data alone.

From December to February, I executed these scripts and collected a total of 2153 listings with publicly available prices and 77 listings with hidden prices.

Step 1: Data Cleaning

When scraping data from HTML tables, the information collected is often very messy. Common occurrences include excess white space around the data, commas, periods, and slashes. Removing these manually for over 2000 rows would be a herculean task, fortunately R has great tools to programmatically clean data. The first step to converting the columns for price and mileage to numeric data was to separate the previously mentioned listings with hidden prices.

A quirk of used car marketplaces like Autotrader is that they allow cars to be listed with prices like, "Contact Dealer for Price". These are few and far between, but character strings being mixed in with numerical data must be fixed immediately. Luckily, each website is consistent with their naming conventions for these listings. Creating subsets of the data for listings with "Contact Dealer for Price", "Not Priced", and "No Price" was an easy solution.

Removing extraneous characters like commas from listed prices was done through using a substitution function. The substitution function looks for any commas in a column of data then replaces them with blank spaces. This process is then repeated for any non-numeric characters left. Once the data had been properly cleaned, I turned my sights to another issue: duplicate listings.

When scraping listings from these sites, it is important to recognize that there are many cars listed that do not sell between scraping attempts. For example, if a car is overpriced relative to its competition, consumers looking to purchase a vehicle will choose a more competitively priced option, leaving the overpriced car to languish in the search results. As this car sits unpurchased, successive scraping attempts will create duplicates which, if unchecked, will skew analysis towards these cars. Removing these listings involves running a duplicate command that filters the data based on a defined criteria, in this case, listings that had matching years, mileages, and websites were removed. By removing duplicates, this reduced the sample from 2153 listings to 1344, a decrease of 809 listings.

These two issues, extraneous characters and duplicate listings, were the biggest hurdles in the data cleaning process. The only thing left on the agenda was to generate the ID columns in preparation for creating a database in MySQL. The ID columns are integers that correspond to the car model and the website. These IDs act as keys and are integral to database management using SQL. Once the keys are generated, the data is then ready for load into MySQL.

Step 2: Data Load

Importing the data into MySQL was a simple process using the Import Wizard. Attention to detail in ensuring the data was organized and clean pays off in this step because SQL is very particular about the format of databases. 

This data analysis project could do without SQL due to the relatively small scale and narrow scope compared to other common SQL use cases, like serving as a data warehouse for an entire company. However, SQL does have benefits that I was happy to take advantage of in this project.

First, having a dedicated organization system for this project was a huge convenience due to the massive number of individual datasets in the project folder. Each web scraping session would produce 18 datasets, 9 for each combination of car and website and 9 sets of hidden priced data. Additionally, the ability to query the SQL database in Tableau and Excel is useful because any changes in the data are updated upstream through MySQL.

 Step 3: Data Analysis

To begin the analysis, it is beneficial to get a broad overview of the data. The summary statistics table below does a great job demonstrating what the listings look like for the average car model. With 630 listings in the sample, nearly half of the listings collected were Scion tC's and they feature the highest average price and the lowest average mileage. Despite having many of the same internal parts, the average Scion xB is over $1,000 cheaper with almost 8,000 more miles. The Toyota Avalon rounds out the trio with the smallest sample size, 247 listings, the lowest median price, and the highest average mileage.

An interesting thing to note is that for each model, the mean mileage is higher than the median mileage. This indicates that there more used cars listed with high mileage than low mileage. Peering into the data confirms this, as there are 26 listings for vehicles with over 200,000 miles: 6 xBs, 6 tCs, and 14 Toyota Avalons.

 

 

 

 

 

To test the hypotheses, I constructed three linear regressions, one for each vehicle. The dependent variable in each equation is price; the independent variables are mileage and dummy variables for year. With dummy variables, it is important to note that the first year in the sequence for each car becomes the baseline year, in which the impact of future years are compared. The first model year for the Scion xB was 2004, 2005 for the tC, and 2005 for the third generation Avalon.  

The table below displays the results. The first specification is for the Scion xB. The intercept represents the predicted price if mileage was zero and the model year is 2004. Typically, the intercept in linear regression tends to not be informative because independent variables being zero either unrealistic or impossible. While it is unrealistic to find a seventeen-year-old car with zero miles, it seems like a surprisingly good estimate considering the MSRP in 2004 was $14,480.

The coefficient on the thousands of miles is negative and statistically significant. Every thousand miles is associated with a 34 dollar decline in expected listing price. As the model years become more recent, the expected listing price follows a consistent trend of becoming more expensive. While the model years of 2005, 2006, and 2008 do not demonstrate a strong enough association with price compared to the 2004 model to be considered statistically significant. Every from 2009 to 2015 demonstrates significance, culminating with a 2015 model having an expected value over $5,000 higher than the 2004 model.

In the second specification, the Scion tC tells a very similar story. The predicted effect of one thousand miles on price is steeper at 39 dollars and statistically significant. While latter model years are also statistically significant, with the 2016 Scion tC having a cost of over $5,800 more than the 2005 model.

While the Toyota Avalon does not nearly have as much in common as the tC and xB, I previously mentioned that one would expect the Avalon to depreciate at a lower rate due to the market segment the vehicle is in and the type of owner, when compared to the Scion pair. While the results of this model are not capable of supporting the hypothesis, it does demonstrate correlation. The Avalon has the highest intercept and the lowest rate of depreciation per thousand miles, at $29. Furthermore, the most last years of the third generation Avalon, the 2011 and 2012 have higher expected listing cost than the xB and similar costs to the tC.

 

 

 

 

 Step 4: Data Visualization

The final component of this workflow is visualizing the collected data. Data Analysts employee a wide variety of software to accomplish this task. Spreadsheets, Business Intelligence software, SQL databases, and statistical programming languages all work in tandem to efficiently produce reports.

When creating charts for this project, I established a goal: to create a set of visualizations in R using ggplot2, then recreate them in Excel and Tableau. By doing so, I could better understand the strengths, weaknesses, and use cases for each program. 

To start, I made three graphs in R, a scatter plot demonstrating the relationship between mileage and price, a box plot that displayed the quartiles of each model, and a combination histogram density plot that displayed the separated frequencies of price by website. These three charts provided a broad overview of all aspects of the data: the relationship between the dependent and independent variable, differences in price spread between models, and difference between aggregate listing prices on each website.

In presenting the data, I opted to use Shiny, a package in R that enables the user to create interactive web apps. Programming in Shiny was a step up in complexity compared to the usual statistical programming I engage in, but the results were certainly worthwhile. The dashboard I created is shown below; it features an interactive drop-down menu which enables users to select the visualization from a preset list of choices. Of note, this app features two more graphs that were previously discussed, a scatter plot showing the relationship between price and model year, and a residual plot displaying the differences between observed priced and the predicted prices of the model. These were added during the data analysis phase, fortunately, refactoring the Shiny app was not a hassle.

Creating visualizations in R has a much steeper learning curve compared to Excel or Tableau. If a user can overcome the hurdle of statistical programming, they are rewarded with a fantastic package in ggplot2. Ggplot2 is a data visualization package implements the "Grammar of Graphics", a system created by Leland Wilkinson, which breaks down graphs into their most basic components. This system enables the user to have fine grain control of each individual aspect of the graph. For example, I was able to specify exactly where I wanted the legend to be located using the "legend.position" command.

Excel and Tableau have much more in common relative to R. Both programs use graphical user interfaces to specify graphs, and both programs have many systems in place designed to streamline the visualization process. Where the two programs differ is in specialization, while I would consider Excel to be more utilitarian, Tableau is foremost designed for data visualization. For simple use cases, Excel or Tableau are equally effective, but even in this modest project; Excel's performance was bogged down by the size of the data set and the rendered graphics.

Excel's strengths lie in its jack of all trades nature, the software can manipulate or visualize data, write complex functions, or create distributable spreadsheets. A feature that I took advantage of was pivot tables, which allows the user to summarize data based on a variety of conditions. The table below was created using pivot tables which displays the count of listings from each website, then further divides based on car model.

As stated previously, Tableau is a program designed primarily for data visualization. The user interface is designed to allow for huge amounts of control for the final product. There are so many menus in Tableau that it's very easy to lose track of where a feature is, I experienced this when trying to add the box-and-whiskers to the graph. In my opinion, where Tableau is at its strongest are the dashboard features, which enable the user to create sleek and interactive infographics. 

The dashboard below was created in Tableau Desktop, then hosted online using Tableau Public. The objective was to recreate the Shiny dashboard to the best of my abilities. The stand out difference with the Tableau dashboard is the two drop-down menus as apposed to the single menu in Shiny. In my research when creating this dashboard, I wasn't able to find a way to define the items in the drop-down as whole graphs. Instead, the items in the menu are variables for their respective axes. To add onto the troubles, the function I wrote to define the items in the drop-down menus would not let me use bins in the numerical expression. This meant that the histogram was not able to be created. These hindrances speak more to my relative inexperience with the software and the particular nature of my use case rather than faults of Tableau.