<jupyter_start><jupyter_text>Data Cleaning  Light Rail Link Stations<jupyter_code># importing pandas for the notebook
import pandas as pd

#link for the tables
linkLink="https://en.wikipedia.org/wiki/List_of_Link_light_rail_stations"

# fetching the tables
stationsData=pd.read_html(linkLink,header=0,flavor="bs4",attrs={'class':"wikitable"})
stationVars=stationsData[1].copy()
stationVars.head()
stationVars
# drop "line" and "location" columns
columnsToDrop=[1,2]

# drop and update the data frame
stationVars.drop(labels=stationVars.columns[columnsToDrop],axis=1,inplace=True)
stationVars.columns
# remove brackets
import re
stationVars.columns=stationVars.columns.str.replace("\[(n )?1\]","",regex=True)
# see current columns
stationVars.columns
# check frequnecy of table
stationVars['Station'].value_counts()
stationVars
# replace the matching strings
stationVars_updated = stationVars.replace(to_replace ='( †)|\[(n )?[0-9]?[0-9]\]', value = '', regex = True)

# Print the updated dataframe
print(stationVars_updated)
stationVars_updated['Opened Year'] = pd.DatetimeIndex(stationVars_updated['Opened']).year
stationVars_updated
# save to csv format
stationVars_updated.to_csv("stationVars.csv",index=False)<jupyter_output><empty_output><jupyter_text>Station Zip Codes<jupyter_code># extract address from site
import requests
from bs4 import BeautifulSoup

def getdata(url):
    r = requests.get(url)
    return r.text

htmldata = getdata("https://www.soundtransit.org/ride-with-us/stops-stations/northgate-station")
soup = BeautifulSoup(htmldata, 'html.parser')
data = soup.find_all('p')[0].text
# set pandas display option
pd.set_option('display.max_colwidth', None)

# For the scrape:
from bs4 import BeautifulSoup as BShtml
import urllib.request as ur

# Make empty dataframe
station_zips = pd.DataFrame({"station":[], "zip":[]})

urls = ['https://www.soundtransit.org/ride-with-us/stops-stations/northgate-station',
       'https://www.soundtransit.org/ride-with-us/stops-stations/roosevelt-station',
      'https://www.soundtransit.org/ride-with-us/stops-stations/u-district-station',
       'https://www.soundtransit.org/ride-with-us/stops-stations/university-washington-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/capitol-hill-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/westlake-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/university-street-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/pioneer-square-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/international-district-chinatown-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/stadium-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/sodo-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/beacon-hill-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/mount-baker-station-transit-center',
        'https://www.soundtransit.org/ride-with-us/stops-stations/columbia-city-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/othello-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/rainier-beach-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/tukwila-international-boulevard-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/seatac-airport-station',
        'https://www.soundtransit.org/ride-with-us/stops-stations/angle-lake-station']

# Populate dataframe with quotes for first three pages
for url in urls:
    r = ur.urlopen(url).read()
    soup = BShtml(r, "html.parser")
    for post in soup.find_all('div', class_="station-map-card-content sidebar-container__content"):
        new_result = pd.DataFrame({
            "station":[post.find_all("span", class_="station-map-card-name")],
            "zip":[post.find_all('p')[0]]
        })
        station_zips = station_zips.append(new_result)
station_zips
# convert values to strings
station_zips['station'] = station_zips['station'].astype('str')
station_zips['zip'] = station_zips['zip'].astype('str')
# remove html tags from strings
def remove_tags(string):
    result = re.sub('<.*?>','',string)
    return result
station_zips['station']=station_zips['station'].apply(lambda cw : remove_tags(cw))
station_zips['zip']=station_zips['zip'].apply(lambda cw : remove_tags(cw))
station_zips
# clean up row values
station_zips['station'] = station_zips['station'].replace('\[|\]','', regex = True)
station_zips['zip'] = station_zips.zip.str.extract('([0-9]{5})\n')
station_zips
# reset row numbers
station_zips.reset_index(drop=True,inplace=True)
station_zips
# save to csv format
station_zips.to_csv("station_zips.csv",index=False)<jupyter_output><empty_output><jupyter_text>ACS Data Using an API<jupyter_code># install censusdata
!pip install CensusData
import censusdata
# set variables for census data
api_key='7502bfbf0a9ccaeb30b91a8e2c0e9425e216c8b5'
dsource='zbp'
state='53'
zipcode='98125, 98115,98105,98195,98102,98101,98104,98134,98144,98108,98118,98188'
# 2019: pull transportation by car, truck, or van alone
data_url = f'https://api.census.gov/data/2019/acs/acs5/subject?get=NAME,group(S0802)&for=zip%20code%20tabulation%20area:{zipcode}&key=<KEY>'
transportation_response=requests.get(data_url)
print(transportation_response.text)
# pull 2019 transportation into dataframe
data=transportation_response.json()
transportation=pd.DataFrame(data[1:], columns=data[0])
transportation.head()
# add year column to 2019 data
transportation['Year'] = '2019'
transportation
# 2011-2019: pull median income in the past 12 months (in inflation-adjusted dollars) by place of birth
years=list(range(2011,2019))
allUrls = [f'https://api.census.gov/data/'+str(y)+"/acs/acs5/subject?get=NAME,group(S1903)&for=zip%20code%20tabulation%20area:*&in=state:53&key=<KEY>" for y in years]
# pull 2010-2018 median income into dataframe
year=2011
zipcodes=['98125', '98115','98105','98195','98102','98101','98104','98134','98144','98108','98118','98188']
allDataFramesTransportation=[]
for url in allUrls:
    transportation_response=requests.get(url)
    data=transportation_response.json()
    transportation=pd.DataFrame(data[1:], columns=data[0])
    transportation['Year']=year
    transportation=transportation[transportation['zip code tabulation area'].isin(zipcodes)]
    allDataFramesTransportation.append(transportation)
    year+=1
# 2019: pull hispanic or latino origin by race
zipcode='98125, 98115,98105,98195,98102,98101,98104,98134,98144,98108,98118,98188'
data_url = f'https://api.census.gov/data/2019/acs/acs5/subject?get=NAME,group(S1903)&for=zip%20code%20tabulation%20area:{zipcode}&in=state:53&key=<KEY>'
race_response=requests.get(data_url)
print(race_response.text)

# pull 2019 race data into dataframe
data=race_response.json()
race_data=pd.DataFrame(data[1:], columns=data[0])
race_data.head()

# add year column to 2019 data
race_data['Year']='2019'
race_data

# 2011-2019: pull race data
allUrlsRace = [f'https://api.census.gov/data/'+str(y)+"/acs/acs5/subject?get=NAME,group(S1903)&for=zip%20code%20tabulation%20area:*&in=state:53&key=<KEY>" for y in years]

# pull 2010-2018 race data into dataframe
year=2011
zipcode=['98125', '98115','98105','98195','98102','98101','98104','98134','98144','98108','98118','98188']
allDataFrames=[]
for url in allUrlsRace:
    race_response=requests.get(url)
    data=race_response.json()
    race_data=pd.DataFrame(data[1:], columns=data[0])
    race_data['Year']=year
    race_data=race_data[race_data['zip code tabulation area'].isin(zipcode)]
    allDataFrames.append(race_data)
    year+=1

# concatenate, append
pd.concat(allDataFrames, ignore_index=True)

# append all years of data
race_data=race_data.append(allDataFrames, ignore_index=True)
race_data
allDataFramesIncome.Year.unique()
transportation
# append all years of data
transportation=transportation.append(allDataFramesTransportation, ignore_index=True)
transportation
# keep wanted columns
transportation=transportation.filter(regex='(zip code tabulation area)|S0802_C02_0(20|12|19)E')
transportation
# drop unwanted columns
transportation.drop(transportation.filter(regex='EA$').columns, axis=1, inplace=True)
transportation
transportation.Year.unique()
# clean up median income data frame
medianIncome=medianIncome.rename({'S1903_C01_001E':'Household Median Income','zip code tabulation area':'Zip Code'}, axis=1)
medianIncome.drop(medianIncome.columns[[0,2]], axis=1, inplace = True)
medianIncome
# move zip code to first column
medianIncome.insert(0, 'Zip Code', medianIncome.pop('Zip Code'))
medianIncome
# reset row numbers
medianIncome.reset_index(drop=True,inplace=True)
medianIncome
# save to csv format
medianIncome.to_csv("median_income.csv",index=False)
# 2019: pull hispanic or latino origin by race
zipcode='98125, 98115,98105,98195,98102,98101,98104,98134,98144,98108,98118,98188'
data_url = f'https://api.census.gov/data/2019/acs/acs5?get=NAME,group(B03002)&for=zip%20code%20tabulation%20area:{zipcode}&in=state:53&key=<KEY>'
race_response=requests.get(data_url)
print(race_response.text)
# pull 2019 race data into dataframe
data=race_response.json()
race_data=pd.DataFrame(data[1:], columns=data[0])
race_data.head()
# add year column to 2019 data
race_data['Year']='2019'
race_data
# 2011-2019: pull race data
allUrlsRace = [f'https://api.census.gov/data/'+str(y)+"/acs/acs5?get=NAME,group(B03002)&for=zip%20code%20tabulation%20area:*&in=state:53&key=7502bfbf0a9ccaeb30b91a8e2c0e9425e216c8b5" for y in years]
# pull 2010-2018 race data into dataframe
year=2011
zipcode=['98125', '98115','98105','98195','98102','98101','98104','98134','98144','98108','98118','98188']
allDataFrames=[]
for url in allUrlsRace:
    race_response=requests.get(url)
    data=race_response.json()
    race_data=pd.DataFrame(data[1:], columns=data[0])
    race_data['Year']=year
    race_data=race_data[race_data['zip code tabulation area'].isin(zipcode)]
    allDataFrames.append(race_data)
    year+=1
pd.concat(allDataFrames, ignore_index=True)
# append all years of data
race_data=race_data.append(allDataFrames, ignore_index=True)
race_data
race_data.Year.unique()
# drop unwanted columns
race_data.drop(race_data.filter(regex='EA|M|NAME|state|GEO_ID|_002|01[01]|01[3-9]|02[0-1]').columns, axis=1, inplace=True)
race_data
# rename columns
race=race_data.rename({'zip code tabulation area':'Zip Code','B03002_001E': 'TotalPopulation','B03002_003E':'White alone', 'B03002_004E':'Black or African American alone', 'B03002_005E':'American Indian and Alaska Native alone', 'B03002_006E':'Asian alone', 'B03002_007E':'Native Hawaiian and Other Pacific Islander alone', 'B03002_008E':'Some other race alone', 'B03002_009E':'Two or more races alone', 'B03002_012E':'Hispanic or Latino'}, axis=1)
race
race['TotalPopulation'].apply(pd.to_numeric)
race = pd.DataFrame({
    'Year': race['Year'],
    'Zip Code': race['Zip Code'],
    'TotalPopulation': race['TotalPopulation'].apply(pd.to_numeric),
    'White alone': race['White alone'].apply(pd.to_numeric),
    'Hispanic or Latino': race['Hispanic or Latino'].apply(pd.to_numeric),
    'Black or African American alone': race['Black or African American alone'].apply(pd.to_numeric),
    'American Indian and Alaska Native alone': race['American Indian and Alaska Native alone'].apply(pd.to_numeric),
    'Asian alone': race['Asian alone'].apply(pd.to_numeric),
    'Native Hawaiian and Other Pacific Islander alone': race['Native Hawaiian and Other Pacific Islander alone'].apply(pd.to_numeric),
    'Some other race alone': race['Some other race alone'].apply(pd.to_numeric),
    'Two or more races alone': race['Two or more races alone'].apply(pd.to_numeric)
})
race = pd.DataFrame({
    'Year': race['Year'],
    'Zip Code': race['Zip Code'],
    'TotalPopulation': race['TotalPopulation'],
    'White alone': race['White alone'].div(race.TotalPopulation, axis=0),
    'Hispanic or Latino': race['Hispanic or Latino'].div(race.TotalPopulation, axis=0),
    'Black or African American alone': race['Black or African American alone'].div(race.TotalPopulation, axis=0),
    'American Indian and Alaska Native alone': race['American Indian and Alaska Native alone'].div(race.TotalPopulation, axis=0),
    'Asian alone': race['Asian alone'].div(race.TotalPopulation, axis=0),
    'Native Hawaiian and Other Pacific Islander alone': race['Native Hawaiian and Other Pacific Islander alone'].div(race.TotalPopulation, axis=0),
    'Some other race alone': race['Some other race alone'].div(race.TotalPopulation, axis=0),
    'Two or more races alone': race['Two or more races alone'].div(race.TotalPopulation, axis=0)
})
race
# move zip code to first column
race.insert(0, 'Zip Code', race.pop('Zip Code'))
race
# reset row numbers
race.reset_index(drop=True,inplace=True)
race
# save to csv format
race.to_csv("race.csv",index=False)<jupyter_output><empty_output><jupyter_text>Data Integration  Merge ACS Data<jupyter_code>medianIncome.columns
# merge race & median income data
acsData=race.merge(medianIncome,left_on=['Year','Zip Code'],right_on=['Year','Zip Code'])
acsData
# save to csv format
acsData.to_csv("acsData.csv",index=False)<jupyter_output><empty_output><jupyter_text>Merge Light Rail Link Stations<jupyter_code># link to station wiki data
linkStationWiki='https://raw.githubusercontent.com/oeuyown/computational-thinking/main/deliverable_1/stationVars.csv'
stationWiki=pd.read_csv(linkStationWiki)

# link to station zips
linkStationZips='https://raw.githubusercontent.com/oeuyown/computational-thinking/main/deliverable_1/station_zips.csv'
stationZips=pd.read_csv(linkStationZips)

# link to acs data
linkACS='https://raw.githubusercontent.com/oeuyown/computational-thinking/main/deliverable_1/acsData.csv'
acsMerge=pd.read_csv(linkACS)
# check current columns
stationWiki.columns
stationWiki['Station'] = stationWiki['Station'].astype(str) + ' Station'
stationWiki
stationZips = stationZips.replace(to_replace ='Mount Baker Station &amp; Transit Center', value = 'Mount Baker Station', regex = True)
stationZips
# merge station zips to station wiki data
stationMerge=stationWiki.merge(stationZips,left_on="Station",right_on="station",how='outer',indicator='True')
stationMerge
# drop left_only from merge (not line 1 stops)
stationMerge=stationWiki.merge(stationZips,left_on="Station",right_on="station")
stationMerge
# move zip code to first column
stationMerge.insert(0, 'zip', stationMerge.pop('zip'))
stationMerge<jupyter_output><empty_output><jupyter_text>Merge ACS Data to Link Data<jupyter_code>acsMerge
# merge acs data with link data
finalMerge=acsMerge.merge(stationMerge,left_on="Zip Code",right_on="zip",how='outer',indicator=True)
finalMerge.head()
# check merge outputs
finalMerge['_merge'].value_counts()
# drop and update the data frame
columnsToDrop = [16,17,18]
finalMerge.drop(labels=finalMerge.columns[columnsToDrop],axis=1,inplace=True)
finalMerge.head()
# reorder columns
finalMerge = finalMerge[["Zip Code", "Station", "Opened", "Opened Year","Year","Weekday ridership", "Household Median Income", "Hispanic or Latino", "White alone", "Black or African American alone", "Asian alone", "American Indian and Alaska Native alone", "Native Hawaiian and Other Pacific Islander alone", "Some other race alone", "Two or more races alone"]]
finalMerge
finalMerge.sort_values('Station')
finalMerge.reset_index(drop=True,inplace=True)
finalMerge
# save to csv format
finalMerge.to_csv("finalMerge.csv",index=False)
# for future use in Python
finalMerge.to_pickle("finalMerge_OK.pkl")
finalMerge_OK=pd.read_pickle("finalMerge_OK.pkl")
finalMerge_OK.info()
## Merge ACS Data to Link Data# save to r
!pip install rpy2

from rpy2.robjects import pandas2ri
pandas2ri.activate()

from rpy2.robjects.packages import importr

base = importr('base')
base.saveRDS(finalMerge,file="finalMerge_OK.RDS")<jupyter_output><empty_output><jupyter_text>Merge Equity Data to Link Data<jupyter_code># link to station wiki data
linkStationWiki='https://raw.githubusercontent.com/oeuyown/computational-thinking/main/deliverable_1/stationVars.csv'
stationWiki=pd.read_csv(linkStationWiki)

# link to station zips
linkStationZips='https://raw.githubusercontent.com/oeuyown/computational-thinking/main/deliverable_1/station_zips.csv'
stationZips=pd.read_csv(linkStationZips)

# link to Seattle equity data
linkEquity='https://raw.githubusercontent.com/oeuyown/computational-thinking/main/deliverable_1/data/equityData.csv'
equityData=pd.read_csv(linkEquity)
equityData.dtypes
stationMerge.dtypes
stationMerge['zip']=stationMerge['zip'].astype(str).astype(object)
# merge equity data with link data
finalMerge=equityData.merge(stationMerge,left_on="ZipCode",right_on="zip",how='outer',indicator=True)
finalMerge.head()
finalMerge
finalMerge=finalMerge.drop([16,17,18,19])
# drop and update the data frame
columnsToDrop = [4,10]
finalMerge.drop(labels=finalMerge.columns[columnsToDrop],axis=1,inplace=True)
finalMerge.head()
finalMerge['Opened Year'] = pd.to_numeric(finalMerge['Opened Year'], downcast='float')
finalMerge
# reorder columns
finalMerge = finalMerge[["ZipCode", "Station", "Opened", "Opened Year","Weekday ridership", "CompositePercentile","RacePercentile","SocioeconomicPercentile"]]
finalMerge
# save to csv format
finalMerge.to_csv("finalMerge.csv",index=False)
# for future use in Python
finalMerge.to_pickle("finalMerge_OK.pkl")
finalMerge_OK=pd.read_pickle("finalMerge_OK.pkl")
finalMerge_OK.info()
## Merge ACS Data to Link Data# save to r
!pip install rpy2

from rpy2.robjects import pandas2ri
pandas2ri.activate()

from rpy2.robjects.packages import importr

base = importr('base')
base.saveRDS(finalMerge,file="finalMerge_OK.RDS")
import pandas as pd
equityData='https://raw.githubusercontent.com/oeuyown/computational-thinking/main/deliverable_1/data/Racial_and_Social_Equity_Composite_Index.csv'
equityData=pd.read_csv(equityData)
!pip install rpy2

from rpy2.robjects import pandas2ri
pandas2ri.activate()

from rpy2.robjects.packages import importr

base = importr('base')
base.saveRDS(equityData,file="equityData.RDS")<jupyter_output>Requirement already satisfied: rpy2 in c:\users\kimoe\anaconda3\lib\site-packages (3.4.5)
Requirement already satisfied: pytz in c:\users\kimoe\anaconda3\lib\site-packages (from rpy2) (2021.3)
Requirement already satisfied: cffi>=1.10.0 in c:\users\kimoe\anaconda3\lib\site-packages (from rpy2) (1.14.6)
Requirement already satisfied: jinja2 in c:\users\kimoe\anaconda3\lib\site-packages (from rpy2) (2.11.3)
Requirement already satisfied: tzlocal in c:\users\kimoe\anaconda3\lib\site-packages (from rpy2) (4.1)
Requirement already satisfied: pycparser in c:\users\kimoe\anaconda3\lib\site-packages (from cffi>=1.10.0->rpy2) (2.20)
Requirement already satisfied: MarkupSafe>=0.23 in c:\users\kimoe\anaconda3\lib\site-packages (from jinja2->rpy2) (1.1.1)
Requirement already satisfied: tzdata in c:\users\kimoe\anaconda3\lib\site-packages (from tzlocal->rpy2) (2021.5)
Requirement already satisfied: pytz-deprecation-shim in c:\users\kimoe\anaconda3\lib\site-packages (from tzlocal->rpy2) (0.1.0.post0[...]
{'fraction_non_alphanumeric': 0.14180152989540512, 'fraction_numerical': 0.04579278763594734, 'mean_word_length': 6.863338788870704, 'pattern_counts': {'":': 4, '<': 34, '<?xml version=': 0, '>': 41, 'https://': 34, 'lorem ipsum': 0, 'www.': 20, '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': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '209529', 'license': 'MIT', 'n_tokens_mistral': 7186, 'n_tokens_neox': 6518, 'n_words': 1202, 'path': 'deliverable_1/deliverable_I.ipynb', 'repo_name': 'oeuyown/computational-thinking', 'size': '19392'}