Reading XLSX Files

#library(tidyverse)
#library(readxl)

#jan_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_01.xlsx",skip=7,col_names=FALSE)
#feb_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_02.xlsx",skip=7,col_names=FALSE)
#mar_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_03.xlsx",skip=7,col_names=FALSE)
#apr_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_04.xlsx",skip=7,col_names=FALSE)
#may_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_05.xlsx",skip=7,col_names=FALSE)
#jun_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_06.xlsx",skip=7,col_names=FALSE)
#jul_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_07.xlsx",skip=7,col_names=FALSE)
#aug_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_08.xlsx",skip=7,col_names=FALSE)
#sep_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_09.xlsx",skip=7,col_names=FALSE)
#oct_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_10.xlsx",skip=7,col_names=FALSE)
#nov_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_11.xlsx",skip=7,col_names=FALSE)
#dec_2016<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2016_12.xlsx",skip=7,col_names=FALSE)
#jan_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_01.xlsx",skip=7,col_names=FALSE)
#feb_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_02.xlsx",skip=7,col_names=FALSE)
#mar_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_03.xlsx",skip=7,col_names=FALSE)
#apr_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_04.xlsx",skip=7,col_names=FALSE)
#may_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_05.xlsx",skip=7,col_names=FALSE)
#jun_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_06.xlsx",skip=7,col_names=FALSE)
#jul_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_07.xlsx",skip=7,col_names=FALSE)
#aug_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_08.xlsx",skip=7,col_names=FALSE)
#sep_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_09.xlsx",skip=7,col_names=FALSE)
#oct_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_10.xlsx",skip=7,col_names=FALSE)
#nov_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_11.xlsx",skip=7,col_names=FALSE)
#dec_2017<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2017_12.xlsx",skip=7,col_names=FALSE)
#jan_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_01.xlsx",skip=7,col_names=FALSE)
#feb_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_02.xlsx",skip=7,col_names=FALSE)
#mar_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_03.xlsx",skip=7,col_names=FALSE)
#apr_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_04.xlsx",skip=7,col_names=FALSE)
#may_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_05.xlsx",skip=7,col_names=FALSE)
#jun_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_06.xlsx",skip=7,col_names=FALSE)
#jul_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_07.xlsx",skip=7,col_names=FALSE)
#aug_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_08.xlsx",skip=7,col_names=FALSE)
#sep_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_09.xlsx",skip=7,col_names=FALSE)
#oct_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_10.xlsx",skip=7,col_names=FALSE)
#nov_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_11.xlsx",skip=7,col_names=FALSE)
#dec_2018<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2018_12.xlsx",skip=7,col_names=FALSE)
#jan_2019<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2019_01.xlsx",skip=7,col_names=FALSE)
#feb_2019<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2019_02.xlsx",skip=7,col_names=FALSE)
#mar_2019<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2019_03.xlsx",skip=7,col_names=FALSE)
#apr_2019<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2019_04.xlsx",skip=7,col_names=FALSE)
#may_2019<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2019_05.xlsx",skip=7,col_names=FALSE)
#jun_2019<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2019_06.xlsx",skip=7,col_names=FALSE)
#jul_2019<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2019_07.xlsx",skip=7,col_names=FALSE)
#aug_2019<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2019_08.xlsx",skip=7,col_names=FALSE)
#sep_2019<-read_excel("/Users/m2lmacbook1/Desktop/ODD_Assignment/2019_09.xlsx",skip=7,col_names=FALSE)

Changing Column Names

#dfs<-c("jan_2016","feb_2016","mar_2016","apr_2016","may_2016","jun_2016","jul_2016","aug_2016","sep_2016","oct_2016","nov_2016","dec_2016","jan_2017","feb_2017","mar_2017","apr_2017","may_2017","jun_2017","jul_2017","aug_2017","sep_2017","oct_2017","nov_2017","dec_2017","jan_2018","feb_2018","mar_2018","apr_2018","may_2018","jun_2018","jul_2018","aug_2018","sep_2018","oct_2018","nov_2018","dec_2018","jan_2019","feb_2019","mar_2019","apr_2019","may_2019","jun_2019","jul_2019","aug_2019","sep_2019")
#for(df in dfs) {
#  df.tmp <- get(df)
#  names(df.tmp) <- c("brand_name","auto_dom","auto_imp","auto_total","comm_dom",
#                     "comm_imp","comm_total","total_dom","total_imp","total_total")
#  assign(df, df.tmp)
#}

Creating Month and Year Column

#jan_2016 <- jan_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 1, year = 2016 )
#feb_2016 <- feb_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 2, year = 2016 )
#mar_2016 <- mar_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 3, year = 2016 )
#apr_2016 <- apr_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 4, year = 2016 )
#may_2016 <- may_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 5, year = 2016 )
#jun_2016 <- jun_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 6, year = 2016 )
#jul_2016 <- jul_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 7, year = 2016 )
#aug_2016 <- aug_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 8, year = 2016 )
#sep_2016 <- sep_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 9, year = 2016 )
#oct_2016 <- oct_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 10, year = 2016 )
#nov_2016 <- nov_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 11, year = 2016 )
#dec_2016 <- dec_2016 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 12, year = 2016 )
#jan_2017 <- jan_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 1, year = 2017 )
#feb_2017 <- feb_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 2, year = 2017 )
#mar_2017 <- mar_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 3, year = 2017 )
#apr_2017 <- apr_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 4, year = 2017 )
#may_2017 <- may_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 5, year = 2017 )
#jun_2017 <- jun_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 6, year = 2017 )
#jul_2017 <- jul_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 7, year = 2017 )
#aug_2017 <- aug_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 8, year = 2017 )
#sep_2017 <- sep_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 9, year = 2017 )
#oct_2017 <- oct_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 10, year = 2017 )
#nov_2017 <- nov_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 11, year = 2017 )
#dec_2017 <- dec_2017 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 12, year = 2017 )
#jan_2018 <- jan_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 1, year = 2018 )
#feb_2018 <- feb_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 2, year = 2018 )
#mar_2018 <- mar_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 3, year = 2018 )
#apr_2018 <- apr_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 4, year = 2018 )
#may_2018 <- may_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 5, year = 2018 )
#jun_2018 <- jun_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 6, year = 2018 )
#jul_2018 <- jul_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 7, year = 2018 )
#aug_2018 <- aug_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 8, year = 2018 )
#sep_2018 <- sep_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 9, year = 2018 )
#oct_2018 <- oct_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 10, year = 2018 )
#nov_2018 <- nov_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 11, year = 2018 )
#dec_2018 <- dec_2018 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 12, year = 2018 )
#jan_2019 <- jan_2019 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 1, year = 2019 )
#feb_2019 <- feb_2019 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 2, year = 2019 )
#mar_2019 <- mar_2019 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 3, year = 2019 )
#apr_2019 <- apr_2019 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 4, year = 2019 )
#may_2019 <- may_2019 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 5, year = 2019 )
#jun_2019 <- jun_2019 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 6, year = 2019 )
#jul_2019 <- jul_2019 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 7, year = 2019 )
#aug_2019 <- aug_2019 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 8, year = 2019 )
#sep_2019 <- sep_2019 %>% mutate_if(is.numeric,funs(ifelse(is.na(.),0,.))) %>% mutate(month = 9, year = 2019 )

Combined Data

#car_data <- rbind(jan_2016, feb_2016, mar_2016, apr_2016, may_2016, jun_2016, jul_2016, aug_2016, sep_2016, oct_2016, nov_2016, dec_2016, jan_2017, feb_2017, mar_2017, apr_2017, may_2017, jun_2017, jul_2017, aug_2017, sep_2017, oct_2017, nov_2017, dec_2017, jan_2018, feb_2018, mar_2018, apr_2018, may_2018, jun_2018, jul_2018, aug_2018, sep_2018, oct_2018, nov_2018, dec_2018, jan_2019, feb_2019, mar_2019, apr_2019, may_2019, jun_2019, jul_2019, aug_2019, sep_2019)
#saveRDS(car_data,file="car_data.rds")