ELECTRIC VEHICLES ANALYSIS

ELECTRIC VEHICLE ANALYSIS

This dataset shows the Battery Electric Vehicles (BEVs) and Plug-in Hybrid Electric Vehicles (PHEVs) that are currently registered through Washington State Department of Licensing (DOL). The dataset was sourced from https://catalog.data.gov/dataset/electric-vehicle-population-data and an extra dataset containing USA states, cities, their locations amongst others was sourced from https://simplemaps.com/data/us-cities.

We will start with reading the dataset and studying it

elect_vec<- read.csv("C:/Users/USER/Documents/data analytics project/NERO FRIEND/Electric_Vehicle_Population_Data.csv", stringsAsFactors = FALSE)

head(elect_vec)

##   VIN..1.10.   County    City State Postal.Code Model.Year  Make    Model
## 1 WAUTPBFF4H     King Seattle    WA       98126       2017  AUDI       A3
## 2 WAUUPBFF2J Thurston Olympia    WA       98502       2018  AUDI       A3
## 3 5YJSA1E22H Thurston   Lacey    WA       98516       2017 TESLA  MODEL S
## 4 1C4JJXP62M Thurston  Tenino    WA       98589       2021  JEEP WRANGLER
## 5 5YJ3E1EC9L   Yakima  Yakima    WA       98902       2020 TESLA  MODEL 3
## 6 1C4JJXP66P Thurston Olympia    WA       98501       2023  JEEP WRANGLER
##                    Electric.Vehicle.Type
## 1 Plug-in Hybrid Electric Vehicle (PHEV)
## 2 Plug-in Hybrid Electric Vehicle (PHEV)
## 3         Battery Electric Vehicle (BEV)
## 4 Plug-in Hybrid Electric Vehicle (PHEV)
## 5         Battery Electric Vehicle (BEV)
## 6 Plug-in Hybrid Electric Vehicle (PHEV)
##   Clean.Alternative.Fuel.Vehicle..CAFV..Eligibility Electric.Range Base.MSRP
## 1             Not eligible due to low battery range             16         0
## 2             Not eligible due to low battery range             16         0
## 3           Clean Alternative Fuel Vehicle Eligible            210         0
## 4             Not eligible due to low battery range             25         0
## 5           Clean Alternative Fuel Vehicle Eligible            308         0
## 6             Not eligible due to low battery range             21         0
##   Legislative.District DOL.Vehicle.ID               Vehicle.Location
## 1                   34      235085336   POINT (-122.374105 47.54468)
## 2                   22      237896795  POINT (-122.943445 47.059252)
## 3                   22      154498865   POINT (-122.78083 47.083975)
## 4                   20      154525493   POINT (-122.85403 46.856085)
## 5                   14      225996361 POINT (-120.524012 46.5973939)
## 6                   22      220675367   POINT (-122.89692 47.043535)
##                               Electric.Utility X2020.Census.Tract
## 1 CITY OF SEATTLE - (WA)|CITY OF TACOMA - (WA)        53033011500
## 2                       PUGET SOUND ENERGY INC        53067011100
## 3                       PUGET SOUND ENERGY INC        53067012226
## 4                       PUGET SOUND ENERGY INC        53067012620
## 5                                   PACIFICORP        53077000800
## 6                       PUGET SOUND ENERGY INC        53067010802

str(elect_vec)

## 'data.frame':    181458 obs. of  17 variables:
##  $ VIN..1.10.                                       : chr  "WAUTPBFF4H" "WAUUPBFF2J" "5YJSA1E22H" "1C4JJXP62M" ...
##  $ County                                           : chr  "King" "Thurston" "Thurston" "Thurston" ...
##  $ City                                             : chr  "Seattle" "Olympia" "Lacey" "Tenino" ...
##  $ State                                            : chr  "WA" "WA" "WA" "WA" ...
##  $ Postal.Code                                      : int  98126 98502 98516 98589 98902 98501 98345 98043 98119 98501 ...
##  $ Model.Year                                       : int  2017 2018 2017 2021 2020 2023 2017 2020 2022 2017 ...
##  $ Make                                             : chr  "AUDI" "AUDI" "TESLA" "JEEP" ...
##  $ Model                                            : chr  "A3" "A3" "MODEL S" "WRANGLER" ...
##  $ Electric.Vehicle.Type                            : chr  "Plug-in Hybrid Electric Vehicle (PHEV)" "Plug-in Hybrid Electric Vehicle (PHEV)" "Battery Electric Vehicle (BEV)" "Plug-in Hybrid Electric Vehicle (PHEV)" ...
##  $ Clean.Alternative.Fuel.Vehicle..CAFV..Eligibility: chr  "Not eligible due to low battery range" "Not eligible due to low battery range" "Clean Alternative Fuel Vehicle Eligible" "Not eligible due to low battery range" ...
##  $ Electric.Range                                   : int  16 16 210 25 308 21 53 322 23 53 ...
##  $ Base.MSRP                                        : int  0 0 0 0 0 0 0 0 0 0 ...
##  $ Legislative.District                             : int  34 22 22 20 14 22 23 1 36 22 ...
##  $ DOL.Vehicle.ID                                   : int  235085336 237896795 154498865 154525493 225996361 220675367 162720022 6293899 207620633 237392459 ...
##  $ Vehicle.Location                                 : chr  "POINT (-122.374105 47.54468)" "POINT (-122.943445 47.059252)" "POINT (-122.78083 47.083975)" "POINT (-122.85403 46.856085)" ...
##  $ Electric.Utility                                 : chr  "CITY OF SEATTLE - (WA)|CITY OF TACOMA - (WA)" "PUGET SOUND ENERGY INC" "PUGET SOUND ENERGY INC" "PUGET SOUND ENERGY INC" ...
##  $ X2020.Census.Tract                               : num  5.30e+10 5.31e+10 5.31e+10 5.31e+10 5.31e+10 ...

DATA FIELDS WE INTEND TO CREATE:

1. Vehicle Age: I will calculate the age of each vehicle by subtracting the Model Year from the current year.

2. Vehicle Age Category: i will categorize the vehicle age into groups such as “New”, “Relatively New”, “Mid-aged”, “Old”, etc. This will allow you to visualize the distribution of vehicles across different age categories.

3. MSRP Category: I will categorize the Base MSRP into different price ranges (Low, Medium, High).

4. Longitude Location: I will determine the longitude Vehicle Location based off the the vehicle location column .

5. Latitude Location: I will determine the latitude Vehicle Location based off the the vehicle location column .

CLEANING THE DATA FOR RELEVANT COLUMN:

To ensure data is normalized and clean, we will check for missing values, duplicates, outliers, and ensure the data types are properly formatted

#Changing all column names that have a '.' to an '_'
colnames(elect_vec) <- gsub("\\.", "_", colnames(elect_vec))
colnames(elect_vec) <- gsub("__", "_", colnames(elect_vec))
colnames(elect_vec)

##  [1] "VIN_1_10_"                                      
##  [2] "County"                                         
##  [3] "City"                                           
##  [4] "State"                                          
##  [5] "Postal_Code"                                    
##  [6] "Model_Year"                                     
##  [7] "Make"                                           
##  [8] "Model"                                          
##  [9] "Electric_Vehicle_Type"                          
## [10] "Clean_Alternative_Fuel_Vehicle_CAFV_Eligibility"
## [11] "Electric_Range"                                 
## [12] "Base_MSRP"                                      
## [13] "Legislative_District"                           
## [14] "DOL_Vehicle_ID"                                 
## [15] "Vehicle_Location"                               
## [16] "Electric_Utility"                               
## [17] "X2020_Census_Tract"

#Checking for missing values

print(paste("Number of missing values in Model Year is:", sum(is.na(elect_vec$Model_Year))))

## [1] "Number of missing values in Model Year is: 0"

print(paste("Number of missing values in Electric Vehicle Type is:",sum(is.na(elect_vec$Electric_Vehicle_Type))))

## [1] "Number of missing values in Electric Vehicle Type is: 0"

print(paste("Number of missing values in Base MSRP:",sum(is.na(elect_vec$Base_MSRP))))

## [1] "Number of missing values in Base MSRP: 0"

print(paste("Number of missing values in X2020 Census Tract is:", sum(is.na(elect_vec$X2020_Census_Tract))))

## [1] "Number of missing values in X2020 Census Tract is: 3"

##finding the missing values with their DOL ID
missing_cen_track <- elect_vec %>% 
  filter(is.na(X2020_Census_Tract)) %>%
  select(DOL_Vehicle_ID,X2020_Census_Tract)
print(missing_cen_track)

##   DOL_Vehicle_ID X2020_Census_Tract
## 1      220643132                 NA
## 2      159850029                 NA
## 3      475254825                 NA

#creating a new dataframe where missing track are excluded 
elect_vec_clean <- elect_vec %>%
  filter(!is.na(X2020_Census_Tract))

#checking if missing values are still present
print(paste("Number of missing values in X2020 Census Tract is:", sum(is.na(elect_vec_clean$X2020_Census_Tract))))

## [1] "Number of missing values in X2020 Census Tract is: 0"

# Find distinct values for Model.Year
distinct_model_year <- unique(elect_vec_clean$Model_Year)
print("Distinct values for Model.Year:")

## [1] "Distinct values for Model.Year:"

print(distinct_model_year)

##  [1] 2017 2018 2021 2020 2023 2022 2015 2013 2019 2014 2016 2024 2011 2012 2010
## [16] 2008 2000 2002 1998 1997 1999 2003

# Find distinct values for Electric.Vehicle.Type
distinct_ev_type <- unique(elect_vec_clean$Electric_Vehicle_Type)
print("Distinct values for Electric.Vehicle.Type:")

## [1] "Distinct values for Electric.Vehicle.Type:"

print(distinct_ev_type)

## [1] "Plug-in Hybrid Electric Vehicle (PHEV)"
## [2] "Battery Electric Vehicle (BEV)"

# Check for duplicates in the "DOL_Vehicle_ID" column
if (any(duplicated(elect_vec_clean$DOL_Vehicle_ID))) {
  print("Duplicates found in the DOL_Vehicle_ID column.")
} else {
  print("No duplicates found in the DOL_Vehicle_ID column.")
}

## [1] "No duplicates found in the DOL_Vehicle_ID column."

CREATING THE FIRST FEILD: VEHICLE AGE COLUMN

# Calculate vehicle age
current_year <- as.numeric(format(Sys.Date(), "%Y"))  # Get the current year
elect_vec_clean$Vehicle_Age <- current_year - elect_vec_clean$Model_Year
head(elect_vec_clean)

##    VIN_1_10_   County    City State Postal_Code Model_Year  Make    Model
## 1 WAUTPBFF4H     King Seattle    WA       98126       2017  AUDI       A3
## 2 WAUUPBFF2J Thurston Olympia    WA       98502       2018  AUDI       A3
## 3 5YJSA1E22H Thurston   Lacey    WA       98516       2017 TESLA  MODEL S
## 4 1C4JJXP62M Thurston  Tenino    WA       98589       2021  JEEP WRANGLER
## 5 5YJ3E1EC9L   Yakima  Yakima    WA       98902       2020 TESLA  MODEL 3
## 6 1C4JJXP66P Thurston Olympia    WA       98501       2023  JEEP WRANGLER
##                    Electric_Vehicle_Type
## 1 Plug-in Hybrid Electric Vehicle (PHEV)
## 2 Plug-in Hybrid Electric Vehicle (PHEV)
## 3         Battery Electric Vehicle (BEV)
## 4 Plug-in Hybrid Electric Vehicle (PHEV)
## 5         Battery Electric Vehicle (BEV)
## 6 Plug-in Hybrid Electric Vehicle (PHEV)
##   Clean_Alternative_Fuel_Vehicle_CAFV_Eligibility Electric_Range Base_MSRP
## 1           Not eligible due to low battery range             16         0
## 2           Not eligible due to low battery range             16         0
## 3         Clean Alternative Fuel Vehicle Eligible            210         0
## 4           Not eligible due to low battery range             25         0
## 5         Clean Alternative Fuel Vehicle Eligible            308         0
## 6           Not eligible due to low battery range             21         0
##   Legislative_District DOL_Vehicle_ID               Vehicle_Location
## 1                   34      235085336   POINT (-122.374105 47.54468)
## 2                   22      237896795  POINT (-122.943445 47.059252)
## 3                   22      154498865   POINT (-122.78083 47.083975)
## 4                   20      154525493   POINT (-122.85403 46.856085)
## 5                   14      225996361 POINT (-120.524012 46.5973939)
## 6                   22      220675367   POINT (-122.89692 47.043535)
##                               Electric_Utility X2020_Census_Tract Vehicle_Age
## 1 CITY OF SEATTLE - (WA)|CITY OF TACOMA - (WA)        53033011500           7
## 2                       PUGET SOUND ENERGY INC        53067011100           6
## 3                       PUGET SOUND ENERGY INC        53067012226           7
## 4                       PUGET SOUND ENERGY INC        53067012620           3
## 5                                   PACIFICORP        53077000800           4
## 6                       PUGET SOUND ENERGY INC        53067010802           1

CREATING FIRST DATA FRAME AND FIRST VISUALS:

This frame will contain the DOL_Vehicle_ID column and Vehicle_Age, we will be using the dataframe to plot a histogram of the distribution of vehicles across their age

#creating the histogram data frame
elect_vec_clean_hist <- elect_vec_clean %>%
                        select(DOL_Vehicle_ID, Vehicle_Age)

##creating the ggplot histogram
ggplot(elect_vec_clean_hist, aes(x = Vehicle_Age)) +
  geom_histogram(fill = "skyblue", color = "black", binwidth = 1) +
  labs(title = "Distribution of Vehicles by Age",
       x = "Vehicle Age",
       y = "Count") +
  theme_minimal()

From the graph we can see that most vehicles on the dataset were manufactured a year ago

CREATING THE SECOND FEILD: VEHICLE AGE GROUP COLUMN

# Define age categories
elect_vec_clean$Vehicle_Age_Category <- ifelse(elect_vec_clean$Vehicle_Age <= 2, "New",
                                  ifelse(elect_vec_clean$Vehicle_Age <= 5, "Relatively New",
                                  ifelse(elect_vec_clean$Vehicle_Age <= 10, "Mid-aged", "Old")))
head(elect_vec_clean)

##    VIN_1_10_   County    City State Postal_Code Model_Year  Make    Model
## 1 WAUTPBFF4H     King Seattle    WA       98126       2017  AUDI       A3
## 2 WAUUPBFF2J Thurston Olympia    WA       98502       2018  AUDI       A3
## 3 5YJSA1E22H Thurston   Lacey    WA       98516       2017 TESLA  MODEL S
## 4 1C4JJXP62M Thurston  Tenino    WA       98589       2021  JEEP WRANGLER
## 5 5YJ3E1EC9L   Yakima  Yakima    WA       98902       2020 TESLA  MODEL 3
## 6 1C4JJXP66P Thurston Olympia    WA       98501       2023  JEEP WRANGLER
##                    Electric_Vehicle_Type
## 1 Plug-in Hybrid Electric Vehicle (PHEV)
## 2 Plug-in Hybrid Electric Vehicle (PHEV)
## 3         Battery Electric Vehicle (BEV)
## 4 Plug-in Hybrid Electric Vehicle (PHEV)
## 5         Battery Electric Vehicle (BEV)
## 6 Plug-in Hybrid Electric Vehicle (PHEV)
##   Clean_Alternative_Fuel_Vehicle_CAFV_Eligibility Electric_Range Base_MSRP
## 1           Not eligible due to low battery range             16         0
## 2           Not eligible due to low battery range             16         0
## 3         Clean Alternative Fuel Vehicle Eligible            210         0
## 4           Not eligible due to low battery range             25         0
## 5         Clean Alternative Fuel Vehicle Eligible            308         0
## 6           Not eligible due to low battery range             21         0
##   Legislative_District DOL_Vehicle_ID               Vehicle_Location
## 1                   34      235085336   POINT (-122.374105 47.54468)
## 2                   22      237896795  POINT (-122.943445 47.059252)
## 3                   22      154498865   POINT (-122.78083 47.083975)
## 4                   20      154525493   POINT (-122.85403 46.856085)
## 5                   14      225996361 POINT (-120.524012 46.5973939)
## 6                   22      220675367   POINT (-122.89692 47.043535)
##                               Electric_Utility X2020_Census_Tract Vehicle_Age
## 1 CITY OF SEATTLE - (WA)|CITY OF TACOMA - (WA)        53033011500           7
## 2                       PUGET SOUND ENERGY INC        53067011100           6
## 3                       PUGET SOUND ENERGY INC        53067012226           7
## 4                       PUGET SOUND ENERGY INC        53067012620           3
## 5                                   PACIFICORP        53077000800           4
## 6                       PUGET SOUND ENERGY INC        53067010802           1
##   Vehicle_Age_Category
## 1             Mid-aged
## 2             Mid-aged
## 3             Mid-aged
## 4       Relatively New
## 5       Relatively New
## 6                  New

##Creating the third field: MSRP Category

# Find the maximum and minimum values of Base.
elect_vec_clean %>%
  filter(Base_MSRP != 0)%>%
  summarise(
    max_msrp = max(Base_MSRP, na.rm = TRUE),
    min_msrp = min(Base_MSRP, na.rm = TRUE)
  ) 

##   max_msrp min_msrp
## 1   845000    31950

# Define breaks and labels
breaks <- c(-Inf, 0, 50000, 100000, 500000, Inf)
labels <- c("No Evaluation", "Low Range", "Medium Range", "High Range", "Premium")

# Create the category column using cut
elect_vec_clean <- elect_vec_clean %>%
  mutate(
    category = cut(Base_MSRP, breaks = breaks, labels = labels, include.lowest = TRUE)
  )



unique(elect_vec_clean$category)   

## [1] No Evaluation Medium Range  Low Range     High Range    Premium      
## Levels: No Evaluation Low Range Medium Range High Range Premium

CREATING SECOND DATA FRAME AND SECOND VISUALS:

This frame will contain the DOL_Vehicle_ID column and Vehicle_Age_Category, we will be using the dataframe to plot a barchart of the distribution of vehicles across the age categories

#creating the barchart data frame
elect_vec_clean_bar <- elect_vec_clean %>%
                        select(DOL_Vehicle_ID, Vehicle_Age_Category)


#creating the barchart plot
ggplot(elect_vec_clean_bar, aes(x = Vehicle_Age_Category)) +
  geom_bar(fill = "skyblue", color = "black") +
  labs(title = "Distribution of Vehicles by Age Category",
       x = "Vehicle Age Category",
       y = "Count") +
  theme_minimal()

From the graph, we can see that most vehicles in the dataset are newly made as their manufacturing date falls within the range of 2 years or less

CREATING THIRD DATA FRAME AND THIRD VISUALS:

For the third data frame we will be merging the cleaned electric vehicle dataset with the US cities dataset so as to get the longitude and latitude of all vehicles in washington state. We will then plot a map showing where all the vehicles can be found within the state

CREATING FOURTH AND FIFTH FIELD:LONGITUDE AND LATITUDE FIELDS

# Create a new dataframe and extract latitude and longitude 
washington_cities <- read.csv("C:/Users/USER/Documents/data analytics project/NERO FRIEND/uscities.csv") %>%
  filter(state_name == "Washington")%>%
  select(city, county_name, lat, lng)

# Merge with elect_vec_clean dataframe based on city and countynames
merged_elect_vec_clean <- elect_vec_clean %>%
  left_join(washington_cities, by = c("City" = "city", "County" = "county_name"))
 
head(merged_elect_vec_clean)

##    VIN_1_10_   County    City State Postal_Code Model_Year  Make    Model
## 1 WAUTPBFF4H     King Seattle    WA       98126       2017  AUDI       A3
## 2 WAUUPBFF2J Thurston Olympia    WA       98502       2018  AUDI       A3
## 3 5YJSA1E22H Thurston   Lacey    WA       98516       2017 TESLA  MODEL S
## 4 1C4JJXP62M Thurston  Tenino    WA       98589       2021  JEEP WRANGLER
## 5 5YJ3E1EC9L   Yakima  Yakima    WA       98902       2020 TESLA  MODEL 3
## 6 1C4JJXP66P Thurston Olympia    WA       98501       2023  JEEP WRANGLER
##                    Electric_Vehicle_Type
## 1 Plug-in Hybrid Electric Vehicle (PHEV)
## 2 Plug-in Hybrid Electric Vehicle (PHEV)
## 3         Battery Electric Vehicle (BEV)
## 4 Plug-in Hybrid Electric Vehicle (PHEV)
## 5         Battery Electric Vehicle (BEV)
## 6 Plug-in Hybrid Electric Vehicle (PHEV)
##   Clean_Alternative_Fuel_Vehicle_CAFV_Eligibility Electric_Range Base_MSRP
## 1           Not eligible due to low battery range             16         0
## 2           Not eligible due to low battery range             16         0
## 3         Clean Alternative Fuel Vehicle Eligible            210         0
## 4           Not eligible due to low battery range             25         0
## 5         Clean Alternative Fuel Vehicle Eligible            308         0
## 6           Not eligible due to low battery range             21         0
##   Legislative_District DOL_Vehicle_ID               Vehicle_Location
## 1                   34      235085336   POINT (-122.374105 47.54468)
## 2                   22      237896795  POINT (-122.943445 47.059252)
## 3                   22      154498865   POINT (-122.78083 47.083975)
## 4                   20      154525493   POINT (-122.85403 46.856085)
## 5                   14      225996361 POINT (-120.524012 46.5973939)
## 6                   22      220675367   POINT (-122.89692 47.043535)
##                               Electric_Utility X2020_Census_Tract Vehicle_Age
## 1 CITY OF SEATTLE - (WA)|CITY OF TACOMA - (WA)        53033011500           7
## 2                       PUGET SOUND ENERGY INC        53067011100           6
## 3                       PUGET SOUND ENERGY INC        53067012226           7
## 4                       PUGET SOUND ENERGY INC        53067012620           3
## 5                                   PACIFICORP        53077000800           4
## 6                       PUGET SOUND ENERGY INC        53067010802           1
##   Vehicle_Age_Category      category     lat       lng
## 1             Mid-aged No Evaluation 47.6211 -122.3244
## 2             Mid-aged No Evaluation 47.0417 -122.8959
## 3             Mid-aged No Evaluation 47.0462 -122.7933
## 4       Relatively New No Evaluation 46.8538 -122.8606
## 5       Relatively New No Evaluation 46.5923 -120.5496
## 6                  New No Evaluation 47.0417 -122.8959

library(leaflet)

## Warning: package 'leaflet' was built under R version 4.2.3

# Create a leaflet map
map <- leaflet() %>%
  # Add base map tiles
  addProviderTiles("CartoDB.Positron") %>%
  # Add circles for vehicle locations
  addCircleMarkers(data = merged_elect_vec_clean, 
                   lng = ~lng, 
                   lat = ~lat, 
                   radius = 3,
                   color = "blue",
                   popup = ~paste("City: ", City, "<br>",
                                  "County: ", County)) %>%
  # Add map controls
  addLegend("bottomright", 
            colors = "blue",
            labels = "Vehicle Locations",
            title = "Legend")

## Warning in validateCoords(lng, lat, funcName): Data contains 10221 rows with
## either missing or invalid lat/lon values and will be ignored

# Print the map
map

From the map we can see that most of the vehicles are found within Seattle and its environs

CREATING FOURTH DATA FRAME AND FOURTH VISUALS:

We will be creating a dataframe where we will have the total no of vehicles, each manufacturer has made in the dataset and their percentage contribution. We will then limit the dataframe to show the top 10 manufacturers and their percentage total contribution, we will also group other manufacturers who aren’t in the top 10 as others and also show their contribution. A piechart will then be created to show these results.

# Group the data by the Make column and count the number of occurrences
make_counts <- elect_vec_clean %>% 
  group_by(Make) %>% 
  summarise(Total = n()) %>% 
  arrange(desc(Total))


# Keep only the top 10 brands
top_10_brands <- head(make_counts, 10)

# Calculate the sum of counts for the remaining brands
other_brands_count <- sum(make_counts$Total) - sum(top_10_brands$Total)

# Create a new dataframe for the top 10 brands and 'Others'
top_10_and_others <- rbind(top_10_brands, data.frame(Make = "Others", Total = other_brands_count))

# Calculate the percentage contribution for each brand
top_10_and_others <- top_10_and_others %>%
  mutate(Percentage = Total / sum(Total) * 100)

# Define custom colors for the pie chart
custom_colors <- c("#1f77b4", "#ff7f0e", "#2ca02c", "#d62728", "#9467bd",
                   "#8c564b", "#e377c2", "#7f7f7f", "#bcbd22", "#17becf",
                   "#aec7e8")

# Create the pie chart with custom colors and percentage labels inside
ggplot(top_10_and_others, aes(x = "", y = Percentage, fill = Make)) +
  geom_bar(stat = "identity", width = 1) +
  coord_polar("y", start = 0) +
  geom_text(aes(label = paste0(round(Percentage), "%")), position = position_stack(vjust = 0.5)) +  # Add percentage labels
  labs(title = "Percentage Distribution of Top 10 Vehicle Manufacturers",
       fill = "Make",
       y = "Percentage") +
  scale_fill_manual(values = custom_colors) +  # Use custom colors
  scale_y_continuous(labels = scales::percent_format()) +
  theme_void() +
  theme(legend.position = "right")

From the pie chart we can see that Tesla owns majority of the electric vehicle market in the state with 45% of the vehicles manufactured in the dataset being manufactured by them.

CREATING THE FIFTH VISUAL:

we will be using a line graph to show the total number of vehicles recorded by the top 3 makes across the years

# Filter the data for Tesla, Nissan, and Chevrolet and exclude the year 2025
selected_makes <- c("TESLA", "NISSAN", "CHEVROLET")
filtered_data <- elect_vec_clean %>%
  filter(Make %in% selected_makes)

# Group the filtered data by Model_Year and Make, count the number of entries
vehicle_counts <- filtered_data %>%
  group_by(Model_Year, Make) %>%
  summarise(Count = n())

## `summarise()` has grouped output by 'Model_Year'. You can override using the
## `.groups` argument.

# Plot the line graph
ggplot(vehicle_counts, aes(x = Model_Year, y = Count, color = Make)) +
  geom_line() +
  labs(title = "Number of Registered Vehicles by Make Over the Years (Tesla, Nissan, Chevrolet)",
       x = "Year",
       y = "Number of Registered Vehicles") +
  theme_minimal()

We can see from the graph that Tesla has the most number of vehicles recorded in the State of Washignton

LIST OF DATAFRAMES AND SAVING THEM IN A DATABASE

#dataframe created for line graph visuals on total vehicles produced by top 3 makes across the years
head(vehicle_counts)

## # A tibble: 6 × 3
## # Groups:   Model_Year [4]
##   Model_Year Make      Count
##        <int> <chr>     <int>
## 1       1997 CHEVROLET     1
## 2       2008 TESLA        20
## 3       2010 TESLA        20
## 4       2011 CHEVROLET    74
## 5       2011 NISSAN      679
## 6       2011 TESLA         7

#dataframe created for pie chart visuals on percentage distribution of top 10 vehicle makes 
head(top_10_and_others)

## # A tibble: 6 × 3
##   Make      Total Percentage
##   <chr>     <int>      <dbl>
## 1 TESLA     80816      44.5 
## 2 NISSAN    14037       7.74
## 3 CHEVROLET 13864       7.64
## 4 FORD       9527       5.25
## 5 BMW        7680       4.23
## 6 KIA        7642       4.21

#dataframe containing all new fields added during the course of the project
head(merged_elect_vec_clean)

##    VIN_1_10_   County    City State Postal_Code Model_Year  Make    Model
## 1 WAUTPBFF4H     King Seattle    WA       98126       2017  AUDI       A3
## 2 WAUUPBFF2J Thurston Olympia    WA       98502       2018  AUDI       A3
## 3 5YJSA1E22H Thurston   Lacey    WA       98516       2017 TESLA  MODEL S
## 4 1C4JJXP62M Thurston  Tenino    WA       98589       2021  JEEP WRANGLER
## 5 5YJ3E1EC9L   Yakima  Yakima    WA       98902       2020 TESLA  MODEL 3
## 6 1C4JJXP66P Thurston Olympia    WA       98501       2023  JEEP WRANGLER
##                    Electric_Vehicle_Type
## 1 Plug-in Hybrid Electric Vehicle (PHEV)
## 2 Plug-in Hybrid Electric Vehicle (PHEV)
## 3         Battery Electric Vehicle (BEV)
## 4 Plug-in Hybrid Electric Vehicle (PHEV)
## 5         Battery Electric Vehicle (BEV)
## 6 Plug-in Hybrid Electric Vehicle (PHEV)
##   Clean_Alternative_Fuel_Vehicle_CAFV_Eligibility Electric_Range Base_MSRP
## 1           Not eligible due to low battery range             16         0
## 2           Not eligible due to low battery range             16         0
## 3         Clean Alternative Fuel Vehicle Eligible            210         0
## 4           Not eligible due to low battery range             25         0
## 5         Clean Alternative Fuel Vehicle Eligible            308         0
## 6           Not eligible due to low battery range             21         0
##   Legislative_District DOL_Vehicle_ID               Vehicle_Location
## 1                   34      235085336   POINT (-122.374105 47.54468)
## 2                   22      237896795  POINT (-122.943445 47.059252)
## 3                   22      154498865   POINT (-122.78083 47.083975)
## 4                   20      154525493   POINT (-122.85403 46.856085)
## 5                   14      225996361 POINT (-120.524012 46.5973939)
## 6                   22      220675367   POINT (-122.89692 47.043535)
##                               Electric_Utility X2020_Census_Tract Vehicle_Age
## 1 CITY OF SEATTLE - (WA)|CITY OF TACOMA - (WA)        53033011500           7
## 2                       PUGET SOUND ENERGY INC        53067011100           6
## 3                       PUGET SOUND ENERGY INC        53067012226           7
## 4                       PUGET SOUND ENERGY INC        53067012620           3
## 5                                   PACIFICORP        53077000800           4
## 6                       PUGET SOUND ENERGY INC        53067010802           1
##   Vehicle_Age_Category      category     lat       lng
## 1             Mid-aged No Evaluation 47.6211 -122.3244
## 2             Mid-aged No Evaluation 47.0417 -122.8959
## 3             Mid-aged No Evaluation 47.0462 -122.7933
## 4       Relatively New No Evaluation 46.8538 -122.8606
## 5       Relatively New No Evaluation 46.5923 -120.5496
## 6                  New No Evaluation 47.0417 -122.8959

library(DBI)

## Warning: package 'DBI' was built under R version 4.2.3

library(RSQLite)

## Warning: package 'RSQLite' was built under R version 4.2.3

# Connect to SQLite database
con <- dbConnect(RSQLite::SQLite(), "my_database.db")

# Create tables and insert data
dbWriteTable(con, "vehicle_counts", vehicle_counts, overwrite = TRUE)
dbWriteTable(con, "top_10_and_others", top_10_and_others, overwrite = TRUE)
dbWriteTable(con, "merged_elect_vec_clean", merged_elect_vec_clean, overwrite = TRUE)

# Close connection
dbDisconnect(con)

# Save data frames as CSV files
write.csv(vehicle_counts, "vehicle_counts.csv", row.names = FALSE)
write.csv(top_10_and_others, "top_10_and_others.csv", row.names = FALSE)
write.csv(merged_elect_vec_clean, "merged_elect_vec_clean.csv", row.names = FALSE)