# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # NYC AQI data cleaning and preprocessing

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# ## Read in the six years of data

aqi_2015 = pd.read_csv('../datasets/air-quality-reports/2015/daily_aqi_by_county_2015.csv')
aqi_2016 = pd.read_csv('../datasets/air-quality-reports/2016/daily_aqi_by_county_2016.csv')
aqi_2017 = pd.read_csv('../datasets/air-quality-reports/2017/daily_aqi_by_county_2017.csv')
aqi_2018 = pd.read_csv('../datasets/air-quality-reports/2018/daily_aqi_by_county_2018.csv')
aqi_2019 = pd.read_csv('../datasets/air-quality-reports/2019/daily_aqi_by_county_2019.csv')
aqi_2020 = pd.read_csv('../datasets/air-quality-reports/2020/daily_aqi_by_county_2020.csv')

# + jupyter={"outputs_hidden": true} tags=[]
aqi_2015.head()

# + jupyter={"outputs_hidden": true} tags=[]
aqi_2016.head()

# + jupyter={"outputs_hidden": true} tags=[]
aqi_2017.head()

# + jupyter={"outputs_hidden": true} tags=[]
aqi_2018.head()

# + jupyter={"outputs_hidden": true} tags=[]
aqi_2019.head()

# + jupyter={"outputs_hidden": true} tags=[]
aqi_2020.head()
# -

# ### Convert date column to datetime

aqi_2015['Date'] = pd.to_datetime(aqi_2015['Date'])

aqi_2016['Date'] = pd.to_datetime(aqi_2016['Date'])
aqi_2017['Date'] = pd.to_datetime(aqi_2017['Date'])
aqi_2018['Date'] = pd.to_datetime(aqi_2018['Date'])
aqi_2019['Date'] = pd.to_datetime(aqi_2019['Date'])
aqi_2020['Date'] = pd.to_datetime(aqi_2020['Date'])

# ### Set the date as index

aqi_2015.set_index('Date', inplace=True)
aqi_2016.set_index('Date', inplace=True)
aqi_2017.set_index('Date', inplace=True)
aqi_2018.set_index('Date', inplace=True)
aqi_2019.set_index('Date', inplace=True)
aqi_2020.set_index('Date', inplace=True)

aqi_2015.head(1)

# ## Concat six years of data into single DataFrame

aqi_2015_2020 = pd.concat([aqi_2015, aqi_2016, aqi_2017, aqi_2018, aqi_2019, aqi_2020])

aqi_2015_2020

aqi_2015_2020.sort_index(inplace=True)

aqi_2015_2020

# ## Isolate the New York City

# ### Isolate the New York State data

aqi_NYS = aqi_2015_2020[aqi_2015_2020['State Name'] == 'New York'].copy(deep=True)

aqi_NYS.drop(columns='State Code', inplace = True)

# ### Isolate the 5 counties which comprise New York City

aqi_NYC = pd.concat(
    [aqi_NYS[aqi_NYS['county Name'] == 'New York'],
     aqi_NYS[aqi_NYS['county Name'] == 'Kings'],
     aqi_NYS[aqi_NYS['county Name'] == 'Queens'],
     aqi_NYS[aqi_NYS['county Name'] == 'Bronx'],
     aqi_NYS[aqi_NYS['county Name'] == 'Richmond']]
)

aqi_NYC.sort_index(inplace=True)

aqi_NYC

aqi_NYC_agg = aqi_NYC.copy(deep = True)

# #### Drop unnecessary columns

aqi_NYC_agg.drop(columns=['State Name', 'county Name', 'County Code', 'Number of Sites Reporting', 'Category', 'Defining Parameter', 'Defining Site'], inplace=True)

aqi_NYC_agg = aqi_NYC_agg.groupby('Date').mean()

# ## Visualize the data

plt.figure(figsize=(20, 4))
plt.plot(aqi_NYC_agg)

# ## Export the data

aqi_NYC_agg.to_pickle('../datasets/air-quality-reports/aggregates/NYC/NYC.pkl')

aqi_NYC_agg.to_csv('./datasets/air-quality-reports/aggregates/NYC/nyc_daily_2015_2020.csv')



StarcoderdataJupyterScriptsDedupFiltered