Create new fields to begin to standardize date formatting across data sources. Use Excel’s LEFT, MID, and RIGHT text functions to extract the year, month, and day from a raw date field, and learn the importance of creating consistent fields that can be used to join data together from multiple sources.
- [Voiceover] In order to standardize date formats…across data sources I'll show you how to extract…components from a date stored as text…using Excel's left, mid, and right functions.…So, open up the 02_01_Weather excel file…and start by inserting three new columns.…So, I can right click column G,…select insert, and then use the F4 shortcut…two more times.…And I'll name these columns year, month and day.…And this is where I'll extra pieces of the date in column F,…which is stored as text.…
And the way to check that is to right click…one of the values, select format cells,…and if I select the date category…you'll see a bunch of hash marks.…And that means that Excel doesn't know how…to interpret this as a date…and its treating it just as a text string.…So, if I expand the filter and the column header here…in column F.…I can see that all of these dates are eight characters long…and they consist of the four digit year,…followed by a two digit month, and a two digit day.…
So, that's the information that I need to extract,…
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