ODD Car Sales Data Analysis

ShineRs have collected monthly data from Automative Distributors Association website. We compared total car sales on a quarterly basis, described top selling brands per units sold & analyzed the sales trend for domestic and imported cars.

Initial Steps

Firstly imported the following libraries and set the working directory

library(tidyverse)
library(readxl)
library(lubridate)
library(scales)
library(gridExtra)

Preprocessing

After downloading monthly data, regularizing file format & naming, we merged and cleaned data for analysis

# excel files must be in the working directory
setwd("~/Dropbox/BDA2/DataAnalyticsEssentials/ODD_Car_Data")

col_names <- c("brand_name","auto_dom","auto_imp","auto_total","comm_dom",
               "comm_imp","comm_total","total_dom","total_imp","total_total")

# Import all monthly sales data from 2016 to 2019
# Excel files must fit the indicated pattern
# make sure file ends in .xlsx and not XLSX or anything else

file.list <- list.files(pattern='201[0-9].ODD.monthly.[0-1][0-9].xlsx')

df.list <- lapply(file.list, read_excel, skip = 7, col_names = col_names)
names(df.list) <- file.list


# add a column, date, month, year, and quarter for each data frame in df.list

year <- 2016
counter <- 1

for (i in 1:length(df.list)) {
  
  if (counter > 12) {
    counter <- 1
    year <- year + 1
  }
  
  df.list[[i]] <- df.list[[i]] %>% mutate(date = ymd(paste(year,counter,28, sep = "-")),
                                          month = month(date),
                                          year = year(date),
                                          quarterly = paste(year,"Q",quarter(date),sep = ""))
  counter <- counter + 1
}

## Merge & Clean-up data
ODD_all <- bind_rows(df.list)

ODD_all <- ODD_all %>% filter(brand_name != "TOPLAM:" & brand_name != "TOPLAM" & brand_name!="")

ODD_all[is.na(ODD_all)] <- 0

ODD_all <- filter(ODD_all,!grepl("ODD", brand_name, fixed = TRUE))

ODD_all <- ODD_all %>% mutate(brand_name=replace(brand_name, brand_name 
                                                 %in% c("ASTON MART?N","ASTON MARTİN"),"ASTON MARTIN"))

Plots & Analysis

Quarterly Comparison

Between 2016 and 2019 four brands dominated all four quarters in total sales: Fiat, Ford, Renault, Volkswagen.

First 8 Brands per Automobile Sold

As it can be seen from the ODD Auto graph, the first 8 brand of the sector generates 65.2% of the total sales with nearly 1.4 billion cars from 2016 to 2019. Considering that the sales results of the top 3 companies account for approximately 55% of the total sales of these eight brands, it can be said that they constitute an important concentration in the sector.