<jupyter_start><jupyter_text>Preparation of the LoS tables Saves price and accessibility attribute to PT LoS table Needs PT LoS table and links<jupyter_code>input_path = '../input_static/'
output_path = '../output/'
model_path = '../model/'
los = stepmodel.read_json(model_path + 'de_pt_los')
sm = stepmodel.read_json(model_path + 'de_pt_network_agg')
sm.pt_los = los.pt_los
sm.links.sample(2)<jupyter_output><empty_output><jupyter_text>Prices<jupyter_code># Cost function
def monetary_cost(length, rf, vc, ac, al, ec, el, sf):
    """ calculate monetary cost of travelling one O-D realtion within the LoS table
    :param length: length of main leg in meter
    :param df: regression factor for length (to be calibrated beforehand)
    :param vc: distance-specific transport cost
    :param ac: access cost
    :param al: access link length (m)
    :param ec: egress cost
    :param el: egress link length (m)
    :param sf: sharing factor (e.g. occupancy factor of a car)
    :return: monetary cost"""
    return (math.pow(length, rf) * vc + ac * al + ec * el) / sf
# Generate length for PT links as the crow flies
if 'length' not in sm.links.columns:
    sm.links['length'] = sm.links['geometry'].apply(
        lambda l: geodesic(l.coords[0], l.coords[-1]).m)
sm.links['price'] = 0
type_dict = sm.links['route_type'].to_dict()<jupyter_output><empty_output><jupyter_text>Rail short-distanceShort-distance rail services have the price structure stated in the DB document. However, most people who use it have some kind of abonement and don't buy single trip tickets. cal10 shows that for trips under 50km distance there is a 50% share of permanent abonements. Moreover, it doesn't make sense to choose short-distance rail services on long distances. Still, if so, people choose the "Quer-durchs-Land-Ticket" for 42€.<jupyter_code>mask = sm.links['route_type']=='rail_short'
# Load list of distance-related cost
c = pd.read_csv(input_path + 'level_of_service_DB_2020_preisliste_fahrplanjahr_2020.csv')
c.head(3)
sm.links.loc[mask, 'length'].hist(bins=20, figsize=(3,3), log=True)
# Use second class single trip prices
# Choose a fit for a distance range up to 60 km (longer links are rare)
z = np.polyfit(c.loc[0:30, 'km'], c.loc[0:30, 'single_trip_second_class'], deg=1)
p = np.poly1d(z)
plt.plot(c.loc[0:30, 'km'], p(c.loc[0:30, 'km']), linestyle='--', marker=' ', label='Regression')
plt.plot(c.loc[0:30, 'km'], c.loc[0:30, 'single_trip_second_class'], linestyle='--', marker=' ', label='2nd class')
plt.legend()
print('y={}*x+{}'.format(np.round(z[0], 3), np.round(z[1], 3)))
# Apply the slope to links
sm.links.loc[mask, 'price'] = sm.links.loc[mask, 'length'].apply(lambda d: d*0.233/1000)
# Sum link cost
price_dict = sm.links.loc[mask, 'price'].to_dict()
sm.pt_los.loc[sm.pt_los['route_type']=='rail_short', 'price'] = \
    sm.pt_los.loc[sm.pt_los['route_type']=='rail_short', 'link_path'].apply(
        lambda l: sum([price_dict[link] if type_dict[link]=='rail_short' else 0
                       for link in l]))
# Add the intercept
sm.pt_los.loc[sm.pt_los['route_type']=='rail_short', 'price'] += 1.468
# Add a price cap and floor for short-distance rail
sm.pt_los.loc[sm.pt_los['route_type']=='rail_short', 'price'] = np.clip(
    sm.pt_los.loc[sm.pt_los['route_type']=='rail_short', 'price'], 5, 42)
# Add abonements share for short-distance rail
mask = (sm.pt_los['route_type']=='rail_short') & (sm.pt_los['length']<50000)
sm.pt_los.loc[mask, 'price'] = sm.pt_los.loc[mask, 'price'] / 2
x = sm.pt_los.loc[sm.pt_los['route_type']=='rail_short']['length'] / 1000
y = sm.pt_los.loc[sm.pt_los['route_type']=='rail_short']['price']
plt.scatter(x, y, marker='o', alpha=.01, label='price')
plt.legend()
len(sm.pt_los.loc[(sm.pt_los['route_type']=='rail_short') & (sm.pt_los['price']<5)])<jupyter_output><empty_output><jupyter_text>Rail long distanceLong-distance services have a complicated price structure that is not published by DB. Thus, we need to make assumptions based on a regression analysis of observation data (see prep32):* Rail transport is purely path-distance and -duration-dependent* Price dependency is linear* There is a price cap of 139 EUR* Assume 50/50 distribution of Sparpreis and Flexpreis<jupyter_code># Apply the slope to links
mask = sm.links['route_type']=='rail_long'
sm.links.loc[mask, 'price'] = [d*(0.00856735+0.07809195)/2/1000 + t*(7.0490619+9.13199971)/2/3600
    for d,t in zip(sm.links.loc[mask, 'length'], sm.links.loc[mask, 'time'])]
# Sum link cost
price_dict = sm.links.loc[mask, 'price'].to_dict()
sm.pt_los.loc[sm.pt_los['route_type']=='rail_long', 'price'] = \
    sm.pt_los.loc[sm.pt_los['route_type']=='rail_long', 'link_path'].apply(
        lambda l: sum([price_dict[link] if type_dict[link]=='rail_long' else 0
                       for link in l]))
# Add an intercept
sm.pt_los.loc[sm.pt_los['route_type']=='rail_long', 'price'] += (4.67+14.61)/2
# Add a price cap and floor for long-distance rail
sm.pt_los.loc[sm.pt_los['route_type']=='rail_long', 'price'] = np.clip(
    sm.pt_los.loc[sm.pt_los['route_type']=='rail_long', 'price'], 19, 139)
x = sm.pt_los.loc[sm.pt_los['route_type']=='rail_long']['length'] / 1000
y = sm.pt_los.loc[sm.pt_los['route_type']=='rail_long']['price']
plt.scatter(x, y, marker='o', alpha=.01, label='price')
plt.legend()<jupyter_output><empty_output><jupyter_text>CoachAssume a linear cost function based on distance. verage cost in Germany: https://de.statista.com/statistik/daten/studie/380601/umfrage/kilometerpreise-fernbuslinien-in-deutschland/<jupyter_code>sm.links.loc[sm.links['route_type']=='coach', 'price'] = \
    sm.links['length'] / 1000 * 0.107
# Sum up prices in LoS table based on link fares
price_dict = sm.links['price'].to_dict()
sm.pt_los.loc[sm.pt_los['route_type']=='coach', 'price'] = \
    sm.pt_los.loc[sm.pt_los['route_type']=='coach', 'link_path'].apply(
        lambda l: sum([price_dict[link] if type_dict[link]=='coach' else 0
                       for link in l]))
x = sm.pt_los.loc[sm.pt_los['route_type']=='coach']['length'] / 1000
y = sm.pt_los.loc[sm.pt_los['route_type']=='coach']['price']
plt.scatter(x, y, marker='o', alpha=.05, label='price')
plt.legend()<jupyter_output><empty_output><jupyter_text>AirMatch air links with prices from an online search in Sept. 2020<jupyter_code># Load connection list
air = pd.read_excel(input_path + 'public_transport/air_connections_germany.xlsx', engine='xlrd')
air = air.loc[air['direct']==True]
air['a'] = air['origin'].str[:3]
air['b'] = air['destination'].str[:3]
print('Average economy price: {} EUR'.format(air['price_economy'].mean()))
print('Average business price: {} EUR'.format(air['price_business'].mean()))
air[['price_business', 'price_economy', 'time']].set_index('time').plot(linestyle=' ', marker='o')
# Correct air time if neccessary
if sm.links.loc[sm.links['route_type']=='air', 'time'].mean() < 5*60:
    # Convert time to seconds
    sm.links.loc[sm.links['route_type']=='air', 'time'] = \
        sm.links.loc[sm.links['route_type']=='air', 'time'] * 60
# Air trips
# Take the economy price because its share is higher
# than 90% within Germany
air['trip'] = air['a'] + '-' + air['b']
price_dict = air.set_index('trip', drop=True)['price_economy'].to_dict()
sm.links.loc[sm.links['route_type']=='air', 'price'] = \
    sm.links.loc[sm.links['route_type']=='air', 'trip_id'].map(price_dict)
print(len(sm.links.loc[(sm.links['route_type']=='air') & (sm.links['price'].isna())]))
sm.links.loc[sm.links['route_type']=='air'].head(3)<jupyter_output>0<jupyter_text>Local transportLocal transport fares are added on top, as they are usually included into the rail ticket price or have a standalone price map dependent on ordinal distance classes.<jupyter_code># Add a fare for local transport services
# Get links with bus service at start or end of the trip
mode_dict = sm.links['route_type'].to_dict()
bus_at_o = sm.pt_los['link_path'].apply(lambda l: mode_dict[l[0]]=='bus' if len(l)>0 else False)
bus_at_d = sm.pt_los['link_path'].apply(lambda l: mode_dict[l[-1]]=='bus' if len(l)>0 else False)
only_bus = sm.pt_los['route_type']=='bus'
# Bus tickets are included in long-distance rail tickets
rail_long = sm.pt_los['route_types'].apply(lambda t: 'rail_long' in t)
# Define price structure by regional type
# Average value from ADAC for cities: https://www.adac.de/reise-freizeit/ratgeber/tests/oepnv-preise-vergleich/
region_dict = sm.zones['urbanisation'].to_dict()
region_prices = {1: 2.74, 2: 4, 3: 4}

# Case 1: Multimodal
sm.pt_los.loc[(bus_at_o) & ~(only_bus) & ~(rail_long), 'price'] += sm.pt_los.loc[
    (bus_at_o) & ~(only_bus) & ~(rail_long), 'origin'].map(region_dict).map(region_prices)
sm.pt_los.loc[(bus_at_d) & ~(only_bus) & ~(rail_long), 'price'] += sm.pt_los.loc[
    (bus_at_o) & ~(only_bus) & ~(rail_long), 'destination'].map(region_dict).map(region_prices)

# Case 2: Only bus
sm.pt_los.loc[only_bus, 'price'] = [
    region_prices[max(region_dict[o], region_dict[d])]
    for o,d in zip(sm.pt_los.loc[only_bus, 'origin'], sm.pt_los.loc[only_bus, 'destination'])]
sm.pt_los.groupby('route_type').agg(
    {'time': 'mean', 'price': 'mean', 'length': 'mean', 'ntransfers': 'mean'})
for t in sm.pt_los['route_type'].unique():
    x = sm.pt_los.loc[sm.pt_los['route_type']==t]['length']
    y = sm.pt_los.loc[sm.pt_los['route_type']==t]['price']
    try:
        z = np.polyfit(x, y, deg=1)
        p = np.poly1d(z)
        plt.plot(x, p(x), linestyle='--', marker=' ', label=t)
    except:
        plt.scatter(x, y, marker='o', alpha=.01, label=t)
plt.legend()<jupyter_output><empty_output><jupyter_text>Accessibility<jupyter_code>def accessibility(dist_origin, dist_destination, weight=20):
    '''calculate an accessibility metric based on distances
    :param dist_origin: distance to the next mean of transport from origin in km
    :param dist_destination: distance from the used mean of transport to destination in km
    :param weight: (optional) weight of distance values
    :return: accessibility factor on a range from 0 to 1'''
    return max(0, 1 - dist_origin/weight - dist_destination/weight)
# Take average weighted distances to the next PT stop
# They were computed in prep16 from census data
clusters = pd.read_csv('../input/spatial_census_refined.csv')
dist_col = 'weighted_dist'
mean_dist_col = 'mean_weighted_dist'
cent_col = 'cluster_center'
#pop_col = 'population'
# Convert to km
clusters[mean_dist_col] = clusters[mean_dist_col] / 1000
type_dict = {'rail_short_distance': 'rail_short', 'rail_long_distance': 'rail_long'}
clusters['route_type'].replace(type_dict, inplace=True)
mean_w_dist_dict = clusters.groupby(
    ['NUTS_ID', 'route_type']).agg(
    {mean_dist_col: 'mean'}).to_dict()[mean_dist_col]
# The accessibility is a metric from the mean weighted distance in km
sm.pt_los['accessibility'] = 1
# Divide into PT and all walk
walk = sm.pt_los.loc[sm.pt_los['link_path'].apply(lambda l: len(l)==0)]
pt = sm.pt_los.loc[sm.pt_los['link_path'].apply(lambda l: len(l)>0)]
link_type = sm.links['route_type'].to_dict()
def get_accessibility(o, d, l_path, weight=20):
    try:
        return accessibility(mean_w_dist_dict[o, link_type[l_path[0]]],
                             mean_w_dist_dict[d, link_type[l_path[-1]]],
                             weight)
    except KeyError:
        # No such type in this region
        return 0
pt['accessibility'] = [get_accessibility(*a) for a in tuple(
    zip(pt['origin'], pt['destination'], pt['link_path'])#, [30 for _ in range(len(pt))])
)]
pt.groupby('route_type').agg(
    {'accessibility': 'mean', 'origin': 'count'})
# The accessibility does not depend on the
# route_type columns of pt_los, but on the link path
pt.loc[pt['accessibility']>0].groupby('route_type').hist(
    column='accessibility', bins=100, figsize=(4,4), log=False)
sm.pt_los = pt.append(walk)<jupyter_output><empty_output><jupyter_text>Save<jupyter_code>print(sm.pt_los.columns)
print(sm.pt_los.shape)
sm.pt_los.sample(3)
sm.to_json(model_path + 'de_pt_los',
           only_attributes=['pt_los'],
           encoding='utf-8')
# Split links in graph and auxiliary information
# for file sizes being compatible with github's size limit
cols = ['link_sequence', 'route_id', 'time', 'trip_id', 'length', 'headway', 'price']
auxiliary = sm.links[cols]
sm.links.drop(cols, axis=1, inplace=True)
sm.to_json(model_path + 'de_pt_network_agg', only_attributes=[
    'zones', 'links', 'nodes', 'pt_route_types'],
           encoding='utf-8')
# Save auxiliary information seperately
auxiliary['index'] = auxiliary.index
auxiliary.reset_index(drop=True, inplace=True)
auxiliary.to_json(model_path + 'de_pt_network_agg/links_quetzaldata.json')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.16763642082088379, 'fraction_numerical': 0.018949520364837238, 'mean_word_length': 5.262432299359921, 'pattern_counts': {'":': 0, '<': 39, '<?xml version=': 0, '>': 40, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '245523', 'license': 'Apache-2.0', 'n_tokens_mistral': 4807, 'n_tokens_neox': 4514, 'n_words': 1046, 'path': 'quetzal_germany-master/notebooks/prep31_prices_acc_pt.ipynb', 'repo_name': 'avi-shake/Flensburg_Modeling', 'size': '12718'}