<jupyter_start><jupyter_text>USAID Sites and Geospatial IntelligenceThis notebook covers the creating of geospatial data<jupyter_code>import os

import pandas as pd
import numpy as np
#os.mkdir("geospatial_data")
%matplotlib inline
# Load the site data provided by USAID
site_data = pd.read_csv("final_data/service_delivery_site_data.csv")
site_data.head()
import geopandas as gpd
# Use the data provided by UN Geospatial Repsitory
gdf = gpd.read_file('civ_admbnda_adm3_cntig_ocha_itos_20180706/civ_admbnda_adm3_cntig_ocha_itos_20180706.shp')

gdf.head()<jupyter_output><empty_output><jupyter_text>Lets start trying to find a shared column to match on<jupyter_code>districts = site_data['site_district'].unique()
print(len(districts))
districts.sort()
districts
site_data.head()<jupyter_output><empty_output><jupyter_text>'ABOBO-EST' is a neighborhood in AbidjanMatch city to district then aggregate the district level.Need to do some manual analysis to figure this whole matching up.  Data Processing- Observe there is a missing I so you think perhaps the two will match on ADM2_PCODE but this is a mistake. **There is no relationship between the two**```site_data['site_code'].head()gdf['ADM2_PCODE'].head() Insert Stringins_char = lambda x: x[0:1]+"I"+x[1:]site_data['ADM2_PCODE'] = site_data['site_code'].apply(ins_char)```  Data Processing  The codes are not matching up between the two dataframes- On inspection we can see there is an I missing, lets try tp add that and see if that fixes thing<jupyter_code>print("Num sites: ", len(site_data))

print("Num boundary shapes at ADM3: ",len(gdf))<jupyter_output>Num sites:  156
Num boundary shapes at ADM3:  510<jupyter_text>Geospatial Data  Regional Data-  Wikipedia data- Data from UN Geospatial Data Repository  Noteshttps://en.wikipedia.org/wiki/Subdivisions_of_Ivory_Coasthttps://www.youtube.com/watch?v=6pYorKr3XFQ&ab_channel=AlJazeeraEnglishhttps://www.youtube.com/watch?v=O1_wpzPX7C8&ab_channel=FRANCE24Englishhttps://fr.wikipedia.org/wiki/R%C3%A9gions_de_C%C3%B4te_d%27Ivoire<jupyter_code>from io import StringIO

# Taken from this Wikipedia Page
# https://fr.wikipedia.org/wiki/R%C3%A9gions_de_C%C3%B4te_d%27Ivoire


wikipedia_table = """
District	Chef-lieu de district	Région	Chef-lieu de région
Zanzan	Bondoukou	Bounkani	Bouna
Zanzan	Bondoukou	Gontougo	Bondoukou
Yamoussoukro (district autonome)	—	—	—
Woroba	Séguéla	Béré	Mankono
Woroba	Séguéla	Bafing	Touba
Woroba	Séguéla	Worodougou	Séguéla
Vallée du Bandama	Bouaké	Hambol	Katiola
Vallée du Bandama	Bouaké	Gbêkê	Bouaké
Savanes	Korhogo	Poro	Korhogo
Savanes	Korhogo	Tchologo	Ferkessédougou
Savanes	Korhogo	Bagoué	Boundiali
Sassandra-Marahoué	Daloa	Haut-Sassandra	Daloa
Sassandra-Marahoué	Daloa	Marahoué	Bouaflé
Montagnes	Man	Tonkpi	Man
Montagnes	Man	Cavally	Guiglo
Montagnes	Man	Guémon	Duékoué
Lagunes	Dabou	Agnéby-Tiassa	Agboville
Lagunes	Dabou	Mé	Adzopé
Lagunes	Dabou	Grands Ponts	Dabou
Lacs	Dimbokro	N’Zi	Dimbokro
Lacs	Dimbokro	Iffou	Daoukro
Lacs	Dimbokro	Bélier	Toumodi
Lacs	Dimbokro	Moronou	Bongouanou
Gôh-Djiboua	Gagnoa	Gôh	Gagnoa
Gôh-Djiboua	Gagnoa	Lôh-Djiboua	Divo
Denguélé	Odienné	Folon	Minignan
Denguélé	Odienné	Kabadougou	Odienné
Comoé	Abengourou	Indénié-Djuablin	Abengourou
Comoé	Abengourou	Sud-Comoé	Aboisso
Bas-Sassandra	San-Pédro	Nawa	Soubré
Bas-Sassandra	San-Pédro	San-Pédro	San-Pédro
Bas-Sassandra	San-Pédro	Gbôklé	Sassandra
Abidjan (district autonome)	—	—	—"""

wiki_region_mappings = pd.read_csv(StringIO(wikipedia_table),sep="\t")

gdf.groupby(['ADM1_FR','ADM2_FR']).size().sort_values(ascending =False)<jupyter_output><empty_output><jupyter_text>Site Data<jupyter_code>site_data.head()
site_data.groupby(['site_region','site_district'])['site_code'].size().sort_values(ascending=False).head(10)<jupyter_output><empty_output><jupyter_text>Analysis of Site Data Boundary Structure- Data is organized by Region and then by Site, this is contrary to the way that Cote d'Ivoire organizes itself which is by:     1. District    2. Region    3. Department    4. Village    5. Commune    - The USAID dataset seems to be presented as    1. Region: regions or multiple regions combined under one administrative boundary    2. District: regions and departments  Fuzz-Matching of regional names in the dataset- Calculate the character lexical similarity in the strings to determine the best matches.<jupyter_code>from fuzzywuzzy import fuzz
from fuzzywuzzy import process


def get_fuzzy_match_results(ref_array,custom_array):
    # Create a dictionary to hold all the string matching calculations
    custom_mapping = {}
    
    # Lambda functions to extract the match name from the tuple
    get_match_name = lambda x: match_dict[x][0]
    
    # Lambda functions to extract the match score from the tuple
    get_match_score = lambda x: match_dict[x][1]
    
    # Create_Reference_table
    reference_table = pd.DataFrame({'ref':ref_array})
    # Iterate over every commune name in the reference table
    for custom_label in custom_array:
        # Skip values if not string
        if type(custom_label) == str:

            ffuzzy_match = lambda x : fuzz.partial_ratio(custom_label,x) 
            reference_table[custom_label] = reference_table['ref'].apply(ffuzzy_match)
            
            #reference_table[custom_label] = fuzzy_ratios
            ref_max_value = reference_table[custom_label].max()
            # Identify the record that has the highest score with the provided custom_label
            matching_recs = reference_table.loc[reference_table[custom_label]==ref_max_value]

            # If there are two communes that have an equal score, select the first value
            if len(matching_recs)>1:
                #print("Multiple matches: ",custom_label)
                match_site_name = matching_recs['ref'].values[0]
            else:
                match_site_name = matching_recs['ref'].values[0]

            # Update the match_dict with a tuple to store the final string and its corresponding score
            custom_mapping[custom_label] = {'est_site_name':match_site_name,'fratio':ref_max_value}
        else:
            custom_mapping[custom_label] = None
        
        mapping_df = pd.DataFrame(custom_mapping).transpose()
        ref_df = pd.DataFrame(reference_table).set_index('ref')
    return mapping_df,ref_df<jupyter_output><empty_output><jupyter_text>Create a series of vectors to perform fuzzy wuzzy matching to create mappings<jupyter_code>import seaborn as sns; sns.set()
## USAID

# Isolate USAID Region values
usaid_civ_site_region = site_data['site_region'].str.upper().unique()
#print(len(usaid_civ_site_region))
#print(usaid_civ_site_region)

# Isolate USAID District values
usaid_civ_site_district = site_data['site_district'].str.upper().unique()
#print(len(usaid_civ_site_district))
#print(usaid_civ_site_district)


## Wikipedia

# Isolate Wikipedia Region labels
wiki_admn_region = wiki_region_mappings['Région'].str.upper().unique()
#print(len(wiki_admn_region))
#print(wiki_admn_region)

# Isolate Wikipedia Department labels
wiki_admn_dept = wiki_region_mappings['Chef-lieu de région'].str.upper().unique()
print(len(wiki_admn_dept))
#print(wiki_admn_dept)


## UN Geospatial

# Isolate UN ADM1_FR labels
geospatial_admn_1 = gdf['ADM1_FR'].str.upper().unique()
#print(len(geospatial_admn_1))
#print(geospatial_admn_1)

# Isolate UN ADM3_FR labels
geospatial_admn_2 = gdf['ADM2_FR'].str.upper().unique()
print(len(geospatial_admn_2))
#print(geospatial_admn_2)

# Isolate UN ADM4_FR labels
geospatial_admn_3 = gdf['ADM3_FR'].str.upper().unique()
print(len(geospatial_admn_3))
#print(geospatial_admn_3)
import matplotlib.pyplot as plt
def make_fuzzy_matching_evaluation(ref,labels,ref_name='x',label_name='y'):
    mapping ,ref_table = get_fuzzy_match_results(ref,labels)
    
    mapping['fratio'] = pd.to_numeric(mapping['fratio'])
    
    plt.subplots(figsize=(8,6))
    sns.heatmap(ref_table_1)
    plt.title(f"{ref_name} - {len(ref)} vs {label_name} - {len(labels)}",fontdict={'fontsize':20})
    plt.show()
    print(mapping.fratio.describe())
    return mapping ,ref_table
wu_reg_reg_map, wu_reg_reg_tbl = make_fuzzy_matching_evaluation(wiki_admn_region,usaid_civ_site_region,'Wikipedia-Regions','USAID-Regions')<jupyter_output><empty_output><jupyter_text>Wikipedia: Spatial-Regions vs USAID-Regions- First we capitalize the results and there started to be several more higher fuzzy ratio scores calculated.  Initially there were very weak results 'Me' was the highest match for all of the initial values.*Lets try Wikipedia Regions vs USAID - districts....*<jupyter_code>wu_reg_dis_map, wu_reg_dis_tbl = make_fuzzy_matching_evaluation(
    wiki_admn_region,
    usaid_civ_site_district,'Wikipedia-Regions','USAID-Districts')<jupyter_output><empty_output><jupyter_text>Results: Wikipedia-Regions vs USAID-Regions- Great results startings to see many  matches over 80*Lets try Wikipedia-departments_ with USAID-regions*<jupyter_code>wu_dep_dis_map, wu_dep_dis_tbl = make_fuzzy_matching_evaluation(
    wiki_admn_dept,
    usaid_civ_site_district,'Wikipedia-Departments','USAID-Districts')<jupyter_output><empty_output><jupyter_text>Matching with the geospatial data and making custom maps   ADM1_FR<jupyter_code># Compare ADM1_FR and USAID's Region Codes 

ug_reg_dis_map, ug_reg_dis_tbl = make_fuzzy_matching_evaluation(
    usaid_civ_site_region,
    geospatial_admn_1,'USAID-Regions','UN Geospatial ADM1_FR')
# Using the matches from fuzzy model as a base with ADM1_FR and USAID Region
# Region vs Region

# This will drop one region from the reference index
# Drop Yamaoussoukora
civ_strong_matches = ug_reg_dis_map[ug_reg_dis_map.fratio>50]
print(len(civ_strong_matches))
print(civ_strong_matches)


civ_region_adm1_mapping = {index:data['est_site_name'] for index, data in civ_strong_matches.iterrows()}<jupyter_output>32
                                       est_site_name  fratio
INDENIE-DJUABLIN                    INDENIE-DJUABLIN     100
DISTRICT AUTONOME D'ABIDJAN                ABIDJAN 2      88
N'ZI                               N'ZI-IFOU-MORONOU     100
SUD-COMOE                                  SUD-COMOE     100
ME                                  AGNEBY-TIASSA-ME     100
AGNEBY-TIASSA                       AGNEBY-TIASSA-ME     100
GRANDS PONTS                  ABIDJAN 1-GRANDS PONTS     100
IFFOU                              N'ZI-IFOU-MORONOU      80
GONTOUGO                           BOUNKANI-GONTOUGO     100
MORONOU                            N'ZI-IFOU-MORONOU     100
GBEKE                                          GBEKE     100
BELIER                                        BELIER     100
HAMBOL                                        HAMBOL     100
GUEMON                                CAVALLY-GUEMON     100
PORO                            PORO-TCHOLOGO-BAGOUE     100
KABADOUGOU           [...]<jupyter_text>Lets compare ADM2_Fr and USAID DistrictIn the past example we used 50 as our cut-off criteria now as we have stronger matches we will increase it to 75  ADM2_FR<jupyter_code># Compare ADM1_FR and USAID's Region Codes 

gu_dep_dis_map, gu_dep_dis_tbl = make_fuzzy_matching_evaluation(
    geospatial_admn_2,
    usaid_civ_site_district,'UN Geospatial ADM1_FR','USAID-Districts')


strong_matches = gu_dep_dis_map[gu_dep_dis_map.fratio>50]
print("Strong matches")
print(len(strong_matches))
print(strong_matches.sort_values(by='fratio',ascending=False).head(10))
weak_matches = gu_dep_dis_map[gu_dep_dis_map.fratio<70]

print("Weak matches")
print(len(weak_matches))
print(weak_matches)

# Drop values with a bad mapping
# Using the weak matches we can identify the worst performing matches
# We want to keep the last records though, it seems the accent is being interpretted poorly
drop_indices = weak_matches.index[:-1]


civ_dist_adm2_mapping = {index:data['est_site_name'] for index, data in gu_dep_dis_map.drop(drop_indices).iterrows()}<jupyter_output><empty_output><jupyter_text>ADM3_FR Lets compare ADM3_Fr and USAID DistrictI have already pruned the matches by evaluating weak matches.<jupyter_code>geospatial_admn_3 = gdf['ADM3_FR'].str.upper().unique()
print(len(geospatial_admn_3))
#print(civ_admn_dept)

print(len(usaid_civ_site_district))
site_dist_geo_admn3_res,site_dist_geo_admn3_tbl = get_fuzzy_match(geospatial_admn_3,usaid_civ_site_district)
sns.heatmap(site_dist_geo_admn3_tbl.set_index('ref'))


# Compare ADM1_FR and USAID's Region Codes 

gu_dep3_dis_map, gu_dep3_dis_tbl = make_fuzzy_matching_evaluation(
    geospatial_admn_3,
    usaid_civ_site_district,'UN Geospatial ADM1_FR','USAID-Districts')


strong_matches = gu_dep3_dis_map[gu_dep3_dis_map.fratio>50]
print("Strong matches")
print(len(strong_matches))
print(strong_matches.sort_values(by='fratio',ascending=False).head(10))
weak_matches = gu_dep3_dis_map[gu_dep3_dis_map.fratio<70]

print("Weak matches")
print(len(weak_matches))
print(weak_matches)

# Drop values with a bad mapping
# Using the weak matches we can identify the worst performing matches
drop_indices = weak_matches.index[:-1]


civ_dist_adm3_mapping = {index:data['est_site_name'] for index, data in gu_dep3_dis_map.drop(drop_indices).iterrows()}<jupyter_output>506
81<jupyter_text>Lets Apply the mappings- Starting from region, create a mapping to map each value in the USAID data set with our pruned mappings<jupyter_code>site_data['adm3_fr'] = site_data['site_district'].map(civ_dist_adm3_mapping)
site_data['adm2_fr'] = site_data['site_district'].map(civ_dist_adm2_mapping)<jupyter_output><empty_output><jupyter_text>Apply the mappings to the geospatial data- Because of how the USAID regions incorporated multiple existing geospatial boundaries, I applied the USAID regional mapping onto the existing ADM1_FR names in order to gain access to map projections with those groupings.<jupyter_code>gdf['usaid_admin_region'] = gdf['ADM1_FR'].str.upper().map(civ_region_adm1_mapping)

gdf.to_file("geospatial_data/Custom_CIV.shp")
gdf['usaid_admin_region'].head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08252288329519451, 'fraction_numerical': 0.014302059496567507, 'mean_word_length': 4.733907339073391, 'pattern_counts': {'":': 0, '<': 56, '<?xml version=': 0, '>': 58, 'https://': 5, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 7, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '488668', 'license': 'Apache-2.0', 'n_tokens_mistral': 5217, 'n_tokens_neox': 4923, 'n_words': 1101, 'path': 'Connecting Site and Geospatial data.ipynb', 'repo_name': 'Ayiti-Analytics/usaid_foecasting_challenge', 'size': '13984'}