Who You Gonna Call? 311 Complaints in Astoria, NYC


Every New Yorker has an apartment horror story. From moldy walls to freezing temperatures to mice, rats, roaches, and everything in between, calling 311 has almost become a right of passage. For those outside of NYC, 311 is the city’s answering service for any non-emergencies (read more here) and has become a major outlet for tenant complaints.  In New York, apartment living can sometimes feel like an impossible feat, as crooked or simply uncaring landlords seem to be more the norm than the exception. The recent explosion in the East Village is an extreme example as police are currently investigating whether it may have been caused by the landlord illegally siphoning gas from the downstairs restaurant. I have a vested interest in helping people avoid such horrors due to my own personal experience with a Brooklyn slumlord. Two years ago my boyfriend’s apartment was nearly destroyed after the landlord neglected to connect the radiator to the water pipes, causing water to flood the apartment, soaking their belongings and causing thousands of dollars worth of damage.

After discovering the 311 data from NYC OpenData, I hoped to create a visualization that might help prospective renters from avoiding the same distress. Using both Carto DB and Tableau, I created several visualizations that charted 311 calls from Astoria apartment dwellers in 2014, highlighting both the categories and numbers of complaints per address.

When first pulled from the system, the data was made up thousands and thousands of rows, far too large to process through CartoDB so I decided to cull. First, I narrowed the year to only 2014, then the neighborhoods to just my own neighborhood, Astoria, purely out of selfish curiosity. I wanted to focus on serious complaints of neglect so I then removed any noise complaints, traffic or parking complaints, etc. I was thus left with three major categories – Heat and Hot Water, Electricity, and Unsanitary Conditions. Although I had addresses and zip codes for each complaint, I lacked the longitude and latitude needed to map these complaints. To do so, I first reformatted my addresses. When pulled from the initial data, my addresses were all in the following format with the zip code in a separate column:

25-32 45TH STREET

I created an additional column in Excel, with each row populated as “ASTORIA, NEW YORK, and concatenated all three columns so that the addresses reflected as below:


I now had an address that, through the power of CartoDB, I could successfully georeference. Essentially, CartoDB takes the address and matches the appropriate longitudes and latitudes so that the data points can be placed on a map. I used an Open StreetMap already in CartoDB as, due to the nature of the dataset, I wanted to see the where the individual complaints occurred.

Next, I wanted to split my data out both by category and number of complaints. To do this, I first created a cluster map that reflected the number of complaints in certain areas or “clusters” through the size of the marker. Then, in order to separate by the three major categories of complaint, I created three layers to my map, filtering each layer to only show one category and changing the marker color accordingly. This can be accomplished either by applying filters or by writing a SQL query along this line:

SELECT * FROM table_311_request_with_geocode WHERE complaint_type IN (‘UNSANITARY CONDITION’)

I tried to choose colors that naturally reflected the nature of the complaint so Heat/Hot Water became red, Electricity became yellow, and Unsanitary Conditions became a brownish-grey.

Cluster Map

While I liked the look of this map, the clustering wasn’t as exact as I has desired. Knowing which areas had more complaints is interesting from a sociological perspective (for instance, perhaps a high-volume area is near a construction site that is causing a pest problem or addresses near a flood zone had more mold complaints), but it couldn’t show me the number of complaints per each address. CartoDB also disables the infowindow function on cluster maps so I couldn’t add any informational captions to this map, such as the address or description of a certain complaint.

To solve these problems, I next created a bubble map and followed the same filtering method to once again split the map into the three major categories of complaints.

Bubble Map

Although this map does not show the totals on the different markers as the cluster map did, I was now able to display additional information when a certain marker was selected. I wanted the user to be able to see the address of the incident, the total number of complaints, and a description of the specific complaints. One flaw in this design is that the descriptor is only completely accurate for those addresses with only one complaint or the same complaint made over and over again. For all addresses with multiple complaints, CartoDB appears to be pulling the descriptor from a random data row. It may be pulling the most common complaint type, but that is unclear. It would be more effective if the user could dig deeper into that data category, for instance pull up a table that shows the type for each complaint or at least a breakdown of the numbers of each kind of complaint, similar to the below:

Descriptor Number of Complaints
Pests 3
Mold 6
Garbage/Recycling 1

This map is also problematic due to the category breakdown. There are inevitable overlaps as addresses with multiple categories of complaints display as multiple bubbles. The totals displayed for these do not also show the total number of complaints overall, which would be useful for the user. The user can, of course, compare between the markers and add them up, but this is not exactly user-friendly.

Finally, I decided to see how I could create this same sort of visualization in Tableau. I exported my data from CartoDB as a GeoJSON file and then converted to CSV using Google Refine so that I could load the data into Tableau. I pulled the latitude and the longitude into the rows and columns respectively to plot the points on the map. I then defined the complaint types in the same color scheme as I used with the CartoDB maps and again used size to reflect the total numbers of complaints.

Tableau 311-2

This map turned out to be almost identical to the bubble map from CartoDB, but I think that Tableau is ultimately more functional for my aims for one major reason – the ability to create a dashboard. An ideal next step for this data would be to make a dashboard that displays a cluster style map similar to my first map from CartoDB for an overview of the neighborhood picture, a map that breaks down the data by individual address, and then a map similar to this final bubble map that shows the numbers per category.