import pandas as pd
import geopandas as gpd
import altair as alt
#https://movement.uber.com/cities/berlin/downloads/speeds?lang=en-US&tp[y]=2019&tp[q]=2
uber_data= pd.read_csv(r"movement-speeds-quarterly-by-hod-berlin-2019-Q2.csv\movement-speeds-quarterly-by-hod-berlin-2019-Q2.csv")
uber_data.head()
year | quarter | hour_of_day | segment_id | start_junction_id | end_junction_id | osm_way_id | osm_start_node_id | osm_end_node_id | speed_kph_mean | speed_kph_stddev | speed_kph_p50 | speed_kph_p85 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 2019 | 2 | 0 | d0034ae2336f81ef5933a211f2ce2d979f0aff2a | 66081f9fe2860af2a498e1248334cb51d6ea5073 | 9c00a9375aeaba9c08f4f2fd507cc1808ba83b89 | 9932085 | 1236497729 | 1236497752 | 41.258 | 9.239 | 41.330 | 46.680 |
1 | 2019 | 2 | 23 | d0034ae2336f81ef5933a211f2ce2d979f0aff2a | 66081f9fe2860af2a498e1248334cb51d6ea5073 | 9c00a9375aeaba9c08f4f2fd507cc1808ba83b89 | 9932085 | 1236497729 | 1236497752 | 35.794 | 6.706 | 35.832 | 41.398 |
2 | 2019 | 2 | 22 | d0034ae2336f81ef5933a211f2ce2d979f0aff2a | 66081f9fe2860af2a498e1248334cb51d6ea5073 | 9c00a9375aeaba9c08f4f2fd507cc1808ba83b89 | 9932085 | 1236497729 | 1236497752 | 32.766 | 13.401 | 35.171 | 43.469 |
3 | 2019 | 2 | 23 | 277640ca389fc7f0fc8a583386e6063df80485f0 | ad26106a25d52c3409bd0165f05de3047a4e96b5 | 9c00a9375aeaba9c08f4f2fd507cc1808ba83b89 | 9932085 | 81398215 | 1236497752 | 34.507 | 4.639 | 35.207 | 38.160 |
4 | 2019 | 2 | 1 | 277640ca389fc7f0fc8a583386e6063df80485f0 | ad26106a25d52c3409bd0165f05de3047a4e96b5 | 9c00a9375aeaba9c08f4f2fd507cc1808ba83b89 | 9932085 | 81398215 | 1236497752 | 41.512 | 6.049 | 41.128 | 47.681 |
#http://overpass-turbo.eu/s/PVN
osm_links = gpd.read_file('berlin.geojson')
osm_links['osm_way_id']=osm_links['id'].str[4:]
osm_links.osm_way_id=osm_links.osm_way_id.astype(int)
osm_links.head(2)
id | @id | cycleway:right | highway | lanes | lit | maxspeed | name | oneway | postal_code | ... | after_construction:lanes | after_construction:parking:condition:right | after_construction:parking:lane:right | construction | construction:end_date | disused:cycleway:left | protected_bike_lane:right | cycleway:right:buffer | geometry | osm_way_id | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | way/4045243 | way/4045243 | track | primary | 3 | yes | 50 | Frankfurter Allee | yes | 10247 | ... | None | None | None | None | None | None | None | None | LINESTRING (13.45421 52.51571, 13.45439 52.515... | 4045243 |
1 | way/4045656 | way/4045656 | None | secondary | 2 | None | 50 | Hauptstraße | None | None | ... | None | None | None | None | None | None | None | None | LINESTRING (13.37282 52.59334, 13.37281 52.593... | 4045656 |
2 rows × 431 columns
osm_herm= osm_links[(osm_links.name=='Hermannstraße') | (osm_links.name=='Hermannbrücke')].copy()
uber_data_g=uber_data.groupby(['osm_way_id','hour_of_day'])['speed_kph_mean'].mean().reset_index().copy()
data_merg= pd.merge(osm_herm[['geometry','osm_way_id','name','postal_code', 'maxspeed']],uber_data_g[['speed_kph_mean','osm_way_id','hour_of_day']] )
data_merg.head()
geometry | osm_way_id | name | postal_code | maxspeed | speed_kph_mean | hour_of_day | |
---|---|---|---|---|---|---|---|
0 | LINESTRING (13.42487 52.48166, 13.42486 52.48128) | 4526452 | Hermannstraße | 12049 | 50 | 28.319 | 0 |
1 | LINESTRING (13.42487 52.48166, 13.42486 52.48128) | 4526452 | Hermannstraße | 12049 | 50 | 31.935 | 1 |
2 | LINESTRING (13.42487 52.48166, 13.42486 52.48128) | 4526452 | Hermannstraße | 12049 | 50 | 36.163 | 2 |
3 | LINESTRING (13.42487 52.48166, 13.42486 52.48128) | 4526452 | Hermannstraße | 12049 | 50 | 36.777 | 3 |
4 | LINESTRING (13.42487 52.48166, 13.42486 52.48128) | 4526452 | Hermannstraße | 12049 | 50 | 39.343 | 4 |
Disclaimer: in Q2 2019, war noch kein Tempo 30 in Hermannstraße angeordnet!!!!
source=data_merg
## main chart
chart= alt.Chart(source).mark_point().encode(
x=alt.X('hour_of_day', axis=alt.Axis(title='Time')),
y=alt.Y('speed_kph_mean:Q', axis=alt.Axis(title='Speed')),
color='maxspeed',
).properties(
width=700,
height=460,
)
## line
chart_line= alt.Chart(source).mark_line().encode(
x='hour_of_day',
y='mean(speed_kph_mean):Q',
color='maxspeed',
)
chart+chart_line
data_merg=data_merg.to_crs('epsg:3035')
C:\Users\Simon\Anaconda3\envs\geo_julab\lib\site-packages\pyproj\crs.py:77: FutureWarning: '+init=<authority>:<code>' syntax is deprecated. '<authority>:<code>' is the preferred initialization method. return _prepare_from_string(" ".join(pjargs))
#length of each link
data_merg['len']=data_merg.geometry.apply(lambda x: x.length)
data_merg['weight']=data_merg.len/(data_merg.len.sum()/24)
data_merg['weighted_speed']=data_merg.weight*data_merg.speed_kph_mean
data_merg['weighted_speed_mean']=data_merg.groupby(['hour_of_day'])['weighted_speed'].transform('sum')
data_merg.head()
geometry | osm_way_id | name | postal_code | maxspeed | speed_kph_mean | hour_of_day | len | weight | weighted_speed | weighted_speed_mean | |
---|---|---|---|---|---|---|---|---|---|---|---|
0 | LINESTRING (4553585.112 3269069.619, 4553586.9... | 4526452 | Hermannstraße | 12049 | 50 | 28.319 | 0 | 42.347656 | 0.012485 | 0.353571 | 37.680660 |
1 | LINESTRING (4553585.112 3269069.619, 4553586.9... | 4526452 | Hermannstraße | 12049 | 50 | 31.935 | 1 | 42.347656 | 0.012485 | 0.398718 | 39.135229 |
2 | LINESTRING (4553585.112 3269069.619, 4553586.9... | 4526452 | Hermannstraße | 12049 | 50 | 36.163 | 2 | 42.347656 | 0.012485 | 0.451506 | 40.349164 |
3 | LINESTRING (4553585.112 3269069.619, 4553586.9... | 4526452 | Hermannstraße | 12049 | 50 | 36.777 | 3 | 42.347656 | 0.012485 | 0.459172 | 40.783351 |
4 | LINESTRING (4553585.112 3269069.619, 4553586.9... | 4526452 | Hermannstraße | 12049 | 50 | 39.343 | 4 | 42.347656 | 0.012485 | 0.491209 | 41.464624 |
source=data_merg
## POINTS: Straßenabschnitte je h
chart_point= alt.Chart(source).mark_point(opacity=0.3).encode(
x=alt.X('hour_of_day', axis=alt.Axis(title='Time [h]')),
y=alt.Y('speed_kph_mean:Q', axis=alt.Axis(title='Speed [km/h]')),
size=alt.Size('len',legend=alt.Legend(title="Länge Straßenabschnitte [m]")),
).properties(
width=700,
height=460,
#title='(gewichtete) Geschwindigkeitsverteilung in der Hermannstraße 2019-Q2'
)
## LIN: gewichtete durchschnittliche Geschwindigkeit je h
source['text9']=''
chart_line= alt.Chart(source).mark_line().encode(
x='hour_of_day',
y='weighted_speed_mean:Q',
color=alt.Color('text9',legend=alt.Legend(title="gewichtete Ø-Geschw."))
)
## style1
source['line_pos_y']=50
source['text1']='aktuelle Geschwindigkeitsbegrenzung'
source['text_pos']=12#source.speed_kph_mean.max()*0.8
rule = alt.Chart(source).mark_rule(color='red', opacity=0.3).encode(
y='line_pos_y',
size=alt.value(0.1)
)
text=alt.Chart(source).mark_text(color='red', baseline='top', opacity=0.7).encode(
y='line_pos_y', #alt.value(50)
x='text_pos',
text='text1')
## style2
source['line_pos_y2']=30
source['text2']='mögliche Geschwindigkeitsbegrenzung?'
source['text_pos2']=4#source.speed_kph_mean.max()*0.8
rule2 = alt.Chart(source).mark_rule(color='green', opacity=0.3).encode(
y='line_pos_y2',
size=alt.value(0.1)
)
text2=alt.Chart(source).mark_text(color='green', baseline='top', opacity=0.7).encode(
y='line_pos_y2',
x='text_pos2',
text='text2')
chart=chart_point+chart_line + (rule+text)+ (rule2+text2)
# adding Titel and Subtitle
title = alt.Chart(
{"values": [{"text": "Geschwindigkeitsverteilung in der Hermannstraße/Berlin 2019-Q2"}]}
).mark_text(size=20, align='left').encode( #,anchor='start' , align='left'
text="text:N",
x=alt.value(0)
)
subtitle = alt.Chart(
{"values": [{"text": 'Basierend auf veröffentlichten Uber-Geschwindigkeitsdaten je h je OSM-Straßenabschnitt'}]}
).mark_text(size=11, align='left').encode(
text="text:N",
x=alt.value(0)
#x=alt.value(100)
# xOffset=-20
)
fullchart_title= alt.vconcat(
title,
subtitle,#
chart
).configure_view(
stroke=None
).configure_concat(
spacing=1
)
#export
fullchart_title.save('output/herm/chart_herm.html')
fullchart_title
import folium
import branca
## TEST
#
#df= data_merg[data_merg.hour_of_day==12].copy()
#df.maxspeed=df.maxspeed.astype(int)
#
#field='speed_kph_mean'
#
#
#colorscale = branca.colormap.LinearColormap(['green','lightgreen','orange','red'],
# index=[20,30,40,50],
# vmin=20,#df.speed_kph_mean.min(),
# vmax=50,#df.speed_kph_mean.max(),
# caption='')
#def style_function(feature):
# col=feature['properties'][field]
# return {
# 'weight': 3,
# 'color': 'grey' if col is None else colorscale(col)
# }
#
#m = folium.Map(location=[52.46, 13.45],
# tiles="cartodbpositron",
# name="CartoDB light",
# zoom_start=14)
#
#folium.GeoJson(
# df,
# style_function=style_function,
#).add_to(m)
#
#
#colorscale.caption = 'Speed [km/h]'
#m.add_child(colorscale)
#
#m
# helper
import folium.plugins
from folium.features import *
class DivIcon(MacroElement):
def __init__(self, html='', size=(30,30), anchor=(0,0), style=''):
"""TODO : docstring here"""
super(DivIcon, self).__init__()
self._name = 'DivIcon'
self.size = size
self.anchor = anchor
self.html = html
self.style = style
self._template = Template(u"""
{% macro header(this, kwargs) %}
<style>
.{{this.get_name()}} {
{{this.style}}
}
</style>
{% endmacro %}
{% macro script(this, kwargs) %}
var {{this.get_name()}} = L.divIcon({
className: '{{this.get_name()}}',
iconSize: [{{ this.size[0] }},{{ this.size[1] }}],
iconAnchor: [{{ this.anchor[0] }},{{ this.anchor[1] }}],
html : "{{this.html}}",
});
{{this._parent.get_name()}}.setIcon({{this.get_name()}});
{% endmacro %}
""")
for h in uber_data.hour_of_day.unique():
# Tempo 30
#get ready
uber_data_h=uber_data[uber_data.hour_of_day==h].copy()
uber_data_h=uber_data_h.groupby('osm_way_id')['speed_kph_mean'].mean().reset_index().copy()
osm_herm= osm_links[(osm_links.name=='Hermannstraße') | (osm_links.name=='Hermannbrücke')].copy()
#merge uber and osm
data_merg= pd.merge(osm_herm[['geometry','osm_way_id','name','postal_code', 'maxspeed']],uber_data_h[['speed_kph_mean','osm_way_id']] )
data_merg.maxspeed= data_merg.maxspeed.apply(lambda x: 50 if x=='DE:urban' else x)
data_merg.maxspeed=data_merg.maxspeed.astype(float)
df=data_merg
#df= data_merg[data_merg.hour_of_day==h].copy()
#df.maxspeed=df.maxspeed.astype(int)
field='speed_kph_mean'
colorscale = branca.colormap.LinearColormap(['green','lightgreen','orange','red'],
index=[20,30,40,50],
vmin=20,#df.speed_kph_mean.min(),
vmax=50,#df.speed_kph_mean.max(),
caption='')
def style_function(feature):
col=feature['properties'][field]
return {
'weight': 3,
'color': 'grey' if col is None else colorscale(col)
}
m = folium.Map(location=[52.475, 13.43],
tiles="cartodbpositron",
name="CartoDB light",
zoom_start=15)
folium.GeoJson(
df,
style_function=style_function,
).add_to(m)
folium.map.Marker(
#[34.0302, -118.2352],
#[52.449463, 13.404811],
[52.47149,13.41880],
icon=DivIcon(
size=(150,36),
anchor=(150,0),
html=str(h)+' Uhr',
style="""
font-size:36px;
background-color: transparent;
border-color: transparent;
text-align: right;
"""
)
).add_to(m)
colorscale.caption = 'Speed [km/h]'
m.add_child(colorscale)
#m
m.save("output/herm/map_"+str(h)+".html")
from selenium import webdriver #requires chromesdriver
from PIL import Image
import os
options = webdriver.ChromeOptions()
#options.add_argument('--headless')
driver = webdriver.Chrome(options=options)
for h in uber_data.hour_of_day.unique():
driver.get(os.getcwd() + '\\output\\herm\\map_'+str(h)+'.html')
driver.save_screenshot('output\\herm\\map_'+str(h)+'.png')
# newImg = Image.open('map_'+str(h)+'.png')
# newImg.save('map_'+str(h)+'_compressed.png', 'PNG', dpi=[200,200])
driver.close()
import glob
filenames= glob.glob("output\\herm\\map*.png")
# sort by hour
import re
def atoi(text):
return int(text) if text.isdigit() else text
def natural_keys(text):
return [ atoi(c) for c in re.split(r'(\d+)', text) ]
filenames.sort(key=natural_keys)
print(filenames)
['output\\herm\\map_0.png', 'output\\herm\\map_1.png', 'output\\herm\\map_2.png', 'output\\herm\\map_3.png', 'output\\herm\\map_4.png', 'output\\herm\\map_5.png', 'output\\herm\\map_6.png', 'output\\herm\\map_7.png', 'output\\herm\\map_8.png', 'output\\herm\\map_9.png', 'output\\herm\\map_10.png', 'output\\herm\\map_11.png', 'output\\herm\\map_12.png', 'output\\herm\\map_13.png', 'output\\herm\\map_14.png', 'output\\herm\\map_15.png', 'output\\herm\\map_16.png', 'output\\herm\\map_17.png', 'output\\herm\\map_18.png', 'output\\herm\\map_19.png', 'output\\herm\\map_20.png', 'output\\herm\\map_21.png', 'output\\herm\\map_22.png', 'output\\herm\\map_23.png']
import imageio
images = []
for filename in filenames:
images.append(imageio.imread(filename))
imageio.mimsave('output\\herm\\uber_berlin2019Q2_herm_24h.gif', images, format='GIF', duration=0.2)
#imageio.mimsave(exportname, frames, format='GIF', duration=5)
#compress gif with gifsicle (install gifsicle first)
os.system("C:\ProgramData\gifsicle-1.92-win64\gifsicle-1.92\gifsicle.exe -O3 --colors 256 output\\herm\\uber_berlin2019Q2_herm_24h.gif -o output\\herm\\uber_berlin2019Q2_herm_24h.gif")
0