Use conditional statements, logical operators, and lookup functions to categorize raw, real-time data in Excel. Understand the importance of defining consistent dimensions, and practice using precipitation and temperature data from the Weather Underground API to blend real-time conditions with historical weather records.
- [Voiceover] Categorizing real-time data allows you to join together both current and historical information and analyze how past trends may impact the present day. I'll show you an example of how you can categorize real-time weather conditions using precipitation and temperature data in order to blend it with historical weather records. So, open up the 05_03 exercise file in Excel and jump to the Raw Weather Data tab. Here you'll see columns N through P, which are fields that I created to define certain types of weather conditions and types.
So in column N, the Conditions field, essentially looks at the precipitation data in columns L and M and returns a value of either dry, rain, or snow. The Type column, column O, looks up the actual amount of precipitation within the Rain Percentiles tab, or the Snow Percentile's tab and returns a label of either Light or Heavy, depending on the amount of precipitation. Last but not least, on the Raw Weather Data tab, we have a Weather Label field in column P, which essentially just combines the values of N and O.
So what I'll show you how to do, is create very similar consistent fields in our Weather Dashboard tab to categorize our real-time data in the same way. So, I'll right click row 11 and insert three new rows here. And what I'll do is replicate the fields that we create in the Raw Weather Data tab. So Conditions, Type and Weather Label. So the next step is to populate these fields using the real-time information that I have.
And I've got temperature data and precipitation data. So for Conditions, I'm gonna start with a conditional statement and type If Precipitation Field equals zero, so if there's nothing falling out of the sky, I wanna call the conditions Dry. If, and my precipitation is greater than zero, so if there is precipitation falling from the sky and also the temperature is greater than or equal to 32 degrees Fahrenheit, which is the freezing point, then my conditions will be Rain.
If and my precipitation is greater than zero, and my temperature is below the freezing point, or 32 degrees, I want conditions to be Snow. And then last but not least, my value if false catch all will be Other. So close it out with three parenthesis. And as you can see, current conditions in Cambridge Massachusetts are rainy because we do have precipitation falling and the temperature is above the freezing point. Next up, I'll define the Type field, using similar conditional statements, in this case, if the conditions that I just defined equal rain, then what I need to do is look up the precipitation amount in cell F4 within my Rain Percentiles tab.
I'm gonna select columns A through C, lock in that reference. I want the label in the third column over. And for range look up, here's the key, I can't use exact match like you typically might with a Vlookup function. You need to use the approximate match here because not every recorded value of precipitation will have a value matched in this table. So, And I'll select columns A through C. And lock in that reference with the F4 shortcut.
My column index number will be a three. And the range look up, here I won't use the exact match like you typically would with a Vlookup function, I'll use approximate match, or a one because not every recorded value for precipitation will have an exact match within this table. So what an approximate match does, is it allows that data to match to the closest value within this table. So close out that Vlookup function and move on to my next If statement.
So in this case, IFfmy Weather Conditions equal Snow, now I need to take a similar process. I'll Vlookup the precipitation amount, so F4. The difference is that this time, I'll look it up in the Snow Percentiles tab, again selecting columns A through C, locking that reference with F4. Third column over, approximate match, close off the Vlookup statement and then comma to my value if false, or my catch all value.
And if neither of these things are true, if the conditions aren't Rain or Snow, then the only other option is Dry. Close it out with two parenthesis, hit Enter and it returns Light because six millimeters of rain falls into the Light category as opposed to Heavy. Last but not least, for Weather Label, I'll type an If statement here. And say, if my conditions are Dry, then let's just call the Weather Label Dry, otherwise, let's combine the type, add a space in between and the Conditions.
So what this does, is it just creates a user-friendly label, in this case, Light Rain. And what I've done here is I've created a consistent set of fields that now allow me to blend this real-time data with the historical weather data. So key takeaway, create a link between historical and real-time data by defining consistent fields or categories.
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