# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-1b6b75485105e36c", "locked": true, "schema_version": 1, "solution": false}
# # BLU03 - Exercises Notebook

# + deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-ee9dcdd4eb1308b9", "locked": true, "schema_version": 1, "solution": false}
import hashlib # for grading purposes
import math
import numpy as np
import pandas as pd
import requests
import sqlalchemy

from bs4 import BeautifulSoup

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-cc67f3fcb340fbcd", "locked": true, "schema_version": 1, "solution": false}
# ## Part A - SQL exercises
#
# ### Querying the FIFAdb with a SQL client
#
# Open your favorite SQL client and connect to the FIFAdb.
# The connection settings are the following.
#
# * host: data-wrangling-batch3.cl9uj9cucww7.eu-west-1.rds.amazonaws.com
# * port: 5432
# * user: ldsa_student
# * database: fifa
# * schema: public
# * password: <PASSWORD> (shared through slack)
#
# This is a different database than the one we used in the learning notebooks. This database contains information about football matches, players, teams, and which league and country these matches took place in. Additionally, it also countains the player's and team's "attributes", sourced from the EA Sports' FIFA video game series.
#
# The tables in this database are the following:
#
# 1. Match: has information about the the football matches: who were the 11 home and away players (identified by their player_id), how many goals did each team score, the date of the match, the league id and the home/away team id's.
# 2. Player: contains informations about the players.
# 3. Team: contains information about the teams.
# 4. League: contains information about the football leagues, including the id of the country where they take place.
# 5. Country: names and id's of the countries
# 6. Player_Attributes: contains the attributes for each player.
# 7. Team_Attributes: contains the attributes for each team.
#
# You can preview these tables using the SQL client.
#
# ### Q1. Select the name of the player with id 30981
#
# Write a query that selects the name of the player whose id is 30981, and run it in the SQL client.
#
# Then, assign the result to variable id30981_player_name (just copy and paste the name you obtained).

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-03b22b93be7b587e", "locked": false, "schema_version": 1, "solution": true}
# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-92e8f002ca863db1", "locked": true, "points": 1, "schema_version": 1, "solution": false}
expected_hash = 'e3ccd9684de593c7c6b6354cbe413d233959e7677258bfc3727d807e5900dce2'
assert hashlib.sha256(id30981_player_name.encode()).hexdigest() == expected_hash
# -

# ### Q2. Calculate the maximum number of goals scored by team with id 9825, when playing at home
#
# Write a query that calculates the highest amount of goals scored by team with id 9825, when playing at home.
#
# Then, assign the result to variable max_goals_by_team_id_9825 (just copy and paste the value).

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-be58fad2af1735bd", "locked": false, "schema_version": 1, "solution": true}
# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-1f2f87262d1b5fc4", "locked": true, "points": 1, "schema_version": 1, "solution": false}
expected_hash = '7902699be42c8a8e46fbbb4501726517e86b22c56a189f7625a6da49081b2451'
assert hashlib.sha256(str(max_goals_by_team_id_9825).encode()).hexdigest() == expected_hash

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-010c418f565b3108", "locked": true, "schema_version": 1, "solution": false}
# ### Q3. Calculate the average overall rating of players whose first name is Cristiano 
#
# Are Cristianos predisposed to be good Football players? Only one way to find out!
#
# Write a query that calculates the average overall_rating attribute of players whose name is "Cristiano *something else*" (that is, players whose first name is Cristiano, and last name varies), and run it in the SQL client.
#
# Then, assign the result to variable avg_cristiano_rating (round up to the nearest integer).
#
# **Hint**: check the [LIKE](https://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-LIKE) keyword for this exercise.

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-3a7464176782e2cb", "locked": false, "schema_version": 1, "solution": true}
# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-f1f24a46b0d15dc9", "locked": true, "points": 1, "schema_version": 1, "solution": false}
expected_hashes = ['8722616204217eddb39e7df969e0698aed8e599ba62ed2de1ce49b03ade0fede', '96061e92f58e4bdcdee73df36183fe3ac64747c81c26f6c83aada8d2aabb1864', 'eb624dbe56eb6620ae62080c10a273cab73ae8eca98ab17b731446a31c79393a']
assert hashlib.sha256(str(avg_cristiano_rating).encode()).hexdigest() in expected_hashes

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-f1ef8aaf001e8015", "locked": true, "schema_version": 1, "solution": false}
# ### Q4. Count how many different teams played in Portugal
#
# Write a query that counts the number of different teams that played in Portugal, across all games. You can calculate this value considering only the home or away team (it should be the same because every team has played on both sides of the field!).
#
# Assign the result to variable number_of_portuguese_teams (just copy and paste the value).
#
# **Hints**: keep in mind you only want to count DISTINCT team names. For this, the [DISTINCT](https://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT) keyword will be essential. Also, remember that the relationship between Country and Match isn't explicitly presented on the Match table, but there is a relationship between League and Country.

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-645c2fbe3b16c880", "locked": false, "schema_version": 1, "solution": true}
# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-41d99aabd53c87e7", "locked": true, "points": 1, "schema_version": 1, "solution": false}
expected_hash = '35135aaa6cc23891b40cb3f378c53a17a1127210ce60e125ccf03efcfdaec458'
assert hashlib.sha256(str(number_of_portuguese_teams).encode()).hexdigest() == expected_hash

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-24c0142cc3082a0c", "locked": true, "schema_version": 1, "solution": false}
# ### Q5. Find out which team has the highest average goal count while playing away
#
# Write a query to find out which team scores the most goals on average while playing away. Assign the team's name to the variable team_with_highest_average_goals_away.
#
# Also find out what this average amount of goals is, and assign it to the variable average_goals_away (round to the nearest integer).

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-c5ef88aa51ea99f6", "locked": false, "schema_version": 1, "solution": true}
# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-f0f9a337b81de14a", "locked": true, "points": 1, "schema_version": 1, "solution": false}
expected_team_hash = '09c58af5e0ed9ebb10922ebc55ceacbb6ea1dc78d6b0237fe5e37b408d9a84d6'
assert hashlib.sha256(team_with_highest_average_goals_away.encode()).hexdigest() == expected_team_hash

expected_goals_hash = 'd4735e3a265e16eee03f59718b9b5d03019c07d8b6c51f90da3a666eec13ab35'
assert hashlib.sha256(str(average_goals_away).encode()).hexdigest() == expected_goals_hash

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-3a1dca85f9b02ebf", "locked": true, "schema_version": 1, "solution": false}
# ### Querying the FIFAdb with pandas
#
# In these exercises, the goal is to query the FIFAdb using pandas.
#
# ### Q6. Find the teams who are the least successful at pressuring their opponents' defence.
#
# The connection settings to use in this exercise are the same ones as in the previous exercises.
#
# Write a query to find the name, short_name and *average amount of goals scored when playing at home* of the teams with a high "defencepressure" team attribute (*greater than 55*).
#
# Search only for teams with:
# * an *average amount of goals scored when playing at home* lesser than 2 (the least successful teams at pressuring the opponents' defence and scoring goals); 
# * more than 30 games played at home, to reduce the number of statistically insignificant results.
#
# Order the results by the average amount of goals scored, in ascending order.
#
# Assign the result to dataframe df6.

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-f2cd873c0ac14ca0", "locked": false, "schema_version": 1, "solution": true}
# Create an engine that allows to to connect to the FIFAdb PostgreSQL database
# engine = sqlalchemy.create_engine(...)
# YOUR CODE HERE
raise NotImplementedError()


# Write the query as specified in the question
# query = ...
# YOUR CODE HERE
raise NotImplementedError()

# Use pandas read_sql_query function to read the query result into a DataFrame
# df6 = pd.read_sql_query(...)
# YOUR CODE HERE
raise NotImplementedError()
# -

df6.iloc[2]["name"]


# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-28986c4b783959be", "locked": true, "points": 1, "schema_version": 1, "solution": false}
assert type(engine) == sqlalchemy.engine.base.Engine
assert len(df6) == 16
assert len(df6.columns) == 3

expected_hash = '29adea3d0c885434d0f6f957aeb61f87c49c416fd1ecc895d438b552a5ec90e9'
assert hashlib.sha256(df6.iloc[2]["name"].encode()).hexdigest() == expected_hash

expected_hash = 'fe34924d143b814542bfb9714341fa68ac9fca7a0b4eeda1b654abacae2d1a50'
assert hashlib.sha256(df6.iloc[4].short_name.encode()).hexdigest() == expected_hash

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-06b122f1f318a355", "locked": true, "schema_version": 1, "solution": false}
# ### Q7. Select players with high shot power and agility, and order the results by height
#
# In this exercise, we want to query a local SQLite database.
# In order to do this, connect to the FIFAdb.sqlite database, as was done in the learning notebooks for the_movies.db. The database file we're using is in the same directory as this Exercises Notebook.
#
# Write a query that selects the player name, height, weight, shot_power and agility for all players with shot_power higher than 85 and agility higher than 80. Order these results in descending order by player height.
#
# Use pandas to read this query into a DataFrame called df7 with five columns: name, height, weight, shot_power and agility.

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-8d45d74ae0d84110", "locked": false, "schema_version": 1, "solution": true}
# Create an engine that allows to to connect to the the_movies.db SQLite database
# engine = sqlalchemy.create_engine(...)
# YOUR CODE HERE
raise NotImplementedError()


# Write the query as specified in the question
# query = ...
# YOUR CODE HERE
raise NotImplementedError()

# Use pandas read_sql_query function to read the query result into a DataFrame
# df7 = pd.read_sql_query(...)
# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-19abd085a003ab6f", "locked": true, "points": 1, "schema_version": 1, "solution": false}
assert type(engine) == sqlalchemy.engine.base.Engine
assert len(df7) == 8
assert len(df7.columns) == 5
assert df7.columns.tolist() == ['name', 'height', 'weight', 'shot_power', 'agility']

expected_hash = '51f5bb089774886799fada5cfdf18780d7d33ed30e85b0e0783ab8e1f13b06ea'
assert hashlib.sha256(df7.loc[0, 'name'].encode()).hexdigest() == expected_hash

expected_hash = '397edbc247ad5e6500752bde4715741a87369467b217220279c3e7adbd7c7ea0'
assert hashlib.sha256(str(df7.loc[2, 'height']).encode()).hexdigest() == expected_hash

expected_hash = 'ed145fcc7ab03071c1e1548a515ba1f2b66bee9623532681476b6515c3ffc7fd'
assert hashlib.sha256(df7.loc[7, 'name'].encode()).hexdigest() == expected_hash

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-a63384ff91b49abf", "locked": true, "schema_version": 1, "solution": false}
# ## Part B - Public APIs
#
# In this exercises, the goal is to get data from a public API. We'll use the [Star Wars API](https://swapi.co/).
#
# In order to complete the exercises, you'll have to consult the API's [documentation](https://swapi.co/documentation).
#
# <br>
#
# <img src="media/Star-wars.jpg" width=600>
#
# <br>
#
# ### Q8. Get planet 7 from the API
#
# Read the [documentation](https://swapi.co/documentation) of the API in order to find out how to get the planet with id 7.
# In order to get this data, you'll need to do an HTTP GET request.
#
# The result should be a JSON object (which is the same as a dictionary in Python), and assigned to variable planet_7.

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-3e7084c9bf8398d0", "locked": false, "schema_version": 1, "solution": true}
# Do an HTTP GET request to the Star Wars API to get planet 7 as a JSON object
# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-50d051ce26525944", "locked": true, "points": 3, "schema_version": 1, "solution": false}
assert type(planet_7) == dict
assert set(planet_7.keys()) == {'climate',  'created',  'diameter',  'edited',  'films',  'gravity',  'name',  'orbital_period',  'population',  'residents',  'rotation_period',  'surface_water',  'terrain',  'url'}

expected_planet_name_hash = '97479479e5561d6d1124f45e97533586e6f56b12e7b90151409a3ebe7f4e7fe7'
assert hashlib.sha256(planet_7['name'].encode()).hexdigest() == expected_planet_name_hash

expected_planet_climate_hash = '20f52b106bdf3451be03acc441d360b6b14e057765745311553ca9ce264a7284'
assert hashlib.sha256(planet_7['climate'].encode()).hexdigest() == expected_planet_climate_hash

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-90ff2db8962c1d5b", "locked": true, "schema_version": 1, "solution": false}
# ### Q9. Filter characters from the API
#
# Read the documentation of the API in order to find out how to filter information using a GET request with the 'search' parameter.
#
# Then, find all the characters from the Skywalker family, by searching for 'Skywalker'. The response should be a JSON object with the characters' information. 
#
# The desired results can be found in the 'results' field of the response. Please assign them to variable skywalker_family. 

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-3c68a66b3b6b56d0", "locked": false, "schema_version": 1, "solution": true}
# Do an HTTP GET request to filter characters according to the criteria above
# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-15c07449c3238657", "locked": true, "points": 3, "schema_version": 1, "solution": false}
assert type(skywalker_family) == list
assert len(skywalker_family) == 3
assert set(skywalker_family[0].keys()) == {'birth_year', 'created', 'edited', 'eye_color', 'films', 'gender', 'hair_color', 'height', 'homeworld', 'mass', 'name', 'skin_color', 'species', 'starships', 'url', 'vehicles'}
assert skywalker_family[0]['hair_color'] == 'blond'
assert skywalker_family[0]['skin_color'] == 'fair'
assert skywalker_family[0]['eye_color'] == 'blue'

expected_hash = 'e7c68f75d23b428e7afc5b72ab7fca0d5db7e8f0779d89d5c4b89b3c77f4eadd'
assert hashlib.sha256(skywalker_family[1]['name'].encode()).hexdigest() == expected_hash

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-91ec6c1c0bc1f4f5", "locked": true, "schema_version": 1, "solution": false}
# ## Part C - Web scraping
#
# In this exercise, we're going to use web scraping to get data about some albums that were released in 2018, from [this web page](https://www.albumoftheyear.org/2018/releases/). We are only going to focus on the first page.
#
#
# ### Q10. Scrape all scores (critic and user) for albums on the first page.
#
# Assign a list with the scores' values to variable score_list.
# In the list, each score should be a float.
#
# *(Extra food for thought, and not required for this exercise: observe what happens to the URL when you press the "Next" page button. Based on this observation, could you figure out how to scrape the ratings for ALL the albums of 2018? Keep in mind - always scrape responsibly and wait some time between scraping each page, so you don't overload the websites!)*

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-bd8d7b19855c5b9f", "locked": false, "schema_version": 1, "solution": true}
# Assign the URL of the page to be scraped to variable url
# url = ...
# YOUR CODE HERE
raise NotImplementedError()

# Do a GET request to get the page content, using the url we've just defined
# response = ...
# YOUR CODE HERE
raise NotImplementedError()

# Instanciate a soup object using the response of the GET request
# YOUR CODE HERE
raise NotImplementedError()
    
# Now it's the tricky part!
# Parse the soup in order to retrieve the scores.
# In the end, store the scores in a list and assign it to variable score_list.
# Make sure that all the score_list in the list are floats!
# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-335a90721257cd1a", "locked": true, "points": 6, "schema_version": 1, "solution": false}
assert type(score_list) == list
assert len(score_list) == 204

assert type(score_list[0]) == float
assert score_list[0] == 84.0

assert math.isclose(72.72, np.mean(score_list), rel_tol=1e-2)

# + [markdown] deletable=false editable=false nbgrader={"grade": false, "grade_id": "cell-ff83f6395e265feb", "locked": true, "schema_version": 1, "solution": false}
# ### Q11. Where did you find the scores?
#
# When you were scraping the album scores, you found out that the information you needed was in an HTML element, which looks like this:
#
# ```
# <tagname class=classname>Score goes here</tagname>
# ```
#
# Regarding the HTML element where you found the rating's value:
#
# * Assign the tagname to variable score_tagname
# * Assign the classname to variable score_classname
#
# In both cases you don't need to code, just copy and paste the values into the two variables.

# + deletable=false nbgrader={"grade": false, "grade_id": "cell-88e2603722dac1e3", "locked": false, "schema_version": 1, "solution": true}
# score_tagname = ...
# score_classname = ...

# YOUR CODE HERE
raise NotImplementedError()

# + deletable=false editable=false nbgrader={"grade": true, "grade_id": "cell-f78d31cd4e4779eb", "locked": true, "points": 1, "schema_version": 1, "solution": false}
expected_hash = 'cd35a2426062b7d58fd4a63f813cc506ef87e449087d28d256b8c393f20fa364'
assert hashlib.sha256(score_tagname.encode()).hexdigest() == expected_hash

expected_hash = '112895d7af6c125315cd807a911473c05eb9779bf7cd459c5e29db919ccbf408'
assert hashlib.sha256(score_classname.encode()).hexdigest() == expected_hash

StarcoderdataJupyterScriptsDedupFiltered