<jupyter_start><jupyter_text>Setting<jupyter_code>import subprocess

# GeoPandas
try:
    import geopandas as gpd
except ImportError:
    print('geopandas package not installed. Installing ...')
    subprocess.check_call(["python", '-m', 'pip', 'install', 'geopandas'])
    import geopandas as gpd


# Geemap
try:
    import geemap
except ImportError:
    print('geemap package not installed. Installing ...')
    subprocess.check_call(["python", '-m', 'pip', 'install', 'geemap'])

# Google Colab
try: 
  import google.colab
  import geemap.eefolium as emap
except:
  import geemap as emap

# Authenticates and initializes Earth Engine
import ee

try:
    ee.Initialize()
except Exception as e:
    ee.Authenticate()
    ee.Initialize()<jupyter_output><empty_output><jupyter_text>Data<jupyter_code>world = gpd.read_file(gpd.datasets.get_path('naturalearth_lowres'))
europe = world.loc[world.continent == 'Europe'].reset_index(drop=True)
europe.head()

europe_stats = europe[["name", "pop_est", "gdp_md_est"]]
europe_stats.head()
europe_boundaries = europe[["name", "geometry"]]
europe_boundaries.head()<jupyter_output><empty_output><jupyter_text>Stafrt from Table JoinUse an attribute table join to merge data<jupyter_code>europe = europe_boundaries.merge(europe_stats, on="name")
europe.head()<jupyter_output><empty_output><jupyter_text>Spatial Join<jupyter_code>import pandas as pd
import numpy as np
from geopandas.tools import geocode

def my_geocoder(row):
  try:
    point = geocode(row, provider="nominatim").geometry.iloc[0]
    return pd.Series({"Latitude":point.y, "Longitude":point.x, "geometry": point})
  except:
    return None

universities = pd.read_csv("/content/drive/My Drive/Colab Notebooks/Geospatial/top_universities.csv")
universities[ ["Latitude", "Longitude", "geometry"] ] = universities.apply(lambda x: my_geocoder(x["Name"]), axis=1)
universities = universities.loc[~np.isnan(universities["Latitude"])]
universities = gpd.GeoDataFrame(universities, geometry=universities.geometry)
universities.head()
universities = universities.to_crs("epsg:4326")
print(universities.crs)
print(europe.crs)
european_universities = gpd.sjoin(universities, europe, how="inner", op="intersects")
m2 = emap.Map(center=[54,15], zoom=4)

for idx, row in european_universities.iterrows():
  emap.folium.Marker([row["Latitude"], row["Longitude"]], popup=row["Name"]).add_to(m2)

m2<jupyter_output><empty_output><jupyter_text>Starbucks<jupyter_code>starbucks = pd.read_csv("/content/drive/My Drive/Colab Notebooks/Geospatial/starbucks_locations.csv")
starbucks.isnull().sum()
import numpy as np
def my_geocoder(row):
  point = geocode(row, provider='nominatim').geometry[0]
  return pd.Series({"Longitude":point.x, "Latitude":point.y})

missing_locations = starbucks[starbucks["City"]=="Berkeley"].apply(lambda x: my_geocoder(x["Address"]), axis=1)
starbucks.update(missing_locations)
starbucks.isnull().sum()
m3 = emap.Map(center=[37.88, -122.26], zoom=13)

for idx, row in missing_locations.iterrows():
  emap.folium.Marker([ row["Latitude"], row["Longitude"]]).add_to(m3)

m3
CA_counties = gpd.read_file("/content/drive/My Drive/Colab Notebooks/Geospatial/CA_county_boundaries/CA_county_boundaries.shp")
CA_counties.head()
CA_pop = pd.read_csv("/content/drive/My Drive/Colab Notebooks/Geospatial/CA_county_population.csv", index_col="GEOID")
CA_high_earners = pd.read_csv("/content/drive/My Drive/Colab Notebooks/Geospatial/CA_county_high_earners.csv", index_col="GEOID")
CA_median_age = pd.read_csv("/content/drive/My Drive/Colab Notebooks/Geospatial/CA_county_median_age.csv", index_col="GEOID")
CA_counties.crs
cols = CA_pop.join([CA_high_earners, CA_median_age]).reset_index()
CA_stats = CA_counties.merge(cols, on="GEOID")
CA_stats.head()
CA_stats["density"] = CA_stats["population"] / CA_stats["area_sqkm"]<jupyter_output><empty_output><jupyter_text>Query ExampleNext, we create three DataFrames:- `CA_pop` contains an estimate of the population of each county.- `CA_high_earners` contains the number of households with an income of at least $150,000 per year.- `CA_median_age` contains the median age for each county.Collapsing all of the information into a single GeoDataFrame also makes it much easier to select counties that meet specific criteria.Use the next code cell to create a GeoDataFrame `sel_counties` that contains a subset of the rows (and all of the columns) from the `CA_stats` GeoDataFrame.  In particular, you should select counties where:- there are at least 100,000 households making \$150,000 per year,- the median age is less than 38.5, and- the density of inhabitants is at least 285 (per square kilometer).Additionally, selected counties should satisfy at least one of the following criteria:- there are at least 500,000 households making \$150,000 per year,- the median age is less than 35.5, or- the density of inhabitants is at least 1400 (per square kilometer).<jupyter_code># Your code here
sel_counties = CA_stats.loc[((CA_stats.high_earners > 100000) & 
                            (CA_stats.median_age < 38.5) & 
                            (CA_stats.density > 285) & 
                            ((CA_stats.high_earners > 500000) | 
                             (CA_stats.median_age < 35.5) | 
                             (CA_stats.density > 1400)))]
starbucks_gdf = gpd.GeoDataFrame(starbucks, geometry=gpd.points_from_xy(starbucks.Longitude, starbucks.Latitude))
starbucks_gdf = starbucks_gdf.set_crs(epsg=4326)
# When sjoin in Colab doesnt work...
!apt update
!apt upgrade
!apt install gdal-bin python-gdal python3-gdal 
import geopandas
num_stores = len(gpd.sjoin(starbucks_gdf, sel_counties))
print(num_stores)
# Create a base map
m_6 = emap.Map(center=[37,-120], zoom=6)

# Your code here: show selected store locations
selected_location = gpd.sjoin(starbucks_gdf, sel_counties)
for idx, row in selected_location.iterrows():
    emap.folium.Marker([ row["Latitude"], row["Longitude"]]).add_to(m_6)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12456111018224378, 'fraction_numerical': 0.01855876943654907, 'mean_word_length': 5.516339869281046, 'pattern_counts': {'":': 5, '<': 27, '<?xml version=': 0, '>': 29, 'https://': 0, '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': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '273822', 'license': 'MIT', 'n_tokens_mistral': 2156, 'n_tokens_neox': 2026, 'n_words': 455, 'path': 'Table_Join_Process.ipynb', 'repo_name': 'otwn/Geospatial-Data-Science-Examples', 'size': '5981'}