Reshaping Data

NA values

Sometimes, data will be missing. In the crews data set, we see this for many fields. Inside R, missing data is represented with with a special indication as <NA> (for “not available”).

How missing data is encoded may change. Some sets use empty strings; others use the letters “NA”; others use words like “None” or “Null.” And there are different types of unknowns; sometimes you’ll find a dataset where someone has written in “unknown” or “unclear,” which is not the same as missing data in some cases but is the same in others. By default (as you can see by typing ?read_csv into R or looking in the help pane), read_csv uses a value of c("", "NA"), which means that values which are empty ("") or which are literally the string NA will turned into <NA> values. Sometimes this will be wrong–for instance, NA is a perfectly valuable Library of Congress Classification. And sometimes you may want to specify that things are missing by adding new strings to the list of missing values.

It’s also sometimes necessary to work around <NA> values. You’ll see this a number of times in the code in this book. For instance, if you want to take the mean height in the crews dataset, you get “NA” because some heights are not present. There are two ways to deal with this.

One is to apply a filter that uses the function is.na. We can check each element to ensure that the function is.na says “No!” – technically, FALSE– and keep them in the filter if they do. There’s a slightly faster way, which is to use an exclamation point at the beginning of that line. Just as != means “does not equal,”, ! here too is a shorthand for “not.” So you read this simply as Not, is missing, height.

      crews %>% filter(is.na(height) == FALSE)
# (Equivalently, but faster to type)
crews %>% filter(!is.na(height))
    

Another way is use specific functions tactics for handling NA values. For instance, when using functions like average or median, we can say na.rm = TRUE to remove NA values. In practice, this is a little bit harder, I think.

      crews |>
  summarize(height = mean(height, na.rm=TRUE))
    

Character encoding

If you’re working with new data, it’s probably in the Unicode standard. But if it’s older, characters from outside (things like curly quotes created on a Windows machine in 2003) may cause real problems. If you find yourself working with a file in a different character encoding, R generally allows you to read the file in by specifying the name of the standard. The precise instructions for doing so involve ‘locales’. Extensive discussion of this is available in the readr documentation.

Record formats

Even when you’re given perfectly formatted data, you may experience some trouble reading it in.

In R, most problems will arise from conventions around commenting and quoting files. When you use read_table and encounter errors, most often you want to adjust the arguments to comment and quote characters, which instructs the parser to ignore the “#” sign and quotation marks. Note that some files do use quotation marks as a “quote” character, such as the New Bedford whaling data we looked at in class.

Even the simplest of characters can cause problems. Sometimes, you may find that the end-of-line files aren’t behaving as you would expect. This has to do with the so-called “carriage return” character (called \r in regular expressions, as opposed to \n, the standard newline.

There are two ways to avoid these kinds of problems. One, in reading data, is to be sure always to use read_csv or read_tsv instead of the older functions read.csv and read.tsv that come with R. Students that have problems reading data in are often using copy-and-pasted code from online (which, again is a good strategy) that uses the older function: read_csv has more sensible defaults.

Inconsistent category labels

Almost omnipresent is the problem of inconsistent category labels. It gets to the distinction between datasets of strings and datasets of categories. Carefully curated datasets will always use exactly the same labels for categorical labels; but real-world sets often will not. Labels may be inconsistently capitalized–the whaling set uses “Rig” and “rig” for the same type of boat. Labels may occasionally be misspelled. And so on. This problem is exacerbated in visualization, where any bad values will take up a row.

The tidyverse supplies a few ways to get rid of infrequent values by turning strings into what R calls factors. [pandas calls categoricals.] Factors are vectors where each row in the data is a discrete item that might be repeated across items. fct_lump_n combines everything other than the most common elements into a catchall category called “Other.” Try commenting out the middle line in the code below and see how it changes.

      crews |> 
  mutate(Rig = fct_lump_n(Rig, 3)) |> 
  count(Rig)
    

Date-time and other “type” formats

What is the data type for a year? You might reasonably expect it to be a number. But in fact, you’ll often find that dates are strings.

In converting messy dates, you’ll frequently have to use regular expressions to perform these coercsions.

When creating data yourself, you should use ISO 8601, which uses a “year-month-day” format. Countries around the world differ about whether Pearl Harbor happened on 12/7/41 or 7/12/41; the only sound response if you are creating data is to use 1945-12-07 as your format. The full ISO format for times includes ways of representing dates for which the precise date or the year is not specified, as well as ranges and intervals.

Historical dates

Datetime standards work well when used for the time period that computers have existed, but in historical cases things can get a little strange.

Internally, computers generally represent dates as integers to allow addition and subtraction. Dates are actually surprisingly hard to represent as numbers, which is why the ISO convention represents them as text instead. There are a variety of ways in which date calculations can be complicated. Some (like leap seconds and general relativity) are unlikely to matter unless you’re doing something like sending a spacecraft to another planetary body, in which case you probably should probably be reading a different text. But historical dates present challenges to both numeric and textual representations of numbers.

The most widespread convention for software is to represent date and time, together, as the number of seconds since January 1, 1970. This date– chosen in the 1970s–is known as the “Unix Epoch.” You’re probably familiar with the “Y2K” bug, in which years that were represented as two digit numbers could not accomodate the switch from 1999 to 2000. A similar probably may be coming in 2038 when 32-bit integers–most commonly used to store numbers–are not able to store the current date. (My favorite of the names proposed for this is the “epochalypse”).

The converse of the “epochalypse” is that dates with times before 1901 may not be easily represented in all software. It’s possible this will create issues for you. More likely is that you’ll find yourself working with dates from a country that had not yet switched from the Julian to the Gregorian calendar. Officially, the ISO standard should only be used for dates after 1582-10-15, the date on which Pope Gregory XIII’s new calendar began. (In the Gregorian calendar, the dates from October 5 to October 14 1582 were skipped to realign the calendar with the solar equinox.) In practice, ISO dates may present challenges when used for dates before any given country switched from the Julian to the Gregorian calendar (1752 in American and England, 1912 in China, 1918 in Russia).

Even if you choose to represent dates as simply years, pre-modern dates can create minor challenges. If you need to encode dates before 1 CE, you may need to remember that there is no such thing as “year zero;” the year 1BCE was immediately followed by the year 1AD. Although ISO-8601 requires explicit description of any dates before 1582, the general stqndard is to allow the existence of a year 0, corresponding to what’s usually called “1 BCE”: therefore the year “-0582” is not 582 BCE, but 583 BCE, and so forth.

Reading tables and constructive failure.

Often, if there is something even slightly askew about your input data, read_csv will fail. This may be frustrating. Try to be grateful, though, instead. This failure is the first aspect of something we’ll encounter many times in this class that is a general feature of data analysis: programs that don’t receive exactly the input they expect will simply fail to work, usually “throwing” an error message of some sort.

Here, we’ll look a bit more at the original version of the crewlist dataset we explored earlier.

You can read it on your computer by typing the following code.

      library("tidyverse")
crews = read_csv("https://benschmidt.org/crewlist.csv")
    

If you run this, you will see some alarming text in red. You can read it or try to understand; but the basic problem is that there are thousands of parsing failures. The reason has to do with the data types we’ve been talking about. read_csv will automatically guess at data types; but it turns out that it’s getting some of these wrong because:

1. It doesn’t see any eye colors until thousands of entries in;
2. A few ship IDs have letters in them, but it initially learns to read them as numbers.

The first part of our cleaning is just to work around this by telling it to look farther in. As with any function, ?read_csv at the command prompt will give you instructions.

      crews = read_csv("https://benschmidt.org/crewlist.csv", guess_max = 1000000)
    

Now we can inspect it. Going to the “Environment” pane in the upper right-hand corner, you can see what’s in here.

Where did this data come from? Did they do a good job transcribing? Here’s an example of the original source

R is composed of functions: each of these apply on an object. Each is, essentially, a little program of its own: you can run it on data, and see what happens.

In tidyverse R, the most common data structure is a tibble; it’s essentially a table where the rows correspond to observations, and the columns refer to variables. (Tidyverse R borrows this from the incredibly similarly named ‘data.frame’ object that R has had since its birth. There are basically interchangeable, but using the modern versions helps you avoid a few pitfalls.) It resembles a spreadsheet or database table, but every datapoint has a type.

In this data set, as you’ll see, each row corresponds to an individual crew member, and the columns give information about him, such as the ship he sailed on his, his name, his rank, and so forth.

To look at it, we can start with the simplest program: simply looking at the thing. Type its name and run the code block from the upper right hand corner.

      crews
    

Other formats

There are many different libraries out there for reading data. R is blessed with many packages for importing them. You can read excel files using ‘readxl’, for example. But for many purposes, it may be simpler go into Excel and click “save as CSV” to write to a more standard form.

Using mutate and str_replace to clean data.

Note that the date doesn’t follow a standard form. We’re going to use regular expressions again to clean it up. Notice the steps in the chain here. We’re creating a new column called “year.” Then, we feed the output into the function str_replace, which lets us use a regular expression.

Remember the function str_replace: it is among the most useful tools for data cleaning in R. It lets you use the full power of regular expressions for find-replace operations. This regular expression is complicated–you may have to refer back to your sheet to see what’s going on. Note in particular that the parenthesis are performing a grouping operation. str_replace is a substitution function, so you have to tell it what to replace it with. In this case, the escaped phrase \\1 tells R to substitute with the first matched group. (That’s what \\1 means: \\2 will match the second matched group, and so forth.)

So: suppose we want to pull ‘year’ out as a number.

The tidyverse has a function mutate that changes a dataframe: it either adds a new column, or changes an existing one.

This gets a little complicated, so let’s break it down.

pull extracts a column so you can look at it more closely. Let’s do that with our ApproximateDeparture column.

      crews %>%
  pull(ApproximateDeparture) %>%
  head(10)
    

Let’s build up a tiny program that extracts the year from a date. The first step is to use regular expressions to get to just the year part of the expression. A simple way to do that here is to use the .* regex operator to replace everything up to and including a slash with nothing (the empty string, ““).

      crews %>%
  pull(ApproximateDeparture) %>%
  str_replace(".*/", "") %>%
  head(10)
    

We’re not done yet, though, because these are strings and years should be integers. The function as.numeric does this for us. (There’s an inverse function, as.character, that can turn a number into a string.)

      crews %>%
  pull(ApproximateDeparture) %>%
  str_replace(".*/", "") %>%
  as.numeric() %>%
  head(10)
    

Success! But while this program runs, it doesn’t actually change our original frame. To do so, we need to assign.