<jupyter_start><jupyter_text>Cleaning  Contents: - [Import for Cleaning](Import-for-Cleaning) - [Inspecting before Cleaning](Inspecting-before-Cleaning) - [Getting similar Data](Getting-similar-Data) - [More Data](More-Data) - [Cleaning Functions](Cleaning-Functions) - [Merge Clean Data](Merge-Clean-Data) - [More Data to Import](More-Data-to-Import) - [More Data to Merge](More-Data-to-Merge) - [Putting the years together](Putting-the-years-together) - [Add Percent Change Column](Add-Percent-Change-Column) - [Rename Columns](Rename-Columns) - [Export Clean Data](Export-Clean-Data)  Import for Cleaning[(back to top)](Cleaning)Import python libraries and data to clean.Data to be cleaned includes six CSV Files with daily pollutant measures, per pollutant, per year (2017 to 2019), as well as three CSV files with High and Low temperatures observed daily for each of the same years (seven files per year). All data was observed in Cleveland, OH.<jupyter_code>import numpy as np
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

import os
import warnings
warnings.simplefilter("ignore")
os.listdir('../data/')
co = pd.read_csv('../data/2017_all_cleveland_oh_CO.csv')
no2 = pd.read_csv('../data/2017_all_cleveland_oh_NO2.csv')
o3 = pd.read_csv('../data/2017_all_cleveland_oh_O3.csv')
pm10 = pd.read_csv('../data/2017_all_cleveland_oh_PM10.csv')
pm25 = pd.read_csv('../data/2017_all_cleveland_oh_PM2.5.csv')
so2 = pd.read_csv('../data/2017_all_cleveland_oh_SO2.csv')<jupyter_output><empty_output><jupyter_text>Inspecting before Cleaning[(back to top)](Cleaning)Looking over tables, we found that "Site Name" __*GT Craig NCore*__ seems to have the most data. We also noticed that the device used (or "POC") for PM10 and PM2.5 that is labeled "3" has the most data, and all other pollutants have their best "POC" labelled as "1".These are our best values and what we used for our data set.<jupyter_code>co.info()
co.head(2)
no2.info()
no2.head()
co.shape
co['POC'].unique()
co['AQS_PARAMETER_DESC'].unique()
no2.shape
no2['POC'].unique()
no2['AQS_PARAMETER_DESC'].unique()
o3.shape
o3['POC'].unique()
o3['AQS_PARAMETER_DESC'].unique()
pm10.shape
pm10['POC'].unique()
pm10['AQS_PARAMETER_DESC'].unique()
pm25.shape
pm25['POC'].unique()
pm25['AQS_PARAMETER_DESC'].unique()
so2.shape
so2[(so2['POC']==1) & (so2['Site Name'] == 'GT Craig NCore')].sort_values('Date').shape
so2['POC'].unique()
so2['AQS_PARAMETER_DESC'].unique()<jupyter_output><empty_output><jupyter_text>Getting similar Data[(back to top)](Cleaning)Here we did some more exploration and extracted the relevant data: Pollutant measuremant and Daily AQI value.<jupyter_code>co_gt1 = co[(co['POC']==1) & (co['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(co_gt1.shape)
co_gt1.head(4)
no2_gt1 = no2[(no2['POC']==1) & (no2['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(no2_gt1.shape)
no2_gt1.head(4)
o3_gt1 = o3[(o3['POC']==1) & (o3['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(o3_gt1.shape)
o3_gt1.head(4)
pm10_gt3 = pm10[(pm10['POC']==3) & (pm10['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(pm10_gt3.shape)
pm10_gt3.head(4)
pm25_gt3 = pm25[(pm25['POC']==3) & (pm25['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(pm25_gt3.shape)
pm25_gt3.head(4)
so2_gt1 = so2[(so2['POC']==1) & (so2['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(so2_gt1.shape)
so2_gt1.head(4)
co_gt1.columns<jupyter_output><empty_output><jupyter_text>More Data[(back to top)](Cleaning)After cleaning up one year's worth of data for pollutants, we decided it would be best to get more data; We pulled two more years woht of data in addition to our 2017 data (that is, we added 2018, and 2019 to our data set).The best Site and devices were consistent with the 2017 data.<jupyter_code>co_2018 = pd.read_csv('../data/2018_all_cleveland_oh_CO.csv')
no2_2018 = pd.read_csv('../data/2018_all_cleveland_oh_NO2.csv')
o3_2018 = pd.read_csv('../data/2018_all_cleveland_oh_O3.csv')
pm10_2018 = pd.read_csv('../data/2018_all_cleveland_oh_PM10.csv')
pm25_2018 = pd.read_csv('../data/2018_all_cleveland_oh_PM2.5.csv')
so2_2018 = pd.read_csv('../data/2018_all_cleveland_oh_SO2.csv')

co_2019 = pd.read_csv('../data/2019_all_cleveland_oh_CO.csv')
no2_2019 = pd.read_csv('../data/2019_all_cleveland_oh_NO2.csv')
o3_2019 = pd.read_csv('../data/2019_all_cleveland_oh_O3.csv')
pm10_2019 = pd.read_csv('../data/2019_all_cleveland_oh_PM10.csv')
pm25_2019 = pd.read_csv('../data/2019_all_cleveland_oh_PM2.5.csv')
so2_2019 = pd.read_csv('../data/2019_all_cleveland_oh_SO2.csv')
co_gt1_2018 = co_2018[(co_2018['POC']==1) & (co_2018['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(co_gt1_2018.shape)
no2_gt1_2018 = no2_2018[(no2_2018['POC']==1) & (no2_2018['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(no2_gt1_2018.shape)
o3_gt1_2018 = o3_2018[(o3_2018['POC']==1) & (o3_2018['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(o3_gt1_2018.shape)
pm10_gt3_2018 = pm10_2018[(pm10_2018['POC']==3) & (pm10_2018['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(pm10_gt3_2018.shape)
pm25_gt3_2018 = pm25_2018[(pm25_2018['POC']==3) & (pm25_2018['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(pm25_gt3_2018.shape)
so2_gt1_2018 = so2_2018[(so2_2018['POC']==1) & (so2_2018['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(so2_gt1_2018.shape)
co_gt1_2019 = co_2019[(co_2019['POC']==1) & (co_2019['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(co_gt1_2019.shape)
no2_gt1_2019 = no2_2019[(no2_2019['POC']==1) & (no2_2019['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(no2_gt1_2019.shape)
o3_gt1_2019 = o3_2019[(o3_2019['POC']==1) & (o3_2019['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(o3_gt1_2019.shape)
pm10_gt3_2019 = pm10_2019[(pm10_2019['POC']==3) & (pm10_2019['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(pm10_gt3_2019.shape)
pm25_gt3_2019 = pm25_2019[(pm25_2019['POC']==3) & (pm25_2019['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(pm25_gt3_2019.shape)
so2_gt1_2019 = so2_2019[(so2_2019['POC']==1) & (so2_2019['Site Name'] == 'GT Craig NCore')].sort_values('Date')
print(so2_gt1_2019.shape)
no2_df = no2[['Date', 'COUNTY', 'Daily Max 1-hour NO2 Concentration', 'DAILY_AQI_VALUE']]
no2_df['date_county'] = no2_df['Date'] + ", " + no2_df['COUNTY']
no2_df.set_index('date_county', inplace=True)
no2_df.head(2)
no2_df.drop(columns=['Date', 'COUNTY'], inplace=True)
no2_df.head()
no2_df.isna().sum()
no2_df.info()
no2_df2 = no2.drop(columns=['Source', 'Site ID', 'POC', 'UNITS', 'Site Name', 'AQS_PARAMETER_CODE', 'AQS_PARAMETER_DESC', 'CBSA_CODE', 'CBSA_NAME', 'STATE_CODE', 'STATE', 'COUNTY', 'COUNTY_CODE'])
no2_df2
no2_df2.columns
no2_df2.rename(columns={
    'Date': 'Date',
    'Daily Max 1-hour NO2 Concentration': 'no2_max',
    'DAILY_AQI_VALUE': 'aqi_val',
    'DAILY_OBS_COUNT': 'obs',
    'PERCENT_COMPLETE': 'complete',
    'SITE_LATITUDE': 'lat',
    'SITE_LONGITUDE': 'lon' 
}, inplace=True)
no2_df2
'Daily Max 1-hour NO2 Concentration'.split(' ')[3]
pm25.sort_values('Date', ascending=True).head(20)
pm10.info()
pm10['POC'].value_counts()
pm10.nunique()
poc_mask = pm10['POC'] == 1
pm10[poc_mask]['Date'].unique()
# pm10.nunique()<jupyter_output><empty_output><jupyter_text>Cleaning Functions[(back to top)](Cleaning)Wrote and ran a function to "autpmate" the cleaning process and extract the most useful information so we can later add it to a single, larger data set.<jupyter_code>def clean_cleaveland_air(df_in, col = ''):
    if((len(col) == 0) & (df_in.shape[1] > 4)):
        col = df_in.columns[4]
    col_rename = col.split(' ')[3].lower()
    col_meas = '_max'
    if(col_rename == 'concentration'):
        col_rename = col.split(' ')[2].lower()
        col_meas = '_mean'
    
    df_in.rename(columns={'Date':'date'}, inplace=True)
    df_out = df_in[['date', col, 'DAILY_AQI_VALUE']]#, 'Site Name']]
    df_out['date'] = pd.to_datetime(df_out['date'])
    df_out.set_index('date', inplace=True)
    
    df_out.rename(columns={
        col: col_rename + col_meas, 
        'DAILY_AQI_VALUE': col_rename + '_aqi_val'
#         , 'Site Name': 'site_name'
    }, inplace=True)
    
    print(f'\nSUM OF NaNs: {df_out.isna().sum().sum()}\n')
    return df_out
clean_no2 = clean_cleaveland_air(no2_gt1)#, 'Daily Max 1-hour NO2 Concentration')
clean_no2.head()
clean_co = clean_cleaveland_air(co_gt1) #, 'Daily Max 8-hour CO Concentration')
clean_co.head()
clean_o3 = clean_cleaveland_air(o3_gt1)
clean_o3.head()
clean_pm10 = clean_cleaveland_air(pm10_gt3)
clean_pm10.head()
clean_pm25 = clean_cleaveland_air(pm25_gt3)
clean_pm25.head()
clean_so2 = clean_cleaveland_air(so2_gt1)
clean_so2.head()
clean_no2_2018 = clean_cleaveland_air(no2_gt1_2018)
clean_co_2018 = clean_cleaveland_air(co_gt1_2018)
clean_o3_2018 = clean_cleaveland_air(o3_gt1_2018)
clean_pm10_2018 = clean_cleaveland_air(pm10_gt3_2018)
clean_pm25_2018 = clean_cleaveland_air(pm25_gt3_2018)
clean_so2_2018 = clean_cleaveland_air(so2_gt1_2018)
clean_no2_2019 = clean_cleaveland_air(no2_gt1_2019)
clean_co_2019 = clean_cleaveland_air(co_gt1_2019)
clean_o3_2019 = clean_cleaveland_air(o3_gt1_2019)
clean_pm10_2019 = clean_cleaveland_air(pm10_gt3_2019)
clean_pm25_2019 = clean_cleaveland_air(pm25_gt3_2019)
clean_so2_2019 = clean_cleaveland_air(so2_gt1_2019)<jupyter_output>SUM OF NaNs: 0<jupyter_text>Merge Clean Data[(back to top)](Cleaning)Here we took our 2017 data describing pollutants at "GT Craig NCore" and merged our cleaned and simplified data together and added temperature data to it.<jupyter_code>clean_co.index.value_counts().sort_index()
merge_df = pd.merge(
    left = clean_co,
    right = pd.merge(
        left = clean_no2,
        right = pd.merge(
            left = clean_o3, 
            right = pd.merge(
                left = clean_pm10, 
                right = pd.merge(
                    left = clean_pm25, 
                    right = clean_so2,
                    left_index=True,
                    right_index=True,
                    how='outer'
                ),
                left_index=True,
                right_index=True,
                how='outer'
            ),
            left_index=True,
            right_index=True,
            how='outer'
        ),
        left_index=True,
        right_index=True,
        how='outer'
    ),
    left_index=True,
    right_index=True,
    how='outer'
)
merge_df # .to_csv('20210511_all_aqi.csv')
merge_df['cumulative_aqi'] = merge_df[['co_aqi_val', 'no2_aqi_val', 'ozone_aqi_val', 'pm10_aqi_val', 'pm2.5_aqi_val', 'so2_aqi_val']].max(axis=1)
merge_df.head()
temp = pd.read_csv('../data/ClevelandOHweather2017.csv')
temp
temp.rename(columns={'Date': 'date', 'High': 'temp_high', 'Low': 'temp_low'}, inplace=True)
temp
temp['date'] = pd.to_datetime(temp['date'])
temp['date'] = temp['date'].dt.strftime("%m/%d/%Y")
temp
temp.info()
temp['date'] = pd.to_datetime(temp['date'])
temp['temp_high'] = pd.to_numeric(temp['temp_high'])
temp['temp_low'] = pd.to_numeric(temp['temp_low'])
temp.info()
temp['temp_avg'] = (temp['temp_high'] + temp['temp_low'])/2
temp
merge_temp = pd.merge(left=merge_df, right=temp, how='left', on='date')
merge_temp<jupyter_output><empty_output><jupyter_text>More Data to Import[(back to top)](Cleaning)Since we already had the data for pollutants for 2018 and 2019, we gathered temperature data and imported it here.<jupyter_code>temp_2018 = pd.read_csv('../data/ClevelandOHweather2018.csv')
temp_2018
temp_2018.rename(columns={'Date': 'date', 'High': 'temp_high', 'Low': 'temp_low'}, inplace=True)
temp_2018
temp_2018['date'] = pd.to_datetime(temp_2018['date'])
temp_2018['date'] = temp_2018['date'].dt.strftime("%m/%d/%Y")
temp_2018
temp_2018.info()
temp_2018['date'] = pd.to_datetime(temp_2018['date'])
temp_2018['temp_high'] = pd.to_numeric(temp_2018['temp_high'])
temp_2018['temp_low'] = pd.to_numeric(temp_2018['temp_low'])
temp_2018.info()
temp_2018['temp_avg'] = (temp_2018['temp_high'] + temp_2018['temp_low'])/2
temp_2018
temp_2019 = pd.read_csv('../data/ClevelandOHweather2019.csv')
temp_2019
temp_2019.rename(columns={'Date': 'date', 'High': 'temp_high', 'Low': 'temp_low'}, inplace=True)
temp_2019
temp_2019['date'] = pd.to_datetime(temp_2019['date'])
temp_2019['date'] = temp_2019['date'].dt.strftime("%m/%d/%Y")
temp_2019
temp_2019.info()
temp_2019['date'] = pd.to_datetime(temp_2019['date'])
temp_2019['temp_high'] = pd.to_numeric(temp_2019['temp_high'])
temp_2019['temp_low'] = pd.to_numeric(temp_2019['temp_low'])
temp_2019.info()
temp_2019['temp_avg'] = (temp_2019['temp_high'] + temp_2019['temp_low'])/2
temp_2019<jupyter_output><empty_output><jupyter_text>More Data to Merge[(back to top)](Cleaning)Here we are merging our 2018 and 2019 data together into Data Frames<jupyter_code>merge_df_2018 = pd.merge(
    left = clean_co_2018,
    right = pd.merge(
        left = clean_no2_2018,
        right = pd.merge(
            left = clean_o3_2018, 
            right = pd.merge(
                left = clean_pm10_2018, 
                right = pd.merge(
                    left = clean_pm25_2018, 
                    right = clean_so2_2018,
                    left_index=True,
                    right_index=True,
                    how='outer'
                ),
                left_index=True,
                right_index=True,
                how='outer'
            ),
            left_index=True,
            right_index=True,
            how='outer'
        ),
        left_index=True,
        right_index=True,
        how='outer'
    ),
    left_index=True,
    right_index=True,
    how='outer'
)
merge_df_2018 # .to_csv('20210511_all_aqi.csv')
merge_df_2018['cumulative_aqi'] = merge_df_2018[['co_aqi_val', 'no2_aqi_val', 'ozone_aqi_val', 'pm10_aqi_val', 'pm2.5_aqi_val', 'so2_aqi_val']].max(axis=1)
merge_df_2018.head()
merge_df_2019 = pd.merge(
    left = clean_co_2019,
    right = pd.merge(
        left = clean_no2_2019,
        right = pd.merge(
            left = clean_o3_2019, 
            right = pd.merge(
                left = clean_pm10_2019, 
                right = pd.merge(
                    left = clean_pm25_2019, 
                    right = clean_so2_2019,
                    left_index=True,
                    right_index=True,
                    how='outer'
                ),
                left_index=True,
                right_index=True,
                how='outer'
            ),
            left_index=True,
            right_index=True,
            how='outer'
        ),
        left_index=True,
        right_index=True,
        how='outer'
    ),
    left_index=True,
    right_index=True,
    how='outer'
)
merge_df_2019 # .to_csv('20210511_all_aqi.csv')
merge_df_2019['cumulative_aqi'] = merge_df_2019[['co_aqi_val', 'no2_aqi_val', 'ozone_aqi_val', 'pm10_aqi_val', 'pm2.5_aqi_val', 'so2_aqi_val']].max(axis=1)
merge_df_2019.head()
merge_temp_2018 = pd.merge(left=merge_df_2018, right=temp_2018, how='left', on='date')
merge_temp_2018
merge_temp_2019 = pd.merge(left=merge_df_2019, right=temp_2019, how='left', on='date')
merge_temp_2019<jupyter_output><empty_output><jupyter_text>Putting the years together[(back to top)](Cleaning)Now that the data is all together, all six pollutants along with temperature observations, and their respective columns are named the same, we can merge all the data together into one Data Frame for export.<jupyter_code>concat_df = pd.concat([merge_temp, merge_temp_2018, merge_temp_2019], axis=0, ignore_index=True)
concat_df<jupyter_output><empty_output><jupyter_text>Rename Columns[(back to top)](Cleaning)<jupyter_code># 'high_temp', 'low_temp', 'co_max', 'co_aqi_val', 'no2_max', 'no2_aqi_val', 'ozone_max', 'ozone_aqi_val', 'pm10_mean', 
# 'pm10_aqi_val', 'pm2.5_mean', 'pm2.5_aqi_val', 'so2_max', 'so2_aqi_val', 'cumulitive_aqi', 'pct_change_aqi', 
# 'average_daily_temp', 'month', 'weekday'

concat_df.rename(columns={
    'date': 'date', 
    'co_max': 'co_max', 
    'co_aqi_val': 'co_aqi_val', 
    'no2_max': 'no2_max', 
    'no2_aqi_val': 'no2_aqi_val', 
    'ozone_max': 'ozone_max', 
    'ozone_aqi_val': 'ozone_aqi_val', 
    'pm10_mean': 'pm10_mean', 
    'pm10_aqi_val': 'pm10_aqi_val', 
    'pm2.5_mean': 'pm2.5_mean', 
    'pm2.5_aqi_val': 'pm2.5_aqi_val', 
    'so2_max': 'so2_max', 
    'so2_aqi_val': 'so2_aqi_val', 
    'cumulative_aqi': 'cumulitive_aqi', 
    'temp_high': 'high_temp', 
    'temp_low': 'low_temp', 
    'temp_avg':'average_daily_temp'
})<jupyter_output><empty_output><jupyter_text>Add Columns[(back to top)](Cleaning)<jupyter_code>concat_df['date'] = pd.to_datetime(concat_df['date'])
concat_df['pct_change_aqi'] = concat_df['cumulative_aqi'].pct_change()
concat_df['month'] = concat_df['date'].dt.month
concat_df['weekday'] = concat_df['date'].dt.dayofweek
concat_df<jupyter_output><empty_output><jupyter_text>Export Clean Data[(back to top)](Cleaning)<jupyter_code>output_file = '../data/cleaned_aqi_and_temp_data_2017-2019.csv'
concat_df.to_csv(output_file, index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15679055633473585, 'fraction_numerical': 0.07608695652173914, 'mean_word_length': 4.727242302543507, 'pattern_counts': {'":': 0, '<': 48, '<?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': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '193555', 'license': 'CC0-1.0', 'n_tokens_mistral': 7507, 'n_tokens_neox': 6580, 'n_words': 1113, 'path': 'code/01_cleaning.ipynb', 'repo_name': 'ccaldarella99/AirQuality-Streamlit', 'size': '17112'}