Learning Objectives

  • Describe what a data frame is.
  • Load external data from a .csv file into a data frame.
  • Summarize the contents of a data frame.
  • Describe what a factor is.
  • Convert between strings and factors.
  • Reorder and rename factors.
  • Change how character strings are handled in a data frame.
  • Format dates.

Presentation of the 311 Service Requests Data

We are studying the service requests to the 311 Service in New York City. The dataset is stored as a comma separated value (CSV) file. Each row holds information for a single request, and the columns represent:

Column Description
Unique.Key Unique identifier of a Service Request (SR) in the open data set
Created.Date Date SR was created
Closed.Date Date SR was closed by responding agency
Agency Acronym of responding City Government Agency
Agency.Name Full Agency name of responding City Government Agency
Complaint.Type This is the fist level of a hierarchy identifying the topic of the incident or condition.
Descriptor This is associated to the Complaint Type, and provides further detail on the incident or condition. Descriptor values are dependent on the Complaint Type, and are not always required in SR.
Location.Type Describes the type of location used in the address information
Incident.Zip Incident location zip code, provided by geo validation.
Incident.Address House number of incident address provided by submitter.
Street.Name Street name of incident address provided by the submitter
City City of the incident location provided by geovalidation.
Status Status of SR submitted
Due.Date Date when responding agency is expected to update the SR. This is based on the Complaint Type and internal Service Level Agreements (SLAs).
Borough Provided by the submitter and confirmed by geovalidation.
Open.Data.Channel.Type Indicates how the SR was submitted to 311. i.e. By Phone, Online, Mobile, Other or Unknown.
Latitude Geo based Lat of the incident location
Longitude Geo based Long of the incident location

This file contains all requests for the period December 01, 2017 - January 31, 2018, for the lab purposes half of less important columns were dropped.

We are going to use the R function download.file() to download the CSV file that contains the requests data from KTH Canvas, and we will use read.csv() to load into memory the content of the CSV file as an object of class data.frame.

You are now ready to load the data:

This statement doesn’t produce any output because, as you might recall, assignments don’t display anything. If we want to check that our data has been loaded, we can see the contents of the data frame by typing its name: requests.

Wow… that was a lot of output. At least it means the data loaded properly. Let’s check the top (the first 6 lines) of this data frame using the function head():

#>   Unique.Key           Created.Date            Closed.Date Agency
#> 1   37852239 12/01/2017 12:00:00 AM 12/04/2017 02:45:00 PM    DOT
#> 2   37831819 12/01/2017 12:00:00 AM 12/14/2017 12:00:00 AM  DOHMH
#> 3   37831815 12/01/2017 12:00:00 AM 12/12/2017 12:00:00 AM  DOHMH
#> 4   37831611 12/01/2017 12:00:00 AM 12/08/2017 12:00:00 AM  DOHMH
#> 5   37831575 12/01/2017 12:00:00 AM 12/01/2017 12:00:00 AM  DOHMH
#> 6   37831560 12/01/2017 12:00:00 AM 12/22/2017 12:00:00 AM  DOHMH
#>                               Agency.Name                 Complaint.Type
#> 1            Department of Transportation       Traffic Signal Condition
#> 2 Department of Health and Mental Hygiene Unsanitary Animal Pvt Property
#> 3 Department of Health and Mental Hygiene                         Rodent
#> 4 Department of Health and Mental Hygiene                         Rodent
#> 5 Department of Health and Mental Hygiene                         Rodent
#> 6 Department of Health and Mental Hygiene                         Rodent
#>                     Descriptor                 Location.Type Incident.Zip
#> 1            Pedestrian Signal                                      11233
#> 2                          Dog  3+ Family Apartment Building        11207
#> 3                 Rat Sighting       3+ Family Apt. Building        11238
#> 4 Condition Attracting Rodents 1-2 Family Mixed Use Building        11218
#> 5                 Rat Sighting       3+ Family Apt. Building        11215
#> 6                 Rat Sighting           1-2 Family Dwelling        11691
#>          Incident.Address         Street.Name         City Status
#> 1                                                 BROOKLYN Closed
#> 2  200 HIGHLAND BOULEVARD  HIGHLAND BOULEVARD     BROOKLYN Closed
#> 3          375 LINCOLN PL          LINCOLN PL     BROOKLYN Closed
#> 4 746 CONEY ISLAND AVENUE CONEY ISLAND AVENUE     BROOKLYN Closed
#> 5           634 11 STREET           11 STREET     BROOKLYN Closed
#> 6    22-15 COLLIER AVENUE      COLLIER AVENUE Far Rockaway Closed
#>                 Due.Date  Borough Open.Data.Channel.Type Latitude
#> 1                        BROOKLYN                UNKNOWN 40.67220
#> 2 12/31/2017 08:07:43 AM BROOKLYN                 ONLINE 40.68169
#> 3 12/31/2017 10:37:47 PM BROOKLYN                 MOBILE 40.67318
#> 4 12/31/2017 11:12:13 PM BROOKLYN                 ONLINE 40.63941
#> 5 12/31/2017 01:41:11 PM BROOKLYN                 ONLINE 40.66369
#> 6 12/31/2017 01:46:21 PM   QUEENS                  PHONE 40.59873
#>   Longitude
#> 1 -73.91132
#> 2 -73.89404
#> 3 -73.96364
#> 4 -73.96884
#> 5 -73.97847
#> 6 -73.75743

Note

read.csv assumes that fields are delineated by commas, however, in several countries, the comma is used as a decimal separator and the semicolon (;) is used as a field delineator. If you want to read in this type of files in R, you can use the read.csv2 function. It behaves exactly like read.csv but uses different parameters for the decimal and the field separators. If you are working with another format, they can be both specified by the user. Check out the help for read.csv() by typing ?read.csv to learn more. There is also the read.delim() for in tab separated data files. It is important to note that all of these functions are actually wrapper functions for the main read.table() function with different arguments. As such, the requests data above could have also been loaded by using read.table() with the separation argument as ,. The code is as follows: requests <- read.table(file="data/requests.csv", sep=",", header=TRUE). The header argument has to be set to TRUE to be able to read the headers as by default read.table() has the header argument set to FALSE.

What are data frames?

Data frames are the de facto data structure for most tabular data, and what we use for statistics and plotting.

A data frame can be created by hand, but most commonly they are generated by the functions read.csv() or read.table(); in other words, when importing spreadsheets from your hard drive (or the web).

A data frame is the representation of data in the format of a table where the columns are vectors that all have the same length. Because columns are vectors, each column must contain a single type of data (e.g., characters, integers, factors). For example, here is a figure depicting a data frame comprising a numeric, a character, and a logical vector.

We can see this when inspecting the structure of a data frame with the function str():

Inspecting data.frame Objects

We already saw how the functions head() and str() can be useful to check the content and the structure of a data frame. Here is a non-exhaustive list of functions to get a sense of the content/structure of the data. Let’s try them out!

  • Size:
    • dim(requests) - returns a vector with the number of rows in the first element, and the number of columns as the second element (the dimensions of the object)
    • nrow(requests) - returns the number of rows
    • ncol(requests) - returns the number of columns
  • Content:
    • head(requests) - shows the first 6 rows
    • tail(requests) - shows the last 6 rows
  • Names:
    • names(requests) - returns the column names (synonym of colnames() for data.frame objects)
    • rownames(requests) - returns the row names
  • Summary:
    • str(requests) - structure of the object and information about the class, length and content of each column
    • summary(requests) - summary statistics for each column

Note: most of these functions are “generic”, they can be used on other types of objects besides data.frame.

Challenge

Based on the output of str(requests), can you answer the following questions?

  • What is the class of the object requests?
  • How many rows and how many columns are in this object?
  • How many types of complaints have been recorded during these requests?

Answer 

#> 'data.frame':  457177 obs. of  18 variables:
#>  $ Unique.Key            : int  37852239 37831819 37831815 37831611 37831575 37831560 37831558 37831533 37831511 37831510 ...
#>  $ Created.Date          : Factor w/ 338156 levels "01/01/2018 01:00:00 AM",..: 179262 179262 179262 179262 179262 179262 179262 179262 179262 179262 ...
#>  $ Closed.Date           : Factor w/ 162633 levels "","01/01/2001 01:04:44 AM",..: 103919 126734 122109 114146 100256 144995 142628 105433 98940 105431 ...
#>  $ Agency                : Factor w/ 25 levels "ACS","COIB","DCA",..: 14 12 12 12 12 12 12 12 12 12 ...
#>  $ Agency.Name           : Factor w/ 311 levels "A - Bronx","A - Brooklyn",..: 46 41 41 41 41 41 41 41 41 41 ...
#>  $ Complaint.Type        : Factor w/ 207 levels "Adopt-A-Basket",..: 189 194 158 158 158 158 158 80 158 158 ...
#>  $ Descriptor            : Factor w/ 919 levels "","1 Missed Collection",..: 614 242 676 176 676 676 761 3 676 676 ...
#>  $ Location.Type         : Factor w/ 124 levels "","1-, 2- and 3- Family Home",..: 1 7 8 4 8 3 8 88 32 8 ...
#>  $ Incident.Zip          : Factor w/ 334 levels "","*","00000",..: 211 186 216 197 194 279 142 228 252 185 ...
#>  $ Incident.Address      : Factor w/ 147070 levels "","-999 168 ST W",..: 1 48978 88223 126514 116937 56023 78477 53750 2324 67999 ...
#>  $ Street.Name           : Factor w/ 10014 levels "","*","0 BOND STREET",..: 1 5427 6129 3199 92 3160 8903 6990 29 8370 ...
#>  $ City                  : Factor w/ 196 levels "","*","ANDREWS",..: 24 24 24 24 24 52 22 12 135 24 ...
#>  $ Status                : Factor w/ 7 levels "Assigned","Closed",..: 2 2 2 2 2 2 2 2 5 2 ...
#>  $ Due.Date              : Factor w/ 146703 levels "","01/01/2018 01:00:52 PM",..: 1 145699 146313 146430 144512 144522 145166 112537 145989 146454 ...
#>  $ Borough               : Factor w/ 6 levels "BRONX","BROOKLYN",..: 2 2 2 2 2 4 1 4 4 2 ...
#>  $ Open.Data.Channel.Type: Factor w/ 5 levels "MOBILE","ONLINE",..: 5 2 1 2 2 4 1 2 1 1 ...
#>  $ Latitude              : num  40.7 40.7 40.7 40.6 40.7 ...
#>  $ Longitude             : num  -73.9 -73.9 -74 -74 -74 ...

Indexing and subsetting data frames

Our requests data frame has rows and columns (it has 2 dimensions), if we want to extract some specific data from it, we need to specify the “coordinates” we want from it. Row numbers come first, followed by column numbers. However, note that different ways of specifying these coordinates lead to results with different classes.

: is a special function that creates numeric vectors of integers in increasing or decreasing order, test 1:10 and 10:1 for instance.

You can also exclude certain indices of a data frame using the “-” sign:

Data frames can be subset by calling indices (as shown previously), but also by calling their column names directly:

In RStudio, you can use the autocompletion feature to get the full and correct names of the columns.

Challenge

  1. Create a data.frame (requests_200) containing only the data in row 200 of the requests dataset.

  2. Notice how nrow() gave you the number of rows in a data.frame?

    • Use that number to pull out just that last row in the data frame.
    • Compare that with what you see as the last row using tail() to make sure it’s meeting expectations.
    • Pull out that last row using nrow() instead of the row number.
    • Create a new data frame (requests_last) from that last row.

  3. Use nrow() to extract the row that is in the middle of the data frame. Store the content of this row in an object named requests_middle.

  4. Combine nrow() with the - notation above to reproduce the behavior of head(requests), keeping just the first through 6th rows of the requests dataset.

Answer

Factors

When we did str(requests) we saw that several of the columns consist of integer & numerics (Unique.Key,Latitude & Longitude). The other columns (Created.Date, Closed.Date, Agency, Agency.Name, …) however, are of a special class called factor. Factors are very useful and actually contribute to making R particularly well suited to working with data. So we are going to spend a little time introducing them.

Factors represent categorical data. They are stored as integers associated with labels and they can be ordered or unordered. While factors look (and often behave) like character vectors, they are actually treated as integer vectors by R. So you need to be very careful when treating them as strings.

Once created, factors can only contain a pre-defined set of values, known as levels. By default, R always sorts levels in alphabetical order. For instance, if you have a factor with 2 levels:

R will assign 1 to the level "closed" and 2 to the level "open" (because c comes before o, even though the first element in this vector is "open"). You can see this by using the function levels() and you can find the number of levels using nlevels():

Sometimes, the order of the factors does not matter, other times you might want to specify the order because it is meaningful (e.g., “low”, “medium”, “high”), it improves your visualization, or it is required by a particular type of analysis. Here, one way to reorder our levels in the sex vector would be:

#> [1] open   closed closed open  
#> Levels: closed open
#> [1] open   closed closed open  
#> Levels: open closed

In R’s memory, these factors are represented by integers (1, 2, 3), but are more informative than integers because factors are self describing: "female", "male" is more descriptive than 1, 2. Which one is “male”? You wouldn’t be able to tell just from the integer data. Factors, on the other hand, have this information built in. It is particularly helpful when there are many levels (like the complaint types or borough names in our example dataset).

Converting factors

If you need to convert a factor to a character vector, you use as.character(x).

Converting factors where the levels appear as numbers (such as concentration levels, or years) to a numeric vector is a little trickier. The as.numeric() function returns the index values of the factor, not its levels, so it will result in an entirely new (and unwanted in this case) set of numbers. One method to avoid this is to convert factors to characters, and then to numbers. Another method is to use the levels() function. Compare:

Notice that in the levels() approach, three important steps occur:

  • We obtain all the factor levels using levels(year_fct)
  • We convert these levels to numeric values using as.numeric(levels(year_fct))
  • We then access these numeric values using the underlying integers of the vector year_fct inside the square brackets

Renaming factors

When your data is stored as a factor, you can use the plot() function to get a quick glance at the number of observations represented by each factor level. Let’s look at the number of open and closed requests in our dataset:

In addition to the open and closed requests, there are about 10000 requests with other statuses (Assigned, Email sent, Pending, Started, Unassigned). Let’s simplify our analysis and rename all those to Other. Before doing that, we’re going to pull out the data on status and work with that data, so we’re not modifying the working copy of the data frame:

#> [1] Closed Closed Closed Closed Closed Closed
#> Levels: Assigned Closed Email Sent Open Pending Started Unassigned
#> [1] "Assigned"   "Closed"     "Email Sent" "Open"       "Pending"   
#> [6] "Started"    "Unassigned"
#> [1] "Other"  "Closed" "Open"
#> [1] Closed Closed Closed Closed Closed Closed
#> Levels: Other Closed Open

Challenge

  • Rename “Open” and “Other” to “Waiting decision” and “Being processed” respectively.
  • Now that we have renamed the factor level to “Being processed”, can you recreate the barplot such that “Waiting decision” is the first (before “Being processed”)?

Answer

Using stringsAsFactors=FALSE

By default, when building or importing a data frame, the columns that contain characters (i.e. text) are coerced (= converted) into factors. Depending on what you want to do with the data, you may want to keep these columns as character. To do so, read.csv() and read.table() have an argument called stringsAsFactors which can be set to FALSE.

In most cases, it is preferable to set stringsAsFactors = FALSE when importing data and to convert as a factor only the columns that require this data type.

Challenge

  1. We have seen how data frames are created when using read.csv(), but they can also be created by hand with the data.frame() function. There are a few mistakes in this hand-crafted data.frame. Can you spot and fix them? Don’t hesitate to experiment!

  2. Can you predict the class for each of the columns in the following example? Check your guesses using str(country_climate):
    • Are they what you expected? Why? Why not?
    • What would have been different if we had added stringsAsFactors = FALSE when creating the data frame?
    • What would you need to change to ensure that each column had the accurate data type?

    Answer

    • missing quotations around the names of the animals
    • missing one entry in the “feel” column (probably for one of the furry animals)
    • missing one comma in the weight column
    • country, climate, temperature, and northern_hemisphere are factors; has_kangaroo is numeric
    • using stringsAsFactors = FALSE would have made them character instead of factors
    • removing the quotes in temperature and northern_hemisphere and replacing 1 by TRUE in the has_kangaroo column would give what was probably intended

The automatic conversion of data type is sometimes a blessing, sometimes an annoyance. Be aware that it exists, learn the rules, and double check that data you import in R are of the correct type within your data frame. If not, use it to your advantage to detect mistakes that might have been introduced during data entry (a letter in a column that should only contain numbers for instance).

Learn more in this RStudio tutorial

Formatting Dates

One of the most common issues that new (and experienced!) R users have is converting date and time information into a variable that is appropriate and usable during analyses. As a reminder from earlier in this lesson, the best practice for dealing with date data is to ensure that each component of your date is stored as a separate variable. Using str(), We can confirm that our data frame has a separate column for day, month, and year, and that each contains integer values.

We are going to use the ymd() function from the package lubridate (which belongs to the tidyverse; learn more here). . lubridate gets installed as part as the tidyverse installation. When you load the tidyverse (library(tidyverse)), the core packages (the packages used in most data analyses) get loaded. lubridate however does not belong to the core tidyverse, so you have to load it explicitly with library(lubridate)

Start by loading the required package:

ymd() takes a vector representing year, month, and day, and converts it to a Date vector. Date is a class of data recognized by R as being a date and can be manipulated as such. The argument that the function requires is flexible, but, as a best practice, is a character vector formatted as “YYYY-MM-DD”.

Let’s create a date object and inspect the structure:

Now let’s paste the year, month, and day separately - we get the same result:

Now let’s have a look at the dates in the requests dataset. Coming from US those are recorded in Month/Day/Year format, additionally they have time - hours, minutes and seconds. Accordingly, we should apply mdy_hms() function from the lubridate package:

The resulting POSIXct (Date-Time class) vector can be added to requests instead of the old Created.Date column:

Let’s make sure everything worked correctly. One way to inspect the new column is to use summary():

#>                  Min.               1st Qu.                Median 
#> "2017-12-01 00:00:00" "2017-12-17 09:45:00" "2018-01-02 11:32:15" 
#>                  Mean               3rd Qu.                  Max. 
#> "2018-01-01 04:01:48" "2018-01-15 09:23:32" "2018-01-31 00:00:00"

What about Closed.Date? Let’s convert it and check the summary:

#>                  Min.               1st Qu.                Median 
#> "2001-01-01 01:04:44" "2017-12-22 02:06:10" "2018-01-08 18:58:29" 
#>                  Mean               3rd Qu.                  Max. 
#> "2018-01-09 16:21:13" "2018-01-22 23:40:04" "2018-09-08 00:00:00" 
#>                  NA's 
#>                "8249"

Something went wrong: some dates have missing values. Let’s investigate where they are coming from.

We can use the functions we saw previously to deal with missing data to identify the rows in our data frame that are failing. If we combine them with what we learned about subsetting data frames earlier, we can extract all date-containing columns from the records that have NA in our new column date. We will also use head() so we don’t clutter the output:

#>    Created.Date Closed.Date               Due.Date
#> 17   2017-12-01        <NA> 12/31/2017 10:39:13 AM
#> 44   2017-12-01        <NA> 12/31/2017 10:42:33 AM
#> 46   2017-12-01        <NA> 12/31/2017 12:55:22 PM
#> 51   2017-12-01        <NA> 12/31/2017 11:10:40 PM
#> 53   2017-12-01        <NA> 12/31/2017 03:58:29 PM
#> 62   2017-12-01        <NA> 12/31/2017 08:46:04 PM

Why did these dates fail to parse? If you had to use these data for your analyses, how would you deal with this situation?

Challenge

  • Sometimes there can be troubles with US format of date (MDY).
  • How you could recreate the date columns in EU format (YMD or DMY)?>
  • Print the last six (tail) created dates
  • Hint: check the format() function

Answer

Page built on: 📆 2018-09-20 ‒ 🕢 09:08:05

Oleksii Pasichnyi & Data Carpentry, 2018.
Questions? Feedback? Please mail me to oleksii.pasichnyi@abe.kth.se