Loading and Cleaning Data with R and the tidyverse

Now let’s load the Brooklyn dataset into R from an Excel file. We’ll use the readxl package. We specify the function argument skip = 4 because the row that we want to use as the header (i.e. column names) is actually row 5. We can ignore the first four rows entirely and load the data into R beginning at row 5. Here’s the code:

Note we saved this dataset with the variable name brooklyn for future use.

4. View the Data with tidyr::glimpse()

The tidyverse offers a user-friendly way to view this data with the glimpse() function that is part of the tibble package. To use this package, we will need to load it for use in our current session. But rather than loading this package alone, we can load many of the tidyverse packages at one time. If you do not have the tidyverse collection of packages, install it on your machine using the following command in your R or R Studio session:

Once the package is installed, load it to memory:

Now that tidyverse is loaded into memory, take a “glimpse” of the Brooklyn dataset:

## Observations: 20,185
## Variables: 21
## $ BOROUGH <chr> "3", "3", "3", "3", "3", "3", "…
## $ NEIGHBORHOOD <chr> "BATH BEACH", "BATH BEACH", "BA…
## $ `BUILDING CLASS CATEGORY` <chr> "01 ONE FAMILY DWELLINGS", "01 …
## $ `TAX CLASS AT PRESENT` <chr> "1", "1", "1", "1", "1", "1", "…
## $ BLOCK <dbl> 6359, 6360, 6364, 6367, 6371, 6…
## $ LOT <dbl> 70, 48, 74, 24, 19, 32, 65, 20,…
## $ `EASE-MENT` <lgl> NA, NA, NA, NA, NA, NA, NA, NA,…
## $ `BUILDING CLASS AT PRESENT` <chr> "S1", "A5", "A5", "A9", "A9", "…
## $ ADDRESS <chr> "8684 15TH AVENUE", "14 BAY 10T…
## $ `APARTMENT NUMBER` <chr> NA, NA, NA, NA, NA, NA, NA, NA,…
## $ `ZIP CODE` <dbl> 11228, 11228, 11214, 11214, 112…
## $ `RESIDENTIAL UNITS` <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1…
## $ `COMMERCIAL UNITS` <dbl> 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ `TOTAL UNITS` <dbl> 2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1…
## $ `LAND SQUARE FEET` <dbl> 1933, 2513, 2492, 1571, 2320, 3…
## $ `GROSS SQUARE FEET` <dbl> 4080, 1428, 972, 1456, 1566, 22…
## $ `YEAR BUILT` <dbl> 1930, 1930, 1950, 1935, 1930, 1…
## $ `TAX CLASS AT TIME OF SALE` <chr> "1", "1", "1", "1", "1", "1", "…
## $ `BUILDING CLASS AT TIME OF SALE` <chr> "S1", "A5", "A5", "A9", "A9", "…
## $ `SALE PRICE` <dbl> 1300000, 849000, 0, 830000, 0, …
## $ `SALE DATE` <dttm> 2020-04-28, 2020-03-18, 2019-0…

The glimpse() function provides a user-friendly way to view the column names and data types for all columns, or variables, in the data frame. With this function, we are also able to view the first few observations in the data frame. This data frame has 20,185 observations, or property sales records. And there are 21 variables, or columns.

5. Data Types

Looking at the data types for each column, we see that, in general, the data is stored in a format that is ready to use! For example:

• NEIGHBORHOOD is “character” type, also known as a string.
• GROSS SQUARE FEET (i.e. the size of the property) is of type “double”, which part of the “numeric” class in R.
• SALE PRICE is also numeric.
• SALE DATE is stored in a format that represents calendar dates and times.

So why does this matter? Because GROSS SQUARE FEET and SALE PRICE are numeric, we can perform arithmetic operations on the data right away. For example, we can calculate the average sale price for all properties:

## [1] 1098644

6. Ready to Plot!

It’s useful that SALE DATE is stored in a format that represents calendar dates and times because this enables us to use a single line of code to make a histogram of property sales by date:

## Observations: 20,185
## Variables: 21
## $ BOROUGH <dbl> 3, 3, 3, 3, 3, 3, 3, 3, 3, 3, 3…
## $ NEIGHBORHOOD <chr> "BATH BEACH", "BATH BEACH", "BA…
## $ `BUILDING CLASS CATEGORY` <chr> "01 ONE FAMILY DWELLINGS", "01 …
## $ `TAX CLASS AT PRESENT` <chr> "1", "1", "1", "1", "1", "1", "…
## $ BLOCK <dbl> 6359, 6360, 6364, 6367, 6371, 6…
## $ LOT <dbl> 70, 48, 74, 24, 19, 32, 65, 20,…
## $ `EASE-MENT` <lgl> NA, NA, NA, NA, NA, NA, NA, NA,…
## $ `BUILDING CLASS AT PRESENT` <chr> "S1", "A5", "A5", "A9", "A9", "…
## $ ADDRESS <chr> "8684 15TH AVENUE", "14 BAY 10T…
## $ `APARTMENT NUMBER` <chr> NA, NA, NA, NA, NA, NA, NA, NA,…
## $ `ZIP CODE` <dbl> 11228, 11228, 11214, 11214, 112…
## $ `RESIDENTIAL UNITS` <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1…
## $ `COMMERCIAL UNITS` <dbl> 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0…
## $ `TOTAL UNITS` <dbl> 2, 1, 1, 1, 1, 1, 1, 1, 2, 1, 1…
## $ `LAND SQUARE FEET` <dbl> 1933, 2513, 2492, 1571, 2320, 3…
## $ `GROSS SQUARE FEET` <dbl> 4080, 1428, 972, 1456, 1566, 22…
## $ `YEAR BUILT` <dbl> 1930, 1930, 1950, 1935, 1930, 1…
## $ `TAX CLASS AT TIME OF SALE` <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1…
## $ `BUILDING CLASS AT TIME OF SALE` <chr> "S1", "A5", "A5", "A9", "A9", "…
## $ `SALE PRICE` <dbl> 1300000, 849000, 0, 830000, 0, …
## $ `SALE DATE` <chr> "4/28/20", "3/18/20", "8/15/19"…

To summarize, key differences of loading the data into R with readxl() or read_csv() are that none of the variables have been coerced to the factor data type. Instead. Many of the variables were loaded as character, or string data types.

Also, notice that the special characters are missing from the Sale Price column and the column has been loaded as a double or numeric data type. This means that we can perform calculations relating to sale price right away, without taking extra step to convert the column to numeric!

8. Combining Datasets

If we want to perform data analysis for all five boroughs in New York City it will be helpful to combine the datasets. Also, if the data needs any additional cleaning, it would be great to have to clean the data in only one place instead of five! We’ve verified that the column names are the same across each of the five Excel files. So we can combine the dataframes with the bind_rows() function from the dplyr package (another tidyverse package!):

We are going to use the “assignment pipe” function from the magrittr package to efficiently update all variable names. Pipes are powerful tools that allow R users to chain together multiple operations at once. Pipes also make R code more readable and easier to comprehend. Pipes are used extensively when working with tidyverse tools.

Let’s combine the assignment pipe operator with the str_replace_all() function from the tidyverse stringr package to replace all spaces with underscores. The code for the NYC_property_sales dataframe looks like this:

So what’s going on here?! Think of the %<>% to mean “and then update”. Let’s put this into context. The above line of code essentially means:

Take the column names from the NYC_property_sales data frame, and then update all column names to replace all spaces with underscores, and then update all column names to lower case.

That’s a long sentence! But that demonstrates the value of the pipe operator to chain together multiple commands. Let’s take a look at the updated column names:

## [1] "borough" "neighborhood" 
## [3] "building_class_category" "tax_class_at_present" 
## [5] "block" "lot" 
## [7] "ease-ment" "building_class_at_present" 
## [9] "address" "apartment_number" 
## [11] "zip_code" "residential_units" 
## [13] "commercial_units" "total_units" 
## [15] "land_square_feet" "gross_square_feet" 
## [17] "year_built" "tax_class_at_time_of_sale" 
## [19] "building_class_at_time_of_sale" "sale_price" 
## [21] "sale_date"

That looks better!

10. The Pipe Operation

Typically when working with tidyverse tools, we’ll work with the single-pipe (%>%) from magrittr. The pipe is a way to link together multiple commands. Recall from above how we can think of %<>% as “and then update”? Well, the single pipe can be thought of simply as “and then”. Here’s an example using the commands we’ve learned so far: