<jupyter_start><jupyter_text>Read data **Baseline Water Stress**<jupyter_code>query =(" SELECT s.aq30_id, s.gid_1, bws_raw, bws_score, bws_cat,  bws_label, r.the_geom " 
        " FROM water_risk_indicators_annual_all s "
        " LEFT JOIN y2018m12d06_rh_master_shape_v01 r on s.aq30_id=r.aq30_id "
        " WHERE s.pfaf_id != -9999 and s.gid_1 != '-9999'and s.gid_1 like any (array['ESP%', 'PRT%'])")

account = 'wri-rw'
urlCarto = f"https://{account}.carto.com/api/v2/sql"

sql = {"q": query}
r = requests.get(urlCarto, params=sql)

data = r.json()

bws = gpd.GeoDataFrame(data.get("rows"))
# Change geometry from WKB to WKT format
bws['geometry'] = bws.apply(lambda x: shapely.wkb.loads(x['the_geom'],hex=True), axis=1 )
bws.drop(columns='the_geom', inplace=True)
bws.crs = {'init': 'epsg:4326'}
bws = bws.to_crs({'init': 'epsg:4326'})
bws.head(1)
colors = ['#FFFF99', '#FFE600', '#FF9900', '#FF1900', '#990000']
cat = np.array(bws['bws_cat'].unique())
cat = np.sort(cat[~np.isnan(cat)])

fig, ax = plt.subplots(figsize=[5,5])
ax.set_aspect('equal')

for i in range(len(cat)):
    bws[(bws['gid_1'] == 'ESP.2_1') & (bws['bws_cat'] == cat[i])].plot(ax=ax, color=colors[i], edgecolor=colors[i])<jupyter_output><empty_output><jupyter_text>**[MapSPAM](http://mapspam.info/data/) 2010 data**<jupyter_code>query =(" SELECT aq30_id, cell5m, iso, crop, area, unit_area, the_geom FROM crops_baseline "
        " WHERE iso = 'ESP' and crop in ('wheat', 'maize', 'chickpea','lentil', 'potato') "
        " and irrigation = 'irrigated'")

account = 'wri-rw'
urlCarto = f"https://{account}.carto.com/api/v2/sql"

sql = {"q": query}
r = requests.get(urlCarto, params=sql)

data = r.json()

crops = gpd.GeoDataFrame(data.get("rows"))
# Change geometry from WKB to WKT format
crops['geometry'] = crops.apply(lambda x: shapely.wkb.loads(x['the_geom'],hex=True), axis=1 )
crops.drop(columns='the_geom', inplace=True)
crops.crs = {'init': 'epsg:4326'}
crops = crops.to_crs({'init': 'epsg:4326'})
crops.head(1)<jupyter_output><empty_output><jupyter_text>**Merge datasets**<jupyter_code>crops_bws = pd.merge(left=crops, right=bws.drop(columns='geometry'), how='left', on='aq30_id')
crops_bws.head(1)
colors = ['#FFFF99', '#FFE600', '#FF9900', '#FF1900', '#990000']
cat = np.array(crops_bws['bws_cat'].unique())
cat = np.sort(cat[~np.isnan(cat)])

fig, ax = plt.subplots(figsize=[5,5])
ax.set_aspect('equal')

for i in range(len(cat)):
    crops_bws[(crops_bws['gid_1'] == 'ESP.2_1') & (crops_bws['bws_cat'] == cat[i]) & (crops_bws['crop'] == 'maize')].plot(ax=ax, color=colors[i], edgecolor=colors[i])<jupyter_output><empty_output><jupyter_text>Calculator**Exact Farm Location**<jupyter_code>points = gpd.GeoDataFrame(columns=['geometry'], data= [Point(-1.15,41.75), Point(-1.15,41.75).buffer(1)])
colors = ['#FFFF99', '#FFE600', '#FF9900', '#FF1900', '#990000']
cat = np.array(bws['bws_cat'].unique())
cat = np.sort(cat[~np.isnan(cat)])

fig, ax = plt.subplots(figsize=[5,5])
ax.set_aspect('equal')

for i in range(len(cat)):
    bws[(bws['gid_1'] == 'ESP.2_1') & (bws['bws_cat'] == cat[i])].plot(ax=ax, color=colors[i], edgecolor=colors[i])
points.iloc[:1].plot(ax=ax, color='k')
point = points.geometry.iloc[0]

# precise match
precise_match= bws[bws.intersects(point)]
precise_match<jupyter_output><empty_output><jupyter_text>**Point + Radius**<jupyter_code>colors = ['#FFFF99', '#FFE600', '#FF9900', '#FF1900', '#990000']
cat = np.array(crops_bws['bws_cat'].unique())
cat = np.sort(cat[~np.isnan(cat)])

fig, ax = plt.subplots(figsize=[5,5])
ax.set_aspect('equal')


points.iloc[1:2].plot(ax=ax, color='w', edgecolor='k')
for i in range(len(cat)):
    crops_bws[(crops_bws['gid_1'] == 'ESP.2_1') & (crops_bws['bws_cat'] == cat[i]) & (crops_bws['crop'] == 'maize')].plot(ax=ax, color=colors[i], edgecolor=colors[i])
    
points.iloc[:1].plot(ax=ax, color='k')
gdf = crops_bws[(crops_bws['gid_1'] == 'ESP.2_1') & (crops_bws['crop'] == 'maize')].copy()
area = points.geometry.iloc[1]

# precise match
precise_match= gdf[gdf.intersects(area)]
precise_match.head()
colors = ['#FFFF99', '#FFE600', '#FF9900', '#FF1900', '#990000']
cat = np.array(crops_bws['bws_cat'].unique())
cat = np.sort(cat[~np.isnan(cat)])

fig, ax = plt.subplots(figsize=[5,5])
ax.set_aspect('equal')


points.iloc[1:2].plot(ax=ax, color='w', edgecolor='k')
for i in range(len(cat)):
    precise_match[precise_match['bws_cat'] == cat[i]].plot(ax=ax, color=colors[i], edgecolor=colors[i])
    
points.iloc[:1].plot(ax=ax, color='k')
print('Avg BWS:', np.sum(np.array(precise_match['bws_score'])*np.array(precise_match['area']))/np.sum(precise_match['area']))
gdf = crops_bws[(crops_bws['gid_1'] == 'ESP.2_1') & (crops_bws['crop'] == 'maize')].copy()
area = points.geometry.iloc[1]

# precise match
precise_match= gdf[gdf.intersects(area)]
precise_match.head()
# intersection between the basin and the area
intersection = precise_match.intersection(area)
precise_match['geometry_new'] = list(intersection)

# Percentage of the crop cell intersecting with the area 
def f(*x):
    return x[0].area/x[1].area
precise_match['percentage'] = precise_match[['geometry_new', 'geometry']].apply(lambda x: f(*x), axis=1).round(2)

# Multiply the area with the percentage
def f(*x):
    return x[0]*x[1]
precise_match['area'] = precise_match[['area','percentage']].apply(lambda x: f(*x), axis=1).round(1)


geoms = []
for g in precise_match.geometry_new:
    geoms.append(g)
    
a = list(precise_match.columns)
a.remove('geometry')
a.remove('geometry_new')

gdf = gpd.GeoDataFrame(precise_match[a], geometry=geoms)
colors = ['#FFFF99', '#FFE600', '#FF9900', '#FF1900', '#990000']
cat = np.array(crops_bws['bws_cat'].unique())
cat = np.sort(cat[~np.isnan(cat)])

fig, ax = plt.subplots(figsize=[5,5])
ax.set_aspect('equal')


points.iloc[1:2].plot(ax=ax, color='w', edgecolor='k')
for i in range(len(cat)):
    gdf[gdf['bws_cat'] == cat[i]].plot(ax=ax, color=colors[i], edgecolor=colors[i])
    
points.iloc[:1].plot(ax=ax, color='k')
gdf.head()
print('Avg BWS:', np.sum(np.array(gdf['bws_score'])*np.array(gdf['area']))/np.sum(gdf['area']))<jupyter_output>Avg BWS: 2.921035738056926
{'fraction_non_alphanumeric': 0.2220953002610966, 'fraction_numerical': 0.041612271540469974, 'mean_word_length': 6.64214463840399, 'pattern_counts': {'":': 2, '<': 20, '<?xml version=': 0, '>': 20, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '454183', 'license': 'MIT', 'n_tokens_mistral': 2769, 'n_tokens_neox': 2606, 'n_words': 399, 'path': 'work/BWS_calculation.ipynb', 'repo_name': 'Vizzuality/data_sci_tutorials', 'size': '6128'}