1. Alkaline-Water
  2. MyGeoScripts
In [17]:
# ms-python.python added
import os
try:
	os.chdir(os.path.join(os.getcwd(), 'MyGeoScripts'))
	print(os.getcwd())
except:
	pass
In [4]:
import geopandas as gpd
import pandas as pd

shapefile = '../Data/County/County.shp'

# read shape file using geopandas
county = gpd.read_file(shapefile)
county.head()
Out[4]:
OBJECTID AREA PERIMETER COUNTY3_ COUNTY3_ID COUNTY Shape_Leng Shape_Area geometry
0 1 5.677 15.047 2.0 1.0 Turkana 15.046838 5.676985 POLYGON ((35.79593 5.34449, 35.79659 5.34468, ...
1 2 6.177 11.974 3.0 2.0 Marsabit 11.974165 6.176831 POLYGON ((36.05061 4.45622, 36.23184 4.45124, ...
2 3 2.117 7.355 4.0 3.0 Mandera 7.355154 2.117196 POLYGON ((41.62133 3.97673, 41.62272 3.97860, ...
3 4 4.610 9.838 5.0 4.0 Wajir 9.838408 4.609589 POLYGON ((39.31812 3.47197, 39.31956 3.47168, ...
4 5 0.740 5.030 6.0 5.0 West Pokot 5.030271 0.740481 POLYGON ((35.12745 2.62271, 35.12762 2.62302, ...
In [5]:
county = gpd.read_file(shapefile)[['COUNTY','geometry']]
county.head()
Out[5]:
COUNTY geometry
0 Turkana POLYGON ((35.79593 5.34449, 35.79659 5.34468, ...
1 Marsabit POLYGON ((36.05061 4.45622, 36.23184 4.45124, ...
2 Mandera POLYGON ((41.62133 3.97673, 41.62272 3.97860, ...
3 Wajir POLYGON ((39.31812 3.47197, 39.31956 3.47168, ...
4 West Pokot POLYGON ((35.12745 2.62271, 35.12762 2.62302, ...
In [6]:
datafile = '../Data/County/kewi_water_test_and_results1.csv'
df = pd.read_csv(datafile,sep=',')
df.head()
Out[6]:
SAMPLE_NO DATE TYPE_OF_H2O COUNTY TYPE_OF_ANALYSIS PH ALKALINITY_Mg/L CONDUCTIVITY?S/cm Total_Dissolved_Solids COLOUR OBJECTID
0 19T NaN Tank Machakos Jar Test/Bacteriological Analysis/Full Chemica... 7.90 1.0 136.40 220.0 25 396
1 1484 18/6/2015 Effluent Kajiado Effluent 6.60 1.4 1001.92 1616.0 20 379
2 176 13/6/16 Borehole Kiambu Full Chemical Analysis & Bacteriological Analysis 5.40 2.0 35.34 57.0 0 472
3 89 4/6/2016 Tap Murang'a Full Chemical Analysis/Bacteriological Analysis 5.70 2.0 124.00 200.0 2.5 434
4 737 4/24/2012 Borehole Nairobi Full Chemical Analysis & Bacteriological Analysis 8.21 2.0 15.00 9.2 2.5 43
In [7]:
#take county and water alkalinity levels
alkaline_df1 = df[['COUNTY','ALKALINITY_Mg/L']]

#renamed second column
alkaline_df1.rename(columns={"ALKALINITY_Mg/L" : "ALKALINITY"}, inplace=True)
alkaline_df1.head()
alkaline_df1.describe()

/tmp/ipykernel_6062/4216690743.py:5: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
  alkaline_df1.rename(columns={"ALKALINITY_Mg/L" : "ALKALINITY"}, inplace=True)
Out[7]:
ALKALINITY
count 392.000000
mean 674.590306
std 2736.531968
min 1.000000
25% 38.250000
50% 98.000000
75% 297.000000
max 38600.000000
In [8]:
# remove rows with missing values in alkalinity
alkaline_df2= alkaline_df1.dropna()
alkaline_df2.head()
Out[8]:
COUNTY ALKALINITY
0 Machakos 1.0
1 Kajiado 1.4
2 Kiambu 2.0
3 Murang'a 2.0
4 Nairobi 2.0
In [9]:
alkaline_df2.dtypes
Out[9]:
COUNTY         object
ALKALINITY    float64
dtype: object
In [10]:
# remove duplicated county names by getting the mean alkalinity of the respective duplicates and having it as one county
alkaline_df3 = alkaline_df2.groupby('COUNTY').mean().reset_index()
alkaline_df3.head()
alkaline_df1.describe()
Out[10]:
ALKALINITY
count 392.000000
mean 674.590306
std 2736.531968
min 1.000000
25% 38.250000
50% 98.000000
75% 297.000000
max 38600.000000
In [11]:
# merge county(geodata) and alkaline_df3(alkaline values) frames
# perform left merge to preserve every row on county geodata
merged = county.merge(alkaline_df3,how = 'left')

# replace NaN values with string NoData
merged.fillna('No data',inplace=True)
merged.head()
Out[11]:
COUNTY geometry ALKALINITY
0 Turkana POLYGON ((35.79593 5.34449, 35.79659 5.34468, ... 85.0
1 Marsabit POLYGON ((36.05061 4.45622, 36.23184 4.45124, ... 344.888889
2 Mandera POLYGON ((41.62133 3.97673, 41.62272 3.97860, ... No data
3 Wajir POLYGON ((39.31812 3.47197, 39.31956 3.47168, ... 178.333333
4 West Pokot POLYGON ((35.12745 2.62271, 35.12762 2.62302, ... No data
In [12]:
import json

# read data(a dataframe) to json
merged_json = json.loads(merged.to_json())

# convert to string like object
json_data = json.dumps(merged_json)
# json_data
In [13]:
from bokeh.io import output_notebook, show, output_file
from bokeh.plotting import figure
from bokeh.models import GeoJSONDataSource, LinearColorMapper, ColorBar
from bokeh.palettes import brewer

#Input GeoJSON source that contains features for plotting.
geosource = GeoJSONDataSource(geojson = json_data)

palette = brewer['YlGnBu'][8]

#Reverse color order so that dark blue is highest alkalinity
palette = palette[::-1]

#Instantiate LinearColorMapper that linearly maps numbers in a range, into a sequence of colors.
color_mapper = LinearColorMapper(palette = palette, low = 1, high = 38600)

#Define custom tick labels for color bar.
tick_labels = {'0': '0%','9650':'25%', '19300':'50%','28950':'75%', '38600': '100%'}

#Create color bar. 
color_bar = ColorBar(color_mapper=color_mapper, label_standoff=8,width = 500, height = 20,
border_line_color=None,location = (0,0), orientation = 'horizontal', major_label_overrides = tick_labels)

#Create figure object.
p = figure(title = 'Alkalinity of water in Kenyan counties', plot_height = 700 , plot_width = 700, toolbar_location = None)
p.xgrid.grid_line_color = None
p.ygrid.grid_line_color = None

#Add patch renderer to figure. 
p.patches('xs','ys', source = geosource,fill_color = {'field' :'ALKALINITY', 'transform' : color_mapper},
          line_color = 'black', line_width = 0.25, fill_alpha = 1)

#Specify figure layout.
p.add_layout(color_bar, 'below')

#Display figure inline in Jupyter Notebook.
output_notebook()

#Display figure.
show(p)
Loading BokehJS ...