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

a. Spatial Join (5 pts)

We want to know which community areas each station is in. We can use geopandas’ spatial join (sjoin) functionality to compute this. Specifically, given the points from the station locations, 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_id.

b. Add CAs to Trips (10 pts)

Use the updated station data frame from part a with the bike trip data to add columns that specify the starting and ending community area numbers (start_ca_num and end_ca_num) for each trip. Use the start_station_id and end_station_id and the result from part a to set these.

c. Visualize Station Distribution (20 pts)

We wish to understand which community areas have bike stations and how that affects trips. Using geopandas, generate two plots: (1) the number of stations per community area, and (2) the number of trips starting or ending in each community area. Both require creating a new data frame by aggregating the stations/trips by community area. Then, use the plot command to generate a choropleth map from the GeoDataFrames. This is done by choosing a column to serve as the value in the map and setting a colormap (cmap). Note that for (2), a trip starting in the LOOP and ending in the NEAR WEST SIDE will add one to each of those community areas.

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=("neo4j", "chicago"))

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(rows):
        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 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 second data frame with the counts of the number of times a community area was part of a trip’s path.

d. Visualization (5 pts)

Visualize the counts of the community areas being part of a trip. Use the second data frame from part c along with the same geo data frame we used in the visualizations in Part 2.

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?
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? Search the internet for why this may be and report why this was.