data/notebooks/Lab/QA_h3_wr_data.ipynb
{
"cells": [
{
"cell_type": "markdown",
"id": "f7d18068",
"metadata": {},
"source": [
"## QA h3 calculations on Water Risk and Impact.\n",
"\n",
"This notebooks contains the QA of the h3 calculations performed on the ddbb and stimates the error we may be having from those.\n",
"\n",
"1. Crop production:\n",
"\n",
" 1.1 Transform crop production to h3 \n",
" 1.2 Get the crop production in h3 from the ddbb \n",
" 1.3 Compute differences \n",
" \n",
" \n",
"2. Risk map:\n",
"\n",
" 2.1 Transform rik map for location x to h3 \n",
" 2.2 Get the calculation of risk map in h3 from the database \n",
" 2.3 Compute differences \n",
" \n",
" \n",
"3. Impact map:\n",
"\n",
" 3.1 Search for a sourcing record for purchased of cotton in india and get the purchased volume \n",
" 3.2 Calculatethe average of water risk in india for cotton \n",
" 3.3 Multiply that average value times the purchased volume \n",
" 3.4 Compare that value with the associated indicator record value for water risk. \n",
" \n",
" \n",
" "
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "2d6ad990",
"metadata": {},
"outputs": [],
"source": [
"# import libraries\n",
"from psycopg2.pool import ThreadedConnectionPool"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "9f23a1ab",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"['API_SERVICE_PORT',\n",
" 'API_POSTGRES_HOST',\n",
" 'API_POSTGRES_PORT',\n",
" 'API_POSTGRES_USERNAME',\n",
" 'API_POSTGRES_PASSWORD',\n",
" 'API_POSTGRES_DATABASE',\n",
" 'CLIENT_SERVICE_PORT']"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"## env file for gcs upload\n",
"env_path = \".env\"\n",
"with open(env_path) as f:\n",
" env = {}\n",
" for line in f:\n",
" env_key, _val = line.split(\"=\", 1)\n",
" env_value = _val.split(\"\\n\")[0]\n",
" env[env_key] = env_value\n",
" \n",
"list(env.keys())"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "4d1d62b8",
"metadata": {},
"outputs": [],
"source": [
"postgres_thread_pool = ThreadedConnectionPool(1, 50,\n",
" host=env['API_POSTGRES_HOST'],\n",
" port=env['API_POSTGRES_PORT'],\n",
" user=env['API_POSTGRES_USERNAME'],\n",
" password=env['API_POSTGRES_PASSWORD']\n",
" )"
]
},
{
"cell_type": "markdown",
"id": "6dcbdb38",
"metadata": {},
"source": [
"## Crop production"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "1ecabc97",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'select prod.h3index, prod.\"spam2010V2R0GlobalPCottA\"\\n from h3_grid_spam2010v2r0_global_prod prod \\n where prod.h3index in (\\'86c203047ffffff\\', \\'86c20304fffffff\\')'"
]
},
"execution_count": 10,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"material = \"spam2010V2R0GlobalPCottA\"\n",
"sql = f\"\"\"select prod.h3index, prod.\"{material}\"\n",
" from h3_grid_spam2010v2r0_global_prod prod \n",
" where prod.h3index in ('86c203047ffffff', '86c20304fffffff')\"\"\"\n",
"sql"
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "bfcdede4",
"metadata": {},
"outputs": [],
"source": [
"# connect to the ddbb\n",
"conn = postgres_thread_pool.getconn()\n",
"cursor = conn.cursor()\n",
"\n",
"\n",
"## NOTE: The same logic for the and indicators, materials and admin regions would be applied to the supplier. \n",
"# As all the data is null, I'm not filtering by anything in this case\n",
"cursor.execute(sql)\n",
"\n",
"response = cursor.fetchall()"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "9b04126a",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"[('86c20304fffffff', 0.0), ('86c203047ffffff', 0.0)]"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"response"
]
},
{
"cell_type": "markdown",
"id": "4cfc9ee9",
"metadata": {},
"source": [
"## Risk map:"
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "ffffe657",
"metadata": {},
"outputs": [],
"source": [
"sql = \"\"\"select mprod.h3index, (windicator.\"wfBltotMmyr\" * 0.001)/sum(mprod.\"spam2010V2R0GlobalPCottA\") over() wr_m3_t \n",
" from h3_grid_spam2010v2r0_global_prod mprod\n",
" inner join h3_grid_wf_global wIndicator on wIndicator.h3index = mprod.h3index \n",
" where windicator.\"wfBltotMmyr\" is not null\n",
" and mprod.\"spam2010V2R0GlobalPCottA\" is not null\"\"\""
]
},
{
"cell_type": "markdown",
"id": "4090fb5f",
"metadata": {},
"source": [
"## Impact map:"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "a8cb8945",
"metadata": {},
"outputs": [],
"source": [
"sql = \"\"\"select sr.tonnage, sr.\"year\", sl.\"locationCountryInput\", m.\"name\",i2.\"shortName\", ir.value from sourcing_records sr \n",
"left join sourcing_location sl on sl.id =sr.\"sourcingLocationId\" \n",
"left join material m on m.id =sl.\"materialId\" \n",
"right join indicator_record ir on ir.\"sourcingRecordId\" = sr.id \n",
"left join \"indicator\" i2 on i2.id =ir.\"indicatorId\" \n",
"where sl.\"locationCountryInput\"='India'\n",
"and m.\"name\" = 'Cotton'\n",
"and sr.\"year\"=2010\n",
"and i2.\"shortName\" = 'Unsustainable water use'\"\"\""
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "0c8f4e37",
"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.8.10"
}
},
"nbformat": 4,
"nbformat_minor": 5
}