Download the two provided project files (01_01_Accidents and 01_01_Weather), and revisit the final projects developed during the first two Excel Workshop courses. Explore the dynamic heat map designed to show accident rates by time of day and day of week, as well as the geo-spatial accident maps generated using Excel’s Power Map tool. Examine the new weather data set and review the dimensions and metrics provided.
- [Voiceover] I'd like to kick things off by downloading…and exploring the two project files provided.…0101 accident, and 0101 weather.…Starting with the accident file, you'll see a number…of tabs built during previous Excel workshop courses.…The heat map tab shows accident rates…by time of the day, and day of the week.…The pivot allows you to explore analyze the data set.…In the auto, bicycle, pedestrian accident locations tabs…show exactly where specific types of accidents took place.…
Using Excel's power map plug in.…The tab that you'll be using for this course…is the accident map, 2010 through 2013 tab.…Which contains the raw data.…Including records of every accident that occurred…in the city of Cambridge, Massachusetts…during this time period.…If I scroll all the way to the left, you'll see…a unique crash number for each…of these accidents in a data set.…We have a number of date and time variables…in column B trough M.…Including year, month, day.…
All the way down to the minute that…an accident took place in column K.…
Using conditional statements and text functions like LEFT, MID, RIGHT, and CONCATENATE, you'll learn to standardize data sets from the NCDC and create new dimensions. Then find out how to use VLOOKUP to join the data, and use COUNTIF statements to analyze trends. Once the historical data is in place, Chris shows how to use Excel's powerful WEBSERVICE and FILTERXML functions to tap directly into Weather Underground's API, which provides real-time weather information for any location and allows you to estimate accident rates based on current conditions.
Last but not least, you'll learn how to build custom charts to show the expected accident risk based on real-time inputs, and walk through some additional opportunities for deeper analysis.
- Standardizing data
- Creating new data dimensions fields
- Joining data
- Analyzing trends in the data with COUNTIF
- Creating an API feed with WEBSERVICE
- Extracting components with FILTERXML
- Visualizing the findings in a custom combo chart
- Applying your new skill set