Tidy New York: Wrangling Pedestrian Count Data

I’m a huge fan of open data. I am especially fascinated by data related to transportation and urbanism, anyhting that I can put on a map that would represent some part of how we move and live in cities. That is why I was super excited to find that New York City collects and shares data on pedestrian counts in more than 100 locations across the city. However, the CSV dataset I downloaded needed a lot of love and care -in the form of wrangling- before it reached tidy formatting that could allow me to easily explore it by plotting it in maps and charts. In this post, I will share this tidying process.

To start, I’ll load the needed packages:

library(tidyverse)
library(reshape2)
library(lubridate)

#These last two are needed for building the post itself, rather than loading and cleaning the data
library(knitr)
library(kableExtra)

Now, let’s load the data (which I downloaded from the NYC Open Data portal as a CSV):

pedcount <- read_csv("PedCount.csv")

Take a look at the first few rows of the dataset in its original form:

Now, there are a number of different ways that this table doesn’t really fit the format I need to explore the data in the way that I want. So let’s tackle them one by one.

To start, I know that the way I will map the datapoints will require me to have the latitude and longitude coordinates as separate numeric variables. So, I’ll separate the string that is stored as the_geom and then I’ll change the datatype to numeric.

pedcount <- pedcount %>%
  separate(the_geom, c("temp", "lon", "lat"), sep = " ") %>%
             select(-temp)
pedcount <- mutate(pedcount, lon = as.numeric(substr(pedcount$lon,2,nchar(pedcount$lon))),
         lat = as.numeric(substr(pedcount$lat,1,nchar(pedcount$lat)-1)))

Here’s what our table looks like now:

We are ready now to deal with a much thornier issue: several different values about dates and types of measurement are stored as column names, which makes it harder to deal with the data. These columns are named with the first letters of the month (May or Sept), an underscore, and either D or D2, where D is used for weekday measurements and D2 for weekend. We’ll use the gather() function to create a different row for each observation and keep in a new variable the values that are currently as column names.

pedcount <- pedcount %>%
  gather(key = "date_coding", value = "date", contains("_D")) %>%
  select(lon, lat, Loc, OBJECTID, Borough, Street_Nam, From_Stree, To_Street, Index, date_coding, date, everything()) %>%
    gather(key = "measurement_coding", value = "ped_count", 12:77)

Let’s take a look again:

These changes help, but it’s still pretty confusing… How do I make good use of my new date_coding and measurement_coding variables? Also, I now have a ton of duplicates, because I really only need one combination of Location, date and pedcount, but how do I know which one?

If I look at the table, I can identify some extra rows that I know are redundant by looking at contradictions in the date information that date_coding and the actual date for that row. So, to get rid of these I need to be able to identify matches and filter by them.

For example, I should only keep rows where the the first six characters of date_coding match the first six characters of measurement_coding.

pedcount <- pedcount %>%
  filter(str_sub(date_coding, 1, 6) == str_sub(measurement_coding, 1, 6))

Now our table looks like this:

YAY, we got rid of most of the duplicates. However, each value is still in two different rows, only one of which has properly matching date and measurement coding. We shouldn’t have two ‘May07_AM’ rows for the same location with he same value; the reason we do is that the dates are different, and one is a weekday measurement and the other a weekend measurement. How do we know which is the one to keep? Well, weekdays have one AM and one PM measurement, and weekends have only one measurement, coded MD. So, in this case, we need to keep the row where date_coding is _D. In general, we need to keep all rows where _D in date_coding meets _AM OR _PM in measurement_coding and all rows where _D2 in date_coding matches _MD in measurement coding.

pedcount <- pedcount %>%
  filter((str_sub(date_coding, -1, -1) == "2" & str_sub(measurement_coding, -1, -1) == "D") | (str_sub(date_coding, -1, -1) == "D" & str_sub(measurement_coding, -1, -1) == "M"))

Take a look:

Done! Now we need to extract from date_coding and measurement_coding the information that is relevant and not redundant. We’ll do this by recoding the variables to a human-readable, plain English ‘translation’.

pedcount <- pedcount %>%
  mutate(
   type_of_measurement = case_when(
     str_sub(measurement_coding, -1, -1) == "D" ~ "Weekend Noon",
     str_sub(measurement_coding, -2, -1) == "AM" ~ "Weekday Morning",
     str_sub(measurement_coding, -2, -1) == "PM" ~ "Weekday Evening"),
   season = case_when(
     str_sub(date_coding, 1, 3) == "May" ~ "Spring",
     str_sub(date_coding, 1, 3) == "Sep" ~ "Fall"))

We’re getting really close now!

However, if we take a closer look we’ll notice that both the pedcount and the date variables need some clean-up:

class(pedcount$ped_count)

## [1] "character"

pedcount$ped_count <- as.numeric(pedcount$ped_count)
class(pedcount$ped_count)

## [1] "numeric"

class(pedcount$date)

## [1] "character"

pedcount$date <- mdy(pedcount$date)
class(pedcount$date)

## [1] "Date"

Finally, we drop the redundant ‘coding’ columns and we are all set.

pedcount <- select(pedcount, -c('measurement_coding', 'date_coding'))

There we have it! Our final tidy version of the original dataset:

We are ready now to use this data to map and explore pedestrian counts in New York City over time. But that’s a topic for an entirely different post. Stay tuned!