text_html_output_1.png
import pandas as pd

ramp_path = '../input/rampdatalog/RampDataLog.xlsm'
ramp_df = pd.read_excel(ramp_path, sheet_name='data')

ramp_df.rename(columns={'Cross Street 1': 'CS_1', 'Cross Street 2': 'CS_2'}, inplace=True)

ramp_df.replace(' NE', 'NE', inplace=True)
ramp_df.Notes.fillna('None', inplace=True)

import math
def cs_comb_str(cs1, cs2):
    """
    Take an intersection's cross streets as arguments and return
    a string of the complete location
    """
    inter = ''
    suffixes = ['Ave', 'Ln', 'Terr']
    if 'btw' in cs1:
        inter = cs1.split('btw')[0] + 'Ave btw ' + cs2
    elif type(cs2) != str:
        inter = cs1
    elif '(' in cs2:
        inter = cs1 + ' Ave and ' + cs2.split()[0] + ' Ave ' + cs2.split()[1]
    else:
        if not any([suf in cs1 for suf in suffixes]):
            cs1 += ' Ave'
        if not any([suf in cs2 for suf in suffixes]) and (not any([landmark in cs2 for landmark in ['alleyway', 'exit']])) and (len(cs2.split()[-1]) > 1):
            cs2 += ' Ave'
        inter = ' and '.join([cs1, cs2])
    return inter

ramp_df['Inter'] = ramp_df.apply(lambda row: cs_comb_str(row['CS_1'], row['CS_2']), axis=1)

cols = ramp_df.columns.tolist()
cols = cols[:3] + cols[-2:] + cols[3:-2]
ramp_df = ramp_df[cols]

ramp_df.to_csv('CollingswoodADA_LatLong_checkpoint.csv', index=False)

ramp_df = pd.read_csv('../input/collingswoodada-clean-latlong/CollingswoodADA_LatLong_checkpoint.csv')
ramp_df.head()