<jupyter_start><jupyter_text>All data  Sort - Trip counts<jupyter_code>filenames = ['bern_2019_tripCount_daily','bern_2019_tripCount_hourly',
              'geneva_2019_tripCount_daily','geneva_2019_tripCount_hourly',
              'geneva_2020_tripCount_daily','geneva_2020_tripCount_hourly',
              'lausanne_2019_tripCount_daily','lausanne_2019_tripCount_hourly',
              'lausanne_2020_tripCount_daily','lausanne_2020_tripCount_hourly',
              'zurich_2019_tripCount_daily','zurich_2019_tripCount_hourly',
              'zurich_2020_tripCount_daily','zurich_2020_tripCount_hourly']
columns = ['date','total','transport_train','transport_road','transport_highway','commute','non_commute']
df_names = ['df_total','df_incoming','df_outto','df_within','df_passing']
for filename in filenames:
    # import df
    df = pd.read_pickle('data_df/' + filename + '.pkl')
    
    # separate trip locational types and append date to each
    df_total = df.iloc[:,1:7]
    df_total = pd.concat([df['Date'],df_total],axis=1)

    df_incoming = df.iloc[:,7:13]
    df_incoming = pd.concat([df['Date'],df_incoming],axis=1)

    df_outto = df.iloc[:,13:19]
    df_outto = pd.concat([df['Date'],df_outto],axis=1)

    df_within = df.iloc[:,19:25]
    df_within = pd.concat([df['Date'],df_within],axis=1)

    df_passing = df.iloc[:,25:]
    df_passing = pd.concat([df['Date'],df_passing],axis=1)
    
    # rename columns
    df_total.columns = columns
    df_incoming.columns = columns
    df_outto.columns = columns
    df_within.columns = columns
    df_passing.columns = columns
    
    # delete title rows
    df_total = df_total.drop([0,1]).reset_index(drop=True)
    df_incoming = df_incoming.drop([0,1]).reset_index(drop=True)
    df_outto = df_outto.drop([0,1]).reset_index(drop=True)
    df_within = df_within.drop([0,1]).reset_index(drop=True)
    df_passing = df_passing.drop([0,1]).reset_index(drop=True)
    
    # combine dfs into dict
    df_dict = dict(zip(df_names,[df_total, df_incoming, df_outto, df_within, df_passing]))
    
    # save dict to pkl
    pkl_file = open('dict_'+filename+'.pkl','wb')
    pickle.dump(df_dict,pkl_file)
    pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Demographics<jupyter_code>filenames = ['bern_2019_demographics',
             'geneva_2019_demographics','geneva_2020_demographics',
             'lausanne_2019_demographics','lausanne_2020_demographics',
             'zurich_2019_demographics','zurich_2020_demographics']
df_names = ['df_gender','df_age','df_nationality']
for filename in filenames:
    # import df
    df = pd.read_pickle('data_df/' + filename + '.pkl')
    
    # sort gender 
    df_gender = df.iloc[:2,:2]
    df_gender.columns = ['male','female']
    df_gender = df_gender.drop([0])
    
    # sort age
    df_age = df.iloc[:2,2:]
    df_age.columns = ['<20','20-39','40-64','65+']
    df_age = df_age.drop([0])
    
    # sort nationality
    df_nationality = df.iloc[5:]
    df_nationality = df_nationality.drop(columns=['Age','Unnamed: 3','Unnamed: 4','Unnamed: 5'])
    df_nationality.columns = ['nationality','ratio']
    df_nationality = df_nationality.reset_index(drop=True)
    
    # combine dfs into dict
    df_dict = dict(zip(df_names,[df_gender,df_age, df_nationality]))
    
    # save dict to pkl
    pkl_file = open('dict_'+filename+'.pkl','wb')
    pickle.dump(df_dict,pkl_file)
    pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Origins - Incoming<jupyter_code>filenames = ['bern_2019_incomingFrom_origin',
             'geneva_2019_incomingFrom_origin','geneva_2020_incomingFrom_origin',
             'lausanne_2019_incomingFrom_origin','lausanne_2020_incomingFrom_origin',
             'zurich_2019_incomingFrom_origin','zurich_2020_incomingFrom_origin']
columns = ['id','name','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in filenames:
    # import df
    df = pd.read_pickle('data_df/' + filename + '.pkl')
    
    # separate levels of regions
    df_postcode = df.iloc[:,:3]
    df_postcode = df_postcode.dropna(how='all')
    df_municipality = df.iloc[:,3:6]
    df_municipality = df_municipality.dropna(how='all')
    df_district = df.iloc[:,6:9]
    df_district = df_district.dropna(how='all')
    df_canton = df.iloc[:,9:]
    df_canton = df_canton.dropna(how='all')
    
    # sort columns
    df_postcode.columns = columns
    df_municipality.columns = columns
    df_district.columns = columns
    df_canton.columns = columns
    
    # combine dfs into dict
    df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
    # save dict to pkl
    pkl_file = open('dict_'+filename+'.pkl','wb')
    pickle.dump(df_dict,pkl_file)
    pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Destinations - Outbounding<jupyter_code>filenames = ['bern_2019_fromHereTo_dest',
             'geneva_2019_fromHereTo_dest','geneva_2020_fromHereTo_dest',
             'lausanne_2019_fromHereTo_dest','lausanne_2020_fromHereTo_dest',
             'zurich_2019_fromHereTo_dest','zurich_2020_fromHereTo_dest']
columns = ['id','name','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in filenames:
    # import df
    df = pd.read_pickle('data_df/' + filename + '.pkl')
    
    # separate levels of regions
    df_postcode = df.iloc[:,:3]
    df_postcode = df_postcode.dropna(how='all')
    df_municipality = df.iloc[:,3:6]
    df_municipality = df_municipality.dropna(how='all')
    df_district = df.iloc[:,6:9]
    df_district = df_district.dropna(how='all')
    df_canton = df.iloc[:,9:]
    df_canton = df_canton.dropna(how='all')
    
    # sort columns
    df_postcode.columns = columns
    df_municipality.columns = columns
    df_district.columns = columns
    df_canton.columns = columns
    
    # combine dfs into dict
    df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
    # save dict to pkl
    pkl_file = open('dict_'+filename+'.pkl','wb')
    pickle.dump(df_dict,pkl_file)
    pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Origins - PassingThru<jupyter_code>filenames = ['bern_2019_passingThru_origin',
             'geneva_2019_passingThru_origin','geneva_2020_passingThru_origin',
             'lausanne_2019_passingThru_origin','lausanne_2020_passingThru_origin',
             'zurich_2019_passingThru_origin','zurich_2020_passingThru_origin']
columns = ['id','name','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in filenames:
    # import df
    df = pd.read_pickle('data_df/' + filename + '.pkl')
    
    # separate levels of regions
    df_postcode = df.iloc[:,:3]
    df_postcode = df_postcode.dropna(how='all')
    df_municipality = df.iloc[:,3:6]
    df_municipality = df_municipality.dropna(how='all')
    df_district = df.iloc[:,6:9]
    df_district = df_district.dropna(how='all')
    df_canton = df.iloc[:,9:]
    df_canton = df_canton.dropna(how='all')
    
    # sort columns
    df_postcode.columns = columns
    df_municipality.columns = columns
    df_district.columns = columns
    df_canton.columns = columns
    
    # combine dfs into dict
    df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
    # save dict to pkl
    pkl_file = open('dict_'+filename+'.pkl','wb')
    pickle.dump(df_dict,pkl_file)
    pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Destinations - PassingThru<jupyter_code>filenames = ['bern_2019_passingThru_dest',
             'geneva_2019_passingThru_dest','geneva_2020_passingThru_dest',
             'lausanne_2019_passingThru_dest','lausanne_2020_passingThru_dest',
             'zurich_2019_passingThru_dest','zurich_2020_passingThru_dest']
columns = ['id','name','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in filenames:
    # import df
    df = pd.read_pickle('data_df/' + filename + '.pkl')
    
    # separate levels of regions
    df_postcode = df.iloc[:,:3]
    df_postcode = df_postcode.dropna(how='all')
    df_municipality = df.iloc[:,3:6]
    df_municipality = df_municipality.dropna(how='all')
    df_district = df.iloc[:,6:9]
    df_district = df_district.dropna(how='all')
    df_canton = df.iloc[:,9:]
    df_canton = df_canton.dropna(how='all')
    
    # sort columns
    df_postcode.columns = columns
    df_municipality.columns = columns
    df_district.columns = columns
    df_canton.columns = columns
    
    # combine dfs into dict
    df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
    # save dict to pkl
    pkl_file = open('dict_'+filename+'.pkl','wb')
    pickle.dump(df_dict,pkl_file)
    pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Routes - PassingThru<jupyter_code>filenames = ['bern_2019_passingThru_routes',
             'geneva_2019_passingThru_routes','geneva_2020_passingThru_routes',
             'lausanne_2019_passingThru_routes','lausanne_2020_passingThru_routes',
             'zurich_2019_passingThru_routes','zurich_2020_passingThru_routes']
columns = ['id_from','name_from','id_to','name_to','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in filenames:
    # import df
    df = pd.read_pickle('data_df/' + filename + '.pkl')
    
    # separate levels of regions
    df_postcode = df.iloc[:,:5]
    df_postcode = df_postcode.dropna(how='all')
    df_municipality = df.iloc[:,5:10]
    df_municipality = df_municipality.dropna(how='all')
    df_district = df.iloc[:,10:15]
    df_district = df_district.dropna(how='all')
    df_canton = df.iloc[:,15:]
    df_canton = df_canton.dropna(how='all')
    
    # sort columns
    df_postcode.columns = columns
    df_municipality.columns = columns
    df_district.columns = columns
    df_canton.columns = columns
    
    # combine dfs into dict
    df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
    # save dict to pkl
    pkl_file = open('dict_'+filename+'.pkl','wb')
    pickle.dump(df_dict,pkl_file)
    pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Dwell time<jupyter_code>filenames = ['bern_2019_dwellTimes',
             'geneva_2019_dwellTimes','geneva_2020_dwellTimes',
             'lausanne_2019_dwellTimes','lausanne_2020_dwellTimes',
             'zurich_2019_dwellTimes','zurich_2020_dwellTimes']
columns = ['length_min','count']
for filename in filenames:
    # import df
    df = pd.read_pickle('data_df/' + filename + '.pkl')
    
    # sort columns
    df.columns = columns
    df.iloc[-1,0] = '>8h'
    
    # save dict to pkl
    pkl_file = open('dict_'+filename+'.pkl','wb')
    pickle.dump(df,pkl_file)
    pkl_file.close()<jupyter_output><empty_output><jupyter_text>Daily Data  Sort - Demographics<jupyter_code>df_names = ['df_gender','df_age','df_nationality']
for filename in os.listdir('/Users/anniezhi/Desktop/HackZurich/Swisscom/data_df_daily'):
    if 'demographics' in filename:
        # import df
        df = pd.read_pickle('data_df_daily/' + filename)
    
        # sort gender 
        df_gender = df.iloc[:2,:2]
        df_gender.columns = ['male','female']
        df_gender = df_gender.drop([0])
    
        # sort age
        df_age = df.iloc[:2,2:]
        df_age.columns = ['<20','20-39','40-64','65+']
        df_age = df_age.drop([0])
    
        # sort nationality
        df_nationality = df.iloc[5:]
        df_nationality = df_nationality.drop(columns=['Age','Unnamed: 3','Unnamed: 4','Unnamed: 5'])
        df_nationality.columns = ['nationality','ratio']
        df_nationality = df_nationality.reset_index(drop=True)
    
        # combine dfs into dict
        df_dict = dict(zip(df_names,[df_gender,df_age, df_nationality]))
    
        # save dict to pkl
        pkl_file = open('dict_'+filename,'wb')
        pickle.dump(df_dict,pkl_file)
        pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Origins - Incoming<jupyter_code>columns = ['id','name','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in os.listdir('/Users/anniezhi/Desktop/HackZurich/Swisscom/data_df_daily'):
    if 'incomingFrom_origin' in filename:
        # import df
        df = pd.read_pickle('data_df_daily/' + filename)
    
        # separate levels of regions
        df_postcode = df.iloc[:,:3]
        df_postcode = df_postcode.dropna(how='all')
        df_municipality = df.iloc[:,3:6]
        df_municipality = df_municipality.dropna(how='all')
        df_district = df.iloc[:,6:9]
        df_district = df_district.dropna(how='all')
        df_canton = df.iloc[:,9:]
        df_canton = df_canton.dropna(how='all')
    
        # sort columns
        df_postcode.columns = columns
        df_municipality.columns = columns
        df_district.columns = columns
        df_canton.columns = columns
    
        # combine dfs into dict
        df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
        # save dict to pkl
        pkl_file = open('dict_'+filename,'wb')
        pickle.dump(df_dict,pkl_file)
        pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Destinations - Outbounding<jupyter_code>columns = ['id','name','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in os.listdir('/Users/anniezhi/Desktop/HackZurich/Swisscom/data_df_daily'):
    if 'fromHereTo_dest' in filename:
        # import df
        df = pd.read_pickle('data_df_daily/' + filename)
    
        # separate levels of regions
        df_postcode = df.iloc[:,:3]
        df_postcode = df_postcode.dropna(how='all')
        df_municipality = df.iloc[:,3:6]
        df_municipality = df_municipality.dropna(how='all')
        df_district = df.iloc[:,6:9]
        df_district = df_district.dropna(how='all')
        df_canton = df.iloc[:,9:]
        df_canton = df_canton.dropna(how='all')
    
        # sort columns
        df_postcode.columns = columns
        df_municipality.columns = columns
        df_district.columns = columns
        df_canton.columns = columns
    
        # combine dfs into dict
        df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
        # save dict to pkl
        pkl_file = open('dict_'+filename,'wb')
        pickle.dump(df_dict,pkl_file)
        pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Origins - PassingThru<jupyter_code>columns = ['id','name','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in os.listdir('/Users/anniezhi/Desktop/HackZurich/Swisscom/data_df_daily'):
    if 'passingThru_origin' in filename:
        # import df
        df = pd.read_pickle('data_df_daily/' + filename)
    
        # separate levels of regions
        df_postcode = df.iloc[:,:3]
        df_postcode = df_postcode.dropna(how='all')
        df_municipality = df.iloc[:,3:6]
        df_municipality = df_municipality.dropna(how='all')
        df_district = df.iloc[:,6:9]
        df_district = df_district.dropna(how='all')
        df_canton = df.iloc[:,9:]
        df_canton = df_canton.dropna(how='all')
    
        # sort columns
        df_postcode.columns = columns
        df_municipality.columns = columns
        df_district.columns = columns
        df_canton.columns = columns
    
        # combine dfs into dict
        df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
        # save dict to pkl
        pkl_file = open('dict_'+filename,'wb')
        pickle.dump(df_dict,pkl_file)
        pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Destinations - PassingThru<jupyter_code>columns = ['id','name','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in os.listdir('/Users/anniezhi/Desktop/HackZurich/Swisscom/data_df_daily'):
    if 'passingThru_dest' in filename:
        # import df
        df = pd.read_pickle('data_df_daily/' + filename)
    
        # separate levels of regions
        df_postcode = df.iloc[:,:3]
        df_postcode = df_postcode.dropna(how='all')
        df_municipality = df.iloc[:,3:6]
        df_municipality = df_municipality.dropna(how='all')
        df_district = df.iloc[:,6:9]
        df_district = df_district.dropna(how='all')
        df_canton = df.iloc[:,9:]
        df_canton = df_canton.dropna(how='all')
    
        # sort columns
        df_postcode.columns = columns
        df_municipality.columns = columns
        df_district.columns = columns
        df_canton.columns = columns
    
        # combine dfs into dict
        df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
        # save dict to pkl
        pkl_file = open('dict_'+filename,'wb')
        pickle.dump(df_dict,pkl_file)
        pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Routes - PassingThru<jupyter_code>columns = ['id_from','name_from','id_to','name_to','count']
df_names = ['df_postcode','df_municipality','df_district','df_canton']
for filename in os.listdir('/Users/anniezhi/Desktop/HackZurich/Swisscom/data_df_daily'):
    if 'passingThru_routes' in filename:
        # import df
        df = pd.read_pickle('data_df_daily/' + filename)
    
        # separate levels of regions
        df_postcode = df.iloc[:,:5]
        df_postcode = df_postcode.dropna(how='all')
        df_municipality = df.iloc[:,5:10]
        df_municipality = df_municipality.dropna(how='all')
        df_district = df.iloc[:,10:15]
        df_district = df_district.dropna(how='all')
        df_canton = df.iloc[:,15:]
        df_canton = df_canton.dropna(how='all')
    
        # sort columns
        df_postcode.columns = columns
        df_municipality.columns = columns
        df_district.columns = columns
        df_canton.columns = columns
    
        # combine dfs into dict
        df_dict = dict(zip(df_names,[df_postcode,df_municipality, df_district, df_canton]))
    
        # save dict to pkl
        pkl_file = open('dict_'+filename,'wb')
        pickle.dump(df_dict,pkl_file)
        pkl_file.close()<jupyter_output><empty_output><jupyter_text>Sort - Dwell time<jupyter_code>columns = ['length_min','count']
for filename in os.listdir('/Users/anniezhi/Desktop/HackZurich/Swisscom/data_df_daily'):
    if 'dwellTimes' in filename:
        # import df
        df = pd.read_pickle('data_df_daily/' + filename)
    
        # sort columns
        df.columns = columns
        df.iloc[-1,0] = '>8h'
    
        # save dict to pkl
        pkl_file = open('dict_'+filename,'wb')
        pickle.dump(df,pkl_file)
        pkl_file.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13026901080279601, 'fraction_numerical': 0.021181952976064393, 'mean_word_length': 3.592655642023346, 'pattern_counts': {'":': 0, '<': 63, '<?xml version=': 0, '>': 63, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, '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': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '294471', 'license': 'MIT', 'n_tokens_mistral': 7178, 'n_tokens_neox': 6807, 'n_words': 932, 'path': 'preproc_df_sort.ipynb', 'repo_name': 'octavianussps/HackZurich2020', 'size': '18884'}