{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport warnings\nwarnings.simplefilter('ignore')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from kaggle_secrets import UserSecretsClient\nfrom google.oauth2.credentials import Credentials\nimport ee","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#ee.Authenticate()\n#!cat ~/.config/earthengine/credentials","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"citid19 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Disclosing/2019_Cities_Disclosing_to_CDP.csv')\ncitires19 = pd.read_csv('../input/cdp-unlocking-climate-solutions/Cities/Cities Responses/2019_Full_Cities_Dataset.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"area = citires19[(citires19['Question Number']=='0.6') & (citires19['Column Number']==1)][['Account Number','Response Answer']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#assume city is a square centered on given (lat, long) with area as declared in the response, and get coordinates of corners\ncitid19 = citid19[['Account Number','City Location']]\ncitid19 = pd.merge(citid19, area, on='Account Number')\ncitid19 = citid19[~(citid19['City Location'].isna()) & ~(citid19['Response Answer'].isna())]\ncitid19 = citid19.rename(columns={'Response Answer':'Area'})\ncitid19['Longc'] = citid19['City Location'].apply(lambda x: float(x.split(' ')[1].split('(')[1]))\ncitid19['Latc'] = citid19['City Location'].apply(lambda x: float(x.split(' ')[2].split(')')[0]))\ncitid19['d']=np.sqrt(citid19['Area'].astype(float)/2)\ncitid19['d'] = citid19['d'].apply(lambda x: 50 if x>50 else x)\ncitid19['lats']= citid19['Latc'] -(1 / 110.54)*citid19['d']\ncitid19['latn']= citid19['Latc'] +(1 / 110.54)*citid19['d']\ncitid19['longe']= citid19['Longc'] -(1 / (111.320 * np.cos(citid19['Latc']))) * citid19['d']\ncitid19['longw']= citid19['Longc'] +(1 / (111.320 * np.cos(citid19['Latc']))) * citid19['d']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"citid19.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"mysecret = \"mysecret\" \nrefresh_token = UserSecretsClient().get_secret(mysecret)\ncredentials = Credentials(\n        None,\n        refresh_token=refresh_token,\n        token_uri=ee.oauth.TOKEN_URI,\n        client_id=ee.oauth.CLIENT_ID,\n        client_secret=ee.oauth.CLIENT_SECRET,\n        scopes=ee.oauth.SCOPES)\nee.Initialize(credentials=credentials)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"column = 'tropospheric_NO2_column_number_density'\ndataset = \"COPERNICUS/S5P/OFFL/L3_NO2\"\nbegin_date = '2019-01-01'; end_date = '2019-12-31'\ns5p = ee.ImageCollection(dataset).filterDate(begin_date, end_date)\nime = s5p.select(column).reduce(ee.Reducer.mean())\nime.getInfo()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nconcs = []\nfor i in range(len(citid19)):\n    row = citid19.iloc[i]\n    lats, latn, longe, longw = row['lats'], row['latn'], row['longe'], row['longw']\n    aoi = ee.Geometry.Polygon([[[longw, lats],[longw, latn],[longe, latn],[longe, lats]]], None, False)\n    no2 = ime.sampleRectangle(aoi)\n    arr = no2.getInfo()\n    concs.append(np.mean(arr['properties']['tropospheric_NO2_column_number_density_mean']))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"citid19['no2']=concs","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"citid19.to_csv('citid19.csv', index=False)","execution_count":null,"outputs":[]}],"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}