Solium and GeoPandas for Regional Analysis

Question

Based on my previous question : pandas and melting of columns

here is again full code and output :

import pandas as pd
data =pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx",skiprows=[0])
# data.dropna(axis=0,inplace=True)
data.drop(["Year","Quarter","Total","Other regions"],axis=1,inplace=True)
data["Country"] ="Georgia"
data.columns.name = 'Regions'
data = pd.melt(data, value_name='Visitors').assign(Country='Georgia')
data = data[data["Regions"].str.contains("Country") == False]
data.dropna(axis=0,inplace=True)
data =data.groupby(["Regions","Country"])['Visitors'].sum().reset_index()
# data.columns =["Regions","Country","Total_Visitors"]
# data["Country"] ="Georgia"
print(data.head())

result is :

         Regions  Country     Visitors
0         Adjara A/R  Georgia  4885.150598
1            Imereti  Georgia  7453.780645
2            Kakheti  Georgia  3288.265509
3       Kvemo Kartli  Georgia  2920.524336
4  Mtskheta-Mtianeti  Georgia  2765.860204

now i decided to use folium and geopandas for displaying result on Map, here is my current code :

import pandas as pd
import folium
import geopandas
data =pd.read_excel("https://geostat.ge/media/52189/visits-by-visited-region.xlsx",skiprows=[0])
# data.dropna(axis=0,inplace=True)
data.drop(["Year","Quarter","Total","Other regions"],axis=1,inplace=True)
data["Country"] ="Georgia"
data.columns.name = 'Regions'
data = pd.melt(data, value_name='Visitors').assign(Country='Georgia')
data = data[data["Regions"].str.contains("Country") == False]
data.dropna(axis=0,inplace=True)
data =data.groupby(["Regions","Country"])['Visitors'].sum().reset_index()
world = geopandas.read_file(geopandas.datasets.get_path('naturalearth_lowres'))
table= world.merge(data ,how="left", left_on=['name'], right_on=['Country'])
table.dropna(axis=0,inplace=True)
# print(table.head())
earth =folium.Map()

folium.Choropleth(
    geo_data=table,
    name='choropleth',
    data=table,
    columns=['Regions','Visitors'],
    key_on='feature.properties.name',
    fill_color='OrRd',
    fill_opacity=0.7,
    line_opacity=0.2,
    legend_name='Visitors per Region'
).add_to(earth)
earth.save('visitors.html')

here is screenshot of visitors.html

i am not sure whether this result is correct or not -yes it is Georgia(Sakartvelo), but i want its regions, am i missing something?i was thing about getting coordinates or Latitude position and Longitude position ?actually for instance Kakheti Kakheti region

covers large area and maybe because of this it shows such black surface on georgia? i will try to select some regions and also please give me your recomendations

Answer 1

Following your approach, you need a dataset that hold the geographic info of Georgia's regions :

url = "https://github.com/wmgeolab/geoBoundaries/raw/905b0ba/" \
      "releaseData/gbOpen/GEO/ADM1/geoBoundaries-GEO-ADM1_simplified.geojson"

pgs = gpd.read_file(url)
pgs["shapeName"] = pgs["shapeName"].replace(
                              {"Adjara": "Adjara A/R", "–": "-"}, regex=True)

out = pgs.merge(data, left_on="shapeName", right_on="Regions")

m = folium.Map(location=[pgs.centroid.y.mean(), pgs.centroid.x.mean()], zoom_start=7)

folium.Choropleth(
    geo_data=out,
    name="choropleth",
    data=merged,
    columns=["Regions", "Visitors"],
    key_on="properties.shapeName",
    fill_color="RdGy",
    fill_opacity=0.8,
    line_opacity=0.8,
    legend_name="Visitors",
).add_to(m)

m.save("visitors.html")

Output :