{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Introduction\n\nThis dataset has been generated by combining two publically available datasets on weather data and city coordinates and demographics. In a nutshell, it uses a city's coordinates to identify the nearest weather station and then extracts the historical weather data of this station. \n\nA dataset on weather patterns is generated here by using the following open/public datasets:\n\ni)**Global Historical Climatology Network (GHCN) (https://www.ncdc.noaa.gov/ghcn-daily-description):**\n\"GHCN (Global Historical Climatology Network)-Daily is an integrated database of daily climate summaries from land surface stations across the globe. Like its monthly counterpart (GHCN-Monthly) , GHCN-Daily is comprised of daily climate records from numerous sources that have been integrated and subjected to a common suite of quality assurance reviews.\n\nGHCN-Daily contains records from over 100,000 stations in 180 countries and territories. NCEI provides numerous daily variables, including maximum and minimum temperature, total daily precipitation, snowfall, and snow depth; however, about one half of the stations report precipitation only. Both the record length and period of record vary by station and cover intervals ranging from less than a year to more than 175 years.\n\nii) **World Cities Database (https://simplemaps.com/data/world-cities):** A database of 26000 cities containing city names, geographical co-ordinates, population, density and country names. The database is available under Creative Commons Attribution 4.0 license. \n\n\n## 1. Motivation: \n\ni) Climate change is known to induce high global temperatures, changes in precipitation patterns, shortened frost/winter season, extreme weather events, heat waves, droughts etc.\n\nii) **Local data provides perspective:** From NASAs Global Climate Change: \"...the extent of climate change effects on individual regions will vary over time and with the ability of different societal and environmental systems to mitigate or adapt to change.\" Hence, analysing localized weather patterns, population and changes over and around the cities participating in CDP surveys can provide perspective on adaptation and mitigation measures chosen by a city, infrastructure priorities, social equity programs and policies, gender biases, effect on health of the population etc. National level population and weather data may not account for local variations especially in large diverse countries like USA, China, India, Brazil etc. Hence, localized data is prefereable to national level data.\n\niii) **High Quality Measurable data:** Weather and demographic data are primary, unbiased, recorded, historical data reflecting the on-ground situation. Local weather data like temperature, precipitation, snowfall etc. are regularly measured with quality assurances in place. Similarly, demographic data like population and density have been regularly measured for decades. \n\niv) **Unbiased:** Other data types like projections, self reported data, extrapolations suffer from bias/assumptions of the reporter or analyst/estimator. Indices and basket/combination indicators suffer from biases in weightage given to different parameters. We need to be cautious while using projections, indices etc as they can inject existing biases in current analysis. Organizations and administrations can and are known to 'game' such indicators. Hence, it is preferable to use primary, unbiased and measurable data like weather and population demographics to build KPIs\n\n## 2. Advantages: \n\nThe dataset generated and the code in this notebook has applications beyond the current competition. \n\ni) **Ease of Access:** The dataset makes climate data on thousands of cities instantly accessible through year-wise CSV files. Additionally , a 'key file' which links each city to the nearest weather station makes it easy  to retrieve weather data with only the city and country name. It is presented in a human readable format. It can be easily manipulated using Python, R or even Microsoft Excel/ Google Sheets.\n\nii) **Scope and Usage** The dataset covers thousands of cities beyond the CDP competition dataset. Hence, its potential uses extend to individuals and organizations engaged in sustainability, climate change, metereology etc. Although the notebook generates data for only the last 5 years, it can be modified further to obtain data for any other years from the NOAA database. \n\niii) **Size**  NOAA GHCN annual files sizes range from 1-2 GB. The extracted dataset files sizes are in the range of 150-200 MB i.e. almost 10-20% of the original files\n\n\n## 3. Method\n\nI) Obtain files from NOAA and Simplemaps\n\nII) Use the co-ordinates of each City and weather station to find the weather station closest to each city\n\nIII) Extract the data for these weather stations and shape it in a human readable format.\n\n\n## 4. Limitations:\n\nWeather data on certain cities is missing or inaccurate because of the following reasons:\n\ni) Some of the weather stations identified in the notebook are not polled by the NOAA in recent years. Hence, weather data on certain cities is missing.\n\nii) In a few cases, the identified weather stations are very far from the respective cities."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"collapsed":true},"cell_type":"code","source":"from tqdm import tqdm\nimport pandas as pd\nimport plotly.express as px\nimport csv\nimport haversine\n\ntqdm.pandas()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# I. Get files from simplemaps and NOAA"},{"metadata":{},"cell_type":"markdown","source":"## 1. List of all NOAA GHCN stations\nThis file is in ASCII format so we will convert it into a DataFrame and also save it for future use."},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"!wget ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/ghcnd-stations.txt","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"colspecs = [(0,11), (12,20), (21,30), (31,37), (38,40), (41,71), (72,75), (76,79), (80,85)]\nstations = pd.read_fwf('ghcnd-stations.txt', colspecs=colspecs, header=None, index_col=None)\nstations.columns = [\"ID\", \"LATITUDE\", \"LONGITUDE\", \"ELEVATION\", \"STATE\", \"NAME\", \"GSNFLAG\", \"HCNFLAG\", \"WMOID\"]\nstations.reset_index(inplace=True)\nstations[\"COORDS\"] = list(zip(stations.LATITUDE, stations.LONGITUDE)) #Savinf coordinates as a tuple will be useful later\nstations.to_csv('ghcnd-stations.csv', quoting=csv.QUOTE_NONNUMERIC)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2. GHCN Daily Weather data files\nThese files are available as zipped archives for each year. The zipped archives contain a CSV file without any header. "},{"metadata":{"trusted":true},"cell_type":"code","source":"!wget ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/2020.csv.gz\n!wget ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/2019.csv.gz\n!wget ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/2018.csv.gz","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"!wget ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/2017.csv.gz\n!wget ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/2016.csv.gz\n!wget ftp://ftp.ncdc.noaa.gov/pub/data/ghcn/daily/by_year/2015.csv.gz","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"!gunzip 2020.csv.gz\n!gunzip 2019.csv.gz\n!gunzip 2018.csv.gz","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"!gunzip 2017.csv.gz\n!gunzip 2016.csv.gz\n!gunzip 2015.csv.gz","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 3. World Cities Data\nData on 26000 cities inculding geo co-ordinates, country, and population. This data is available from https://simplemaps.com/data/world-cities"},{"metadata":{"trusted":true},"cell_type":"code","source":"!wget https://simplemaps.com/static/data/world-cities/basic/simplemaps_worldcities_basicv1.71.zip","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"!unzip ./simplemaps_worldcities_basicv1.71.zip","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# II. Finding the closest weather station for each city\n\n1. We will use one helper function to calculate the Haversine distance between two points. We will use this function to calculate distances between each city and all other stations in the NOAA GHCN Station list. The function will return the 'ID' of the closest station and the distance between the city and station.\n\n2. We will limit our analysis to the top 2500 cities by population because of resource constraints of Kaggle Notebooks. For sure, the code below can be used for analysis of a larger number of cities as also custom lists of cities."},{"metadata":{"trusted":true},"cell_type":"code","source":"from haversine import haversine\n\ndef NearestStations(lat,long):\n    yo = stations\n    coords = (lat,long)\n    yo[\"Dist\"]= yo.COORDS.apply(lambda x: haversine(coords,x))\n    #yo.reset_index(inplace=True)\n    closest = yo.loc[yo[\"Dist\"].idxmin()]\n    return(str(closest.ID),float(closest.Dist))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"world_cities = pd.read_csv(\"worldcities.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"world_top2500 = world_cities.nlargest(2500,\"population\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"world_top2500['Closest Station ID'], world_top2500['Closest Station Distance'] = \\\n                            zip(*world_top2500.progress_apply(lambda x: NearestStations(float(x['lat']),float(x['lng'])), axis=1))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"world_top2500.to_csv(\"World Cities Nearest Stations.csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# III. Extracting weather data for stations\n\n1. We will first extract the list of unique weather station from our list of top 2500 cities and their associated weather stations.\n\n2. Next we will iterate over each years weather data. \n\n3. In each iteration we will extract the data of only those stations appearing in our list. \n\n4. We will also transform the data to a more readable format using the pivot() function of pandas dataframe\n\n5. The final transformed data will be saved on file and yields our dataset."},{"metadata":{"trusted":true},"cell_type":"code","source":"stations_list = list(world_top2500[\"Closest Station ID\"].unique())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"years = ['2015','2016','2017','2018','2019','2020']\nfor y in years:\n    print(y)\n    weather_temp = pd.read_csv(y+\".csv\")\n    weather_temp.columns = ['ID','Date','Element','Value','A','B','C','D']\n    weather_temp2 = weather_temp[weather_temp.ID.isin(stations_list)]\n    wp = weather_temp2.pivot(index = [\"Date\",\"ID\"], columns = \"Element\", values=\"Value\")\n    wp.reset_index(inplace=True)\n    wp.to_csv(\"Weather top 2500 cities \"+y+\".csv\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# IV. Analysis\n\n## 1. Analysis of distance between cities and closest weather station\n\ni) About 50% of the cities have a weather station within a 7 kilometer range\nii) About 60% of the cities have a station within a 15 kilometer range\niii) Some cities don't seem to have any nearby stations on the NOAA list"},{"metadata":{"trusted":true},"cell_type":"code","source":"world_top2500[\"Closest Station Distance\"].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"100*world_top2500[world_top2500[\"Closest Station Distance\"]<15].shape[0]/world_top2500.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"hist = px.histogram(world_top2500, \"Closest Station Distance\")\nhist.show()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## 2. How many weather stations from our list were polled by NOAA for weather data?\n\n1. In each year between 2015-2020, NOAA GHCN collected data from about 53-57% of the stations in our list"},{"metadata":{"trusted":true},"cell_type":"code","source":"unique_stations_lists = []\nunique_stations_nos = []\n\nfor y in years:\n    w = pd.read_csv(\"Weather top 2500 cities \"+y+\".csv\")\n    yo = list(w['ID'].unique())\n    yo_len = len(yo)\n    unique_stations_lists.append(yo)\n    unique_stations_nos.append(yo_len)\n\nunique_stations_all = sorted(set(sum(unique_stations_lists, []))) #ID of every station that was polled at least once \n\ntotal_unique_stations_nos = len(list(world_top2500[\"Closest Station ID\"].unique()))\nunique_stations_percentages = [yo * 100/total_unique_stations_nos for yo in unique_stations_nos]\n\nPercentage_Stations_Polled = pd.DataFrame(list(zip(years, unique_stations_percentages)), columns=[\"Year\", \"Percentage of Stations Polled\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Percentage_Stations_Polled.head(6)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Cities_Not_Polled = world_top2500[-(world_top2500[\"Closest Station ID\"].isin(unique_stations_all))]\nCities_Not_Polled.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Cities_Not_Polled[\"country\"].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Cities_Polled = world_top2500[-(world_top2500[\"Closest Station ID\"].isin(unique_stations_all))]\nCities_Polled.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Cities_Not_Polled[\"country\"].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":""}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}