Create a new field to categorize and label different types of weather conditions (Rain, Snow, or Dry) based on precipitation data, using a combination of logical operations (<, >, =) and conditional IF and AND functions. Recognize the importance of using custom dimensions to help organize and analyze raw data.
- [Voiceover] Creating new fields or dimensions…to categorize your data can be a great way…to enhance a raw data set, improve organization…and encourage deeper analytics.…As an example, I'll demonstrate how to create a new column…to categorize weather conditions…based on raw precipitation data.…So I'll open up file 03_01_Weather and scroll to the right.…Here I have two columns, K and L,…which capture precipitation data.…Column K, PRCP captures any precipitation…that was recorded that day, whether it was rain or snow.…
Column L captures snowfall…specifically measured in millimeters.…So what I need to do is create a new category…or a new field called Conditions in column M…and us logical operators and conditional statements…to create three different buckets of conditions,…dry, rain and snow.…And I can create those conditions using my data…in columns K and L.…So I know that if there's no precipitation…and no snowfall, the conditions for that day are dry.…
If precipitation is greater than zero…but snowfall equals zero, I know that's a rainy day.…
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