<jupyter_start><jupyter_text>Store CSV into DataFrame<jupyter_code>csv_file = "../Resources/avocado.csv"
avocado_data_df = pd.read_csv(csv_file)
avocado_data_df.head()
csv_file = "../Resources/population.csv"
population_data_df = pd.read_csv(csv_file)
population_data_df.head()
csv_file = "../Resources/avocado_volume.csv"
avocado_volume_df = pd.read_csv(csv_file)
avocado_volume_df.head()<jupyter_output><empty_output><jupyter_text>Transform avocado DataFrame<jupyter_code># Create a filtered dataframe from specific columns
avocado_data_df_cols = ["Date","AveragePrice", "Total Volume", "year", "region"]
avocado_transformed = avocado_data_df[avocado_data_df_cols].copy()

# Rename the column headers
avocado_transformed = avocado_transformed.rename(columns={"year":"Year", "Total Volume":"TotalVolume", "region":"City"})

# Set index
avocado_transformed.set_index("City", inplace=True)

avocado_transformed.head()<jupyter_output><empty_output><jupyter_text>Transform population DataFrame<jupyter_code>population_cols = ["City","Total_Population"]
population_transformed = population_data_df[population_cols].copy()

# Rename the column headers
population_transformed = population_transformed.rename(columns={"Total_Population":"TotalPopulation"})

# Set index
population_transformed.set_index("City", inplace=True)

population_transformed.head()<jupyter_output><empty_output><jupyter_text>Transform Avocado Volume DataFrame<jupyter_code>avocado_volume_df_cols = ["Date","California","Mexico","Chile","Dominican Republic","New Zealand","Peru","Total Volume in Pounds"]
avocado_volume_transformed = avocado_volume_df[avocado_volume_df_cols].copy()

# Rename the column headers
avocado_volume_transformed = avocado_volume_transformed.rename(columns={"Total Volume in Pounds":"Total Volume"})

# Set index
avocado_volume_transformed.set_index("Date", inplace=True)

avocado_volume_transformed.head()<jupyter_output><empty_output><jupyter_text>Load DataFrames into database<jupyter_code>pymysql.install_as_MySQLdb()
connection_string = "root:password@localhost/avocado"
engine = create_engine("mysql://root:password@localhost/avocado", pool_size=10, max_overflow=20)
engine.table_names()<jupyter_output>/Users/Christine/anaconda3/lib/python3.6/site-packages/pymysql/cursors.py:170: Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")
  result = self._query(query)<jupyter_text>Load DataFrames into database<jupyter_code>avocado_transformed.to_sql(name='avocados', con=engine, if_exists='append', index=True)
population_transformed.to_sql(name='population', con=engine, if_exists='append', index=True)
avocado_volume_transformed.to_sql(name='avocado_volume', con=engine, if_exists='append', index=True)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1199442119944212, 'fraction_numerical': 0.007322175732217573, 'mean_word_length': 10.954166666666667, 'pattern_counts': {'":': 5, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 0, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '245571', 'license': 'MIT', 'n_tokens_mistral': 1000, 'n_tokens_neox': 916, 'n_words': 149, 'path': 'christine/Extract/.ipynb_checkpoints/extract_transform-checkpoint.ipynb', 'repo_name': 'christineton/Extract_Transform_Load_Project', 'size': '2868'}