<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/'
# Load scenario parameters
params = excel.read_var(file='../input/parameters.xls', scenario=scenario)
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)
if 'route_type_disagg' in sm.pt_los.columns:
    sm.pt_los['route_type'] = sm.pt_los['route_type_disagg']<jupyter_output><empty_output><jupyter_text>Prices<jupyter_code># Generate length for PT links as the crow flies
if 'length' not in sm.links.columns:
    sm.links['length'] = skims.distance_from_geometry(sm.links['geometry']).astype(int)
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)
# Use second class single trip prices
# Choose a fit for a distance range up to 60 km (longer links are rare)
if manual:
    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)))
# Regression results
rail_short_slope = params['pt_price']['rail_short_slope']
rail_short_intercept = params['pt_price']['rail_short_intercept']
# Apply the slope to links
sm.links.loc[mask, 'price'] = sm.links.loc[mask, 'length'].apply(lambda d: rail_short_slope)
# Sum link cost
price_dict = sm.links['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'] += rail_short_intercept
# Add a price floor for short-distance rail (and cap for consistency)
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'],
    params['pt_price']['min_rail_short'],
    params['pt_price']['max_rail_short'])
# 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'] * params['pt_price']['rail_short_abo_share']
if manual:
    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>rl_dist_slope = params['pt_price']['rail_long_dist_slope']
rl_time_slope = params['pt_price']['rail_long_time_slope']
rl_intercept = params['pt_price']['rail_long_intercept']
# Apply the slope to links
mask = sm.links['route_type']=='rail_long'
sm.links.loc[mask, 'price'] = [d*rl_dist_slope + t*rl_time_slope
    for d,t in zip(sm.links.loc[mask, 'length'], sm.links.loc[mask, 'time'])]
# Sum link cost
price_dict = sm.links['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'] += rl_intercept
# 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'],
    params['pt_price']['min_rail_long'],
    params['pt_price']['max_rail_long'])
if manual:
    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. There are several sample studies available:* Average cost in Germany (0.107 EUR/pkm): https://de.statista.com/statistik/daten/studie/380601/umfrage/kilometerpreise-fernbuslinien-in-deutschland/* Average cost on selected OD pairs with cheapest prices: https://www.fernbusse.de/aktuelles/studie-fernbuspreise-steigen-leicht-bahntickets-guenstiger-1628/The latter is more realistic<jupyter_code># Load list of OD pair cost
if manual:
    c = pd.read_excel(input_path + 'level_of_service/coach_prices_2017.xls')
    c.head(3)
sm.links.loc[sm.links['route_type']=='coach', 'price'] = \
    sm.links['length'] / 1000 * params['pt_price']['coach']
# 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]))
# Add a price cap and floor
sm.pt_los.loc[sm.pt_los['route_type']=='coach', 'price'] = np.clip(
    sm.pt_los.loc[sm.pt_los['route_type']=='coach', 'price'],
    params['pt_price']['min_coach'],
    params['pt_price']['max_coach'])
if manual:
    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.xls', 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'] *= 60
# Take the economy price because its share is higher
# than 90% within Germany
air['trip'] = air['a'] + '-' + air['b']
air_price_dict = air.set_index('trip')['price_economy'].to_dict()
mask = sm.links['route_type']=='air'
sm.links.loc[mask, 'price'] = sm.links.loc[mask, 'trip_id'].map(air_price_dict)
#print(len(sm.links.loc[(mask) & (sm.links['price'].isna())]))
sm.links.loc[mask].sample()
# Sum up prices in LoS table based on link fares
price_dict = sm.links['price'].to_dict()
mask = sm.pt_los['route_type']=='air'
sm.pt_los.loc[mask, 'price'] = sm.pt_los.loc[mask, 'link_path'].apply(
    lambda l: sum([price_dict[link] if type_dict[link]
                   in ['air', 'rail_short', 'rail_long', 'coach'] else 0
                   for link in l]))
# Add intercepts of rail modes
sm.pt_los.loc[(mask) & (sm.pt_los['route_types'].apply(lambda l: 'rail_short' in l)),
              'price'] += rail_short_intercept
sm.pt_los.loc[(mask) & (sm.pt_los['route_types'].apply(lambda l: 'rail_long' in l)),
              'price'] += rail_long_intercept
sm.pt_los.loc[sm.pt_los['route_type']=='air', 'price'].mean()
len(sm.pt_los.loc[sm.pt_los['price'].isna()])<jupyter_output><empty_output><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: params['pt_price']['bus_urban1'],
                 2: params['pt_price']['bus_urban2'],
                 3: params['pt_price']['bus_urban3']}

# 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_d) & ~(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'])]
len(sm.pt_los.loc[sm.pt_los['price'].isna()])
sm.pt_los.groupby('route_type').agg(
    {'time': 'mean', 'price': 'mean', 'length': 'mean', 'ntransfers': 'mean'})<jupyter_output><empty_output><jupyter_text>Save<jupyter_code>sm.pt_los.sample()
sm.to_json(model_path + 'de_pt_los',
           only_attributes=['pt_los'],
           encoding='utf-8')<jupyter_output>to_hdf(overwriting): 100%|█████████████████████████████████████████████████████████████| 35/35 [00:08<00:00,  3.95it/s]
{'fraction_non_alphanumeric': 0.1789587852494577, 'fraction_numerical': 0.013557483731019523, 'mean_word_length': 5.531877213695395, 'pattern_counts': {'":': 0, '<': 36, '<?xml version=': 0, '>': 34, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '423305', 'license': 'MIT', 'n_tokens_mistral': 4247, 'n_tokens_neox': 3996, 'n_words': 816, 'path': 'notebooks/prep31_prices_pt.ipynb', 'repo_name': 'marlinarnz/quetzal_germany', 'size': '11064'}