library(dplyr)
library(tidyverse)
library(scales)Car sales report of March 2017 is downloaded from ODD website and renamed to “odd_retail_sales_2017_03.xlsx”. And then the file is uploaded to github.
This code is for creating a temporary .xlsx file, downloading raw data from local/github and refine for analysis:
tmp<-tempfile(fileext=".xlsx")
download.file("https://github.com/MEF-BDA503/pj18-oktayekici/blob/master/odd_retail_sales_2017_03.xlsx?raw=true",destfile=tmp, mode = "wb")
raw_data<-readxl::read_excel(tmp,skip=7,col_names=FALSE)
file.remove(tmp)## [1] TRUEraw_data <- raw_data %>% slice(-c(46,47))
head(raw_data)## # A tibble: 6 x 10
## X__1 X__2 X__3 X__4 X__5 X__6 X__7 X__8 X__9 X__10
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 ALFA ROMEO NA 59 59 NA NA 0 0 59 59
## 2 ASTON MARTIN NA 3 3 NA NA 0 0 3 3
## 3 AUDI NA 1287 1287 NA NA 0 0 1287 1287
## 4 BENTLEY NA 0 0 NA NA 0 0 0 0
## 5 BMW NA 1346 1346 NA NA 0 0 1346 1346
## 6 CHERY NA 0 0 NA NA 0 0 0 0We are now editing column names for better look:
#Adding column names
colnames(raw_data) <- c("brand_name","auto_dom","auto_imp","auto_total","comm_dom","comm_imp","comm_total","total_dom","total_imp","total_total")
#Changing nulls with "0"
car_data_mar_17 <- raw_data %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.)))%>% mutate(year=2017,month=3)
print(car_data_mar_17,width=Inf)## # A tibble: 45 x 12
## brand_name auto_dom auto_imp auto_total comm_dom comm_imp comm_total
## <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 ALFA ROMEO 0 59 59 0 0 0
## 2 ASTON MARTIN 0 3 3 0 0 0
## 3 AUDI 0 1287 1287 0 0 0
## 4 BENTLEY 0 0 0 0 0 0
## 5 BMW 0 1346 1346 0 0 0
## 6 CHERY 0 0 0 0 0 0
## 7 CITROEN 0 1765 1765 1 424 425
## 8 DACIA 0 3534 3534 0 523 523
## 9 DS 0 1 1 0 0 0
## 10 FERRARI 0 3 3 0 0 0
## total_dom total_imp total_total year month
## <dbl> <dbl> <dbl> <dbl> <dbl>
## 1 0 59 59 2017 3
## 2 0 3 3 2017 3
## 3 0 1287 1287 2017 3
## 4 0 0 0 2017 3
## 5 0 1346 1346 2017 3
## 6 0 0 0 2017 3
## 7 1 2189 2190 2017 3
## 8 0 4057 4057 2017 3
## 9 0 1 1 2017 3
## 10 0 3 3 2017 3
## # ... with 35 more rowssaveRDS(car_data_mar_17,file="~/rodevi/odd_car_sales_data_mar_17.rds")car_data_mar_17 %>%
filter(auto_total > 0 & comm_total > 0) %>%
select(brand_name,total_total) %>%
arrange(desc(total_total))## # A tibble: 14 x 2
## brand_name total_total
## <chr> <dbl>
## 1 RENAULT 10193
## 2 FIAT 9383
## 3 VOLKSWAGEN 8850
## 4 FORD 8217
## 5 HYUNDAI 4548
## 6 DACIA 4057
## 7 PEUGEOT 3130
## 8 MERCEDES-BENZ 2995
## 9 TOYOTA 2640
## 10 NISSAN 2420
## 11 CITROEN 2190
## 12 KIA 1014
## 13 MITSUBISHI 242
## 14 SSANGYONG 53Top domestic car productions by brand
car_data_mar_17 %>%
filter(auto_total > 0 & comm_total > 0) %>%
select(brand_name,total_dom) %>%
arrange(desc(total_dom))## # A tibble: 14 x 2
## brand_name total_dom
## <chr> <dbl>
## 1 FIAT 8664
## 2 RENAULT 5718
## 3 FORD 5086
## 4 HYUNDAI 1743
## 5 TOYOTA 1712
## 6 MITSUBISHI 65
## 7 CITROEN 1
## 8 PEUGEOT 1
## 9 DACIA 0
## 10 KIA 0
## 11 MERCEDES-BENZ 0
## 12 NISSAN 0
## 13 SSANGYONG 0
## 14 VOLKSWAGEN 0