<jupyter_start><jupyter_text>Scrap the data from the web using pandas<jupyter_code>#url to 5 years period combine stats

url2020 = 'https://www.pro-football-reference.com/draft/2020-combine.htm#combine'
url2019 = 'https://www.pro-football-reference.com/draft/2019-combine.htm'
url2018 = 'https://www.pro-football-reference.com/draft/2018-combine.htm'
url2017 = 'https://www.pro-football-reference.com/draft/2017-combine.htm'
url2016 =  'https://www.pro-football-reference.com/draft/2016-combine.htm'
url2015 = 'https://www.pro-football-reference.com/draft/2015-combine.htm'
#tables for 5 years period
tables2020 = pd.read_html(url2020)
tables2019 = pd.read_html(url2019)
tables2018 = pd.read_html(url2018)
tables2017 = pd.read_html(url2017)
tables2016 = pd.read_html(url2016)
#Check the type
type(tables2020)
#check the lenght
len(tables2020)<jupyter_output><empty_output><jupyter_text>Clean the data using pandas* Convert the data into a DataFrame* Delete (drop) the columns we are not using: 'College' and 'Drafted (tm/rnd/yr)'<jupyter_code># Convert the tables to a DataFrame
df_2020 = tables2020[0]
df_2019 = tables2019[0]
df_2018 = tables2018[0]
df_2017 = tables2017[0]
df_2016 = tables2016[0]

# Clean 2020 DataFrame
df2020 = df_2020.drop(columns = 'Drafted (tm/rnd/yr)')
df2020 = df2020.drop(columns = 'College')
df2020
# Clean 2019 DataFrame
df2019 = df_2019.drop(columns = 'Drafted (tm/rnd/yr)')
df2019 = df2019.drop(columns = 'College')
df2019
# Clean 2018 DataFrame
df2018 = df_2018.drop(columns = 'Drafted (tm/rnd/yr)')
df2018 = df2018.drop(columns = 'College')
df2018
# Clean 2017 DataFrame
df2017 = df_2017.drop(columns = 'Drafted (tm/rnd/yr)')
df2017 = df2017.drop(columns = 'College')
df2017
# Clean 2016 DataFrame
df2016 = df_2016.drop(columns = 'Drafted (tm/rnd/yr)')
df2016 = df2016.drop(columns = 'College')
df2016<jupyter_output><empty_output><jupyter_text>Append the data into a single DataFrame to post it to the database<jupyter_code># Add a year column for data storage porpuses
df2020['year']='2020'
df2019['year']='2019'
df2018['year']='2018'
df2017['year']='2017'
df2016['year']='2016'
df2020

# Append the 5 years DataFrames into one for storage porpuses
df = df2020.append(df2019,ignore_index=True,verify_integrity=True)
df = df.append(df2018,ignore_index=True,verify_integrity=True)
df = df.append(df2017,ignore_index=True,verify_integrity=True)
df = df.append(df2016,ignore_index=True,verify_integrity=True)

df<jupyter_output><empty_output><jupyter_text>Post to the SQL Database<jupyter_code># Connect to local database
# Pior: Create a python file called info in your local repo with user, password and database name.

from info import user, password, database
#rds_connection_string = "<insert user name>:<insert password>@localhost:5432/customer_db"
rds_connection_string = f'{user}:{password}@localhost:5432/{database}"
engine = create_engine(f'postgresql://{rds_connection_string}')
# Check the tables names to make sure where are we posting
engine.table_names()
# Append data using pandas. name stands for table name, change it if neccesary.
df.to_sql(name='combine_stats', con=engine, if_exists='append', index=False)
#Confirm the data has been appended. Check table name.
pd.read_sql_query('select * from combine_stats', con=engine).head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12628398791540785, 'fraction_numerical': 0.1015105740181269, 'mean_word_length': 6.664351851851852, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 19, 'https://': 6, 'lorem ipsum': 0, 'www.': 6, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '111230', 'license': 'MIT', 'n_tokens_mistral': 1411, 'n_tokens_neox': 1136, 'n_words': 269, 'path': 'combine_stats/combine_stats2.ipynb', 'repo_name': 'sdfeinsilver/ETL-Challenge', 'size': '3310'}