{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Understanding Data\n", "\n", "We have three sets of data: \n", "1. Citizen Data\n", "2. Official Meteorological Data From Weather Stations\n", "3. Topographical Data for Sofia \n", "4. Data available via API at 5 minute intervals" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## I use pandas frame work to inspect the citizen data first \n", "We need some setup to handle all the file paths easily" ] }, { "cell_type": "code", "execution_count": 52, "metadata": {}, "outputs": [], "source": [ "##\n", "# Author: Neelesh Kamkolkar \n", "# Constants for file management \n", "# Assumes the notebook is running from same directory that \n", "# the airsofia.zip file is extracted in. \n", "#\n", "## \n", "\n", "import pandas as pd\n", "import numpy as np\n", "import pygeohash as pgh\n", "import timeit\n", "from autovizwidget.widget.utils import display_dataframe\n", "\n", "debug=1\n", "SOFIA_FILES_HOME = '.'\n", "EEA_FILES_PATH = SOFIA_FILES_HOME + '/EEA Data/'\n", "EEA_DATA_METADATA_FILE = EEA_FILES_PATH + 'metadata.xlsx'\n", "\n", "\n", "CITIZEN_AIR_TUBE_FILES_PATH = SOFIA_FILES_HOME + '/Air Tube/'\n", "\n", "\n", "SOFIA_TOPO_FILE = SOFIA_FILES_HOME + '/TOPO-DATA/sofia_topo.csv'\n", "TOPO_FILTER_ABOVE_ELEV = 800\n", "\n", "SOFIA_WEATHER_INFO_FILE = SOFIA_FILES_HOME + '/METEO-data/lbsf_20120101-20180917_IP.csv'\n", "\n", "\n", "EEA_FILES = [\n", " \n", " [\n", " 'BG_5_9421_2013_timeseries.csv', \n", " 'BG_5_9421_2014_timeseries.csv',\n", " 'BG_5_9421_2015_timeseries.csv',\n", " 'BG_5_9421_2016_timeseries.csv',\n", " 'BG_5_9421_2017_timeseries.csv',\n", " 'BG_5_9421_2018_timeseries.csv'\n", " ],\n", "\n", " [\n", " 'BG_5_9484_2013_timeseries.csv',\n", " 'BG_5_9484_2014_timeseries.csv',\n", " 'BG_5_9484_2015_timeseries.csv'\n", " ],\n", "\n", " [\n", " 'BG_5_9572_2013_timeseries.csv',\n", " 'BG_5_9572_2014_timeseries.csv',\n", " 'BG_5_9572_2015_timeseries.csv',\n", " 'BG_5_9572_2016_timeseries.csv',\n", " 'BG_5_9572_2017_timeseries.csv',\n", " 'BG_5_9572_2018_timeseries.csv'\n", " ], \n", "\n", " [\n", " 'BG_5_9616_2013_timeseries.csv',\n", " 'BG_5_9616_2014_timeseries.csv',\n", " 'BG_5_9616_2015_timeseries.csv',\n", " 'BG_5_9616_2016_timeseries.csv',\n", " 'BG_5_9616_2017_timeseries.csv',\n", " 'BG_5_9616_2018_timeseries.csv'\n", "\n", " ],\n", "\n", " [\n", " 'BG_5_9642_2013_timeseries.csv',\n", " 'BG_5_9642_2014_timeseries.csv', \n", " 'BG_5_9642_2015_timeseries.csv',\n", " 'BG_5_9642_2016_timeseries.csv',\n", " 'BG_5_9642_2017_timeseries.csv',\n", " 'BG_5_9642_2018_timeseries.csv',\n", "\n", " ], \n", "\n", " [\n", " 'BG_5_60881_2018_timeseries.csv'\n", " ]\n", " ]\n", "\n", "\n", "AIR_TUBE_FILES = [\n", " ['data_bg_2017.csv'],\n", " ['data_bg_2018.csv'],\n", " #['sample_data_bg_2018.csv']\n", " ]\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Data Mangling Citizen Data\n", "The functions below help load a citizen data file from the local file system.\n", "Provide some basic summary functions for the data inspections and a function to wrangle citize data \n", "into a master data frame. The wrangling ensure there are no NaN's in the geohash for citizen data and deals with \n", "specific data quality issues in one of the files. \n", "Finally it converts the geohash codes to latitude and longitude and adds them to the master data frame while grouping the master data frame by geohash so we can find stations that are consistenly reporting data." ] }, { "cell_type": "code", "execution_count": 60, "metadata": {}, "outputs": [], "source": [ "##\n", "# Function: Load citizen data from file name \n", "# inputs : sample csv file with citizen gathered data\n", "# outputs : data frame with citizen data from input file \n", "#\n", "##\n", "\n", "def load_citizen_data(aFileName):\n", " citizen_df = pd.read_csv(aFileName, header=0, encoding='utf-8')\n", " return citizen_df \n", "\n", "\n", "##\n", "# Function: summarize_citizen_data : provide high level summary of the citizen data\n", "# inputs : none \n", "# outputs : console print of summary (TODO: Perhaps write this as a csv file?)\n", "#\n", "##\n", "def summarize_citizen_data():\n", " \n", " for i in range(len(AIR_TUBE_FILES)): \n", " fName = CITIZEN_AIR_TUBE_FILES_PATH+str(AIR_TUBE_FILES[i][0])\n", " print(\"*********************************************\")\n", " print(\"File Name: \", fName)\n", " c_df = load_citizen_data(fName)\n", " c_df = c_df.sort_values('time')\n", " \n", " print(\"Data Shape: \", c_df.shape)\n", " print(\"Column counts with null/nans\", c_df.isnull().sum())\n", " print(\"Total geo hashses\", len(c_df['geohash']))\n", " print(\"Unique geo hashses\", len(c_df['geohash'].unique()))\n", " print(\"Total time stamps\", len(c_df['time']))\n", " print(\"Unique time stamps\", len(c_df['time'].unique()))\n", " print(\"First time stamp: \",c_df['time'][0])\n", " print(\"Last time stamp: \",c_df['time'][len(c_df['time'])-1])\n", " \n", " print(\"------------------Converting GeoHashCodes----------------\\r\\n\")\n", "\n", " #This just converts the geohash codes to lat long \n", " geo_df = geohash_to_latlong(c_df)\n", "\n", " print(\"Post Wrangling Data Shape: \", geo_df.shape)\n", " print(\"Column counts with null/nans\", geo_df.isnull().sum())\n", " print(\"Total geo hashses\", len(geo_df['geohash']))\n", " print(\"Unique geo hashses\", len(geo_df['geohash'].unique()))\n", " print(\"Total time stamps\", len(geo_df['time']))\n", " print(\"Unique time stamps\", len(geo_df['time'].unique()))\n", " print(\"First time stamps: \",geo_df['time'][0])\n", " print(\"Last time stamp: \",c_df['time'][len(c_df['time'])-1])\n", "\n", "\n", "\n", "\n", "## \n", "# Function: wrangle_citizen_data - iterate through all citizen data files and create a master data frame that is clean\n", "# inputs: None \n", "# outputs: wrangled citizen_data frame igrouped by locations\n", "# \n", "##\n", "\n", "def wrangle_citizen_data():\n", " \n", " c_df_list = []\n", " #use a loop to read the file list so it's easier to add more citizen data in future \n", " #to add new citizen data files, drop in correct folder and add file name to the list. \n", " for i in range(len(AIR_TUBE_FILES)): \n", " \n", " fName = CITIZEN_AIR_TUBE_FILES_PATH+str(AIR_TUBE_FILES[i][0])\n", " print(\"Loading File Name: \", fName)\n", " c_df = load_citizen_data(fName)\n", " print(\"wrangle_citizen_data: c_df.shape: {} \".format(c_df.shape))\n", " c_df_list.append(c_df)\n", " \n", " \n", " #pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,\n", " # keys=None, levels=None, names=None, verify_integrity=False,\n", " # copy=True)\n", " \n", " df_all = pd.concat([c_df_list[0], c_df_list[1]])\n", "\n", " \n", " print(\"wrangle_citizen_data: converting master data frame geo hash to lat/long {} \".format(df_all.shape))\n", " lat_long_df = geohash_to_latlong(df_all)\n", " \n", " print(\"wrangle_citizen_data: lat_long_df.shape: {}\".format(lat_long_df.shape))\n", " \n", " #DataFrame.groupby(by=None, axis=0, level=None, as_index=True, sort=True, \n", " # group_keys=True, squeeze=False, observed=False, **kwargs)\n", " grouped_tab = lat_long_df.groupby(by='geohash')\n", " \n", " print(\"=============================================\")\n", " print(\"grouped_tab table: {} \".format(grouped_tab.head(10)))\n", " print(\"=============================================\")\n", " #print(\"grouped_tab table: {} \".format(grouped_tab.tail(10)))\n", " \n", " \n", " return grouped_tab\n", "\n", "\n", "\n", "##\n", "# Function: geohash_to_latlong : converts geohash codes to lat / long \n", "# inputs : data frame that has a 'geohash' column with geohash codes. Will drop any NaN geohahes.\n", "# outputs : an expanded data frame with 2 new columns 'Latitude' and 'Longitude' which has new lat/long\n", "# also any NaN's are dropped so the size of the data frame is reduced by number of NaN's rows.\n", "#\n", "##\n", "def geohash_to_latlong(aCitizenDataFrame):\n", " \n", " df = aCitizenDataFrame\n", " \n", " #drop na if the geohash column has any nan's \n", " df = df.dropna(subset=['geohash'], how='any')\n", " \n", " #print(\"df.shape: {}\".format(df.shape))\n", " #this specific geohash does not decode in panda's using pygeohash. it also doesn't decode in external tools \n", " #this is a data quality issue, drop this unparsable geohash \n", " df = df.drop(df[df.geohash == 'm-2105171'].index)\n", " #print(\"df.shape: {} \".format(df.shape))\n", "\n", " #print(\"df: \", df.head(5))\n", " # apply a lamda funciton that decodes the lat/long from the geohash 'x' from the 'geohash' column.\n", " out = df['geohash'].apply(lambda x: pgh.decode(x))\n", " \n", " #unpack the returned tuple into two different columns. \n", " df['Latitude'], df['Longitude'] = zip(*out)\n", " \n", " print(\"geohash_to_latlong: df.shape\".format(df.shape))\n", " print(\"geohash_to_latlong: df: \", df.head(5)) \n", " print(\"&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\") \n", " \n", " return df\n", "\n", "\n", "\n" ] }, { "cell_type": "code", "execution_count": 61, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "*********************************************\n", "File Name: ./Air Tube/data_bg_2017.csv\n", "Data Shape: (651492, 7)\n", "Column counts with null/nans time 0\n", "geohash 0\n", "P1 0\n", "P2 0\n", "temperature 0\n", "humidity 0\n", "pressure 0\n", "dtype: int64\n", "Total geo hashses 651492\n", "Unique geo hashses 383\n", "Total time stamps 651492\n", "Unique time stamps 2774\n", "First time stamp: 2017-09-06T20:00:00Z\n", "Last time stamp: 2017-12-31T23:00:00Z\n", "------------------Converting GeoHashCodes----------------\n", "\n", "geohash_to_latlong: df.shape\n", "geohash_to_latlong: df: time geohash P1 P2 temperature humidity \\\n", "0 2017-09-06T20:00:00Z sx8d5r7wmxr 9 8 14 55 \n", "97 2017-09-06T20:00:00Z sx87p012fmj 12 10 11 78 \n", "96 2017-09-06T20:00:00Z sx8dkx7wz36 11 10 19 41 \n", "95 2017-09-06T20:00:00Z sx8dgvgen25 41 18 14 60 \n", "94 2017-09-06T20:00:00Z sx8dd6mu425 4 3 17 50 \n", "\n", " pressure Latitude Longitude \n", "0 0 42.580 23.351 \n", "97 94766 42.715 23.161 \n", "96 95008 42.624 23.406 \n", "95 95551 42.703 23.373 \n", "94 93905 42.640 23.310 \n", "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n", "Post Wrangling Data Shape: (651491, 9)\n", "Column counts with null/nans time 0\n", "geohash 0\n", "P1 0\n", "P2 0\n", "temperature 0\n", "humidity 0\n", "pressure 0\n", "Latitude 0\n", "Longitude 0\n", "dtype: int64\n", "Total geo hashses 651491\n", "Unique geo hashses 382\n", "Total time stamps 651491\n", "Unique time stamps 2774\n", "First time stamps: 2017-09-06T20:00:00Z\n", "Last time stamp: 2017-12-31T23:00:00Z\n", "*********************************************\n", "File Name: ./Air Tube/data_bg_2018.csv\n", "Data Shape: (2958654, 7)\n", "Column counts with null/nans time 0\n", "geohash 4\n", "P1 0\n", "P2 0\n", "temperature 0\n", "humidity 0\n", "pressure 0\n", "dtype: int64\n", "Total geo hashses 2958654\n", "Unique geo hashses 1254\n", "Total time stamps 2958654\n", "Unique time stamps 5461\n", "First time stamp: 2018-01-01T00:00:00Z\n", "Last time stamp: 2018-08-16T12:00:00Z\n", "------------------Converting GeoHashCodes----------------\n", "\n", "geohash_to_latlong: df.shape\n", "geohash_to_latlong: df: time geohash P1 P2 temperature humidity \\\n", "0 2018-01-01T00:00:00Z sx3wvzu7f6h 55 34 4 64 \n", "232 2018-01-01T00:00:00Z sx8dccvfcep 626 349 2 73 \n", "231 2018-01-01T00:00:00Z sx3xsjq7nnf 171 71 3 76 \n", "230 2018-01-01T00:00:00Z sx8dq538g3f 29 14 1 68 \n", "229 2018-01-01T00:00:00Z sx8f9p23293 146 55 0 78 \n", "\n", " pressure Latitude Longitude \n", "0 99161 42.011 24.868 \n", "232 94890 42.681 23.288 \n", "231 99795 42.129 24.794 \n", "230 93485 42.601 23.469 \n", "229 95603 42.667 23.599 \n", "&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&\n", "Post Wrangling Data Shape: (2958650, 9)\n", "Column counts with null/nans time 0\n", "geohash 0\n", "P1 0\n", "P2 0\n", "temperature 0\n", "humidity 0\n", "pressure 0\n", "Latitude 0\n", "Longitude 0\n", "dtype: int64\n", "Total geo hashses 2958650\n", "Unique geo hashses 1253\n", "Total time stamps 2958650\n", "Unique time stamps 5461\n", "First time stamps: 2018-01-01T00:00:00Z\n", "Last time stamp: 2018-08-16T12:00:00Z\n" ] } ], "source": [ "##\n", "# Run the code against the data set to create a wrangled_citizen_data.csv file. \n", "#\n", "##\n", "\n", "summarize_citizen_data()\n", "#clean_data = wrangle_citizen_data() \n", "#clean_data.to_csv('./wrangled_citizen_data.csv')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.6" } }, "nbformat": 4, "nbformat_minor": 2 }