 And welcome back to Hawaii, the state of clean energy on think tech Hawaii. I'm your host, Mitch Yuan. And today on our show, we'll be discussing data is power, decarbonizing vehicles with our guest Troy Wooten from the Hawaii State Energy Office. So welcome Troy, thanks for coming on our show. And I'm just thinking for having me giving a little elevator pitch on your background where you come from and how you got to arrive at the Hawaii State Energy Office. Sure. Thank you very much for having me on the show. And thank you to everyone who's tuning in. And I'm Troy Wooten. I'm the data science specialist here at the Hawaii State Energy Office. And I just recently had the pleasure of starting with the office back in January of this year. Before that, I was completing my master's of science degree in information and computer sciences here at UH Manoa and I completed my master's this past fall. And the laboratory that I worked at as a research assistant is known as the laboratory for advanced visualization and applications. And we've had some longstanding data visualization collaborations with the state energy office. One of those projects is the fairly well publicized at this point Haven project. It's a geospatial visualization where data showing renewable energy scenarios and RPS scenarios is visualized on a 3D map that you can actually physically interact with and touch in the room. So I have these goggles on and a virtual reality kind of a show. So this one, this project, the Haven project in its current form or in its past iterations, I guess, hasn't required the use of the VR headset. It's more of kind of, I guess you would call it a mixed reality display. So you have a projector that's above a table that projects the data layers down onto an actual physical map model of the island of Oahu and shows you kind of the projected plan development of solar projects and wind and various types of renewable energy. We've got to have you back on the show for round two to go through this because this sounds like that gee whiz rocket science stuff, so that'd be really great. It kind of is, yeah. And yeah, definitely would be something that we did discuss on a later iteration of the show for sure. Well, let's get on with data is power and decarbonizing our vehicles here at Hawaii. So why don't we kick it off and let's go to the first slide. OK, great. Yeah, so as Mitch mentioned, I'm here today to talk about data is power and how we can leverage data and data analytics to inform our efforts to decarbonize transportation and vehicles within the state of Hawaii. So if you don't mind, please go into the next slide. Yeah, so to share a little bit of the motivation behind today's talk. So Hawaii is set an ambitious goal of fully decarbonizing our economy by 2045. And in order to effectively and accurately allocate our effort to decarbonizing our economy, we have to first understand where is the majority of the energy consumption being being allocated within the state. And so here on the right, you see a donut chart showing energy use in the state of Hawaii by in sector. This is from 2018 data from the Energy Information Administration, state energy data system. And so we can see quickly in this chart that transportation accounts for over half of the energy use in the state of Hawaii. Additionally, the state has set a goal to reduce our reliance on imported fossil fuels in conjunction with this goal of decarbonizing our economy in general. Since ground transportation amounts to such a large proportion of our energy consumption, it makes sense to include specific stated goals for ground transportation within our larger goal of decarbonizing the economy. And within transportation, being a very diversified sector, encompassing various modes and means of transportation, light duty vehicles represent a sort of low hanging fruit that we can initially target our decarbonization efforts towards. And the reason being that light duty vehicles, those are your typical consumer car like a sedan or a pickup or an SUV that you might drive to work or just use as your personal car. And more so today than in over the past decade, we've seen a proliferation of available EV models come on the market from a variety of manufacturers and starting to penetrate into pickup trucks and SUVs in recent years. And so what we would like to do as the state is lead by example and initially focus our decarbonization efforts on decarbonizing the state fleet specifically. And so what I'm going to talk to you about today is an analysis of a data set that encompasses the state of Hawaii's vehicle fleet and what the makeup of that fleet is. And starting to discuss what we can do to prioritize our efforts to decarbonize our fleet. So if we don't mind moving to the next slide. So to really quickly give you the rundown of the sources of data that were used in this analysis, the primary source of data that the primary group that maintains the data set of registered vehicles that are registered to the state government is the city and county of Honolulu's IT department. And so they provide us a data set of or each row within the data set is a unique vehicle. And for each vehicle, they are also kind enough to maintain and provide the vehicle identification number or VIN number. And for those folks who are watching who aren't already aware, the VIN number is essentially kind of like a unique ID for your vehicle. And not only uniquely IDs your vehicle as a unique entity, but it also has information encoded into it that describes what kind of vehicle it is. So whether it's a SUV or a pickup or a sedan, whether it's an electric vehicle, a hybrid vehicle or an internal combustion engine vehicle, as well as the vehicle weight, which is really important in discussing, you know, how many lights, medium and heavy duty vehicles we have. And many other descriptive indicators as we'll see on in later slides and later on the talk. So what I did with this data set, since the initial data set, it's a very valuable resource to have, but it's not complete in terms of the descriptive indicators of what kinds of vehicles we have in the fleet. I then took those VIN numbers and passed them to an API that's provided by the National Highway Traffic Safety Administration. They have a publicly available API that's free to use. And essentially what you do is you, if we can move to the next slide, please, and I'll describe how this process works mechanically. So as I mentioned previously, within your 17-digit VIN number, you have a subset of those digits that are labeled VDS, and VDS stands for Vehicle Descriptor Sequence. And so this is the region of the VIN number that's going to tell you things about your vehicle that make it unique from other vehicles in terms of the type of vehicle that it is, whether it's a passenger car or an SUV or a pickup truck, what its primary and secondary fuel types are, as well as its weight. And so in order to decode the VIN and get these descriptive fields in a decompressed form, I take the VIN number for each vehicle in the registered state fleet data set and pass it to the API. An API stands for Application Programming Interface, for those of you out there that don't know. Essentially you can think of it as being like a website. So you type in a URL into your web browser and that your computer sends a packet to a web server that then sends you back the web page that you requested. And so an API works much the same way. I have a URL that I plug the VIN number into and send that as a request to their API and then their API server sends back all of the information on that vehicle. And I'm able to access a number of descriptive fields, roughly 130 descriptive fields in total, including things such as vehicle weight and fuel type, etc., as I mentioned previously. And so if we could go to the next slide. So as is often the case when you're working with data, especially data coming from other sources that you don't really have a lot of control over how the data is gathered or entered or quality checked necessarily, there will be missing values and there will be VIN numbers in this case that were not input into the database correctly. You know, human error is inevitable when you're typing at the keyboard, if there's no input. Yeah, right? Who would have thought, right? People make mistakes. Who would have ever thought that? Yeah, so because I want to be able to leverage this data in a comprehensive fashion, we want the data set to be as complete as possible. So after decoding the roughly 5,000 rows present in the data set, meaning we have on the order of 5,000 vehicles in the state fleet, at least recent as of December of last year, there were still 160 rows left with missing information. And so in this slide, what I'm presenting is an algorithm that I utilized to attempt to make a best effort to fill in a lot of that missing data. And so what it entails essentially is we isolate the rows that have complete data with good VINs, VIN numbers that were entered correctly into the system, and we also isolate the rows with missing information. And in an attempt to fill in missing information, we search through the rows that have complete information and we do an edit distance search by VIN number. And what that means is you try to find existing VIN numbers in the complete data that closely match the VIN number that you're trying to find data or information for and fill in the gaps. And so in this example, we see an example of a bad VIN number that was put in the system incorrectly and we're able to search through the good rows and find a VIN number that has an edit distance of two, meaning that its VIN number differs in two positions from the bad VIN. And we're able to see that, okay, the make and the model of that car with the good VIN matches. And so we're able to say, okay, we'll just go ahead and map the gross vehicle weight rating and the body style of that vehicle to the row that had missing data as an example. A quick question, Troy. Was this your own design? I mean, was this an off-the-shelf program or is this something that you developed yourself? Oh, I see. So a lot of the work in this analysis is sort of hand-coded by me. So for those of us in the audience who are slightly programming savvy, I prefer to do all of my data analysis work in the Python programming language. It's a scripting language similar to R and other languages that are used for statistics. But essentially, a lot of the code is handwritten by me and I'm on the fly. But I'll take inspiration and leverage heavily from publicly available libraries and packages that are out there for doing data visualization and different types of analyses. So I don't have to just write it all from scratch necessarily, but I'll kind of find the bits and pieces that are out there that are available that I can leverage and then stitch them together, essentially. Well, good job. Thank you. Okay, and so once we've gone through this procedure of cleaning up the data and pre-processing it and filling in missing values, we're now ready to kind of dive into some questions that we can answer using this data set. And so one of the questions that we would want to answer first at sort of a high level is just how many light, medium, and heavy-duty vehicles do we have in the state fleet? It's a really basic question, but there's actually a lot that goes into answering it. And so to begin with, the NHTSA API that I used to decode the VINs is a very granular view of the vehicle weight rating of each vehicle. And the specific weight rating that's used is called GVWR, and that stands for Gross Vehicle Weight Rating. And what that essentially means is that's the weight of your vehicle. When it's at full capacity, it's got a full set of passengers on board, a full tank of gas or a full tank of fuel, whatever fuel it happens to run on. And that is its gross vehicle weight rating. And so the US EPA, the Environmental Protection Agency, they maintain a set of categorizations of vehicles by gross vehicle weight rating. And essentially, vehicles can be broken down into light, medium and heavy-duty vehicles by their gross vehicle weight, and light duty being any vehicle that's less than 8,500 pounds or equal to, medium duty being 8,501 to 10,000 pounds, and heavy duty being 10,000 pounds plus. And so the reason why we want to group the vehicles in this way is the vehicles that will tend to have EV or zero-emission vehicle equivalent models are going to fall into your light-duty vehicle categorization, whereas your medium and heavy-duty vehicles tend to be sort of a special-purpose vehicle, such as a refuse truck or, you know, an oil tanker truck or a gas truck, et cetera, city transit buses and school buses that will tend to run on large diesel engines that generate a lot of torque and enable the vehicle to carry heavy loads. Although this is changing, you know, all the time, and we are seeing more EV buses and alternative fuel, medium and heavy-duty vehicles come on board and there's a lot of research and development in those areas as I'm sure you're aware, Mitch, of being a hydrogen guy yourself. Yeah, awesome. So anyway, so that's kind of how I go about categorizing the vehicles into light, medium, and heavy-duty based on the EPA-published categorizations. And so if we go to the next slide. So despite the fact that I expended a fair amount of effort in filling in the missing data after going through this edit distance search approach, I was still left with 66 roads that had missing information. And so in order to ascertain, you know, why am I still unable to get complete information for 66 rows of data? Even though it only represents like a percent of the overall data set, it's still important to have an understanding of what's going on here. So if we actually go and look at the rows and look at the descriptions of what these vehicles actually are, we see, you know, a pattern about immediately you have some water tankers in there, some dump trucks. There's also the ZIP mobile, which for folks who are not aware is this big vehicle that's used to open and close the zipper lane on the freeway every day. And so these are essentially what would amount to special purpose vehicles, you know, vehicles that are intended to serve a special purpose. And so even if we don't have, you know, precise vehicle weight information for these vehicles, we can assume that, you know, these will tend to fall into your medium or heavy duty classification. And so what I did is I just stuck the label special purpose on those vehicles as a way to handle those extra missing rows. And so now if we go to the next slide. So now that I have a complete summarization of the state vehicle plate broken down by weight pating into light, medium, heavy duty and special purpose, the question then becomes, OK, how do you visualize this data in a chart so that you can represent it to the public and present it to stakeholders and policy makers? OK, and so we're going to discuss this a little bit. And this is going to be a little bit of a data visualization one on one. So if we move to the next slide. So many people when they see data that, you know, can be broken down into various subcategories, right, they tend to think, oh, that would work well with a pie chart, right? Let me stick it into a pie chart and I'll use Excel or whatever my, you know, go to visualization tool is. And I'll plug it in and it'll spit out its pie chart. And then I'll throw that into my slideshow and call it a day, right? And everybody will be ready to to ogle this pie chart and they'll they'll get a lot of great insight out of it. However, this is not as well known, although it's pretty well known among visualization experts and that's that there are issues with the pie chart. And so, Mitch, I'd like to pose you a question. So here on this slide, we have three pie charts, each pie chart being generated by a different underlying data set. And if I were to pose you the question of the first chart, which group is the biggest of A, B, C, D or E? And of the second chart, which is the smallest? And of the third chart, how do the groups compare? What would be your guess? It looks like D and maybe E are like the bigger ones on that first pie chart to me. And then if I go down the line because, you know, at the end of the day, they all kind of look the same. Exactly, right? Determine ones bigger than the other. They're all the same, but there may be some differences in there. So you're going to tell us why I'm wrong or how we can do this better. Exactly. So if we move to the next slide, so now we have the same data that was in the pie charts on the previous slide, but now instead of visualizing them in a pie chart, we're using a bar chart. And now where I depose you the question of the first chart, which group is the biggest? What would be your answer? Well, obviously it's in the first one is Echo or E. Of course, right? And then of the second chart, which is the smallest? The other two, yeah. So it could be troops right out, you can tell. Exactly. So among, you know, within a matter of a couple of seconds of looking at these visualizations, the trend jumps out at you immediately. And the way the comparison between these groups and their magnitude sticks out right away. And so for this reason, in the biz community, we tend to prefer alternative ways of representing categorical data where our data set is broken down into various groups of different sizes, other than the pie chart, which you see thrown all over the place and overused and often misused. And albeit this is a contrived example. This is fake data. The color scheme used isn't ideal necessarily, but it's sort of illustrative of why you would tend to want to shy away from a pie chart in general. Or if you just are too much of a pie chart fan, stick to donut charts like the chart on my first slide. And those will serve you well enough. So now, if we move on to the next slide, you'll see the chart that I actually chose to represent the breakdown of the state vehicle fleet is the less well-known waffle chart. And so essentially in this chart, you have groups of counts of vehicles being symbolized by symbols. And light duty vehicles are represented as these green sort of sedan looking car icon. Medium duty vehicles being represented by blue sort of a passenger van, larger passenger van looking car icon. And then heavy duty vehicles being represented by this sort of moving truck, the red moving truck. And then finally, we have our special purpose vehicles, those 66 vehicles that we couldn't get full information for as sort of a gray tractor indicating those are kind of another group. But upon looking at this chart, you're able to quickly see not only the magnitude of each of these groups. So we can kind of roughly see that three fifths of the fleet are light duty vehicles. And then another, let's say 25% are medium duty and then a remaining 15% are heavy duty. We also not in addition to magnitude are able to have more of a sense of sort of a proportionality of each of these groups and how they add up to the larger whole that is the state vehicle fleet, right? So if we can move on to the next slide, we'll dive into sort of take a deeper look at the other kinds of questions that we can answer from this data analysis that was done on the vehicle fleet. So not only do we wanna know how many light duty vehicles there are in the state fleet but also what kinds of vehicles are we talking about when we say light duty, right? So light duty is really just this weight based classification but it doesn't necessarily tell you if they are we talking about just sedans or are we including other types of vehicles? And so here we have a bar chart ordered by count of the type of vehicle. And we can see that the most actually the most frequent type of vehicle falling into light duty is your pickup truck. And those of us who grew up in Hawaii or have lived in Hawaii long enough, we know that the most popular car by far is the Toyota Tacoma, which is itself a pickup truck, of course. But then a close runner up to the pickup truck is the sedan and then followed by the SUV or multi-purpose vehicle. And then tapering off with other types of vehicles like station wagons and vans and cargo vans, et cetera. Okay, so this kind of gives us a sense of when we talk about light duty vehicles, what are we really talking about essentially? And so now if we move on to the next slide, the other thing that we can start to look at is, okay, you know, since we know that in this, you know, in the current day and age, when we wanna start assessing, you know, whether we can replace certain vehicles with equivalent zero emission vehicle models, in general passenger cars will tend to be the category of vehicles for which there are a plethora or an abundance of options to choose from when it comes to choosing electric vehicles, battery electric vehicles or hydrogen vehicles as your zero emission options. And so here we have a visualization showing the number of light duty passenger cars by State Department. And so right at the top, we have DAGs followed by, I believe, UH and DOT. And so this kind of gives us a sense of, you know, where the majority of the State Fleet is, which departments they fall under and can kind of start to inform us as to where we should initially prioritize our efforts in carbonizing our own fleet. And just for those of you who are wondering or if there is any doubt of passenger cars, essentially a sedan or a coupe or a hatchback, you know, the typical thing you would drive to work that's gonna be a fuel-efficient vehicle that's meant for carrying very few passengers and not much else. And within the State Fleet, passenger cars or light duty passenger cars account for 1,033 vehicles in total. So we're not talking about pocket changes, this is a substantial number of vehicles. However, in addition to simply knowing, you know, which departments these vehicles fall under or are in use by, we also need to ask, okay, how old are these vehicles? Are these vehicles reaching the end of their serviceable lifetime? Are they ready to be replaced or not? So if we move to that, I'm sorry? We have a graph for that? We do, we do indeed. So if we move to the next slide, I believe. Yeah, so in this slide, on the left, we have a view of the age of the estate vehicle fleet today. And so this is broken down by departments for the top five departments. And then in the sixth waffle chart on the bottom right of the left sort of array of waffle charts, we have others, which it just groups together the remaining departments whose fleets are substantially smaller than the top five. And so you can see that today, with the exception of DAGs, at least half of the fleets currently in service today are 11 years or older in age, essentially. And so what that means is there's this sort of highlights that there's an urgency within certain departments. There will be an urgency to replace some of these vehicles as they are reaching the end of their serviceable lifetime. And so what this tells us is that we need to move fast in sort of accurately assessing where we should prioritize effort in terms of investing in electric vehicle charging infrastructure and deploying that infrastructure so that these agencies are more readily able to replace their current internal combustion engine vehicles with EV electric vehicle models that can be charged on site or in close proximity to where they are stationed. And then on the right side, the right group of waffle charts, what I did was I took the data sets and I synthetically aged it. So keeping the number of vehicles and the vehicles in the data set fixed, but just seeing how this age distribution would change in 2025. And as we can see that in most cases for most departments, their fleets are aging out incredibly quickly. So well over half of their fleets are gonna be in the 11 plus year range. And then in some cases, the fleets are even older than that and the 16 plus year range. And so these departments are gonna be looking to replace their vehicles. And what we wanna avoid is anyone deciding that, okay, this vehicle's reached the end of its serviceable duty lifetime. I'm gonna replace it with another ICE vehicle. That would be really disadvantageous for our effort because essentially when you do that, the clock resets. And we're stuck with that vehicle for at least another 10 years because any department that has gone through the procurement process and spent the money on a new ICE vehicle, isn't gonna wanna go replace it tomorrow or in a few months with a battery electric vehicle. So the time is now really for us to assess where our efforts are best expended and where we can best look at deploying, charging infrastructure and sort of reduce the friction involved with acquiring electric vehicles and sort of familiarizing ourselves with electric vehicles and leveraging them in our efforts to decarbonize our fleet. Although this analysis does a good job of sort of giving a preliminary view, there's still a lot that's gonna go into these decisions. We need to also assess where are these vehicles housed? Where are they located? Because this data sets spans vehicles on all of the main islands as well as Molokai and Lanai. And so we need to look at, okay, do these departments, do certain departments have vehicles that are housed in close proximity to one another? Could we potentially kill multiple birds with one stone by sort of collaboratively deploying infrastructure that can service vehicles that are owned and utilized by multiple departments? Okay, well, believe it or not, Troy, I told you it would go fast. We're at the end of our time. So I'm ready to leave it there. You've been watching and I'm gonna tell you, but thank you very much for being on the show. So you've been watching Hawaii, the state of clean energy on Think Tech Hawaii. And today we've been discussing data is powered and it really is power. Now I can see how you can, what a powerful tool it is that HSEO has developed. And thank you, Troy, for helping us out on that. And Troy, thanks for being on our show. All right, thank you for having me. So we'll see you all next week. And this is Mitchu and signing off, saying aloha.