# A tibble: 4 × 2
incident_id demographics
<int> <chr>
1 1 Male_Adult
2 2 Female_Juvenile
3 3 Male_Juvenile
4 4 Female_Adult Tidy Data
Have you ever opened a dataset and immediately thought, “This is a mess”? Columns don’t make sense. Dates are inconsistent. And even if you can read it, it’s hard to actually do anything with it. If so, then your second thought might have been, “this is going to be a lot of work to fix”.
Sound familiar? That’s not a coincidence. Rather, it is the reality of working with open-source data or data “in the wild”. In data wrangling, we want to corral that wild data and get it into a format with which we can work.
There’s a common misconception that data analysis is mostly about: building models, creating visualizations, creating output, etc. But in practice, most analysts spend the majority of their time cleaning and organizing data. Ironically, when we (i.e. scholars) teach methods and statistics for crime analysis, most of the time is spent on analysis and very little time is spent on preparing data. As a result, most analysts leave school without some foundational skills in cleaning and data management.
“Tidying” data is one of the most important skills you can develop. In this chapter we are going to focus on turning messy data into something you can actually analyze.
What Is “Tidy Data”?
We’re going to use a concept from the tidyverse (a collection of R packages for data transformation) called tidy data. This gives us a standard way to structure datasets so they are easy to work with and is consistent. Also, organizing data in this way is quickly becoming an industry standard, so knowing what it is and how to do it is valuable.
A dataset is considered tidy when it follows three simple rules:
- Each variable has its own column
- Each observation has its own row
- Each value lives in a single cell
That might sound simple and obvious, but most “wild” datasets violate at least one of these rules and are therefore “untidy”. Let’s see some examples.
Untidy Data: Example 1
See any issues? You probably noticed that “gender” and “age group” are mashed together in the variable demographics. This is a problem because demographics is actually two variables. How might we solve this problem?
Well, we just need to create two variables:
# A tibble: 4 × 3
incident_id gender age_group
<int> <chr> <chr>
1 1 Male Adult
2 2 Female Juvenile
3 3 Male Juvenile
4 4 Female Adult Untidy Data: Example 2
Try this one:
# A tibble: 2 × 3
precinct arrests_2022 arrests_2023
<chr> <dbl> <dbl>
1 A 50 60
2 B 30 45If you said “year” is hidden in column names, then you win a prize! Data organized in this way make it hard to analyze trends by year.
Rather, we would prefer data like this:
# A tibble: 4 × 3
precinct year arrests
<chr> <chr> <dbl>
1 A 2022 50
2 A 2023 60
3 B 2022 30
4 B 2023 45Note the difference. We now have a variable for “year”.
Untidy Data: Example 3
Last one (you got this!):
# A tibble: 2 × 2
incident_id subjects
<int> <chr>
1 1 John;Mike
2 2 Sara;AnnaThese data are untidy because two people are stored in one row. It would be difficult to count or analyze individuals properly.
We can fix this by making a variable for “subjects” and listing each one:
# A tibble: 4 × 2
incident_id subjects
<int> <chr>
1 1 John
2 1 Mike
3 2 Sara
4 2 Anna Why Tidy?
You have seen some examples of untidy and tidy data, but why shoudl you ensure that your data is tidy? There are two main advantages:
There’s a general advantage to picking one consistent way of storing data. If you have a consistent data structure, it’s easier to learn the tools that work with it because they have an underlying uniformity.
There’s a specific advantage to placing variables in columns because it allows R’s vectorized nature to shine. Most built-in R functions work with vectors of values. That makes transforming tidy data feel particularly natural.
dplyr (remember dplyr!?!), ggplot2, and all the other packages in the tidyverse are designed to work with tidy data.
Since we will be working with functions that use tidy data, we need to install tidyverse:
install.packages( "tidyverse" )After installing (or if you already have the package installed), we need to use the library() function to load it:
As a reminder, if you try to use a tidyverse function (or any function in R that is loaded from an external library) without first loading the package, you may encounter an error. If you see an error that says could not find function, check that you have loaded the tidyverse package.
Reshaping Data
Now that we know what tidy data should look like, the next step is learning how to transform messy data into that structure. One of the most common problems you’ll run into is that your data is in the wrong shape.
Take a look at this example:
# A tibble: 2 × 3
precinct arrests_2022 arrests_2023
<chr> <dbl> <dbl>
1 A 50 60
2 B 30 45If I asked you to give me a table of “arrests” over time, what problems do you see?
You probably noticed that the year is embedded in column names. This makes it had to extract this information for year.
Wide vs Long Data
This issue has a specific name. These data are wide, meaning that there are many columns and the variable (in this case “year”) is spread over multiple columns. This is in contrast to data that is long, where there are fewer columns and values stacked into rows.
Think of it like this:
- “Wide data spreads information across columns.”
- “Long data stacks information down rows.”
pivot_longer()
There is where the pivot_longer() function comes in.
First, let’s create our simple wide data object:
data_wide <- data.frame(
precinct = c( "A", "B" ),
arrests_2022 = c( 50, 30 ),
arrests_2023 = c( 60, 45 )
)
data_wide precinct arrests_2022 arrests_2023
1 A 50 60
2 B 30 45Now, let’s create the long data object. Let me show you what it does first and we will go through each line:
data_long <- data_wide |>
pivot_longer(
cols = c( "arrests_2022", "arrests_2023"),
names_to = "year",
values_to = "arrests"
)
data_long# A tibble: 4 × 3
precinct year arrests
<chr> <chr> <dbl>
1 A arrests_2022 50
2 A arrests_2023 60
3 B arrests_2022 30
4 B arrests_2023 45Ok, let’s see what we did:
cols = c( "arrests_2022", "arrests_2023")asks “which column are we reshaping?” and we use two column names (note that we have to usec()to combine them into a vector.)names_to = "year"tellspivot_longer()where the old column names are supposed to go.values_to = "arrests"tells the function what to do with the values.
In this case, they are going to go to a variable called num_arrests.
If you are a bit lost, think about it like this: we are taking column names and turning them into data.
If you are a visual learner, think of it this way:
flowchart LR
A["WIDE DATA<br/>(columns contain values)<br/><pre>
┌───────────┬───────────────┬───────────────┐
│ precinct │ arrests_2022 │ arrests_2023 │
├───────────┼───────────────┼───────────────┤
│ A │ 50 │ 30 │
│ B │ 60 │ 45 │
└───────────┴───────────────┴───────────────┘
</pre>"]
B["pivot_longer()<br/>
cols = starts_with('arrests')<br/>
names_to = 'year'<br/>
values_to = 'arrests'"]
C["LONG DATA<br/>(values stacked into rows)<br/><pre>
┌───────────┬────────────────┬─────────┐
│ precinct │ year │ arrests │
├───────────┼────────────────┼─────────┤
│ A │ arrests_2022 │ 50 │
│ A │ arrests_2023 │ 60 │
│ B │ arrests_2022 │ 30 │
│ B │ arrests_2023 │ 45 │
└───────────┴────────────────┴─────────┘
</pre>"]
A --> B --> C
We are almost to a tidy dataset. What is missing? Take a look at the values for the year variable. It contains the string “arrests_” which was carried over from our transformation. What we need to is remove this information. There is a nice function called str_remove() which takes an object or column and removes any characters. The str_remove() function is in a package called stringr which is part of the tidyverse. We can put that into the mutate() function we we already saw in dpylr.
# make sure we load the functions
library( dplyr )
# now remove the unncessary text
data_long <- data_long |>
mutate(
year = str_remove(year, "arrests_")
)
# take a look
data_long# A tibble: 4 × 3
precinct year arrests
<chr> <chr> <dbl>
1 A 2022 50
2 A 2023 60
3 B 2022 30
4 B 2023 45Now, we can create a table using the tools from dplyr!
Perhaps average arrests by year:
# A tibble: 2 × 2
year avg_arrests
<chr> <dbl>
1 2022 40
2 2023 52.5Or average arrests by precinct:
pivot_wide()
So far, we’ve taken messy wide data and made it tidy. But sometimes we actually want to go the other direction. This usually happens when we’re preparing results for reports or summaries. There is where the pivot_wide() function comes in.
To see the functionality of the pivot_wide() function, let’s create a simple long data object. We’ll use the tibble() function to create a small dataset by hand. A tibble is the tidyverse version of a data frame and works much like the data frames we have already been using. Inside tibble(), we provide the name of each variable followed by the values it should contain. This gives us a quick and convenient way to create a simple dataset that we can use for our example.
Suppose we had some data like this for crimes in two neighborhoods:
# A tibble: 6 × 3
neighborhood year crimes
<chr> <chr> <dbl>
1 A 2020 50
2 A 2021 60
3 A 2022 30
4 B 2020 45
5 B 2021 90
6 B 2022 40If we want this in a format that’s easier to read in a report we could have one row per precinct and separate columns for each year. We can do that by creating a data object that is in wide format using the pivot_wide() function:
data_wide <- data_long |>
pivot_wider(
names_from = year,
values_from = crimes
)
data_wide# A tibble: 2 × 4
neighborhood `2020` `2021` `2022`
<chr> <dbl> <dbl> <dbl>
1 A 50 60 30
2 B 45 90 40Ok, let’s see what we did:
names_from = "year"tellspivot_wider()to use values in the year column as new column names.values_from = "crimes"tells the function to fill the values with counts of crimes.
Note the difference from pivot_longer() in terms of the arguments which uses ...._to = whereas pivot_wider() uses ..._from =.
If you are a visual learner, think of it this way:
flowchart LR A["LONG DATA<br/>(values stacked into rows)<br/><pre> ┌───────────────┬──────┬─────────┐ │ neighborhood │ year │ crimes │ ├───────────────┼──────┼─────────┤ │ A │ 2020 │ 50 │ │ A │ 2021 │ 60 │ │ A │ 2022 │ 30 │ │ B │ 2020 │ 45 │ │ B │ 2021 │ 90 │ │ B │ 2022 │ 40 │ └───────────────┴──────┴─────────┘ </pre>"] B["pivot_wider()<br/> names_from = year<br/> values_from = crimes"] C["WIDE DATA<br/>(columns contain values)<br/><pre> ┌───────────────┬───────────────┬───────────────┬───────────────┐ │ neighborhood │ crimes_2020 │ crimes_2021 │ crimes_2022 │ ├───────────────┼───────────────┼───────────────┼───────────────┤ │ A │ 50 │ 60 │ 30 │ │ B │ 45 │ 90 │ 40 │ └───────────────┴───────────────┴───────────────┴───────────────┘ </pre>"] A --> B --> C
Note that this works if we are using multiple variables as well. For example, say we were looking at arrests and calls for service in a neighborhood and our long data looked like this:
# A tibble: 6 × 4
neighborhood year arrests calls
<chr> <chr> <dbl> <dbl>
1 A 2020 50 100
2 A 2021 60 125
3 A 2022 30 90
4 B 2020 45 265
5 B 2021 90 75
6 B 2022 40 182Then, we can create columns by year for arrests and calls like this:
data_wide <- data_long |>
pivot_wider(
names_from = year,
values_from = c( arrests, calls )
)
data_wide# A tibble: 2 × 7
neighborhood arrests_2020 arrests_2021 arrests_2022 calls_2020 calls_2021
<chr> <dbl> <dbl> <dbl> <dbl> <dbl>
1 A 50 60 30 100 125
2 B 45 90 40 265 75
# ℹ 1 more variable: calls_2022 <dbl>In general, use pivot_wider() when you’re making a table for a report, you want side-by-side comparisons, you’re exporting data (Excel, dashboards), and so on. In comparing the two functions, think of it like this: pivot_longer() helps us prepare data for analysis, whereas pivot_wider() helps us present results.
Summary
In this chapter, you learned that effective data wrangling is not just about changing the values in a dataset. It is also about getting those data into the right structure. The concept of tidy data gives us a consistent way to think about that structure: each variable should have its own column, each observation should have its own row, and each value should occupy a single cell. When data violate these principles, even relatively simple analytical tasks can become difficult.
You also learned that one of the most common structural problems involves the shape of our data. Wide data spread information across columns, while long data stack that information across rows. Using pivot_longer(), we can take information stored across multiple columns and transform it into variables that are easier to analyze. In the other direction, pivot_wider() allows us to spread values across columns, which can be particularly useful when preparing tables, reports, or other outputs. A useful way to remember the distinction is that pivot_longer() often helps us prepare data for analysis, while pivot_wider() often helps us present our results.
Most importantly, tidying data is not an end in itself. We tidy data because a consistent structure makes the other tools in our data wrangling toolkit much easier to use. Once our data are tidy, we can apply the dplyr skills from the previous chapter (e.g. filter(), mutate(), group_by(), summarize()) to answer analytical questions more efficiently. As we continue, these ideas will become part of a larger workflow: identify how the data are structured, reshape them when necessary, and then use our data recipe to transform those data into information we can analyze and communicate.
Test Your Knowledge
Before moving on, use the prompts below to test your understanding of the material in this chapter. Try to answer each question or write the R code needed to complete the task without looking back at the examples:
- In your own words, explain what tidy data means. What are the three rules that a dataset should follow to be considered tidy?
- Suppose a dataset contains a variable called
demographicswith values such as"Male_Adult"and"Female_Juvenile". Explain why this violates the principles of tidy data. How should the data be organized instead? - Consider a crime dataset with columns named
crime_2022,crime_2023, andcrime_2024. Is this dataset in wide or long format? What information is currently stored in the column names? - Explain the difference between wide and long data in your own words. Why is long data often more useful for analysis?
- Write code using
pivot_longer()to reshapecrime_2022,crime_2023, andcrime_2024into two new variables calledyearandcrimes. - After completing the previous transformation, suppose the values of
yearare"crime_2022","crime_2023", and"crime_2024". Usemutate()andstr_remove()to remove"crime_"so that the variable contains only the year. - Suppose you have a long dataset containing the variables
county,year, andcrimes. Write code usingpivot_wider()that creates one row for each county and separate columns based onyear, with the cells containing the values ofcrimes. - What is the difference between the
names_toandvalues_toarguments used withpivot_longer()and thenames_fromandvalues_fromarguments used withpivot_wider()? Explain what each tells R to do. - Imagine a dataset contains a
subjectsvariable where multiple people are stored in the same cell, such as"John;Mike". Explain why this creates a problem for analysis and identify the function introduced in this chapter that could be used to place each subject into a separate row. - Imagine you download a crime dataset from an open data portal. Before beginning your analysis, describe how you would determine whether the dataset is tidy. If it is not, identify the types of changes you might need to make before applying the
dplyrtools you learned in the previous chapter.