Import Excel data to Python for generating a heatmap

Question

I would need some help! By now i used this short code to design a map with folium: pyton_folium_test.txt

The next step for me is to learn how to import positions like this from an excel file, but here i got stuck the last two days. I tried to do the import with pandas, xlrd and also OPENPYXL, but i dont fit to the end. Am i right here to find help for it? can someone help me with that?

Excel below will be an example file to import and the screenshot is how the result should look like. Eingabe_Python.xlsx

#Allgemeine Weltkarte
import pandas as pd
import geopandas
import matplotlib.pyplot as plt

##GPS-Daten und Werte
#Hüttensand-Quellen
q = pd.DataFrame(
    {'City': ['Voestalpine Donawitz'],
     'Amount': [150000],
     'Latitude': [47.37831193777984],
     'Longitude': [15.066798524137285]})

gq = geopandas.GeoDataFrame(
    q, geometry=geopandas.points_from_xy(q.Longitude, q.Latitude))

print(gq.head())



#Hüttensand-Bedarf
b = pd.DataFrame(
    {'City': ['Retznei-Zementwerk', 'Peggau-Zementwerk'],
     'Amount': [ 98741, 78908],
     'Latitude': [ 46.74156539750959, 47.22606763599665],
     'Longitude': [ 15.574118966270278, 15.346740145512106]})

gb = geopandas.GeoDataFrame(
    b, geometry=geopandas.points_from_xy(b.Longitude, b.Latitude))


print(gb.head())

#Plot Österreich Karte mit Punkten
world = geopandas.read_file(geopandas.datasets.get_path('naturalearth_lowres'))

ax = world[world.name == 'Germany'].plot(
    color='white', edgecolor='black')

gq.plot(ax=ax, color='red', legend=True, legend_kwds={'label':'City'})
gb.plot(ax=ax, color='blue')


plt.show()

#Heatmap
import folium
from folium.plugins import HeatMap

max_amount = float(gb['Amount'].max() and gq['Amount'].max())
print('der maximale Wert ist:',max_amount)


hmap = folium.Map(location=[47.070714, 15.439504], zoom_start=8,control_scale=True )


hm_quellen = HeatMap( data=gq[['Latitude', 'Longitude','Amount']],
                   min_opacity=0.3,
                   max_val=max_amount,
                   gradient = {.3: 'yellow', .6: 'orange', 1: 'red'},
                   radius=20, blur=10,
                   max_zoom=5,

                 )

hmap.add_child(hm_quellen)

hm_bedarf = HeatMap( data=gb[['Latitude', 'Longitude','Amount']],
                   min_opacity=0.3,
                   max_val=max_amount,
                   gradient = {.3: 'turquoise', .6: 'blue',  1: 'grey'},
                   radius=20, blur=15, 
                   max_zoom=1, 
                 )

hmap.add_child(hm_bedarf)

#Markierungen
# Quellen
folium.Marker([47.37831193777984, 15.066798524137285],
              popup=folium.Popup('integrierte Hüttenwerk - Donawitz',show=True)).add_to(hmap)


# Bedarf
folium.Marker([46.74156539750959, 15.574118966270278],
              popup=folium.Popup('Zementwerk - Retznei',show=True)).add_to(hmap)

folium.Marker([47.22606763599665, 15.346740145512106],
              popup=folium.Popup('Zementwerk - Peggau',show=True)).add_to(hmap)



#karte speichern
import os
hmap.save(os.path.join(r'C:\Users\stefa\Desktop\Bachelorarbeit\Daten_Python', 'zement_heatmap.html'))

Answer 1

A possible full solution. I have split out the data acquisition as a separate task from the data display. Since each one of the 'groups' (Quelle, Bedarf etc) has a different colour scheme, I've added a dictionary to hold this information (this could come from a separate configuration file).

Since I don't have GeoPandas (the installation is not simple on Windows), I have commented those lines out: they should work, but I cannot test.

import pandas as pd
#import geopandas
#import matplotlib.pyplot as plt
import folium
from folium.plugins import HeatMap

def LoadData(filename):
    #Read in 1st sheet of Excel file
    dfLocations = pd.read_excel(filename)

    GroupData = {}
    fields = []
    max_amount = 0.0

    #Run through the column headers
    #to extract all the unique field names and groups
    for hdr in dfLocations.columns:
        parts = hdr.split('_')
        if( len(parts) ) > 1: #column names not in Field_Group format
            if( parts[0] not in fields ):
                fields.append(parts[0])
            GroupData[parts[1]]=None

    #Now parse the data, group by group
    for r in GroupData:
        df = dfLocations[[ fld + '_' + r for fld in fields]].dropna().set_axis(fields, axis=1, inplace=False)
        df.rename(columns={'Name':'City'},inplace=True)
        max_amount = max(max_amount,df.Amount.max())
        GroupData[r] = df

    return GroupData,max_amount

dictGroups,max_amount = LoadData('ImportFile.xlsx')

#Set up colour schemes for plot, markers and heatmaps
#NB: need one line for every different group
colourScheme = [{'loc': 'red','heatmap': {.3: 'yellow', .6: 'orange', 1: 'red'},'radius':20,'blur':10 },
                {'loc': 'blue','heatmap': {.3: 'turquoise', .6: 'blue',  1: 'grey'},'radius':20,'blur':15}]

if len(colourScheme) < len(dictGroups):
    raise ValueError('Insufficient Colour Scheme entries for the number of Groups')
    
#world = geopandas.read_file(geopandas.datasets.get_path('naturalearth_lowres'))
#ax = world[world.name == 'Germany'].plot(color='white', edgecolor='black')

hmap = folium.Map(location=[47.070714, 15.439504], zoom_start=8,control_scale=True )

nGroup = 0
for r in dictGroups:
    dfLocations = dictGroups[r]
    scheme = colourScheme[nGroup]

    #gdf = geopandas.GeoDataFrame(dfLocations, geometry=geopandas.points_from_xy(dfLocations.Longitude, dfLocations.Latitude))
    #gdf.plot(ax=ax,color=scheme['loc'],legend=True,legend_kwds={'label':'City'})

    hmap.add_child(HeatMap( data=dfLocations[['Latitude', 'Longitude','Amount']],
                            min_opacity=0.3,
                            max_val=max_amount,
                            gradient = scheme['heatmap'],
                            radius=scheme['radius'], blur=scheme['blur'],
                            max_zoom=5 ) )

    for idx,location in dfLocations.iterrows():
        folium.Marker([location.Latitude,location.Longitude],
                      popup = folium.Popup(location.City,show=True)).add_to(hmap)
    nGroup += 1

#plt.show()
hmap.save('zement_heatmap.html')