<jupyter_start><jupyter_text>Create Table Scraper<jupyter_code>import requests
import lxml.html as lh
import pandas as pd

def table_parser(url):
    
    #Create a handle, page, to handle the contents of the website
    page = requests.get(url)
    #Store the contents of the website under doc
    doc = lh.fromstring(page.content)
    #Parse data that are stored between <tr>..</tr> of HTML
    tr_elements = doc.xpath('//tr')

    #Create empty list
    col=[]
    i=0
    #For each row, store each first element (header) and an empty list
    for t in tr_elements[0]:
        i+=1
        name=t.text_content()
        #print('%d:"%s"'%(i,name))
        col.append((name,[]))

    #Check the length of the first 12 rows
    length = [len(T) for T in tr_elements[:12]]
    length = length[0]
    
    for j in range(1,len(tr_elements)):
    #T is our j'th row
        T=tr_elements[j]
    
        #If row is not of size 10, the //tr data is not from our table 
        if len(T)!=length:
            break
    
        #i is the index of our column
        i=0
    
        #Iterate through each element of the row
        for t in T.iterchildren():
            data=t.text_content() 
            #Check if row is empty
            if i>0:
            #Convert any numerical value to integers
                try:
                    data=data
                except:
                    pass
            #Append the data to the empty list of the i'th column
            col[i][1].append(data)
            #Increment i for the next column
            i+=1
            
    Dict={title:column for (title,column) in col}
    df=pd.DataFrame(Dict)
    return(df)<jupyter_output><empty_output><jupyter_text>Get Data<jupyter_code>coach = table_parser('https://sports.usatoday.com/ncaa/salaries/football/coach')
coach = coach[:-1]
coach['Total Pay'] = [str(p) for p in coach['Total Pay']]
coach = coach.replace('\$','', regex=True)
coach = coach.replace(',','', regex=True)
coach = coach.replace(r'--', '0', regex=True)
coach['Total Pay'] = [int(p) for p in coach['Total Pay']]
coach = coach[coach['Total Pay']>0]
coach['Type'] = "Head Coach"
coach = coach.drop(['Bonuses paid (2018-19)'], axis=1)
coach = coach.drop(['School Buyout As Of 12/1/19'], axis=1)


college_info = table_parser("https://en.wikipedia.org/wiki/List_of_NCAA_Division_I_FBS_football_programs")
college_info = college_info.replace('\n','', regex=True)
college_info = college_info[['Team\n', "Nickname\n", "City\n", "State[1]\n", "Enrollment\n", "CurrentConference\n"]]
college_info.columns = ['Team', "Nickname", "City", "State", "Enrollment", "CurrentConference"]

assistant = table_parser("https://sports.usatoday.com/ncaa/salaries/football/assistant")
assistant = assistant[:-1]
assistant['Total Pay'] = [str(p) for p in assistant['Total Pay']]
assistant = assistant.replace('\$','', regex=True)
assistant = assistant.replace(',','', regex=True)
assistant = assistant.replace(r'--', '0', regex=True)
assistant['Total Pay'] = [int(p) for p in assistant['Total Pay']]
assistant = assistant[assistant['Total Pay']>0]
assistant["Type"] = "Assistant"

strength = table_parser("https://sports.usatoday.com/ncaa/salaries/football/strength")
strength = strength[:-1]
strength['Total Pay'] = [str(p) for p in strength['Total Pay']]
strength = strength.replace('\$','', regex=True)
strength = strength.replace(',','', regex=True)
strength = strength.replace(r'--', '0', regex=True)
strength['Total Pay'] = [int(p) for p in strength['Total Pay']]
strength = strength[strength['Total Pay']>0]
strength['Type'] = "Strength"

coaches = pd.concat([coach, assistant, strength])
coaches = coaches.drop("Rk", axis=1)
coaches = coaches.drop("Max Bonus", axis=1)
coaches = coaches.drop("Asst Pay Total", axis=1)
coaches = coaches.drop("School Pay", axis=1)<jupyter_output><empty_output><jupyter_text>Aggregate by School<jupyter_code>school_names = {
    'Mississippi': 'Ole Miss', 'Southern Mississippi': 'Southern Miss', 
    'Central Florida': 'UCF', 'Connecticut': 'UConn', 'Florida International': 'FIU', 
    "Louisiana State": 'LSU', 'Massachusetts': 'UMass', 'Miami (Ohio)': 'Miami (OH)', 'Southern California': 'USC', 
    'Texas Christian': 'TCU', 'Texas El Paso': 'UTEP', 'UL Lafayette': 'Louisiana', 
    'UL Monroe': 'Louisiana–Monroe', 'Texas-San Antonio': 'UTSA'}
coaches = coaches.replace({"School": school_names})
school_sum = coaches.groupby("School").sum()
football_totals = school_sum.merge(college_info, left_index=True, right_on="Team")
football_totals['Enrollment'] = football_totals['Enrollment'].replace(',','', regex=True)
football_totals['Enrollment'] = football_totals['Enrollment'].astype(int)
football_totals["Price Per Student"] = football_totals['Total Pay']/football_totals['Enrollment']

football_totals = football_totals[['Team', "Nickname", "City", "State", "CurrentConference", "Price Per Student", "Total Pay", 'Enrollment']]
football_totals.columns = ['Team', "Nickname", "City", "State", "Conference", "Price Per Student", "Total Pay", 'Enrollment']
x = set(school_sum.index)
y = set(college_info["Team"])
no_salary = x-y
print("Schools without salary information:" + str(y-x))
clemson = coaches[coaches['School']=="Clemson"]
Clemson_Total = clemson['Total Pay'].sum()

LSU = coaches[coaches['School']=="LSU"]
LSU_Total = LSU['Total Pay'].sum()

print("The total cost of LSU and Clemson Coaching Staffs Respectively: LSU=$"+str(LSU_Total)+" Clemson=$"+str(Clemson_Total))
clemson = football_totals[football_totals['Team']=="Clemson"]
Clemson_Total = float(clemson['Price Per Student'])

LSU = football_totals[football_totals['Team']=="LSU"]
LSU_Total = float(LSU['Price Per Student'])

print("The total cost of LSU and Clemson Coaching Staffs Respectively: LSU=$"+str(LSU_Total)+" Clemson=$"+str(Clemson_Total))<jupyter_output>The total cost of LSU and Clemson Coaching Staffs Respectively: LSU=$362.5 Clemson=$693.424<jupyter_text>Get Salary Information by State<jupyter_code>by_state = football_totals.groupby("State").sum()
by_state["Price Per Student"] = by_state['Total Pay']/by_state['Enrollment']
by_state_schools = football_totals.groupby("State").count()
by_state_schools = by_state_schools[["Team"]]
by_state = by_state.merge(by_state_schools, left_index=True, right_index=True)


state_codes = {
    'District of Columbia' : 'dc','Mississippi': 'MS', 'Oklahoma': 'OK', 
    'Delaware': 'DE', 'Minnesota': 'MN', 'Illinois': 'IL', 'Arkansas': 'AR', 
    'New Mexico': 'NM', 'Indiana': 'IN', 'Maryland': 'MD', 'Louisiana': 'LA', 
    'Idaho': 'ID', 'Wyoming': 'WY', 'Tennessee': 'TN', 'Arizona': 'AZ', 
    'Iowa': 'IA', 'Michigan': 'MI', 'Kansas': 'KS', 'Utah': 'UT', 
    'Virginia': 'VA', 'Oregon': 'OR', 'Connecticut': 'CT', 'Montana': 'MT', 
    'California': 'CA', 'Massachusetts': 'MA', 'West Virginia': 'WV', 
    'South Carolina': 'SC', 'New Hampshire': 'NH', 'Wisconsin': 'WI',
    'Vermont': 'VT', 'Georgia': 'GA', 'North Dakota': 'ND', 
    'Pennsylvania': 'PA', 'Florida': 'FL', 'Alaska': 'AK', 'Kentucky': 'KY', 
    "Hawai'i": 'HI', 'Nebraska': 'NE', 'Missouri': 'MO', 'Ohio': 'OH', 
    'Alabama': 'AL', 'Rhode Island': 'RI', 'South Dakota': 'SD', 
    'Colorado': 'CO', 'New Jersey': 'NJ', 'Washington': 'WA', 
    'North Carolina': 'NC', 'New York': 'NY', 'Texas': 'TX', 
    'Nevada': 'NV', 'Maine': 'ME'}
by_state = by_state.reset_index()
by_state['state_code'] = by_state['State'].apply(lambda x : state_codes[x])
for col in by_state.columns:
        by_state[col] = by_state[col].astype(str)
by_state['text'] = 'Number of Schools: ' + by_state['Team'] + '<br>' + 'Total Enrollment: ' + by_state['Enrollment']
by_state['Total Pay'] = by_state['Total Pay'].astype(float)
by_state['Team'] = by_state['Team'].astype(float)
by_state['Price Per School'] = by_state['Total Pay']/by_state['Team']<jupyter_output><empty_output><jupyter_text>Plot Total Spending By State<jupyter_code>from plotly.offline import iplot
import plotly.graph_objects as go
    
data = [dict(
    type="choropleth",
    autocolorscale= True,
    locations = by_state['state_code'],
    z = by_state['Total Pay'].astype(float),
    locationmode = 'USA-states',
    text = by_state['text'],
    marker = dict(
    line = dict (
    color = 'rgb(0,0,0)',
    width = 2
 )),
    colorbar = dict(title = "Total Pay of College Staffs<br>in State")
)]

layout = dict(title = '',
        geo=dict(scope="usa",showlakes = True,lakecolor = 'rgb(255, 255, 255)'))

iplot(go.Figure(data=data,layout=layout),validate=False)<jupyter_output><empty_output><jupyter_text>Plot Total Spending Per Student By State<jupyter_code>data = [dict(
    type="choropleth",
    autocolorscale= True,
    locations = by_state['state_code'],
    z = by_state['Price Per Student'].astype(float),
    locationmode = 'USA-states',
    text = by_state['text'],
    marker = dict(
    line = dict (
    color = 'rgb(0,0,0)',
    width = 2
 )),
    colorbar = dict(title = "College Football Staff<br>Price Per Student ($)")
)]

layout = dict(title = '',
        geo=dict(scope="usa",showlakes = True,lakecolor = 'rgb(255, 255, 255)'))

iplot(go.Figure(data=data,layout=layout),validate=False)<jupyter_output><empty_output><jupyter_text>Average Price Per Team in State<jupyter_code>data = [dict(
    type="choropleth",
    autocolorscale= True,
    locations = by_state['state_code'],
    z = by_state['Price Per School'].astype(float),
    locationmode = 'USA-states',
    text = by_state['text'],
    marker = dict(
    line = dict (
    color = 'rgb(0,0,0)',
    width = 2
 )),
    colorbar = dict(title = "College Football Staff<br>Average by State ($)")
)]

layout = dict(title = '',
        geo=dict(scope="usa",showlakes = True,lakecolor = 'rgb(255, 255, 255)'))

iplot(go.Figure(data=data,layout=layout),validate=False)<jupyter_output><empty_output><jupyter_text>By Conference<jupyter_code>football_totals['Conference'] = football_totals['Conference'].replace('American[n 6]', 'American')
by_conference = football_totals.groupby("Conference").sum()
by_conference["Price Per Student"] = by_conference['Total Pay']/by_conference['Enrollment']
by_conference_schools = football_totals.groupby("Conference").count()
by_conference_schools = by_conference_schools[["Team"]]
by_conference = by_conference.merge(by_conference_schools, left_index=True, right_index=True)
by_conference["Average Per Team"] = by_conference['Total Pay']/by_conference["Team"]
by_conference["Average Per Team"] = by_conference["Average Per Team"].astype(str)
by_conference["Average Per Team"] = [x.split(".")[0] for x in by_conference["Average Per Team"]]
by_conference<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17474357767949564, 'fraction_numerical': 0.008751293874094289, 'mean_word_length': 4.904444444444445, 'pattern_counts': {'":': 3, '<': 38, '<?xml version=': 0, '>': 42, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 1}, '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': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '403562', 'license': 'MIT', 'n_tokens_mistral': 3834, 'n_tokens_neox': 3647, 'n_words': 832, 'path': 'College_Football.ipynb', 'repo_name': 'Datadolittle/College_Football_Salaries', 'size': '10627'}