<jupyter_start><jupyter_text>Widgets queries and visuals<jupyter_code>import pandas as pd
import json
import requests
import numpy as np
import ipywidgets as widgets<jupyter_output><empty_output><jupyter_text>Current widgets; migration to the new table structure.   Global, All crops  WATER STRESS ON FOOD PRODUCTION * Dataset_id: 5be16fea-5b1a-4daf-a9e9-9dc1f6ea6d4e * Widget_id: 7b29a2a5-515a-4e22-846f-03ad3d2c0a13 * Units:      * water stress:     * food production: * Dataset csv or json connector sql:    ```sql    ``` * Widget Api sql:    ```sqlwith t as (select sum(area) as area, {{water_column}} as risk, crop from crops_stats group by {{water_column}}, crop order by risk asc), r as (SELECT sum(value) as value, commodity FROM combined01_prepared where year='{{year}}' and impactparameter='Food Demand' group by commodity ), d as ( select (value*100/(sum(value) over())) as value, commodity from r ) select crop, risk,value, (area*100/sum(area) over(partition by crop)) area_perc from d inner join t on lower(commodity)=crop order by risk asc, crop asc    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Marimmeko<jupyter_code>payload = {'q': "select * from water_risk_indicators where indicator = 'water_stress' and model in ('bau', 'historic') and period='year' and type='absolute' and basinid = 7664 order by year asc"}
r = requests.get('https://wri-01.carto.com/api/v2/sql', params=payload)
print(r)
tableStructure= pd.read_json(json.dumps(r.json()['rows']), orient='records')
tableStructure.head(1)
widgets.HTML(
    value='<iframe src="http://aqueduct-water-food-security.herokuapp.com/embed?state=eyJmaWx0ZXJzIjp7ImNyb3AiOiJhbGwiLCJzY29wZSI6Imdsb2JhbCIsImNvdW50cnkiOiJJTkQiLCJwZXJpb2QiOiJ5ZWFyIiwicGVyaW9kX3ZhbHVlIjoiYmFzZWxpbmUiLCJ5ZWFyIjoiYmFzZWxpbmUiLCJmb29kIjoibm9uZSIsImluZGljYXRvciI6IjRiMDAwZGVkLTVmNGQtNGRiZC04M2M5LTAzZjJkZmNkMzZkYiIsImlycmlnYXRpb24iOlsiaXJyaWdhdGVkIiwicmFpbmZlZCJdLCJ0eXBlIjoiYWJzb2x1dGUifSwiZW1iZWQiOnsiaWQiOiI1YmUxNmZlYS01YjFhLTRkYWYtYTllOS05ZGMxZjZlYTZkNGUifX0=" width="600" height="420" frameborder="0" style="border:0" allowfullscreen></iframe>',
    disabled=False
)<jupyter_output><empty_output><jupyter_text>SUMMARY TABLE, TOTAL CROPS PRODUCTION AREA * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Bar chart  COUNTRY WATER RISK EXPOSURE AND RISK OF HUNGER * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Bar chart  Widget nameDataset connector sql:```sql```Widget Api sql:```sql```Parametrization:``` json"params_config": [{		"key":"water_column",		"required":true  		}],  	"sql_config": [{  		"key": "where",   		"key_params":[{  			"key":"iso",  			"required":false},{   			"key":"crop",  			"required":false},{   			"key":"irrigation",  			"required":false}]                }],```Visualization type: Bar chart  RAINFED VS IRRIGATED * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Bar chart  Global, one crop  CROP'S PRODUCING AREAS EXPOSED TO WATER STRESS. * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Marimmeko  Country, one crops  Country summary table * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Marimmeko  CROPS PRODUCED IN AREAS OF HIGH WATER STRESS * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Marimmeko  GROWING CROPS UNDER WATER STRESS CONDITIONS. * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Marimmeko  Global, All crops  Country SUMMARY * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Marimmeko  CROPS GROWING UNDER HIGH WATER STRESS CONDITIONS IN A COUNTRY * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Marimmeko  PERCENTAGE OF COUNTRY'S POPULATION AT HIGH RISK OF HUNGER. * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sqlWITH RESULT AS (SELECT round(value::numeric, 2) AS value, region, year, iso FROM combined01_prepared WHERE year = 2030 AND impactparameter = 'Share Pop. at risk of hunger' AND scenario = 'SSP2-MIRO' and iso = upper('BRA')) SELECT CASE WHEN year > 2010 THEN 'will be' ELSE 'are' END AS verb, CASE WHEN year > 2010 THEN 'in ' || to_char(YEAR, '9999') ELSE '' END AS YEAR, Case when value < 1 then 'less than 1' else to_char(value, '9999') end as value, region AS country, iso FROM RESULT    ``` * Parametrization:    ``` json    "widgetConfig": {          "data": {            "url": "https://wri-01.carto.com/api/v2/sql?q=WITH RESULT AS (SELECT                 round(value::numeric, 2) AS value, region, year, iso FROM combined01_prepared WHERE year = {{year}} AND impactparameter = 'Share Pop. at risk of hunger' AND scenario = 'SSP2-MIRO' and iso = upper('{{iso}}'))  SELECT CASE WHEN year > 2010 THEN 'will be' ELSE 'are' END AS verb, CASE WHEN year > 2010 THEN 'in ' || to_char(year, '9999') ELSE '' END AS year, CASE WHEN value < 1 THEN 'less than 1' ELSE to_char(value, '9999') END AS value, region AS country, iso FROM RESULT"          },          "type": "text",          "template": "{{value}} of country's population {{verb}} at high risk of hunger {{year}} in {{country}}.",          "sql_config": [],          "sql_params": [],          "params_config": [            {              "key": "year",              "required": true            },            {              "key": "iso",              "required": true            }          ],          "template_config": [            {              "key": "verb"            },            {              "key": "value",              "suffix": "%"            },            {              "key": "country"            },            {              "key": "year"            }          ]        }    ``` * Visualization type: Text  WATER RISK * Dataset_id: * Widget_id: * Dataset connector sql:    ```sql    ``` * Widget Api sql:    ```sql    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: Marimmeko  New widgets with crop_area as (SELECT sum(area) area_crop, basinid, crop, iso, irrigation FROM crops group by basinid, crop, iso, irrigation),crop_stats as (select sum(area_crop) over (partition by  crop, irrigation) as T_crop_ha, area_crop, basinid, crop, irrigation, iso from crop_area),statistics as (select crop_stats.*, (wri_subcatchements.area_km2*100) as T_basin_ha, indicator, label, model, period, period_value, type, value, year, (area_crop) as weights from crop_stats inner join wri_subcatchements on wri_subcatchements.basinid=crop_stats.basinid inner join water_risk_indicators on water_risk_indicators.basinid=crop_stats.basinid where indicator in ('water_stress','seasonal_variability') and model in ('bau')),c as (select (sum(value*weights)/sum(distinct T_crop_ha)) water_risk, crop, year from statistics where indicator = 'water_stress' and type='absolute'and irrigation='rainfed' group by crop, year order by crop asc, year asc),s as (select min(water_risk) over (), max(water_risk) over (), c.* from c),categories as (select unnest(Array['all pulses', 'lentils', 'wheat', 'cowpeas', 'barley', 'rice', 'groundnut', 'beans', 'chickpeas', 'plantain', 'potato', 'sorghum', 'banana', 'pigeonpeas', 'millet', 'soybean', 'sweet potato', 'maize', 'yams', 'all cereals', 'cassava']) as crop, unnest(Array[2,2,1,2,1,1,2,2,2,4,3,1,4,2,1,5,4,1,3,1,4]) as crop_type)f as ((select crop, year, (100*(water_risk-min)/(max-min)) as water_risk from s order by crop asc, year asc)union all(select  'total crops avg' as crop, year, avg(100*(water_risk-min)/(max-min)) as water_risk from s group by year,1))  Global, All crops  water stress change over time for irrigated crops by crop type (cereals, legumes + pulses, roots + tubers, fruits, oilseed, and add a line for all crops average) * Dataset_id:  * Widget_id:  * Units:      *      *  * Dataset csv or json connector sql:    ```sql    asd        ``` * Widget Api sql:    ```sqlwith t as (select sum(area) as area, {{water_column}} as risk, crop from crops_stats group by {{water_column}}, crop order by risk asc), r as (SELECT sum(value) as value, commodity FROM combined01_prepared where year='{{year}}' and impactparameter='Food Demand' group by commodity ), d as ( select (value*100/(sum(value) over())) as value, commodity from r ) select crop, risk,value, (area*100/sum(area) over(partition by crop)) area_perc from d inner join t on lower(commodity)=crop order by risk asc, crop asc    ``` * Parametrization:    ``` json    "params_config": [{            "key":"water_column",            "required":true            }],        "sql_config": [{            "key": "where",             "key_params":[{                "key":"iso",                "required":false},{                 "key":"crop",                "required":false},{                 "key":"irrigation",                "required":false}]                    }],    ``` * Visualization type: multi-line-chart<jupyter_code>t = 3 * np.random.rand((3*6))
t
t =np.random.rand((12))*1000 +200
t<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15024665257223396, 'fraction_numerical': 0.013742071881606765, 'mean_word_length': 2.3485134497404436, 'pattern_counts': {'":': 174, '<': 17, '<?xml version=': 0, '>': 19, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '583657', 'license': 'MIT', 'n_tokens_mistral': 4444, 'n_tokens_neox': 4156, 'n_words': 1069, 'path': 'Aqueduct/lab/AQ1.-Widgets_queries.ipynb', 'repo_name': 'weiqi-tori/notebooks', 'size': '14190'}