<jupyter_start><jupyter_text>**<NAME> and <NAME>**Collaboration Plan:We are working together on this via vscode live share and are talking over zoom. In terms of sharing the file we have a github repository set up. We met three times to work in total.<jupyter_code>#IMPORTS
%matplotlib inline
import matplotlib
import matplotlib.pyplot as plt
import sqlite3
import pandas as pd
from scipy import stats
import numpy as np

#sqlite file and connection
sqlite_file = 'lahman2014.sqlite'
conn = sqlite3.connect(sqlite_file)<jupyter_output><empty_output><jupyter_text>Problem 1:<jupyter_code>join_query = "SELECT Teams.teamID, Salaries.yearID, franchID, G, W, CAST(W as REAL)/(Cast(G as REAL))*100 as winPercentage, sum(salary) as total_payroll FROM Teams INNER JOIN Salaries ON Teams.teamID = Salaries.teamID AND Teams.yearID = Salaries.yearID group by Salaries.teamID, Salaries.yearID"
new_df = pd.read_sql(join_query, conn)
new_df.head()<jupyter_output><empty_output><jupyter_text>We didn't have to deal with any "missing data" from the two tables because we used an inner join. This takes the teams (and years) that are present in *both* the Teams and Salaries tables.<jupyter_code>team_query = "SELECT Teams.teamID, Salaries.yearID, franchID, G, W, CAST(W as REAL)/(Cast(G as REAL))*100 as winPercentage, sum(salary) as total_payroll FROM Teams INNER JOIN Salaries ON Teams.teamID = Salaries.teamID AND Teams.yearID = Salaries.yearID WHERE Teams.teamID = 'NYA' group by Salaries.teamID, Salaries.yearID"
nyy_df = pd.read_sql(team_query, conn)
nyy_df<jupyter_output><empty_output><jupyter_text>Problem 2:<jupyter_code>new_df
by_year_mean = new_df[new_df.yearID>=1990].groupby('yearID')['total_payroll'].mean()/1000000
by_year_mean.plot.line(title="Total Payroll by Year",xlabel = "Year", ylabel = "Total Payroll (Millions)",legend=True)
plt.legend(["Mean Payroll"])




#scatter plot of win percentage vs payroll
new_df.plot.scatter(x='total_payroll', y='winPercentage', title="Win Percentage vs Payroll",xlabel = "Total Payroll", ylabel = "Win Percentage")<jupyter_output><empty_output><jupyter_text>Question 1: The range of the teams' total payroll increases drastically. The difference between the top team and bottom team starts out as a small number, and gets to be as big as nearly two hundred million. The mean increases a lot over the past 30 years, but not as much as the maximum total payroll.<jupyter_code>by_year_payroll_max = new_df[new_df.yearID>=1990].groupby('yearID')['total_payroll'].max()/1000000
by_year_mean = new_df[new_df.yearID>=1990].groupby('yearID')['total_payroll'].mean()/1000000
by_year_min = new_df[new_df.yearID>=1990].groupby('yearID')['total_payroll'].min()/1000000
by_year_payroll_max.plot.line(title="Total Payroll by Year",legend=True)
by_year_mean.plot.line(title="Total Payroll by Year",xlabel = "Year",legend=True)
by_year_min.plot.line(title="Total Payroll by Year",xlabel = "Year", ylabel = "Total Payroll (Millions)",legend=True)
plt.legend(["Highest Payroll","Mean Payroll", "Lowest Payroll"])<jupyter_output><empty_output><jupyter_text>Problem 4:<jupyter_code>fig, ax = plt.subplots(1, 5, figsize=(30,10))
for i,j in enumerate([[1990,1995], [1995,2000], [2000,2005], [2005,2010], [2010,2014]]):
    scat_df = new_df[(new_df.yearID>=j[0]) & (new_df.yearID<j[1])]
    by_team = scat_df.groupby('teamID').mean()
    #label each point with their teamID
    for k,v in by_team.iterrows():
        ax[i].annotate(k, (v['total_payroll'], v['winPercentage']))
    
     
    by_team.plot.scatter(ax=ax[i],x='total_payroll', y='winPercentage', title='Win Percentage vs Payroll ({})'.format(str(j[0])+'-'+str(j[1])),xlabel = "Total Payroll", ylabel = "Win Percentage")
    
    
    
    slope, intercept, r_value, p_value, std_err = stats.linregress(scat_df['total_payroll'],
                                                                   scat_df['winPercentage'])
    pts = np.linspace(scat_df['total_payroll'].min(), scat_df['total_payroll'].max(), 500)
    line = slope * pts + intercept
    ax[i].plot(pts, line, lw=1, color='red')
plt.show()
    
#then fro 2010 to 2014<jupyter_output><empty_output><jupyter_text>Question 2:The Yankees are clearly the biggest spenders - and are very good at paying for wins. The Oakland A's are great at outperforming their expectation (based on total payroll). In the Moneyball period, they are one of the best teams in the league while being one of the lowest spending teams.  Problem 5:<jupyter_code>by_year = new_df.groupby('yearID').total_payroll.mean()
std_year = new_df.groupby('yearID').total_payroll.std()
copy_df = new_df.copy()
def help1(year):
    return by_year[year]
def help2(year):
    return std_year[year]

copy_df["mean"] = help1(new_df["yearID"]).reset_index(drop=True)
copy_df["std"] = help2(new_df["yearID"]).reset_index(drop=True)
new_df["standardized_payroll"] = (copy_df["total_payroll"] - copy_df["mean"])/ copy_df["std"]
new_df.head()<jupyter_output><empty_output><jupyter_text>Problem 6:<jupyter_code>fig, ax = plt.subplots(1, 5, figsize=(30,10))
for i,j in enumerate([[1990,1995], [1995,2000], [2000,2005], [2005,2010], [2010,2014]]):
    scat_df = new_df[(new_df.yearID>=j[0]) & (new_df.yearID<j[1])]
    by_team = scat_df.groupby('teamID').mean()
    #label each point with their teamID
    for k,v in by_team.iterrows():
        ax[i].annotate(k, (v['standardized_payroll'], v['winPercentage']))
    
     
    by_team.plot.scatter(ax=ax[i],x='standardized_payroll', y='winPercentage', title='Win Percentage vs Standardized Payroll ({})'.format(str(j[0])+'-'+str(j[1])),xlabel = "Standardized Payroll", ylabel = "Win Percentage")
    
    
    
    slope, intercept, r_value, p_value, std_err = stats.linregress(scat_df['standardized_payroll'],
                                                                   scat_df['winPercentage'])
    pts = np.linspace(scat_df['standardized_payroll'].min(), scat_df['standardized_payroll'].max(), 500)
    line = slope * pts + intercept
    ax[i].plot(pts, line, lw=1, color='red')
plt.show()<jupyter_output><empty_output><jupyter_text>Question 3:It is easier to see now through our linear regression how much payroll affects win percentage. Also, after 2000, the Yankees are far and away spending the most money on their players in relation to other teams. Because payroll is standardized, the line of best fit seems even more aligned - making it a bit harder to see teams clustered towards it, such as in the graph representing 2005-2010. Problem 7:<jupyter_code>fig, ax = plt.subplots(1, 1, figsize=(20,10))
new_df.plot.scatter(ax=ax,x='standardized_payroll', y='winPercentage', title='Win Percentage vs Standardized Payroll',xlabel = "Standardized Payroll", ylabel = "Win Percentage")
slope, intercept, r_value, p_value, std_err = stats.linregress(new_df['standardized_payroll'],
                                                                   new_df['winPercentage'])
pts = np.linspace(new_df['standardized_payroll'].min(), new_df['standardized_payroll'].max(), 500)
line = slope * pts + intercept
ax.plot(pts, line, lw=1, color='red')
plt.show()<jupyter_output><empty_output><jupyter_text>Problem 8:<jupyter_code>new_df['expected_wp'] = 50 + 2.5 * new_df['standardized_payroll']
new_df['efficiency'] = new_df['winPercentage'] - new_df['expected_wp']
new_df.head()
fig, ax = plt.subplots(1, 5,  sharey=True, figsize=(30,10))
for i,team in enumerate(['OAK', 'BOS', 'NYA', 'ATL', 'TBA']):
    line_df = new_df[new_df.teamID==team]
    line_df.plot.line(x='yearID', y='efficiency', title="Efficiency ({}".format(str(team)+')'),xlabel = "Year", ylabel = "Efficiency", ax = ax[i] )
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13900468994267848, 'fraction_numerical': 0.0286607608129234, 'mean_word_length': 5.323723228995058, 'pattern_counts': {'":': 0, '<': 45, '<?xml version=': 0, '>': 49, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '436992', 'license': 'MIT', 'n_tokens_mistral': 2852, 'n_tokens_neox': 2606, 'n_words': 638, 'path': '_projects/project2.ipynb', 'repo_name': 'M-Sender/cmps3160', 'size': '7683'}