Home > Pivot > Layout > Data Analysis Hockey Player Data Analysis ExampleThe sample files on this page have data from the 2018 Winter Olympic Hockey teams, from Canada and the USA. Download the file that has the data only, and experiment on your own. Or, get the file with my example analysis, using pivot tables to compare the teams. Note: You many not resell this sample data, in whole or in part, or include it in paid products. Author: Debra Dalgleish |
Hockey Player Data - IntroductionAt the 2018 Winter Olympics, the USA Women's Hockey team won the Olympic gold medal. They beat our Canadian team, in a hard-fought game that went into overtime, and ended with a shootout. The hockey team rosters were available online, so I used those for a hockey player data analysis, using Excel pivot tables. Would the player data give any clues as to why the USA team won? Since I'm not a hockey expert, or a master statistician, I'll leave the in-depth game analysis to someone else. Instead, here is my rudimentary player data analysis, based on the team rosters. 1) Sample Data - Hockey PlayersThe first sample data file has the team data that I downloaded and cleaned up, I also added columns with calculations, to create more data for sample analysis. In the sections below, you can see where I got the data, how I organized it, and the Excel calculations that were added. -- A) Team Rosters -- B) Calculations Get this File: You can get this sample file (#1) in the Download section, at the bottom of this page. |
A) Team RostersThe hockey team rosters were available online, so I used those for a hockey player data analysis If you'd like to do your own analysis, you can find the hockey team rosters here: Team Canada Site Team USA Site Wikipedia |
Organize the DataThe rosters had player names, height, date of birth, etc., but each roster was set up slightly differently, so there was some cleanup work to do, before starting. I rearranged the columns in each roster, and copied all the roster data into an Excel table. On the main data sheet, there is an Excel table, with the following structure:
|
Manual EntriesEach row shows the following 11 manual entry fields. I copied the roster data into these manual entry columns:
|
B) Calulated FieldsEach row shows these 4 calculated fields:
The details for these calculations are in the sections below -- 1) Age Calculations -- 2) Player Height in Feet -- 3) Height and Weight |
Calc 1) Age CalculationsBefore doing any player data analysis, a few calculations were needed in the rosters.
First, the rosters had each player's date of birth, so what formula could you use to get each player's age? To calculate the ages, I used this DATEDIF formula, but be careful with that function - it can give incorrect results in some versions.
|
Calc 2) Player Height in FeetNext, I had to calculate the height, so it could be used in the data analysis. In the roster, the height was listed as text, with feet and inches, and a foot mark, such as 5'7 How would you convert that to a usable number? I used the following formula to convert the text measurement to feet – for example, change from 5'7 to 5.58.
Here's how the formula works:
|
Calc 3) Height and WeightThe weight was entered as a number, so that didn't need to be changed. I thought it would be interesting to get a height/weight ratio, so I used the BMI calculation for that. weight in pounds / [height in inches x height in inches] x 703 First, I needed another column, to convert the height to inches, from feet. That was a simple formula – multiple the calculated foot height by 12:
Then, for the BMI calculation, I used the following formula – rounding to zero decimals, to limit the number of BMIs. (You could round to 1 or 2 decimals, if you needed greater precision. )
|
2) Sample Data - Hockey Player AnalysisThis sample file has the same data as the file above -- 2018 Olympic Hockey teams, from Canada and the USA. In addition to the data, this file includes several pivot tables that are described in the sections below -- A) Summarize Data in Pivot Table -- B) What Matters? -- C) Team Comparisons -- D) Why Did Team USA Win? Get this File: You can get this sample file (#2) in the Download section, at the bottom of this page. A) Summarize Data in Pivot TableAfter the calculations were completed, I built pivot tables to show the player data analysis. This video shows the steps for creating a pivot table in Excel 2013 and later, using different sample data. For detailed steps on building an Excel pivot table, go to the Create a Pivot Table in Excel page. |
B) What Matters?The team rosters don't show skill rating, or past performance stats, so what could I use for player data analysis?
|
C) Team ComparisonsIn the sample workbook, there are several pivot tables, comparing the teams. Details are in the following sections, below: --1) Teams - Overview --3) Player Age Ranges --5) Home Town Locations 1) Teams - OverviewThe first table shows player counts, for each position, plus age, height and weight. Summary FunctionsIn the pivot table, the age, height and weight are shown using 3 different summary functions - Average, Minimum, and Maximum.
|
Conditional FormattingIn the country subtotal row, conditional formatting highlights:
At the top of the worksheet, click the Slicer, to select the team type -- in the screen shot below, the Women's data was selected. Observation: On average, the USA team is a couple of years younger, a bit shorter and 4 pounds lighter. |
Men's Teams ComparisonFor the Men, the data is similar – the USA team is a little younger, shorter and lighter. 2) Spot Team DifferencesFor the next analysis, I wanted to see the differences between the teams' age, weight and height numbers. Instead of doing the arithmetic in your head, you can get a pivot table to do custom calculations. For these calculations, Iadded two more pivot tables, below the first one, connected to the same Slicer.
I hid the rows for Canada, because they are empty. |
3) Player Age RangesNext, I created a pivot table and pivot chart, to show the age ranges for the Mens and Womens players, from both countries. Observation: The men have a substantially longer career, from what this pivot table shows. NOTE: I created a new Theme for the workbook, with red and blue as the first 2 accent colours. That way, all the charts had red and blue bars. |
4) Player BMI by PositionNext, I set up a pivot table to show the BMIs, with a count of players in each position – Defence, Forward and Goalie.
NOTE: To keep the BMI list consistent, I set that field to "Show Items with No Data". Otherwise, some of the BMIs would not be listed, when you switch between the data for Men and Women. Observation: There's a noticeable difference between the BMIs for the two Women's teams. Or, just look at the player counts per BMI on each of the Women's teams – the patterns are quite different. Was that body type difference a factor in the USA victory? |
5) Home Town LocationsFinally, to see where the player home towns were located, I used the 3D Maps feature in Excel 2016. That command is on the Insert tab, between Charts and Sparklines. NOTE: The maps are not in the sample workbook – I created them in a separate file. If you have the 3D Map tool, you can build your own maps after you download my file. I was surprised that the map was able to get the locations from the shortened state and province names, e.g. "Ont.", instead of requiring full names or official postal abbreviations. |
D) Why Did Team USA Win?From my analysis of the data in the hockey player rosters, here's what I notices:
Maybe that gave them an advantage against our Canadian team. Or maybe they're just better under the pressure of a game-deciding shootout! If you analyze the data, you might find more clues about why the USA Women's team won, but I couldn't find any obvious answers. |
Download Sample Data1) Hockey - Data Only: Click here to get the hockey player data file, with the data only -- there are NO pivot table sheets. The zipped Excel file is in xlsx format, and does not contain any macros. 2) Hockey - Data Analysis: Click here to get the hockey data file, with pivot tables and pivot charts, analyzing the data. The zipped Excel file is in xlsx format, and does not contain any macros. |
Related LinksHere are a few of my Contextures tutorials on working with data in Excel, organizing it in tables, and summarizing the data in pivot tables. -- Excel Tables -- Pivot Tables |
Video – Gold Medal Game HighlightsHere's a video from CBC Sports, showing some of the Women's Hockey gold medal game highlights. |
Last updated: March 14, 2023 1:36 PM