<jupyter_start><jupyter_text>Ex2 - Filtering and Sorting Data This time we are going to pull data directly from the internet. Step 1. Import the necessary libraries<jupyter_code>import pandas as pd
import numpy as np<jupyter_output><empty_output><jupyter_text>Step 2. Import the dataset from this [address](https://raw.githubusercontent.com/guipsamora/pandas_exercises/master/02_Filtering_%26_Sorting/Euro12/Euro_2012_stats_TEAM.csv).   Step 3. Assign it to a variable called euro12.<jupyter_code>url = 'https://raw.githubusercontent.com/guipsamora/pandas_exercises/master/02_Filtering_%26_Sorting/Euro12/Euro_2012_stats_TEAM.csv'
# euro12 = pd.read_csv(url)
euro12 = pd.read_csv('Euro_2012_stats_TEAM.csv')
# euro12.dtypes.value_counts()
euro12.columns
euro12.shape<jupyter_output><empty_output><jupyter_text>Step 4. Select only the Goal column.<jupyter_code>mask = euro12.columns.str.lower().str.contains('goals')
euro12.loc[:,mask].head(2)<jupyter_output><empty_output><jupyter_text>Step 5. How many team participated in the Euro2012?<jupyter_code>euro12['Team'].nunique(), euro12['Team'].unique()<jupyter_output><empty_output><jupyter_text>Step 6. What is the number of columns in the dataset?<jupyter_code>euro12.shape<jupyter_output><empty_output><jupyter_text>Step 7. View only the columns Team, Yellow Cards and Red Cards and assign them to a dataframe called discipline<jupyter_code>discipline = euro12[['Team', 'Yellow Cards', 'Red Cards']]
discipline.head()<jupyter_output><empty_output><jupyter_text>Step 8. Sort the teams by Red Cards, then to Yellow Cards<jupyter_code># discipline.sort_values(['Red Cards', 'Yellow Cards'])
discipline.groupby('Team').sum().sort_values(['Red Cards', 'Yellow Cards'])<jupyter_output><empty_output><jupyter_text>Step 9. Calculate the mean Yellow Cards given per Team<jupyter_code>discipline.groupby('Team')['Yellow Cards'].mean()<jupyter_output><empty_output><jupyter_text>Step 9bis. Calculate the mean Yellow Cards for all the teams<jupyter_code>discipline.groupby('Team')['Yellow Cards'].mean().mean()<jupyter_output><empty_output><jupyter_text>Step 10. Filter teams that scored more than 6 goals<jupyter_code>euro12.groupby('Team').filter(lambda x: x['Goals'].sum()>6)
euro12[euro12['Goals']>6]['Team']
euro12.query('Goals > 6')['Team']<jupyter_output><empty_output><jupyter_text>Step 11. Select the teams that start with G<jupyter_code>euro12[euro12['Team'].str.startswith('G')]<jupyter_output><empty_output><jupyter_text>Step 12. Select the first 7 columns<jupyter_code>euro12.iloc[:,0:7].head(2)<jupyter_output><empty_output><jupyter_text>Step 13. Select all columns except the last 3.<jupyter_code>euro12.iloc[:,0:-3].head(2)<jupyter_output><empty_output><jupyter_text>Step 14. Present only the Shooting Accuracy from England, Italy and Russia<jupyter_code>euro12[euro12['Team'].isin(['England', 'Italy', 'Russia'])][['Team','Shooting Accuracy']]
euro12.loc[euro12['Team'].isin(['England', 'Italy', 'Russia']), ['Team','Shooting Accuracy']]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14058881905391996, 'fraction_numerical': 0.03638769434336751, 'mean_word_length': 9.216216216216216, 'pattern_counts': {'":': 0, '<': 57, '<?xml version=': 0, '>': 60, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '67966', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1188, 'n_tokens_neox': 1076, 'n_words': 201, 'path': '02_Filtering_&_Sorting/Euro12/Exercises.ipynb', 'repo_name': 'SmellyArmure/SmellyArmure', 'size': '3023'}