<jupyter_start><jupyter_text>Load Beijing Air Quality<jupyter_code>beijing_17_18_aq = pd.read_csv(DATA_PATH + 'raw/Beijing/beijing_17_18_aq.csv', parse_dates=['utc_time'])
beijing_802_803_aq = pd.read_csv(DATA_PATH + 'raw/Beijing/beijing_201802_201803_aq.csv', parse_dates=['utc_time'])
beijing_17_18_aq.head()
beijing_17_18_aq.utc_time.min(), beijing_17_18_aq.utc_time.max()
beijing_802_803_aq.head()
beijing_802_803_aq.utc_time.min(), beijing_802_803_aq.utc_time.max()<jupyter_output><empty_output><jupyter_text>Union Beijing Air Quality Data<jupyter_code>def rename_aq_df(df):
    return df.rename(columns={'stationId': 'station_id', 'utc_time': 'utc_datetime'})<jupyter_output><empty_output><jupyter_text>Rename and concatenate both airquality data tables<jupyter_code>beijing_17_18_aq = rename_aq_df(beijing_17_18_aq)
beijing_802_803_aq = rename_aq_df(beijing_802_803_aq)

beijing_aq_data = pd.concat([beijing_17_18_aq, beijing_802_803_aq], ignore_index=True)
beijing_aq_data.shape<jupyter_output><empty_output><jupyter_text>Drop duplicated rows<jupyter_code>beijing_aq_data = beijing_aq_data.drop_duplicates()
beijing_aq_data.shape<jupyter_output><empty_output><jupyter_text>Save as interim feather file<jupyter_code># `to_feather` won't accept the index after `drop_duplicates`. Had to `reset_index()` and drop the current index.

beijing_aq_data.reset_index().drop('index', axis=1).to_feather(DATA_PATH + 'interim/beijing_aq_union.feather')
beijing_aq_data = pd.read_feather(DATA_PATH + 'interim/beijing_aq_union.feather')
beijing_aq_data.head()
beijing_aq_data.utc_datetime.min(), beijing_aq_data.utc_datetime.max()<jupyter_output><empty_output><jupyter_text>Loading Grid Weather Data<jupyter_code>beijing_meo_grid = pd.read_csv(DATA_PATH + 'raw/Beijing/Beijing_historical_meo_grid.csv', parse_dates=['utc_time'])
beijing_meo_grid.head()
beijing_meo_grid.utc_time.min(), beijing_meo_grid.utc_time.max()<jupyter_output><empty_output><jupyter_text>Rename Grid Weather Data<jupyter_code>def rename_weather_data(df):
    return df.rename(columns={'stationName': 'station_id', 'utc_time': 'utc_datetime', 'wind_speed/kph': 'wind_speed'})
beijing_meo_grid = rename_weather_data(beijing_meo_grid)
beijing_meo_grid = beijing_meo_grid.drop_duplicates()
beijing_meo_grid.head()<jupyter_output><empty_output><jupyter_text>Save as interim feather file<jupyter_code># `to_feather` won't accept the index after `drop_duplicates`. Had to `reset_index()` and drop the current index.

beijing_meo_grid.reset_index().drop('index', axis=1).to_feather(DATA_PATH + 'interim/beijing_meo_grid.feather')
beijing_meo_grid = pd.read_feather(DATA_PATH + 'interim/beijing_meo_grid.feather')<jupyter_output><empty_output><jupyter_text>Loading Observed Weather Data<jupyter_code>beijing_meo_observed_17_18 = pd.read_csv(DATA_PATH + 'raw/Beijing/beijing_17_18_meo.csv', parse_dates=['utc_time'])
beijing_meo_observed_802_803 = pd.read_csv(DATA_PATH + 'raw/Beijing/beijing_201802_201803_me.csv', parse_dates=['utc_time'])
beijing_meo_observed_17_18.head()
beijing_meo_observed_17_18.station_id.unique()
beijing_meo_observed_17_18.utc_time.min(), beijing_meo_observed_17_18.utc_time.max()
beijing_meo_observed_802_803.head()
beijing_meo_observed_802_803.utc_time.min(), beijing_meo_observed_802_803.utc_time.max()
beijing_meo_observed_802_803.station_id.unique()<jupyter_output><empty_output><jupyter_text>Union observed weather data<jupyter_code>beijing_meo_observed_17_18 = rename_weather_data(beijing_meo_observed_17_18)
beijing_meo_observed_802_803 = rename_weather_data(beijing_meo_observed_802_803)
beijing_observed_meo_union = pd.concat([beijing_meo_observed_17_18, beijing_meo_observed_802_803], sort=False)
beijing_observed_meo_union = beijing_observed_meo_union.drop_duplicates()
beijing_observed_meo_union.head()<jupyter_output><empty_output><jupyter_text>Save as interim feather file<jupyter_code>beijing_observed_meo_union.reset_index().drop('index', axis=1).to_feather(DATA_PATH + 'interim/beijing_meo_observed_union.feather')
beijing_observed_meo_union = pd.read_feather(DATA_PATH + 'interim/beijing_meo_observed_union.feather')
beijing_observed_meo_union.head()<jupyter_output><empty_output><jupyter_text>Remove/correct outliers  Weather outliers<jupyter_code>beijing_observed_meo_union.describe()
# TODO: Show outliers using boxplot of temperature, pressure, humidity, wind_direction and wind_speed
beijing_observed_meo_union.temperature.plot.box()
beijing_observed_meo_union[beijing_observed_meo_union.temperature > 800000]<jupyter_output><empty_output><jupyter_text>Wind direction is measured in degrees clockwise from due north and so a wind coming from the south has a wind direction of 180 degrees; one from the east is 90 degrees, etc. **If the wind speed is less than 0.5m/s (nearly no wind), the value of the wind_direction is 999017.**<jupyter_code>beijing_observed_meo_union[beijing_observed_meo_union.wind_direction == 999017].head()
def process_observed_meo_outliers(df):
    df = df[df.temperature < 800000]
    df.loc[:, 'wind_direction'] = df.replace(999017, 0)
    return df

beijing_observed_meo_union = process_observed_meo_outliers(beijing_observed_meo_union)   
beijing_observed_meo_union.describe()<jupyter_output><empty_output><jupyter_text>Save as interim feather file<jupyter_code>beijing_observed_meo_union.reset_index().drop('index', axis=1).to_feather(DATA_PATH + 'interim/beijing_meo_observed_union.feather')
beijing_observed_meo_union = pd.read_feather(DATA_PATH + 'interim/beijing_meo_observed_union.feather')
beijing_observed_meo_union.head()<jupyter_output><empty_output><jupyter_text>Air Quality Outliers<jupyter_code>beijing_aq_data.describe()
beijing_aq_data['PM2.5'].plot.box()
beijing_aq_data['PM10'].plot.box()
beijing_aq_data[beijing_aq_data['PM2.5'] > 1400]
## PM2.5 reading for this day seems to be an outlier. We will drop it

beijing_aq_data.loc[beijing_aq_data[beijing_aq_data.utc_datetime == '2018-3-29 14:00:00'].index].head(10)
beijing_aq_data[beijing_aq_data['PM10'] > 3000]
# PM10 is high for that day so we don't drop it.

beijing_aq_data.loc[beijing_aq_data[beijing_aq_data.utc_datetime == '2018-3-28'].index].head(10)
def process_air_quality_outliers(df):
    outliers_index = df[beijing_aq_data['PM2.5'] > 1400].index
    df = df.drop(outliers_index)
    return df

beijing_aq_data = process_air_quality_outliers(beijing_aq_data)<jupyter_output><empty_output><jupyter_text>Save as interim feather file<jupyter_code>beijing_aq_data.reset_index().drop('index', axis=1).to_feather(DATA_PATH + 'interim/beijing_aq_union.feather')
beijing_aq_data = pd.read_feather(DATA_PATH + 'interim/beijing_aq_union.feather')
beijing_aq_data.head()<jupyter_output><empty_output><jupyter_text>Merge Air Quality and Weather Data  Load and process air quality stations excel file<jupyter_code>beijing_aq_stations = pd.read_excel(DATA_PATH + '/raw/Beijing/Beijing_AirQuality_Stations_en.xlsx', names=['station_id', 'longitude', 'latitude'])
def process_aq_stations(df):
    df = df.loc[11:].dropna().reset_index().drop('index', axis=1)
    return df

beijing_aq_stations = process_aq_stations(beijing_aq_stations)
beijing_aq_stations.to_feather(DATA_PATH + 'interim/beijing_aq_stations.feather')
beijing_aq_stations = pd.read_feather(DATA_PATH + 'interim/beijing_aq_stations.feather')
beijing_aq_stations.head()<jupyter_output><empty_output><jupyter_text>Load and process weather grid stations<jupyter_code>beijing_meo_grid_stations = pd.read_csv(DATA_PATH + 'raw/Beijing/Beijing_grid_weather_station.csv', names=['station_id', 'latitude', 'longitude'])
beijing_meo_grid_stations.head()<jupyter_output><empty_output><jupyter_text>Create weather observed stations list<jupyter_code>beijing_observed_meo_union = pd.read_feather(DATA_PATH + 'interim/beijing_meo_observed_union.feather')
beijing_observed_meo_union.head()
beijing_meo_observed_stations = beijing_observed_meo_union[['station_id', 'latitude', 'longitude']].drop_duplicates().dropna().reset_index().drop('index', axis=1)
beijing_meo_observed_stations.head()<jupyter_output><empty_output><jupyter_text>Save observed weather stations list<jupyter_code>beijing_meo_observed_stations.to_feather(DATA_PATH + 'interim/beijing_meo_observed_stations.feather')
beijing_meo_observed_stations = pd.read_feather(DATA_PATH + 'interim/beijing_meo_observed_stations.feather')
beijing_meo_observed_stations.head()<jupyter_output><empty_output><jupyter_text>Merge air quality station data with it's nearest neighbor weather station data<jupyter_code>from sklearn.neighbors import NearestNeighbors
import itertools
import os

# Create directory to save merged air quality and weather data

os.makedirs(DATA_PATH + 'interim/aq_station_merged', exist_ok=True)

# Load beijing air quality, grid and observed weather stations.

beijing_aq_stations = pd.read_feather(DATA_PATH + 'interim/beijing_aq_stations.feather')

grid_weather_stations = pd.read_csv(DATA_PATH + 'raw/Beijing/Beijing_grid_weather_station.csv', names=['station_id', 'latitude', 'longitude'])
grid_weather_data = pd.read_feather(DATA_PATH + 'interim/beijing_meo_grid.feather')

observed_weather_stations = pd.read_feather(DATA_PATH + 'interim/beijing_meo_observed_stations.feather')
observed_weather_data = pd.read_feather(DATA_PATH + 'interim/beijing_meo_observed_union.feather')

beijing_aq_data = pd.read_feather(DATA_PATH + 'interim/beijing_aq_union.feather')

## Fit NearestNeighbor model to grid and observed weather station's coordinates

grid_nn_model = NearestNeighbors(n_neighbors=2, metric='haversine', n_jobs=-1)
grid_nn_model.fit(grid_weather_stations.set_index('station_id'))

observed_nn_model = NearestNeighbors(n_neighbors=1, metric='haversine', n_jobs=-1)
observed_nn_model.fit(observed_weather_stations.set_index('station_id'))

def process_weather_station_data(df, station_name):
    df = df.iloc[:, 3:].copy()
    new_names = [(i,f"{station_name}_{i}") for i in df.iloc[:, 1:].columns.values]
    return df.rename(columns = dict(new_names))

def output_nn_weather_data(aq_station, latitude, longitude, nn_model, weather_stations, weather_data):
        
    nn_result = nn_model.kneighbors([[latitude, longitude]], return_distance=False)
    
    nn_index = nn_result[0]
    nn_station_names = weather_stations.iloc[nn_index].station_id
    
    weather_data_dfs = [weather_data[weather_data.station_id == weather_station_id] for weather_station_id in nn_station_names]
    weather_data_dfs = [process_weather_station_data(df, station_name) for station_name, df in zip(nn_station_names, weather_data_dfs)]
    
    return weather_data_dfs

# Iterate through each air quality station
for index, aq_station_info in beijing_aq_stations.iterrows():
    
    # Fetch KNN grid station weather data
        
    nn_grid_weather_data = output_nn_weather_data(aq_station_info['station_id'], 
                                                  aq_station_info['latitude'], 
                                                  aq_station_info['longitude'], 
                                                  grid_nn_model,
                                                  grid_weather_stations,
                                                  grid_weather_data)
    
    # Fetch KNN observed station weather data
    
    nn_observed_weather_data = output_nn_weather_data(aq_station_info['station_id'], 
                                                     aq_station_info['latitude'], 
                                                     aq_station_info['longitude'], 
                                                     observed_nn_model,
                                                     observed_weather_stations,
                                                     observed_weather_data)
    
    
    
    # Merge to air station air quality data
    station_aq_data = beijing_aq_data[beijing_aq_data.station_id == aq_station_info['station_id']]
    
    for weather_df in itertools.chain(nn_grid_weather_data, nn_observed_weather_data):
        station_aq_data = station_aq_data.merge(weather_df, on='utc_datetime', how='inner')
        
        
    # Fill-in missing hourly values to impute later.
    date_range_index = pd.date_range(start=station_aq_data.utc_datetime.min(), end=station_aq_data.utc_datetime.max(), freq='H')
    station_aq_data = station_aq_data.set_index('utc_datetime').reindex(date_range_index)
    
    # TODO: Add imputation method here
        
    # Save to interim feather file
    station_aq_data = station_aq_data.drop('station_id', axis=1).reset_index().rename(columns={'index': 'utc_datetime'})
    station_name = aq_station_info['station_id']
    station_aq_data.to_feather(DATA_PATH + f"interim/aq_station_merged/{station_name}_merged_weather.feather")<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup