Assignment 5

Hints

• geopandas has the points_from_xy method to construct points from longitude and latitude
• specify the geometry kwarg when constructing a GeoDataFrame from a normal data frame
• Use 'EPSG:4326' for the crs parameter

a. Spatial Join (5 pts)

We want to know which community areas each trip begins and ends in. We can use geopandas’ spatial join (sjoin) functionality to compute this. Specifically, given the points from the stations, we want to know which community areas those points are in. After joining these two datasets, you should be able to find the community area number (area_number) for each station.

b. Add CAs to Trips (10 pts)

Now, we wish to relate the start and end locations for each trip to the community areas. Add columns that specify the starting and ending community area numbers (start_ca_num and end_ca_num) for each trip. Here, we need to use the start_pos and end_pos columns from Part 1, and we will do two spatial joins. You can use a spatial join as before, but make sure the geometry of the GeoDataFrame is set to the correct point (start_pos or end_pos), and then set the geometry to the other before computing the second spatial join.

c. Visualize Stations (10 pts)

We wish to understand which community areas have bike stations and how that affects trips. Using geopandas, generate a plot of the number of stations per community area, This requires creating a new data frame by aggregating the stations by community area. Then, use the plot command to generate a choropleth map from the GeoDataFrame. This is done by choosing a column to serve as the value in the map and setting a colormap (cmap).

d. Visualize Trips (15 pts)

We are interested in the number of trips using pedal bikes versus those using electric bikes (ebikes). Generate a plot showing the percent difference between the trips by electric or pedal bikes starting or ending in each community area. Use the filter of rideable_type == "electric_bike" to differentiate between ebikes and pedal bikes. Note that a trip starting in the LOOP and ending in the NEAR WEST SIDE will add one to both of those community areas. We need to calculate four aggrgations: the first for all starting cas for pedal bikes, the second for all ending cas for pedal bikes, the third for all starting cas for ebikes, and the fourth for all ending cas for ebikes. Then combine the pedal and ebike counts them into a single count. Finally, compute the percentage difference between pedal bikes and ebikes per ca ((# pedal bikes - # ebikes)/# ebikes). Then, merge with the cas, and plot using a diverging colormap for the second visualization.

a. Bordering Areas (10 pts)

First, we need to determine a graph of community areas where an edge indicates that the community areas border each other. To do this, we will compute the spatial join of community areas with themselves. There are a number of operations that can be used for a spatial join, but to make sure that areas with common borders are joined, we will use 'intersects'. Make sure to get rid of pairs of the same area and deduplicate those that are listed twice. There should be 197 pairs of intersecting (bordering) areas.

b. Graph Database Creation (20 pts)

To begin, we will create a new graph database and add a Local DBMS to neo4j. Do this in the neo4j desktop application by creating a new project and creating a Local DBMS associated with that project. You may wish to stop other databases from running, and then start the new DBMS you just created. If you click on the newly created DBMS, you will see the IP address and Bolt port; copy the bolt url. Back in Jupyter, we now wish to connect to this DBMS. Use the following code (potentially with a different url as copied) to connect with the password you created:

from neo4j import GraphDatabase
driver = GraphDatabase.driver("bolt://localhost:7687", auth=("divvy", "divvybikes"))

We also wish to define some helper functions to help us run queries with neo4j (based on functions by CJ Sullivan):

def run_query(query, parameters=None):
    session = None
    response = None
    try: 
        session = driver.session() 
        response = list(session.run(query, parameters))
    finally: 
        if session is not None:
            session.close()
    return response

def batch_query(query, df, batch_size=10000):
    # query can return whatever
    batch = 0
    results = []    
    while batch * batch_size < len(df):
        res = run_query(query, parameters={'rows': df.iloc[batch*batch_size:(batch+1)*batch_size].to_dict('records')})
        batch += 1
        results.extend(res)
        info = {"total": len(results), 
                "batches": batch}
        print(info)
    return results

From this, we wish to create a graph database that consists of the community areas and connections (relationships) to those community areas they border. Take the original community areas data frame from Part 2, and use it to create a node for each community area. Next, add relationships (BORDERS) between community areas that border each other (the 197 relationships from part a). After completing this, you can go to the neo4j Bloom tool and visualize the CommunityArea - BORDERS - CommunityArea graph which should look something like the following:

c. Compute Paths (20 pts)

Next, we wish to use the graph database to compute the paths for the trips from the bike sharing data frame. We will do this for two filtered data frames: (1) ebikes, and (2) pedal bikes; create these two data frames first. We will only use those paths that start and end in different community areas because the shortestpath function doesn’t work with paths starting and ending at the same node. Specifically, we wish to find a shortest path from the community area that the trip starts in to the community area that the trip ends in. (Note that this path is not unique.) From this shortest path, we wish to know the community areas each path goes through. From the results of the query, create a data frame with the counts of the number of times a community area was part of a trip’s path. Add the count of the trips that started and ended in the same community to this dataframe. Make sure to do this for both ebikes and pedal bikes, separately.

d. Visualization (5 pts)

Generate a plot showing the percent difference between the trips by electric or pedal bikes passing through each community area. Use the data frames from part c along with the same community areas geodataframe (cas) we used for the visualizations in Part 2. This should look similar to Part 2d. (If you want to see the difference, compute that difference and plot it.)

a. Hourly Intervals (20 pts)

We wish to find when the rental interval overlaps with our defined intervals; in our case, this will be every hour. Start by creating an interval array for the rentals from the starting_at and ending_at columns from the trips data using IntervalArray.from_arrays. Note, however, that you will get an error because some of the trips have timestamps out of order; drop those rows for this part of the analysis. Now create an interval_range that has hourly intervals from the beginning of July through the end of the month. Compute the number of rental intervals that overlap with each of the hourly intervals. (overlaps helps here, but I think you will need to loop through the hourly intervals, computing the overlaps for each one.) Create a new data frame that has the index equal to the beginning of each hour interval, and values equal to the number of overlapping rental intervals. From this data frame, create a line plot that shows the number of rentals in use during each hour. The first ten rows of the table are show below:

b. Resampled (10 pts)

Using the final data frame from part a, downsample the data to days instead of hours, summing the total. Plot this downsampled data.

c. Questions (10 pts)

In a markdown cell, answer the following three questions:

1. In the plot from part a, what pattern do you notice when comparing weekdays to weekends? (Weekends are July 2-3, July 9-10, etc.)
2. The plot in part b is not showing the number of bike rentals on each day; what is it actually showing?
3. In the plot from part b, which day is anomalous? why?