<jupyter_start><jupyter_text>Calibration Preparation of input dataThis notebook prepares input data from the dataset "[Mobilität in Deutschland 2017](http://www.mobilitaet-in-deutschland.de/) B2" (MiD2017) for model calibration. The German Federal Ministry of Transport and Digital Infrastructure is copyright holder of this dataset and does not allow any form of publication.<jupyter_code>input_path = '../input/transport_demand/'<jupyter_output><empty_output><jupyter_text>What are calibration data requirements?The mathematical formulation of the discrete choice model depends on LoS attributes travel time and price. All parameters are demand group specific. The model formulation for each mode is the same, even though some variables are zero. The MiD2017 trips table builds the calibration dataset with the following columns:*	start cell (KKZ_SO)*	end cell (KKZ_ZO)*	cell urbanisation (SKTYP)*	trip distance [km] (wegkm_imp)*	trip distance routed MiD grouped [km] (km_routing_gr)*	trip duration [min] (wegmin_imp1)*	trip purpose (hwzweck1); return trips reference the original trip purpose, holiday is not specified but part of private*	main mode (hvm_diff2)*	mode combination (vm_kombi)*	number of travellers (anzpers)*	car available in household (auto)*	public transport ticket type or subscription (P_FKARTE)*	mode affinity of person (multimodal)*	distance to the next train station from home (bahn28)*	distance to the next bus stop from home (bus28)*	distance to the next train station at destination (bahn28_zo)*	distance to the next bus stop at destination (bus28_zo)Rows (trips) are filtered for the following criteria:1.	Only trips with detailed specifications2.	Only inner-German trips3.	Only over-regional traffic (trip start cell different to end cell) 4.	Value of each column is specified5.  Remove ferries<jupyter_code>trips = pd.read_csv(input_path + 'MiD2017_Regional_Wege.csv', sep=';')
print(trips.shape)
# Number of persons in the survey
len(trips['HP_ID_Reg'].unique())
trips = trips[['KKZ_SO', 'KKZ_ZO', 'SKTYP', 'wegkm_imp', 'km_routing_gr',
               'wegmin_imp1', 'hwzweck1', 'hvm_diff2', 'vm_kombi', 'anzpers',
               'auto', 'P_FKARTE', 'multimodal', 'bahn28', 'bus28', 'bahn28_zo',
               'bus28_zo', 'W_DETAIL', 'W_SO2']]
trips.columns = ['origin', 'destination', 'SKTYP', 'distance', 'distance_routed',
                 'duration', 'purpose', 'mode', 'mode_combo', 'n_persons',
                 'car_avail', 'ticket_type', 'mode_affinity', 'dist_train_origin',
                 'dist_bus_origin', 'dist_train_destination', 'dist_bus_destination',
                 'W_DETAIL', 'W_SO2']
trips['distance'] = pd.to_numeric(trips['distance'].str.replace(',', '.'))
#trips.head(10)
# Filter for trip details
trips = trips.loc[trips['W_DETAIL']!=0]
print(trips.shape)
# Filter for inner-German trips
trips = trips.loc[(trips['W_SO2']!=2) & (trips['W_SO2']!=3)]
print(trips.shape)
# Filter unspecified rows
trips = trips.loc[(trips['purpose']!=99) &
                  (trips['origin']<99995) &
                  (trips['destination']<99995) &
                  (trips['mode']<24) &
                  (trips['distance_routed']!=708)]
trips = trips.loc[(trips['origin']>1) &
                  (trips['destination']>1)]
print(trips.shape)
# Filter for inter-zonal traffic
trips = trips.loc[trips['origin']!=trips['destination']]
print(trips.shape)
# Filter for longer trips than 10km (taking routed distance)
trips = trips.loc[trips['distance_routed']>5]
print(trips.shape)
# Filter for longer trips than 2km stated
trips = trips.loc[trips['distance']>=2]
print(trips.shape)
# Filter for longer trips than 5min stated
trips = trips.loc[trips['duration']>=5]
print(trips.shape)
# Remove air travel
#trips = trips.loc[trips['mode']!=23]
#print(trips.shape)
# Remove ferries
trips = trips.loc[trips['mode']!=19]
print(trips.shape)
# Make unknown number of persons equal to one
trips.loc[trips['n_persons']>6] = 1
# Convert distances to integer kilometers
dist_dict = {1:1, 2:1, 3:1, 4:2, 5:4, 6:6, 95:20, 701:20}
trips['dist_train_origin'] = trips['dist_train_origin'].map(dist_dict)
trips['dist_bus_origin'] = trips['dist_bus_origin'].map(dist_dict)
trips['dist_train_destination'] = trips['dist_train_destination'].map(dist_dict)
trips['dist_bus_destination'] = trips['dist_bus_destination'].map(dist_dict)
# Convert routed distances to integer km
#routed_dist_dict = {1:1, 2:1, 3:1, 4:4, 5:8, 6:15, 7:35, 8:75, 9:100, 95:0, 701:0, 703:0, 706:0, 708:0}
#trips['distance_routed'] = trips['distance_routed'].map(routed_dist_dict)
trips = trips.loc[trips['origin']!=1] # pandas error
# Align regional specifics with urbanisation degree
urbanisation_dict = {1:1, 2:2, 3:3, 4:3}
trips['urbanisation'] = trips['SKTYP'].map(urbanisation_dict)
# Match trip purpose with VP2030 categories
# Note: Holiday trips are included in private (6)
purpose_mid_vp_dict = {1: 1, 2: 4, 3: 2, 4: 3, 5: 3, 6: 6, 7: 6, 99: 6}
trips['purpose_vp'] = trips['purpose'].map(purpose_mid_vp_dict)
# Match mode with the model modes
# Non-motorised modes are merged
model_modes = {1: 'rail_short', 2: 'rail_long',
               3: 'coach', 4: 'bus', 5: 'air', 6: 'car', 7: 'walk'}
mode_mid_vp_dict = {1:7, 2:7, 3:7, 4:6, 5:6, 6:6, 7:6, 8:6,
                    9:6, 10:6, 11:6, 12:4, 13:4, 14:4, 15:4,
                    16:1, 17:6, 18:6, 19:6, 20:2, 21:3, 22:3,
                    23:5, 24:6}
trips['mode_model'] = trips['mode'].map(mode_mid_vp_dict)<jupyter_output><empty_output><jupyter_text>Save dataset<jupyter_code>trips.to_csv(input_path + 'calibration_inter-cellular_trips_MiD2017.csv')
assert manual<jupyter_output><empty_output><jupyter_text>Visualise the dataset specificsNote: This dataset does not represent average mobility behaviour but trips that are selected to fit the research question of the model being built<jupyter_code># Average speed in km/h
t = trips.copy()
t['speed'] = t['distance'] / (t['duration'] / 60)
t.groupby('mode_model').agg({'speed': 'mean'})
# Average travel group size
trips.groupby(['mode_model', 'purpose_vp']).agg({'n_persons': 'mean'}).unstack('purpose_vp')
# Distance-dependency of occupation factors
fig, ax = plt.subplots(figsize=(16,5))
for mode in trips['mode_model'].unique():
    x = trips.loc[trips['mode_model']==mode, 'distance']
    y = trips.loc[trips['mode_model']==mode, 'n_persons']
    #plt.scatter(x, y, marker='x', alpha=.1, label=mode)
    z = np.polyfit(x, y, deg=1)
    p = np.poly1d(z)
    plt.plot(x, p(x), linestyle='--', marker='', label='trend '+model_modes[mode])
    #print('{}: y={}*x+{}'.format(model_modes[mode], np.round(z[0], 4), np.round(z[1], 3)))
plt.legend()
# Sample size per trip purpose
trips.groupby('purpose_vp').size().plot.bar(
    figsize=(5, 3), title='Sample size by trip purpose (VP2030)',
    legend=False)<jupyter_output><empty_output><jupyter_text>DistanceThis is relevant for calculation of trip cost<jupyter_code># Trip distance
trips['distance'].plot.hist(bins=100, log=True,
                            title='Trip distance distribution, logarithmic')
# Distance validation
# The routed distance is intended to validate the MiD2017 respondent's distance estimations
# High standard deviation is a bad sign for the respondent's estimation capabilities but
# contributes to the validity of the distance-related calibration factor of the model estimation.
# We can only validate categories 1 to 75.
trips.groupby('distance_routed').agg({'distance':'mean'})
# Modal split by distance group
# Group 0 has no information
axs = trips.groupby(['mode_model','distance_routed']).size().unstack(fill_value=0).plot.pie(
    subplots=True, figsize=(16, 4), labels=[m for _, m in model_modes.items()],
    startangle=0, counterclock=False, legend=None, labeldistance=None)
axs[0].legend()<jupyter_output>C:\Users\marlin.arnz\AppData\Local\Continuum\miniconda3\envs\quetzal\lib\site-packages\pandas\plotting\_matplotlib\tools.py:307: MatplotlibDeprecationWarning: 
The rowNum attribute was deprecated in Matplotlib 3.2 and will be removed two minor releases later. Use ax.get_subplotspec().rowspan.start instead.
  layout[ax.rowNum, ax.colNum] = ax.get_visible()
C:\Users\marlin.arnz\AppData\Local\Continuum\miniconda3\envs\quetzal\lib\site-packages\pandas\plotting\_matplotlib\tools.py:307: MatplotlibDeprecationWarning: 
The colNum attribute was deprecated in Matplotlib 3.2 and will be removed two minor releases later. Use ax.get_subplotspec().colspan.start instead.
  layout[ax.rowNum, ax.colNum] = ax.get_visible()
C:\Users\marlin.arnz\AppData\Local\Continuum\miniconda3\envs\quetzal\lib\site-packages\pandas\plotting\_matplotlib\tools.py:313: MatplotlibDeprecationWarning: 
The rowNum attribute was deprecated in Matplotlib 3.2 and will be removed two minor releases later. Use ax.get_subplotspec()[...]<jupyter_text>DurationUsually the most influencive factor for mode choice<jupyter_code># Trip duration
trips['duration'].plot.hist(bins=100, log=True, title='Trip duration distribution, logarithmic')<jupyter_output><empty_output><jupyter_text>The majority of trips is under 1h but the distribution is less steep than the one of distance. Unregularities come from the respondent's tendency to state full, half or quarter hours.<jupyter_code># Modal split by duration
t['bins'] = pd.cut(t['duration'], bins=[0,15,30,60,120,360,500],
                   labels=["0-15", "16-30","31-60","61-120","120-360","361+"])
axs = t.groupby(['mode_model','bins']).size().unstack(fill_value=0).plot.pie(
    subplots=True, figsize=(16, 3), labels=[m for _, m in model_modes.items()],
    startangle=0, counterclock=False, title='Modal split by duration in minutes',
    legend=False, labeldistance=None)
axs[0].legend()
# Distribution
t.groupby('bins').size().plot.pie(subplots=False,figsize=(3, 3),
                                  title='Duration in minutes')
                                  #colormap=rli)
#plt.savefig('figd.png', dpi=300)<jupyter_output><empty_output><jupyter_text>Trip purposeAdjusted to VP2030 with holiday trips being part of private<jupyter_code># Trip purpose
trips.groupby('purpose_vp').size().plot.pie(subplots=False,figsize=(3, 3),
                                           labels=['work', 'education', 'shopping', 'official', 'private'],
                                           title='share of trips by purpose')
# Modal split
axs = trips.groupby(['mode_model','purpose_vp']).size().unstack(fill_value=0).rename(
    columns={1:'work', 2:'education', 3:'shopping', 4:'official', 6:'private'}).plot.pie(
    subplots=True, figsize=(16, 4), labels=[m for _, m in model_modes.items()],
    startangle=0, counterclock=False, title='Modal split by trip purpose',
    legend=False, labeldistance=None)
axs[0].legend()
# Car availability
trips.groupby(['car_avail','purpose_vp']).size().unstack(fill_value=0).rename(
columns={1:'work', 2:'education', 3:'shopping', 4:'official', 6:'private'}).plot.pie(
    subplots=True, figsize=(16, 1.5), labels=['no', 'yes', 'unknown'],
    labeldistance=None, startangle=0, counterclock=False, title='Car availability by trip purpose')
trips['car_avail'].replace({9:0}, inplace=True)
trips.groupby(['car_avail','purpose_vp']).size().unstack(fill_value=0) /\
    trips.groupby('purpose_vp').count()['origin']<jupyter_output><empty_output><jupyter_text>Ticket typesWhich kind of ticket do people use for public transport?<jupyter_code># PT ticket type
axs = trips.loc[trips['mode_model'].isin([1,2,3,4])].groupby(
    ['ticket_type', 'mode_model']).size().unstack(fill_value=0).rename(
    columns={i: model_modes[i] for i in range(1,5)}).plot.pie(
    subplots=True, figsize=(15, 5), labels=['single', 'multiple', 'week/month',
                                           'month/year abo', 'other abo', 'other',
                                           'car driver', 'unknown', 'unknown'],
    labeldistance=None, startangle=0, counterclock=False, legend=None,
    title='Ticket type for public transport trips')
axs[0].legend()
# Share of abonements
for mode, name in model_modes.items():
    n = len(trips.loc[trips['mode_model']==mode].index)
    print(name + ': ' + str(len(trips.loc[
        (trips['mode_model']==mode) &
        trips['ticket_type'].isin([4,5])].index) / n))
# Distance-dependency of abonements
fig, ax = plt.subplots(figsize=(12,4))
for mode in trips['mode_model'].unique():
    x = trips.loc[trips['mode_model']==mode, 'distance']
    y = trips.loc[trips['mode_model']==mode, 'ticket_type'].apply(lambda t: t in [4,5])
    z = np.polyfit(x, y, deg=1)
    p = np.poly1d(z)
    plt.plot(x, p(x), linestyle='--', marker='', label='trend '+model_modes[mode])
plt.legend()<jupyter_output><empty_output><jupyter_text>Regional classificationDifferences between poeple who live in cities vs. rural sitesImportant: This does always specify the cell of household location<jupyter_code># Urbanisation
trips.groupby('SKTYP').size().plot.pie(subplots=False,figsize=(3, 3),
                                       labels=['metropolitan', 'urban', 'dense rural', 'rural'],
                                       title='Urbanisation degree of home cell')
# Mode choice by regional classification
axs = trips.groupby(['mode_model','SKTYP']).size().unstack(fill_value=0).rename(
    columns={1:'metropolitan', 2:'urban', 3:'dense rural', 4:'rural'}).plot.pie(
    subplots=True, figsize=(16, 3), labels=[m for _, m in model_modes.items()],
    startangle=0, counterclock=False, title='Modal split by regional classification in home cell',
    legend=False, labeldistance=None)
axs[0].legend()
# Access/egress mode for PT trips by urbanisation
pt = trips.loc[(trips['mode_model'].isin([1,2,3,4,5])) &
               (trips['mode_combo'].isin([3,4,5,6]))].copy()
pt['access_mode'] = pt['mode_combo'].map({3: 'bicycle', 4: 'walk', 5: 'car', 6: 'car'})
pt.groupby(['access_mode', 'urbanisation']).size().unstack().plot.pie(
    subplots=True, figsize=(16, 3), legend=None)
# access/egress mode shares
pt.groupby(['access_mode', 'urbanisation']).size().unstack(fill_value=0) / \
    [pt.loc[pt['urbanisation']==u, 'mode'].count() for u in [1,2,3]]<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15355778752298768, 'fraction_numerical': 0.03154618757957278, 'mean_word_length': 4.8961634695579646, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 46, 'https://': 0, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 12, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '445389', 'license': 'MIT', 'n_tokens_mistral': 5065, 'n_tokens_neox': 4657, 'n_words': 1145, 'path': 'notebooks/cal10_input_data.ipynb', 'repo_name': 'marlinarnz/quetzal_germany', 'size': '14138'}