将Excel数据导入Python以生成热图

Question

我需要一些帮助！到目前为止，我使用以下简短代码设计了一张带有folium的地图： pyton_folium_test.txt

对我来说，下一步是学习如何从excel文件导入这样的职位，但在这里，我在过去的两天中遇到了麻烦。我试着用pandas、xlrd和OPENPYXL进行导入，但最终我还是做不到。我是来找人帮忙的吗？有人能帮我吗

下面的Excel将是一个导入的示例文件，屏幕截图是结果的样子。 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

一个可能的完整解决方案。我将数据采集作为一项独立于数据显示的任务。由于每个“组”（Quelle、Bedarf等）都有不同的配色方案，我添加了一个字典来保存这些信息（可能来自单独的配置文件）

由于我没有GeoPandas（在Windows上安装并不简单），我已经注释掉了这些行：它们应该可以工作，但我无法测试

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')