Added: Nia Guillotte - Date: 19.11.2021 03:59 - Views: 47284 - Clicks: 4499
in. Now more than ever, I am wishing I was a professional athlete in the best shape library sex game my life, gearing u p for the Library sex game Olympics! But rather than feeling sorry for myself as I set up my spot on the couch with pillows, blankets, and a non-vegetable snack, I figured this would be an opportune time to apply some of my SQL knowledge shoutout to Udemy and Coursera and learn a thing or two from the historical olympic dataset.
Four — well, now five — years is a ificant amount of time. To put it into perspective, at the time of the last Summer Olympics, I had never coded before and had approximately zero exposure to data science. In this span of time, it can be difficult to situate ourselves into the right spirit whilst watching the olympics. I know I personally just stare at the screen in awe of the unimaginable athletic prowess being put on display.
Not this time around, though! The motivation for this project is ask and answer questions about how the Olympics have evolved over time, including questions about the participation and performance of women, the variation in athletic build i. I will be using a historical dataset on the modern Olympic Games, which includes all the Games Summer and Winter from Athens to Rio The source has been scraped from www. The data attributes that we will be working with include the following: id, name, sex, age, height, weight, team, noc, games, year, season, city, sport, event, medal.
Also note that while the Olympic Games now alternate between Summer and Winter sports every 2 years, that has not always been the case, which is why the season column will be useful. In this step-by-step tutorial, I will walk you through different types of SQL queries, ranging in difficulty and complexity. As for getting set up in a coding environment, you will want to make sure you have PostgreSQL and PgAdmin installed on your local machine.
If you do not have these already installed and need help doing so, please consult the references section at the end of this article. Next you will want to fire up a server through your localhost. You can do this through the psql command line or via PgAdmin. The default port will likely be Just make sure you remember the name of your database and your username because we will be needing those later. You will also want to navigate to your preferred code editor for writing Python code.
For the most enjoyable experience, I recommend hopping into a blank Jupyter Notebook. Within your Python environment, make sure you install the following packages: psycopg2pandasand matplotlib. In the first cell of your notebook, paste the following imports:. The first thing we must do is establish a connection to the PostgreSQL database using the psycopg2 library.
Now that all our data is located within our two relational database tables, we should be able to access it with a simple select statement. However, one less than ideal aspect of the psycopg2 library is that it is a bit difficult to view the result set in a digestible format.
While the library sex game class allows us to retrieve data from the database by iteration or using methods such as fetchonefetchmanyand fetchallit would be neat to be able to view the data in a tabular format. Fortunately, the pandas. It then returns a pandas DataFrame which consists of the result set.
Much better! For the remainder of our exploration, I will generally adhere to the following structure for each library sex game. COUNT is a built-in function that retrieves the of rows matching the query criteria. A: You will also see the ORDER BY operator being used, which sorts rows in the result set based on a column value, in either ascending or descending order SQL uses ascending order by default. Between andart competitions were a part of the Olympics.
Medals were awarded for architecture, literature, music, painting, and sculpture. There are actually two ways of doing this:. Use a subquery to specify that we want the result set to contain a sport that is still active as of Both methods yield the same result! It turns out that our new oldest athlete is a 72 year old equestrian from Austria. Though Aurthur von insert really long last name competed all the way back init appears that competing in old age is still common amongst equstrians see 4th row. Here, weight is indicated in kilograms.
For folks in the US, kg is equivalent to about pounds! Looking into height and weight helps me to remember that athleticism takes many different forms… and also that the myths surrounding the cardboard beds for the upcoming Tokyo Olympics will almost certainly be debunked. Since we have been viewing our result sets as pandas DataFrames thanks to sqliowe can easily make matplotlib bar graphs for more powerful magnitude comparisons. Library sex game, we can perform a simple query to get a sense of how many athletes have competed at the Summer Olympic Games over time. It appears that there has been a relatively linear increase of athletes competing in the Summer Olympic Games.
Since the very first Games inwhen there were less than 1, athletes competing, the athlete count has risen to almost 14, Unfortunately, there is a high chance that we will see a ificant dip in the bar for as many athletes have either tested positive for COVID or undergone some other circumstance as a result of the pandemic, rendering them unable to compete. These sports are characterized by their numerous events, as well as being largely individual sports. The top two countries that send the most athletes to the Olympic Games are the USA and Germany, with over 15, athletes in total from each respective country.
I was personally a bit surprised to see that Canada and Japan, and Australia not higher up on the list. Canada is known to have a pretty ificant presence at the Winter Olympic Games, but perhaps it sends less athletes to the Summer Games. A subquery allows you to construct complex queries, essentially performing a query on the of another query. This query goes out to the lady ballers who, I personally believe, do not get nearly enough recognition! I recently learned that they have quite the victory streak going, so I set out to verify that and discover some relevant context on the global stage.
To put it into perspective, they have been the reigning champs since before I was born. That is pretty impressive, but I need to know more now. This is where subqueries will come into play. When a table name in the FROM clause is substituted with a subquery, the subquery forms what is called a derived table. Derived tables can be given aliases to make selecting columns a bit less verbose.
When selecting columns from two different derived tables, we must specify a WHERE predicate which indicates that the share the same index column, otherwise a cross will be performed, which will yield way more rows than we intended. Interesting — Spain and France earned Silver medals in the most recent two Summer Games, respectively. To take this little exploration one step further, it is always important to study the names of the legends who have put the team on their backs over the years.
This section will involve some relatively complex queries consisting of multiple subqueries. If you are new to SQL, please do not be intimidated.
Interpreting the graphs is the more important part of this analysis. That said, if you are a SQL savant or striving to become one, this section introduces the use of aggregate functions such as MINMAXand AVGwhich take a column name and return the computed value after executing the mathematical operation. Since aggregate functions can only be applied to columns consisting of numerical data, the CAST operator will come in handy here, enabling us to convert from one data type strings to another integers or decimals. Unlike past SQL queries, sometimes we may not necessarily know which relationships in the data that we are looking to highlight.
In these scenarios, it is nice to be able to filter our dataset to a manageable size and toss the entire result set into an exploratory seaborn plotting function such as a swarm plot. A swarm plot gives a better representation of the distribution of values, but it does not scale well to large s of observations. The swarm plots above depict the respective height and weight distributions of high-performing athletes by sport. It makes intuitive sense that the sports with the tallest athletes seem to be basketball, handball, and volleyball, while the sports with the shortest athletes appear to be gymnastics, weightlifting, and wrestling.
With respect to weight, we see a wider distribution for wrestling, weightlifting, judo, and boxing because these events have weight classes towards which athletes will deliberately train their bodies. Athletics depicts a bit of tail effect, which is library sex game because many of the throwing events require a ificant amount of power.
Gymnastics, hockey, football, and swimming have the most concentrated distributions. Upon analyzing this figure, it occurred to me that I did not separate the data by sex. The complex SQL query to accomplish this has been library sex game into a gist for those who are interested:. Looking at the weight scatterplot above, we can glean a few interesting discoveries.
Most strikingly, the average weight for female library sex game is quite close to the average weight for male boxers, and we see a similar trend for weightlifting. Perhaps this has something to do with muscle mass or low center of gravity, but one can only speculate. For figure skating, the average female athlete, according to weight data, is more petite and lean, whereas the average male athlete is heavier.
This makes intuitive sense because male figure skaters often lift their female partners while in motion, so they have to have a more muscular physique. Lastly, it is no surprise that gymnasts are among the most lightweight of all athletes. Just for fun, it might be interesting to look at a swarm plot of age, categorized by sports, among medalists. When is the body able to perform at its peak? That said, if you are an equestrian or shooter, do not worry about age! You can be having a mid-life crisis and still go for gold. On the flip side, if your sport is gymnastics, judo, or weightlifting, early to mid thirties seems to be a good time to call it quits.
A beautiful normal distribution! It appears that 23 is the peak age to earn an Olympic medal. It gives us the ability to apply conditional logic to a column and transform the data into values that are more meaningful or convenient for our task. We will apply a CASE statement on the medal column in order to compute a weighted sum.
We want to take into the of gold, silver, and bronze medals that an athlete has earned, scaling them in a logical way rather than counting the of medals received generally. Michael Phelps and Paavo Nurmi are the only names I recognize, but this is pretty cool. There is also a lot of representation from the big three sports: Athletics, Gymnastics, and Swimming! To construct this query, we will use the same aforementioned weighted sum column in order to measure success as a combination of gold medals, silver medals, and bronze medals.
If you made it this far, thank you so much. It has been a while since my last article, but I am planning to keep doing projects that simultaneously push me out of my comfort zone and help me become more knowledgeable about a domain of interest. For those of you who would like to replicate this work or take it in your own direction, here is the GitHub repository which contains all library sex game code and data:.
Feel free to connect with me on LinkedIn or check out my portfolio website! Your home for data science.Library sex game
email: [email protected] - phone:(534) 234-2207 x 1512
Library Sex Games