Chapter 7 Data Wrangling

7.1 Planning your wrangling

Before you start wrangling data, it’s crucial to envision what the goal is to be. In general, the purpose of wrangling is to take the data you have at hand and put it into glyph-ready form. But glyph-ready for what? This is something you, the data analyst, need to determine based on your own objectives.

7.2 A Framework for Data Wrangling

Over the last half century, researchers have identified a small set of patterns that can be used to describe a wrangling process. Because this set is small, it’s feasible for you to learn quickly what you need to describe the wrangling you have planned.

As you know, doing something in R is accomplished using functions. A function takes one or more inputs (the “arguments”) and returns an output. In thinking about data wrangling, it helps to consider these potential forms for inputs and outputs:

• A data frame, a collection of variables and cases.
• A variable, just a single one from the collection in a data frame.
• A scalar, a single number or character string.

There are three broad families of functions involved in data wrangling. The families differ in what form of input they take in and what form they return.

1. Reduction functions take a variable as input and return a scalar.
2. Transformation functions take one or more existing variables as input and return a new variable.
3. Data verbs take an existing data frame as input and return a new data frame.

Each step in data wrangling involves a data verb and one or more reduction or transformation functions.

7.3 Reduction Functions

Reduction functions summarize or reduce a variable to scalar form. You are likely familiar with several reduction functions:

• mean() — find a single typical value
• sum() — add up numbers into a total
• n() — find how many cases there are

Some other frequently used reduction functions:

• min() and max() — find the smallest and largest value in a variable
• median(), sd() and other functions used in statistics
• n_distinct() — how many different levels are there among the cases

7.4 Transformation Functions

Transformation functions take one or more variables as input and return a new variable. In contrast to reduction functions, which produce a single number — a scalar — by combining all the cases, transformation functions produce a result for each individual case.

Often, transformations are mathematical operations, for instance:

• weight / height
• log10( population )
• round( age )

Other transformation functions include numerical and character comparisons, as in Table 7.4.

Table 7.4: Some of the functions used to compare values.

Another important operation, ifelse() allows you to translate each value in a variable to one of two values, depending on the result of a comparison. For instance

ifelse(age >= 18, "voter", "non-voter")

7.5 Data Verbs

Data verbs carry out an operation on a data frame and return a new data frame. Some data verbs involve the modification of variables, the creation of new variables, or the deletion of existing ones. Other data verbs involve changing the meaning of a case or adding or deleting cases.
In addition to a data frame input, each data verb takes additional arguments that provide the specifics of the operation. Very often, these specifics involve reduction and transformation functions.

There are about a dozen data verbs that are commonly used. Additional data verbs will be introduced in Chapter 10 and beyond, but lots of interesting questions can be investigated with just these two:

• summarise() — turns multiple cases into a single case using reduction functions. “Aggregate” is synonym for “summarise.”
• group_by() — modifies the action of reduction functions so that they give a single value for different groups of cases in a data frame.

To illustrate the uses of summarise(), look at the WorldCities data frame (in the dcData package) with information about the most populous cities in each country. Table 7.5 is a subset of the variables:

Table 7.5: World cities

A simple summary of the data is a count of the number of cities.

WorldCities %>%
  summarise(count = n())

The reduction function is n(). The expression count = n() means that the output data frame should have a variable named count containing the results of n(). [Click to see note.]The n() function doesn’t take any arguments.

Perhaps you want to know the total population in these cities, or the average population per city or the smallest city in the table, as in Table 7.6.

WorldCities %>%
  summarise(averPop  = mean(population, na.rm=TRUE),
            totalPop = sum(population, na.rm=TRUE),
            smallest = min(population, na.rm=TRUE))

Table 7.6: Summary statistics of the population of world cities

The average city on the list has about 110,000 people. The total population of people living in these cities is about 2.6 billion — a bit more than one-third of the world population. The smallest city has … zero people! Evidently, the WorldCities data frame has one or more cases that are not really cities.

Some things to notice about the use of summarise():

• The chaining syntax, %>% has been used to pass the first argument to summarize(). This will be useful later on, when there is more than one step in a transfiguration. It’s OK to end a line with %>%, but never start a line with it.
• The output of summarise() is a data frame. (In this example the output has only one case, but it is still a data frame.)
• summarise() takes named arguments. The name of an argument is taken as the name of the corresponding variable created by summarise().

7.6 group_by

The group_by() data verb sets things up so that other data verbs will perform their action on a group-by-group basis. For instance, Table 7.7 shows the number of cities in WorldCities broken down by country.

WorldCities %>% 
  group_by(country) %>%
  summarise(count = n())

Table 7.7: The number of cities in each country listed in WorldCities.

The group_by() verb should always be followed by another verb — summarise() in the above example. group_by() is the way to indicate to the following verbs that reduction operations should be performed on a group-wise basis.

Using group_by() along with summarise() and n(), provides a basis for counting up the number of cases in groups and subgroups, or for calculating group-wise statistics.

Note that the functions used within arguments to summarise() — functions such as n(), max(), sum(), etc. — are not data verbs. A data verb takes a data frame as input and returns a transfigured data frame as output. In contrast, the reduction functions, n(), sum(), and so on, take a variable as input and return a single number as output.

7.7 Is That All?

The summarise() and group_by() data verbs are team players; they work best in combination with other data verbs. Once you learn those data verbs, particularly filter() and mutate(), you’ll be able to carry out many more operations. The richness of the data-verb system comes from the ways the different verbs can be combined together.

7.8 Exercises

Problem 7.1: For each of the operations listed here, say whether it involves a transformation function or a reduction function or neither.

1. Determine the 3rd largest.
2. Determine the 3rd and 4th largest values.
3. Determine the number of cases.
4. Determine whether a year is a leap year.
5. Determine whether a date is a legal holiday.
6. Determine the range of a set, that is, the max minus the min.
7. Determine which day of the week (e.g., Sun, Mon, …) a given date is.
8. Find the time interval in days spanned by a set of dates.

Problem 7.2: Each of these statements have an error. It might be an error in syntax or an error in the way the data tables are used, etc. Describe what each expression apparently attempts to do, as well as the error(s) that cause them to fail.

BabyNames %>% 
  group_by( "First" ) %>%
  summarise( votesReceived=n() )

Tmp <- group_by(BabyNames, year, sex ) %>% 
  summarise( Tmp, totalBirths=sum(count))

Tmp <- group_by(BabyNames, year, sex) 
summarise( BabyNames, totalBirths=sum(count) )

Problem 7.3: Using the Minneapolis2013 data table in the dcData package, answer these questions:

1. How many cases are there?
2. Who were the top 5 candidates in the Second vote selections.
3. How many ballots are marked “undervote” in
   • First choice selections?
   • Second choice selections?
   • Third choice selections?
4. What are the top 3 combinations of First and Second vote selections? (That is, of all the possible ways a voter might have marked his or her first and second choices, which received the highest number of votes?)
5. Which Precinct had the highest number of ballots cast?

Problem 7.4: Each of these statements has an error. It might be an error in syntax or an error in the way the data tables are used, etc. Write down a correct version of the statement.

BabyNames %>% 
  group_by(BabyNames, year, sex) %>%
  summarise(BabyNames, total = sum(count))

ZipGeography <- 
  group_by(State) %>% 
  summarise(pop = sum(Population))

Minneapolis2013 %>%
  group_by(First) ->
  summarise(voteReceived = n())

summarise(votesReceived = n()) %<% 
  group_by(First) <- Minneapolis2013

Problem 7.5: The data verbs group_by() and summarise() are very frequently used in combination. Experiment with the R code, help documentation, etc to investigate each of the following.

1. How has the result VoterData_A apparently been modified when compared to the original Minneapolis2013 data? What does a case represent in VoterData_A?

VoterData_A <-
  Minneapolis2013 %>%
  group_by(First, Second)

2. How has the result VoterData_B apparently been modified when compared to the original Minneapolis2013 data? What does a case represent in VoterData_B?

VoterData_B <-
  Minneapolis2013 %>%
  summarise( total = n() )

3. How has the result VoterData_C apparently been modified when compared to the original Minneapolis2013 data? What does a case represent in VoterData_C?

VoterData_C <-
  Minneapolis2013 %>%
  group_by(First, Second) %>%
  summarise( total = n() )

4. Here, the group_by() and summarise() steps are reversed and now the result is an error indicating that “Column First is unknown.” Clearly the variable First existed in the Minneapolis2013 data frame, why is it now unknown?

VoterData_D <-
  Minneapolis2013 %>%
  summarise( total = n() ) %>%
  group_by(First, Second) 

## Error: Must group by variables found in `.data`.
## * Column `First` is not found.
## * Column `Second` is not found.

Problem 7.6: Using the ZipGeography data

Find the total land area and population in each state.

1. Make a scatter plot showing the relationship between land area and population for each state.

2. Make a choropleth map showing the population of each state.

3. Make a choropleth map showing the population per unit area of each state.

Problem 7.7: Imagine a data table, Patients, with categorical variables name, diagnosis, sex, and quantitative variable age.

You have a statement in the form

Patients %>%
  group_by(  **SOME_VARIABLES** ) %>%
  summarise(count = n(), meanAge = mean(age))

Replacing **SOME_VARIABLES** with each of the following, tell what variables will appear in the output

1. sex
2. diagnosis
3. sex, diagnosis
4. age, diagnosis
5. age

Problem 7.8: Use the ZipDemography data from the dcData package for the following tasks.

1. Make a scatter plot showing the relationship, if any, between the number of Foreignborn people in a zip code and the number who SpeakalanguageotherthanEnglishathome5yearsandover. (Please note that such long variable names ought to be avoided as a matter of good style.)

Warning: do NOT attempt to facet by ZIP code as you explore solutions. Doing so is very computationally intense (i.e., takes a long time if it finishes at all) only to show that one facet for each ZIP code is not a useful data visualization.

2. Explain what would keep you, at this point, from calculating the fraction of people in each state who have a Bachelorsdegreeorhigher. Say how you would go about constructing such a plot — but don’t actually do it! Too much work.