Analyzing and visualization

Working With Data – Olympic Medalists

Introduction

Olympic is the world’s largest sports event. Since 1896, 200 countries have been participated in Olympic games. Starting from 1896, the Games have grown to an extent that almost every nation is representing. There is a major sub-division in Olympic games, Winter Olympics and Summer Olympics. Olympic events consist of 33 different sports and 400 events. Sports such as Aquatics, Archery, Athletics etc. Events such as 10m platform diving, 3m Springboard, 1000m run etc. It also gives opportunity to the host Nation to feature themselves to the world. We are going to explore more about the games, year of Olympics, different countries won the game, and the medalists list.

Data Acquisition

This involves collecting raw data and giving a proper structure to the data. Here we can skip this step as we already have a set of data given in the excel sheet.

Data Examination

The data presented in “Medalsdata1” and “Medalsdata2” mainly focuses on the medals won by any athletes in any of the Olympics sports events from 1896 to 2012. The data gives us brief history and knowledge about the Olympics and Olympics medal (i.e. Gold, Silver, and Bronze) winners. The data includes athlete names and their respective country names who they represented. We got the wide range of sports events and winners data. With the data analysis and visualization, we can know the stats of each countries of their strategies in which fields the countries are mostly winning medals. The data clearly gives us the message of the winners of the Olympics and the countries dominating throughout the years.

Data Examination – Medaldata1

The data also includes event year, sports, events, athlete’s names, their country details, the time they took to finish that event, etc. We do not have any source of truth available to verify these data; however, we can try to validate the data using other reference sources like Olympic, Topendsports, etc. Below table is a simple representation of the data being used in Medaldata1 sheet.

Data Examination – Medaldata2

Data examination involves the Physical properties of the data. Given below table describes the physical properties like size, type and conditions of the data given in Excel sheet.

Data findings from Medaldata1 and Medaldata2:

· Medaldata1 does not have a separate column for gender as this field is very important to analyze data in different categories.

· Medaldata1 data has some issues with the quality as we found 264 records in Athlete(s) column with unreadable format. For an example,

· At a random look, we found some data invalidation for Medaldata1 dataset. This record is about winners of Quadruple Sculls Women sports event in the year 1988, in which we have only 3 athletes given while as per the format of event, it must have 4 participating athletes. This puts the quality of the entire dataset in a question and needs a thorough check.

· Dataset2 has provided the data of Olympics from year 1920 to 2008. It does not have the data before 1920 i.e the data from 1986 to 1920 and the most recent Olympics data.

· Different events are held in different venues, but the data has shown only the host country location. It would have been more relevant if it gives the venue of each event.

· Though it has given the details of the medalists, does not give the total participants in the event.

· It has provided sports and its Disciplines along with different kinds of Events regardless of Gender. Athletes who have won the medals and to which Country are they representing.

· Total number of medals that has been given since 1920- 2008 are 26394(Gold, silver and Bronze).

· Total of 22 NOC’s have won the medals in 1920. Since 1920 to 2008 total of 134 countries have won the medals in Olympics.

Discrepancies between Medaldata1 and Medaldata2:

· The reliability and source of truth for Medaldata1 is not available while we have a reliable data source for Medaldata2 sheet as they refer the data from IOC Research and Reference Service. With that being said, Medaldata2 is more reliable and trustworthy.

· We have only records 4093 in Medaldata1 sheet while we have 26394 records in Medaldata2. Hence with more data, better we can have data analysis, comparison, and even further it can help with future predictions.

· Medaldata1 sheet includes the records from the year 1896 till 2012 while Medaldata2 consists data from the year 1920 till 2008. Both the datasets have excluded the year 1940 and 1944 as Olympics games were cancelled due to World War II. In addition, Medaldata1 sheet does not have any data for the 1916 as that was the time of World War I.

· Medaldata1 dataset has only 4 sports included; perhaps a reason for less amounts of data while Medaldata2 dataset is inclusive of 33 sports.

· Medaldata1 shows all the participating athletes’ names for all the group/team events as a single record while Medaldata2 has given the names of the participating athletes’ names for the same team/group event as individual raw records. For an example,

Medaldata1

Medaldata2

· The name format of Athlete(s) column is different between the two datasets, i.e. Medaldata 1 format is Firstname LASTNAME while Medaldata2 format is Lastname, Firstname.

· Medaldata2 has distinct columns for Discipline, Gender, Event and Event Gender unlike Medaldata1 which has only one column Event consist of Event and Event Gender.

· The naming conventions of the columns between the two datasets are also different; i.e. Medaldata1 has NOC to display country codes while Medaldata2 says CountryCode.

Data Transformation

Data transformation activity involves cleaning, converting, creating and consolidating. This can happen before, during, and after the data examination and data exploration. Data transformation is done to understand and present the data in more relevant way. This step is conducted to see the short comings of the data.

Cleaning:

In Medaldata1 dataset, we found junk records (254 records) with unreadable format in the Athlete(s) column. They have been corrected manually as there was no way of correcting them in an automated way looking at other reference materials. We also extracted Men and Women from Event column. We removed ResultInSeconds column as it is a data redundancy and does not make any meaning to keep two distinct columns (Result and ResultInSeconds) for the same output. All the data examination and cleansing work was done in MS Excel using as sorting and filtering to exclude or correct redundant records. For Medadata2 dataset, we did most of the work by hand, like sorting and filtering the data to see what is missing or what can be added to the data set. As part of this we have removed the column Gender as we already have a column with name event_gender which represents which gender must participate in that particular game.

Converting:

In Medaldata 1, we converted a column name Games as GamesYear as it gives more meaning to the column records what it displays. For Medaldata2, modified edition column from text format to Year format YYYY. Apart from this column all other columns looks fine with the data.

Creating:

For Medaldata1 dataset, we created two new columns to help the data with further analysis and exploration.

ID – This will act as a primary key for all the records. So that the same dataset can be merged, analyzed, or examined further with other dataset.

Gender – Created a new column Gender with the use of Event column from the dataset as it could be a very helpful factor in further analysis.

For Medaldata2 dataset,

ID – Created new column named ID, which helps in organization of data.

There are a few more columns that could have been added to understand the Olympic events deeply. For example we have two main categories in Olympics- Summer Olympics and Winter Olympics. Along with the given data, we would like to consider all the participating athletes irrespective of they have won any medal or not as that will give more analytical value from the each country’s performance wise and their ratio. Medaldata1 has only four categories of sports but it would be nice to include other sports category as well to broaden the scope of findings.

Consolidating:

Placed below is the consolidated sheet after data transformation:

For Medaldata2, we have added field ID and removed Gender field.

Fig 1.0: Consolidated Data

Data Exploration

Here we learn about the characteristics and qualities of data. Initially, we started with examining the data related to different countries participated and the number of medals won by them. This includes number of Gold, Silver and Bronze medals won by each country over the period of 1986 to 2008. As shown in Fig 1.1, we can predict what who will be wining more number of medals in the coming years.

Fig 1.1: Medals vs Country

This made us to analyze about athletes who have won more than 10 medals (including gold, silver and bronze) in this span of time. Which is shown is fig 1.2

Here we can see that for athletics sports category, highest number of medals are won by Larisa Latynina, Soviet Russia and more number of gold medals are won by Michael Phelps, USA.

General analysis can be done to know the ratio of men versus women who are highest earners of the Olympic medals. We can represent that in Pie chart as it gives the percentage of each sections.

Fig: 1.3 – Men versus Women

Even further deep dive into data analysis can tell us the country name who is doing exceptionally well in swimming sports event competition through Olympic history. As shown in figure 1.4, it is clearly evident that United States of America is dominating highly in any kind of swimming events with highest number of medals. They acquire 33% of all the medals given in the swimming events held in Olympics (Medaldata1).

Fig: 1.4 – Men versus Women

We have done other analysis too like medals in each discipline like swimming, archery etc.

Though URS has not participated after 1988 they still have more number of medals recorded.

Predictions: From the above figures we see most of the medals are won by USA. We can predict that in coming years also they will have the upper hand.

We have analyzed based on different sports and we see USA is good in Swimming when compared to Athletics, Canoeing and Rowing.

Fig 1.5: Countries vs Sports and Medals

Conclusion: By studying both the data sets we could conclude, that there is a need for more data to analyze and visualize the data sets. i.e. Summer Olympics and Winter Olympics. There are few more things that could have been added like World Record against each sport. Finally, as always the more we analyze and examine the data, more possibilities and scope for comparisons were seen.

References: