In [2]:
# Import geojson file into geopandas dataframe and remove streets with no rating
import geopandas as gpd
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline
st_quality = gpd.read_file('../data/external/street-assessment/StreetAssessmentRating/StreetAssessmentRating.shp')
st_quality = st_quality[['Rating_B', 'geometry']]
st_quality = st_quality[st_quality.Rating_B != 0 ]
st_quality = st_quality.to_crs({'init' :'epsg:4326'})
st_quality.head()
Out[2]:
In [12]:
# Check crs
st_quality.crs
Out[12]:
In [3]:
# Import citibike station csv into geopandas dataframe
from shapely.geometry import Point
stations = pd.read_csv('../data/processed/stations.csv')
geometry = gpd.GeoSeries([Point(xy) for xy in zip(stations.Longitude, stations.Latitude)])
geometry = geometry.buffer(.0005)
geo_stations = gpd.GeoDataFrame(stations, geometry=geometry)
geo_stations.crs = {'init' :'epsg:4326'}
geo_stations.head()
Out[3]:
In [3]:
# Check number of stations before merge
len(geo_stations)
Out[3]:
In [15]:
# Check crs
geo_stations.crs
Out[15]:
In [4]:
# Merge street quality data with citibike stations using Geopandas Spatial Merge
stations_st_quality = gpd.sjoin(geo_stations, st_quality, how="inner", op='intersects')
In [5]:
# Save only for streets
st_quality.loc[stations_st_quality.index_right.unique(), :].to_csv("../data/map/street-assessment.csv")
In [6]:
stations_st_quality.drop(['index_right', 'Location'], axis=1, inplace=True)
stations_st_quality.head(10)
Out[6]:
In [17]:
# Find average of street quality ratings for each station
stations_st_quality = stations_st_quality.groupby(['Station_id', 'Station_Name', 'Latitude', 'Longitude']).mean()
stations_st_quality.reset_index(inplace=True)
stations_st_quality.head()
Out[17]:
In [18]:
# Check number of stations after merge
len(stations_st_quality)
Out[18]:
In [19]:
# Output merged file to csv
stations_st_quality.drop(['Station_Name', 'Latitude', 'Longitude'], axis=1, inplace=True)
# Save to CSV
stations_st_quality.to_csv("../data/processed/street-assessment.csv")
In [ ]: