Rats-Per-Dollar: Artful info graphics
What you see in these images is a visual representation of two data sets. The first, in the semi-transparent red/orange tones are the amount of NYC Dept. of Health has found evidence of rodent activity at a location. While the skyscraper-esc black columns with illuminated windows illustrate the cost of recent real estate transactions at that location. So clearly, some people are paying a lot of money for furry, four legged neighbors and roommates. 😊
A cloud's eye view with Uptown and the Bronx towards the lower right
The two glowing points near the center and right side of the image denote the max proportional real estate price and failed rodent inspections (334)
Data sources and process:
NYC’s open data project has tons of very cool public access / public domain data. While looking through it for interesting information, I found that the NYC department of health tracks all kinds of data around rat inspections (https://data.cityofnewyork.us/Health/Rodent-Inspection/p937-wjvj) . Included in this is GPS data about where the inspection took place. This is broken down in a number of ways, for city purposes, but the initial lat and long were great for me. In looking at it, the “precision” of the coordinates they gave was pretty dubious. They are reported to 12(!) decimal places. Given that 9 decimal places is recorded in microns, I doubt that NYC is using equipment that’s giving data in something like plank scale unit.
With that in mind, I pulled the most recent year of data (2023) and used SQLite through DBeaver to round the lat and long to 3 decimal places which is about 110 meters, which is with in the neighborhood of the average size of an NYC block (The standard block in Manhattan is about 80 m × 274 m). Then I had it tally the number of times each set of coordinates appeared in a new column.
-----------------------------------------------------
SELECT XRounded, YRounded, COUNT(1) as ZValue
From "Rodent_Inspection_done_in2023"
GROUP BY XRounded, YRounded;
ALTER TABLE "Rodent_Inspection_done_in2023"
ADD YRounded REAL;
ALTER TABLE "Rodent_Inspection_done_in2023"
ADD XRounded REAL;
UPDATE "Rodent_Inspection_done_in2023"
SET XRounded = ROUND(LATITUDE , 3), YRounded = ROUND(LONGITUDE , 3);
From "Rodent_Inspection_done_in2023"
ORDER BY "XRounded" ASC;
-----------------------------------------------------
From here, I searched through Kaggle and found a data set involving recent real estate prices around the city. To further develop my Power BI skills I did a similar processes with this data as I did with the rodent inspection data. I had it round the GPS data to 3 decimal places, and then averaged the amount paid if there were any overlapping data points. Two of the data points were ten to one hundred times greater than the next most expensive ones below them. I excluded these from the data set as it seemed likely that there was an error somewhere in the reporting.
To create the visualization, I found public domain images satellite images of NYC, and used them as a base. Following this I imported the data from the CSV file, converted the coordinates in to X and Y values and used the coordinates tally as a Z height for the rodent data, and the scaled the averaged real estate values where the most expensive property equaled the highest tally for the rodent data (334). Once this was complete, I used the contours of the data points to line it up with the map of the city – Voids lining up with Central Park, and the rivers around NYC made the overlay fairly intuitive.
Lastly, I put my years of art skills to work making it in to the beautiful / horrifying images of data-based-art you see here.
