<jupyter_start><jupyter_text>Pandas Worksheet Solutions<jupyter_code>import pandas as pd
%matplotlib inline<jupyter_output><empty_output><jupyter_text>The goal of this worksheet is to provide practical examples of aggregating (with group by), plotting, and pivoting data with the Pandas Python package.This worksheet is available as a jupyter notebook on github here: https://github.com/JBed/Pandas_Analysis_WorksheetGet the data here: https://www.kaggle.com/dansbecker/nba-shot-logs Finally, if you have any questions, comments, or believe that I did anything incorrectly feel free to email me here: <EMAIL><jupyter_code>df = pd.read_csv('nba-shot-logs.zip')<jupyter_output><empty_output><jupyter_text>The data is structured so that each row corresponds to one shot taking during the 2014-2015 NBA season (We exclude free throws).<jupyter_code>df.head(2)
df.columns<jupyter_output><empty_output><jupyter_text>Most of the column names are self-explanatory. One thing that initially confused me was that there is no column telling us the team of the player taking the shot. It turns out that that information is hidden in the MATCHUP column.<jupyter_code>df.set_index('GAME_ID').loc[21400899]['MATCHUP'].unique()<jupyter_output><empty_output><jupyter_text>We see that the name of the team of the player taking the shot is the first team listed after the date. It turns out that having things structured this way is actually very convenient.  Part1: Questions about SHOT_RESULT for one team in a one game Here It makes sense restructure our data so that each row is referring to one team in one game and the columns give us the number of shots made or missed. This can be done with either DataFrame.groupby or pandas.pivot_table which I show below.<jupyter_code>shot_result_by_matchup = df.groupby(['MATCHUP','SHOT_RESULT']).size().unstack()
shot_result_by_matchup.head()
df.pivot_table(index='MATCHUP', columns='SHOT_RESULT', \
               values='W',aggfunc=lambda x: len(x)).head()<jupyter_output><empty_output><jupyter_text>Personally I find the groupby operation to be more expressive.   Q1.1 which team made the most and least shots in a game This is done by sorting the above on made<jupyter_code>shot_result_by_matchup.sort_values(by='made').head()
shot_result_by_matchup.sort_values(by='made', ascending=False).head()<jupyter_output><empty_output><jupyter_text>Q1.2: which team took the most and least shots in a game. We'll make a new column called total and sort on that.<jupyter_code>shot_result_by_matchup['total'] = shot_result_by_matchup.sum(axis=1)
shot_result_by_matchup.sort_values(by='total').head()<jupyter_output><empty_output><jupyter_text>These are likely game that were canceled<jupyter_code>shot_result_by_matchup.sort_values(by='total',ascending=False).head()<jupyter_output><empty_output><jupyter_text>Q1.3 Which team made the most shots as a percentage of all shots taken in a game we'll make a derived column called make_percent and sort on that.<jupyter_code>shot_result_by_matchup['make_percent'] = \
round((shot_result_by_matchup['made'] / shot_result_by_matchup['total'])*100,1)
shot_result_by_matchup.sort_values(by='make_percent').head()
shot_result_by_matchup.sort_values(by='make_percent', ascending=False).head()<jupyter_output><empty_output><jupyter_text>Part 2: Questions about SHOT_RESULT and W for one team in a one game Here we’ll make a dataframe similar to the above but with 'W' as a column<jupyter_code>shot_and_game_result_by_matchup = df.groupby(['MATCHUP','W','SHOT_RESULT']).size().unstack()
shot_and_game_result_by_matchup.head()<jupyter_output><empty_output><jupyter_text>We want 'W' to be a column not part of the index as it is now<jupyter_code>shot_and_game_result_by_matchup = \
shot_and_game_result_by_matchup.reset_index().set_index('MATCHUP')
shot_and_game_result_by_matchup.head()<jupyter_output><empty_output><jupyter_text>Q2.1 which team had the lowest make percentage (in a single game) but still won that game. Again we'll make a derived column called make_percent<jupyter_code>shot_and_game_result_by_matchup['make_percent'] = \
round((shot_and_game_result_by_matchup['made']/\
       shot_and_game_result_by_matchup.sum(axis=1))*100,1)
shot_and_game_result_by_matchup[\
shot_and_game_result_by_matchup['W']=='W'].sort_values(by='make_percent').head()
shot_and_game_result_by_matchup[\
shot_and_game_result_by_matchup['W']=='L'].sort_values(by='make_percent', \
                                                       ascending=False).head()<jupyter_output><empty_output><jupyter_text>Q2.2 Did winning teams have a higher make percentage on average than losing teams? We'll explore this question with a group and a plot<jupyter_code>shot_and_game_result_by_matchup.groupby('W')['make_percent'].describe().unstack()
shot_and_game_result_by_matchup.boxplot(column='make_percent', by='W',figsize=(12,8))<jupyter_output><empty_output><jupyter_text>Part3: Questions comparing two teams in one game.  Q 3.1: How often did the winning team will have a lower make percentage than the losing team Here it looks like we need to compare two rows in the shot_and_game_result_by_matchup dataframe. Don't do that! Remember that columns are for comparing not rows. Let me say that again  compare columns not rows!!!ok so how do we turn two rows into two columns?<jupyter_code>shot_result_by_gameid_and_w = \
df.groupby(['GAME_ID','W','SHOT_RESULT']).size().unstack()
shot_result_by_matchup_gameid.head(4)
shot_result_by_gameid_and_w['make_percent'] = \
round((shot_result_by_gameid_and_w['made']/\
       shot_result_by_gameid_and_w.sum(axis=1))*100,1)
shot_result_by_gameid_and_w.head(4)
shot_result_by_gameid_and_w = \
shot_result_by_gameid_and_w.reset_index()
shot_result_by_gameid_and_w.head(4)
make_percent_by_gameid = \
shot_result_by_gameid_and_w.pivot(index='GAME_ID', columns='W')
make_percent_by_gameid.head()
make_percent_by_gameid[\
make_percent_by_gameid['make_percent']['W']<\
make_percent_by_gameid['make_percent']['L']].head()
len(make_percent_by_gameid[\
make_percent_by_gameid['make_percent']['W']<\
make_percent_by_gameid['make_percent']['L']])
len(make_percent_by_gameid)
(197./904)*100<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09891708420882496, 'fraction_numerical': 0.010021011798933247, 'mean_word_length': 7.3621621621621625, 'pattern_counts': {'":': 0, '<': 60, '<?xml version=': 0, '>': 58, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '604701', 'license': 'Apache-2.0', 'n_tokens_mistral': 2096, 'n_tokens_neox': 2041, 'n_words': 513, 'path': 'Pandas_Worksheet_Solutions.ipynb', 'repo_name': 'JBed/Pandas_Analysis_Worksheet', 'size': '6198'}