<jupyter_start><jupyter_text>***  AidData ** Democratic Republic of the Congo **<jupyter_code>DRC_locations = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/DRC-AIMS_GeocodedResearchRelease_Level1_v1.3.1/data/locations.csv')
DRC_projects = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/DRC-AIMS_GeocodedResearchRelease_Level1_v1.3.1/data/projects.csv')
DRC_transactions = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/DRC-AIMS_GeocodedResearchRelease_Level1_v1.3.1/data/transactions.csv')
DRC_locations = DRC_locations[['project_id', 'latitude', 'longitude', 'gazetteer_adm_name']]
DRC_projects = DRC_projects[['project_id','project_title','donors', 'donors_iso3', 'recipients', 'recipients_iso3','ad_sector_names', 'status', 'transactions_start_year', 'transactions_end_year', 'total_commitments']]
DRC_transactions = DRC_transactions[['project_id','transaction_year', 'transaction_currency', 'transaction_value']]
DRC_transactions = pd.merge(DRC_transactions, DRC_projects, how='left', on='project_id')
DRC_transactions = pd.merge(DRC_transactions, DRC_locations, how='left', on='project_id')
DRC_transactions.dropna(inplace=True)<jupyter_output><empty_output><jupyter_text>** Nigeria **<jupyter_code>Nigeria_locations = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1/data/locations.csv')
Nigeria_projects = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1/data/projects.csv')
Nigeria_transactions = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/NigeriaAIMS_GeocodedResearchRelease_Level1_v1.3.1/data/transactions.csv')
Nigeria_locations = Nigeria_locations[['project_id', 'latitude', 'longitude', 'gazetteer_adm_name']]
Nigeria_projects = Nigeria_projects[['project_id','project_title','donors', 'donors_iso3', 'recipients', 'recipients_iso3','ad_sector_names', 'status', 'transactions_start_year', 'transactions_end_year', 'total_commitments']]
Nigeria_transactions = Nigeria_transactions[['project_id','transaction_year', 'transaction_currency', 'transaction_value']]
Nigeria_transactions = pd.merge(Nigeria_transactions, Nigeria_projects, how='left', on='project_id')
Nigeria_transactions = pd.merge(Nigeria_transactions, Nigeria_locations, how='left', on='project_id')
Nigeria_transactions.dropna(inplace=True)<jupyter_output><empty_output><jupyter_text>** Senegal **<jupyter_code>Senegal_locations = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/SenegalAIMS_geocodedresearchrelease_level1_v1.5.1/data/locations.csv')
Senegal_projects = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/SenegalAIMS_geocodedresearchrelease_level1_v1.5.1/data/projects.csv')
Senegal_transactions = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/SenegalAIMS_geocodedresearchrelease_level1_v1.5.1/data/transactions.csv')
Senegal_locations = Senegal_locations[['project_id', 'latitude', 'longitude', 'gazetteer_adm_name']]
Senegal_projects = Senegal_projects[['project_id','project_title','donors', 'donors_iso3', 'recipients', 'recipients_iso3','ad_sector_names', 'status', 'transactions_start_year', 'transactions_end_year', 'total_commitments']]
Senegal_transactions = Senegal_transactions[['project_id','transaction_year', 'transaction_currency', 'transaction_value']]
Senegal_transactions = pd.merge(Senegal_transactions, Senegal_projects, how='left', on='project_id')
Senegal_transactions = pd.merge(Senegal_transactions, Senegal_locations, how='left', on='project_id')
Senegal_transactions.dropna(inplace=True)<jupyter_output><empty_output><jupyter_text>** Sierra Leone **<jupyter_code>SierraLeone_locations = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/SierraLeoneAIMS_geocodedresearchrelease_level1_v1.0.0/data/locations.csv')
SierraLeone_projects = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/SierraLeoneAIMS_geocodedresearchrelease_level1_v1.0.0/data/projects.csv')
SierraLeone_transactions = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/SierraLeoneAIMS_geocodedresearchrelease_level1_v1.0.0/data/transactions.csv')
SierraLeone_locations = SierraLeone_locations[['project_id', 'latitude', 'longitude', 'gazetteer_adm_name']]
SierraLeone_projects = SierraLeone_projects[['project_id','project_title','donors', 'donors_iso3', 'recipients', 'recipients_iso3','ad_sector_names', 'status', 'transactions_start_year', 'transactions_end_year', 'total_commitments']]
SierraLeone_transactions = SierraLeone_transactions[['project_id','transaction_year', 'transaction_currency', 'transaction_value']]
SierraLeone_transactions = pd.merge(SierraLeone_transactions, SierraLeone_projects, how='left', on='project_id')
SierraLeone_transactions = pd.merge(SierraLeone_transactions, SierraLeone_locations, how='left', on='project_id')
SierraLeone_transactions.dropna(inplace=True)<jupyter_output><empty_output><jupyter_text>** Somalia **<jupyter_code>Somalia_locations = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/SomaliaAIMS_GeocodedResearchRelease_Level1_v1.1.1/data/locations.csv')
Somalia_projects = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/SomaliaAIMS_GeocodedResearchRelease_Level1_v1.1.1/data/projects.csv')
Somalia_transactions = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/SomaliaAIMS_GeocodedResearchRelease_Level1_v1.1.1/data/transactions.csv')
Somalia_locations = Somalia_locations[['project_id', 'latitude', 'longitude', 'gazetteer_adm_name']]
Somalia_projects = Somalia_projects[['project_id','project_title','donors', 'donors_iso3', 'recipients', 'recipients_iso3','ad_sector_names', 'status', 'transactions_start_year', 'transactions_end_year', 'total_commitments']]
Somalia_transactions = Somalia_transactions[['project_id','transaction_year', 'transaction_currency', 'transaction_value']]
Somalia_transactions = pd.merge(Somalia_transactions, Somalia_projects, how='left', on='project_id')
Somalia_transactions = pd.merge(Somalia_transactions, Somalia_locations, how='left', on='project_id')
Somalia_transactions.dropna(inplace=True)<jupyter_output><empty_output><jupyter_text>** Uganda **<jupyter_code>Uganda_locations = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1/data/locations.csv')
Uganda_projects = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1/data/projects.csv')
Uganda_transactions = pd.read_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/AidData/UgandaAIMS_GeocodedResearchRelease_Level1_v1.4.1/data/transactions.csv')
Uganda_locations = Uganda_locations[['project_id', 'latitude', 'longitude', 'gazetteer_adm_name']]
Uganda_projects = Uganda_projects[['project_id','project_title','donors', 'donors_iso3', 'recipients', 'recipients_iso3','ad_sector_names', 'status', 'transactions_start_year', 'transactions_end_year', 'total_commitments']]
Uganda_transactions = Uganda_transactions[['project_id','transaction_year', 'transaction_currency', 'transaction_value']]
Uganda_transactions = pd.merge(Uganda_transactions, Uganda_projects, how='left', on='project_id')
Uganda_transactions = pd.merge(Uganda_transactions, Uganda_locations, how='left', on='project_id')
Uganda_transactions.dropna(inplace=True)<jupyter_output><empty_output><jupyter_text>** Final table **<jupyter_code>AidData_transactions = pd.concat([DRC_transactions,Nigeria_transactions,Senegal_transactions,
                                 SierraLeone_transactions,Somalia_transactions,Uganda_transactions])
AidData_transactions['lat'] = AidData_transactions['latitude'].apply(lambda x: float('{:10.4f}'.format(x)))
AidData_transactions['lng'] = AidData_transactions['longitude'].apply(lambda x: float('{:10.4f}'.format(x)))
AidData_transactions.drop(columns = ['latitude','longitude'], inplace=True)
AidData_transactions.head()<jupyter_output><empty_output><jupyter_text>Boundaries<jupyter_code>countries = gpd.read_file('/Users/ikersanchez/Vizzuality/DATA/all_countries/all_countries.shp')
# COD border
border_mask = countries['iso_a3'] == 'COD'
COD_border = countries[border_mask]
# NGA border
border_mask = countries['iso_a3'] == 'NGA'
NGA_border = countries[border_mask]
# SEN border
border_mask = countries['iso_a3'] == 'SEN'
SEN_border = countries[border_mask]
# SLE border
border_mask = countries['iso_a3'] == 'SLE'
SLE_border = countries[border_mask]
# SOM border
border_mask = countries['iso_a3'] == 'SOM'
SOM_border = countries[border_mask]
# UGA border
border_mask = countries['iso_a3'] == 'UGA'
UGA_border = countries[border_mask]
# All
borders = pd.concat([COD_border,NGA_border,SEN_border,SLE_border,SOM_border,UGA_border])
fig, ax = plt.subplots(figsize=[12,12])
ax.set_aspect('equal')

borders.plot(ax=ax, color='white', edgecolor='black')
AidData_transactions.plot(ax=ax, x='lng', y='lat', kind='scatter', s=8)<jupyter_output><empty_output><jupyter_text>Remove the points outside the countries<jupyter_code>points = []
for i in range(len(AidData_transactions)):
    x = AidData_transactions['lng'].iloc[i]
    y = AidData_transactions['lat'].iloc[i]
    points.append(Point(x,y))
AidData_transactions['geometry'] = points
# Coordinate reference system : WGS84
AidData_transactions.crs  = {'init': 'epsg:4326'}
# Remove points outside countries
AidData_transactions = gpd.sjoin(AidData_transactions, borders, how='inner', op='intersects')
fig, ax = plt.subplots(figsize=[12,12])
ax.set_aspect('equal')

borders.plot(ax=ax, color='white', edgecolor='black')
AidData_transactions.plot(ax=ax, x='lng', y='lat', kind='scatter', s=8)
# Save table
AidData_transactions.to_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/cartodb/AidData_transactions.csv')<jupyter_output><empty_output><jupyter_text>***   HumanDataExchange ** KENYA,NIGERIA,TANZANIA,UGANDA **  Africa  Electrical Grid<jupyter_code>africa_grid = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/HumanDataExchange/Africa/Electrical Grid/africagrid20170504/africa_grid_20170906final.shp')
africa_grid.head()
africa_grid.plot()<jupyter_output><empty_output><jupyter_text>** Uganda **<jupyter_code>uganda_grid = africa_grid[africa_grid['country'] == 'Uganda']
# UGA boundary
border_mask = countries['iso_a3'] == 'UGA'
uganda_boundary = countries[border_mask]
# Coordinate reference system : WGS84
uganda_grid.crs  = {'init': 'epsg:4326'}
uganda_boundary.crs  = {'init': 'epsg:4326'}
fig, ax = plt.subplots(figsize=[8,8])
ax.set_aspect('equal')

uganda_boundary.plot(ax=ax, color='white', edgecolor='black')
uganda_grid.plot(ax=ax, color='r')<jupyter_output><empty_output><jupyter_text>** Final table **<jupyter_code># Drop columns
africa_grid.drop(columns=['from','to'], inplace=True)
# Add year
africa_grid['year'] = '2017'
# Add iso
africa_grid = pd.merge(africa_grid, countries, how='left', on='country')
africa_grid['iso'] = africa_grid['iso3']
africa_grid.drop(columns='iso3', inplace=True)
# Reorder columns
africa_grid = africa_grid[['status','source','operator','voltage_kV','length_km','iso','country','year','geometry']]
africa_grid.head()
# Save table
africa_grid.to_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/cartodb/electrical_grid.csv')<jupyter_output><empty_output><jupyter_text>***  Mobile Coverage ** INDIA,BANGLADESH,KENYA,NIGERIA,TANZANIA,UGANDA ** Mobile Coverage Explorer is a representation of areas covered by mobile networks around the world. It is supplied as polygon data which has been created from submissions made directly to Collins Bartholomew or the GSMA from network operators who provide roaming detail for inclusion in the GSMA’s Roaming and Coverage Maps web application.The coverage data is supplied as three separate files:- Global_GSM_ which is a representation of the GSM (2G) network submitted by operators. - Global_3G_ which is a representation of the 3G network submitted by operators. - Global_4G_ which is a representation of the 4G network submitted by operators.  Read country maps<jupyter_code># India
indMap = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/gadm36_shp/gadm36_IND.shp')
# Get Uttar Pradesh and Bihar states
indMapStates = indMap[(indMap['NAME_1'] == 'Uttar Pradesh') | (indMap['NAME_1'] == 'Bihar')]

# Kenya
kenMap = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/gadm36_shp/gadm36_KEN.shp')

# Uganda
ugaMap = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/gadm36_shp/gadm36_UGA.shp')

# Bangladesh
bgdMap = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/gadm36_shp/gadm36_BGD.shp')

# Nigeria
ngaMap = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/gadm36_shp/gadm36_NGA.shp')

# Tanzania
tzaMap = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/gadm36_shp/gadm36_TZA.shp')
# Get the boundary of each country
indBoundary = gpd.GeoSeries(cascaded_union(indMapStates['geometry']))
indBoundary = gpd.GeoDataFrame(indBoundary).rename(columns={0: 'geometry'})
indBoundary['country'] = 'India'
indBoundary['iso'] = 'IND'

kenBoundary = gpd.GeoSeries(cascaded_union(kenMap['geometry']))
kenBoundary = gpd.GeoDataFrame(kenBoundary).rename(columns={0: 'geometry'})
kenBoundary['country'] = 'Kenya'
kenBoundary['iso'] = 'KEN'

ugaBoundary = gpd.GeoSeries(cascaded_union(ugaMap['geometry']))
ugaBoundary = gpd.GeoDataFrame(ugaBoundary).rename(columns={0: 'geometry'})
ugaBoundary['country'] = 'Uganda'
ugaBoundary['iso'] = 'UGA'

bgdBoundary = gpd.GeoSeries(cascaded_union(bgdMap['geometry']))
bgdBoundary = gpd.GeoDataFrame(bgdBoundary).rename(columns={0: 'geometry'})
bgdBoundary['country'] = 'Bangladesh'
bgdBoundary['iso'] = 'BGD'

ngaBoundary = gpd.GeoSeries(cascaded_union(ngaMap['geometry']))
ngaBoundary = gpd.GeoDataFrame(ngaBoundary).rename(columns={0: 'geometry'})
ngaBoundary['country'] = 'Nigeria'
ngaBoundary['iso'] = 'NGA'

tzaBoundary = gpd.GeoSeries(cascaded_union(tzaMap['geometry']))
tzaBoundary = gpd.GeoDataFrame(tzaBoundary).rename(columns={0: 'geometry'})
tzaBoundary['country'] = 'Tanzania'
tzaBoundary['iso'] = 'TZA'

boundaries = gpd.GeoDataFrame(pd.concat([indBoundary,kenBoundary,ugaBoundary,bgdBoundary,ngaBoundary,tzaBoundary]))
boundaries.head()<jupyter_output><empty_output><jupyter_text>Global GSM  Global 3G<jupyter_code>global_3d = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/Mobile Coverage Explorer WGS84 v201712 - ESRI SHAPE/ReducedData/Global_3G_201712/Global_3G_201712.shp')
# GROP COLUMN
global_3d.drop(columns='FID', inplace=True)
# Add year
global_3d['year'] = '2017'
global_3d.head()
global_3d.crs
fig, ax = plt.subplots(figsize=[12,12])
ax.set_aspect('equal')

boundaries.plot(ax=ax, color='white', edgecolor='black')
global_3d.plot(ax=ax)
global_3d=gpd.overlay(gpd.GeoDataFrame(global_3d), boundaries, how='intersection')
# Save table
global_3d.to_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/cartodb/mobile_coverage_global_3d.csv')<jupyter_output><empty_output><jupyter_text>Global 4G<jupyter_code>global_4d = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/Mobile Coverage Explorer WGS84 v201712 - ESRI SHAPE/ReducedData/Global_4G_201712/Global_4G_201712.shp')
# GROP COLUMN
global_4d.drop(columns='FID', inplace=True)
# Add year
global_4d['year'] = '2017'
global_4d.head()
global_4d.crs
global_4d.plot()
# Save table
global_4d.to_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/cartodb/mobile_coverage_global_4d.csv')<jupyter_output><empty_output><jupyter_text>***  Urban Areas ** INDIA,BANGLADESH,KENYA,NIGERIA,TANZANIA,UGANDA **<jupyter_code>urban = gpd.read_file('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/Contextual_Layers/ne_10m_urban_areas/ne_10m_urban_areas.shp')
urban.head()
urban.plot()
urban.crs<jupyter_output><empty_output><jupyter_text>** Final table **<jupyter_code># Drop columns
urban.drop(columns=['featurecla','scalerank','min_zoom'], inplace=True)
# Add year
urban['year'] = '2003'
# Reorder columns
urban = urban[['area_sqkm','year','geometry']]
urban.head()
# Save table
urban.to_csv('/Users/ikersanchez/Vizzuality/PROIEKTUAK/i2i/Data/cartodb/urban_areas.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13884750610809846, 'fraction_numerical': 0.017936952505810143, 'mean_word_length': 10.25553319919517, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 69, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '17', 'dir': 'jupyter-structured-clean-dedup', 'id': '318466', 'license': 'MIT', 'n_tokens_mistral': 6645, 'n_tokens_neox': 6239, 'n_words': 694, 'path': 'i2i/i2i_Contextual_Layers.ipynb', 'repo_name': 'Vizzuality/notebooks', 'size': '16781'}