When we first start working with data, it’s easy to assume that once we load a dataset into R, we’re ready to analyze it. In reality, that’s almost never the case. Most open data is messy, inconsistent, and not immediately useful for answering questions. It is wild.
In the previous chapter we discussed how to get data tidy, but even with a tidy dataset we are not yet ready for visualization and analysis. We still have to get the objects of analysis and visualization prepared.
That’s where data transformation comes in. Data transformation is the process of taking raw data and turning it into something we can actually use for analysis. We reshape variables, create new ones, and standardize messy information so that patterns can be identified and decisions can be made.
For example, a dataset might give us:
A date like “2023-07-14”
A time stored as a number like “23”
A text field with inconsistent labels like “phx”, “Phoenix”, and “PHOENIX”
On their own, these aren’t very helpful. But through transformation, we can turn them into:
Day of the week (Was this a weekend incident?)
Time category (Did this occur during a night shift?)
Cleaned location data (Where did this occur?)
In this chapter, we’re going to focus on three major types of transformations.
First, logical transformations. These allow us to ask yes/no questions of our data and create new variables based on conditions. For example: Was the incident on a weekend? Did it occur at night? These kinds of variables are essential for analysis.
Second, string transformations. Many important variables, such as location descriptions or offense types, are stored as text. We’ll learn how to clean, standardize, and search text data so that it becomes usable.
Third, date and time transformations. Time is one of the most important dimensions in crime data. We’ll learn how to convert raw date values into meaningful components like month, day of week, and time of day.
All of these transformations have one ultimate goal: to prepare our data for analysis and visualization.
Why is this important?
When we move into visualization using ggplot2 and start building visual displays, as well as building dynamic visualizations using shiny, we won’t just be plotting data we pulled from an open-source location. We’ll be plotting variables that we created, like shifts, weekends, trends over time, and categorized outcomes. So, we need to know how to get our open-source data in shape for these tasks.
Logical Vectors & Conditional Operations
At its core, a logical vector is just a series of TRUE and FALSE values. These come from asking questions of your data.
For example:
- Is the hour greater than 20?
- Is the day Saturday or Sunday?
- Does this record match a certain condition?
In R, every time you ask a question like that, you get back TRUE or FALSE.
To illustrate, let’s start by creating a toy data set of crime incidents. Here we will use the data.frame() function to create the dataset. Earlier, we used tibble(), but I want to illustrate here that both functions behave in very similar ways. data.frame() is part of base R, whereas tibble() comes from the tidyverse. You will encounter both when working with R, so it is useful to become comfortable recognizing and working with either type of data object.
Let’s think of a few questions we can ask with the data:
Which incidents occurred after 7?
Which incidents occurred on a Friday?
The way we ask questions with logical vectors is to use comparison operators:
== equal to (note: = is used to assign objects in R so we use == for an equality statement)
!= not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
Which incidents occurred after 7?
To answer this we can use the > operator like so:
dat$INC_HOUR>7
Let’s take a closer look at our code: we identify the object dat, we use the $ sign to indicate the specific column we are interested in, here it is INC_HOUR, and then we state the logical statement > 7. Before you run this line, look at the data and think about which cases are going to be TRUE and which are going to be FALSE.
It is a vector of logical values telling us for which case the condition is TRUE or FALSE. For example, the first case has a value of 2 for the INC_HOUR variable so the value for the logical vector is FALSE because it is not greater than 7.
Which incidents occurred on a Friday?
dat$INC_DAY_WEEK=="Friday"
Here, we are saying which cases are equal to "Friday". This is an important point to pause and remind you that the R programming language is case sensitive. Also, that "Friday" is a character (as opposed to say, numeric). Try these and see if it gives you the same results:
These are all FALSE because the condition we stated, that dat$INC_DAY_WEEK is equal to "friday" or "FRIDAY" or "Friday ", is not met by any case. This is because our day of the week variable is a character so it wants the EXACT string of letters. Can you see how these three instances are different?
We will discuss strings more below and how to edit them (particularly when there are inconsistencies).
Operators can be used together to satisfy multiple logical conditions using the | (“or”) or the & (“and”) operators:
Think about what the first statement above says: “incident hour is greater than or equal to 7 or incident day is not equal to Friday”. Think about what the second statement above says: “incident hour is greater than or equal to 7 and incident day is not equal to Friday”.
A final operator that is really useful is the %in% operator. This allows us to specify a set of values or strings and then extract the cases that meet that condition. For example we could write:
As we move on with the material, the use of logical vectors will make a lot more sense because you will see that they are a convenient way to filter data. For example, in the prior chapters we saw how the filter() function in the dplyr package could be used to select specific columns. But, we can plug a logical vector into the filter() function to get a subset of data:
id INC_HOUR INC_DAY_WEEK OFFENSE
1 6 7 Saturday Assault
Here, we took just those cases meeting the condition of the logical vector (note: I used dplyr::filter to call the dplyr package rather than loading the library with library( dplyr ), to save space).
Suppose we want cases that are after noon and on the weekend:
Now we shift from logical thinking to text data, which are everywhere in open-source datasets.
Think about variables like:
Offense descriptions
Location fields
City names
Narrative reports
Unlike numbers, text data are often inconsistent (“Phoenix”, “PHOENIX”, “phx”) and messy (extra spaces, abbreviations) making them hard to analyze directly.
Let’s create a separate toy dataset to work with for strings.
dat<-data.frame( id =1:12, city =c("Phoenix", "PHOENIX ", "phx", "Tempe", "tempe", " Glendale","GLENDALE", "Mesa", "mesa ", "Scottsdale", "SCOTTSDALE", "scottsdale"), offense =c("Assault", "ASSAULT", "assault ", "Burglary", "BURGLARY", "burglary","Domestic Violence", "domestic violence", "Domestic Violence","Theft", "THEFT", " theft "), location =c("123 MAIN ST", "123 Main St", "123 Main Street","45 E 2ND AVE", "45 E 2nd Ave", "45 e 2ND AVE","700 W RIO SALADO PKWY", "700 W Rio Salado Pkwy", "700 W RIO SALADO PARKWAY","100 N CENTER RD", "100 N Center Rd", "100 N CENTER RD"))dat
id city offense location
1 1 Phoenix Assault 123 MAIN ST
2 2 PHOENIX ASSAULT 123 Main St
3 3 phx assault 123 Main Street
4 4 Tempe Burglary 45 E 2ND AVE
5 5 tempe BURGLARY 45 E 2nd Ave
6 6 Glendale burglary 45 e 2ND AVE
7 7 GLENDALE Domestic Violence 700 W RIO SALADO PKWY
8 8 Mesa domestic violence 700 W Rio Salado Pkwy
9 9 mesa Domestic Violence 700 W RIO SALADO PARKWAY
10 10 Scottsdale Theft 100 N CENTER RD
11 11 SCOTTSDALE THEFT 100 N Center Rd
12 12 scottsdale theft 100 N CENTER RD
Take a look through the data. Look for the following issues we commonly find with string variables.
There are several:
inconsistent capitalization
extra spaces
abbreviations
punctuation differences
multi-word labels
alternate spellings for the same thing
This is fairly standard for what you will see with open-source data. Let’s take a look at fixing some of these issues.
Standardize Case
A very common first step is to make everything the same case. Why do this? There are several reasons: It makes values easier to compare, it reduces duplicates caused only by case differences, it makes later cleaning more consistent.
For example, using the unique() function, we can list all the unique values of a variable. Since these are strings, we are looking at the unique strings:
We can see that there are several different cases for each name. We can fix this by using some functions from the stringr package in conjunction with functions from dplyr. You can install the package using install.packages( "stringr" ).
We used a few functions, here is what they are doing:
-str_to_title() converts to title case, where only the first letter of each word is capitalized. If you look, you can see that the first letter of “city” and “offense” are now capitalized. - str_to_upper() converts to upper case. For “location” we made all the letters upper case.
There is also:
- str_to_lower() which converts to lower case. - str_to_sentence() which convert to sentence case, where only the first letter of sentence is capitalized (nice for long strings like quotes or descriptions).
Remove White Space
Extra spaces are one of the most common hidden problems. Take a look through the dat object and see if you can spot instances where there is extra space (i.e. white space) that should not be there.
For the variable “city” you can see that the second and ninth cases have an extra space:
To fix this, we can use the str_squish() function which removes leading spaces (space in front of a word), removes trailing spaces (space after a word), and collapses multiple spaces into one:
[1] "123 MAIN ST" "123 MAIN STREET"
[3] "45 E 2ND AVE" "700 W RIO SALADO PKWY"
[5] "700 W RIO SALADO PARKWAY" "100 N CENTER RD"
Group Similar Values
As you can see above, we still have the issue that different naming and abbreviations are used for the same city. We can fix this by building on what we saw above with the %in% operator and the case_when() function. Essentially what we want to do is find all the unique ways that a city is named, and replaced those with a single consistent name.
To do this, we will create a new variable called city_clean which is the cleaned version of the names. We will create a new variable because we don’t want to write over the old names (in case we want them for reference).
we are using case_when() to check conditions we specify from the first to the last value of the variable “city”. What the function is doing is taking each case and if the condition is TRUE, it assigns the value on the right side.
the statement city %in% c("Phx", "Phoenix") ~ "Phoenix" says “take the variable city and if the string is”Phx” or “Phoenix” make the string in city_clean be “Phoenix”.
we repeated this for Temple, Glendale, Mesa, and Scottsdale.
the last line TRUE ~ city tells the function what to do if the conditions above are not met. For example, what should it do if not of the conditions are above are met? Well, we want it to return the value for “city” (otherwise it will return NA; try it!)
Replace Text
Sometimes you do not want a new label, you just want to fix the text. We can use the str_replace_all() function to accomplish this task.
For example, take a look at the “location” variable and you will see that there are a few different usages of “street”. If we want these to be consistent, then we can replace the text:
We have discussed these separately, but once we know what we want to clean up we can put it all together in a single workflow.
Since we have been writing over our dat object, we need to recreate it from the original:
dat<-data.frame( id =1:12, city =c("Phoenix", "PHOENIX ", "phx", "Tempe", "tempe", " Glendale","GLENDALE", "Mesa", "mesa ", "Scottsdale", "SCOTTSDALE", "scottsdale"), offense =c("Assault", "ASSAULT", "assault ", "Burglary", "BURGLARY", "burglary","Domestic Violence", "domestic violence", "Domestic Violence","Theft", "THEFT", " theft "), location =c("123 MAIN ST", "123 Main St", "123 Main Street","45 E 2ND AVE", "45 E 2nd Ave", "45 e 2ND AVE","700 W RIO SALADO PKWY", "700 W Rio Salado Pkwy", "700 W RIO SALADO PARKWAY","100 N CENTER RD", "100 N Center Rd", "100 N CENTER RD"))dat
id city offense location
1 1 Phoenix Assault 123 MAIN ST
2 2 PHOENIX ASSAULT 123 Main St
3 3 phx assault 123 Main Street
4 4 Tempe Burglary 45 E 2ND AVE
5 5 tempe BURGLARY 45 E 2nd Ave
6 6 Glendale burglary 45 e 2ND AVE
7 7 GLENDALE Domestic Violence 700 W RIO SALADO PKWY
8 8 Mesa domestic violence 700 W Rio Salado Pkwy
9 9 mesa Domestic Violence 700 W RIO SALADO PARKWAY
10 10 Scottsdale Theft 100 N CENTER RD
11 11 SCOTTSDALE THEFT 100 N Center Rd
12 12 scottsdale theft 100 N CENTER RD
To see the functionality, let’s create our toy data set to work with:
dat_dates<-data.frame( id =1:10, incident_date =c("2022-01-15","01/22/2021","March 3, 2024","2023/04/10","5-12-2024","2020-06-18","07/04/2024","August 9 2021","2024.09.14","10/31/2022"))dat_dates
The parse_date_time() Function
Essentially what we want is the dates to be the same format. In lubridate there is a function, parse_date_time(), that does exactly this: it takes the input and parses it into a consistent format.
I will give you the code chunk first, then we will work through it:
dat_dates|>mutate( date_clean =parse_date_time(incident_date, orders =c("ymd", "mdy", "B d Y")))
This prints out the messy data as well as a new column “date_clean” that has been parsed. Ok, let’s take a look at what the function is doing:
using mutate(), we are creating a new variable called “date_clean” with the parse_date_time() function.
the parse_date_time() function takes the messy data, “incident_date”, and then uses some additional info to parse it.
the orders = argument takes several pieces: ymd is year-month-day, mdy is month-day-year, and B d Y is full month name, day, year.
The orders = argument might be a bit confusing. Essentially we are telling the function the different ways that the date is presented in the “incident_date” variable. For example, in the dat_dates object, look at the first case and you will see that the date looks like 2022-01-15. This is year, month, day format which the orders = argument recognizes with the ymd value. How about mdy? Take a look at the second case: 01/22/2021. And so on. You can see these different format codes by examining the help page for the parse_date_time() function with: ?parse_date_time.
Working with dates is often a trial and error process of getting a sense of what formats are being used and how to parse them correctly. For example, let’s say we didn’t see that some of the dates are in ymd format and exclude this from orders =:
dat_dates|>mutate( date_clean =parse_date_time(incident_date, orders =c("mdy", "B d Y")))
We get an error that says it failed to parse several cases. These cases have a value of <NA> in the output object. If we take a look, then we can see that these are cases with the year-month-day format. We just need to add that to our orders = statement.
Date parsing is one of the messiest parts of open-source data wrangling. Just keep in mind that the goal is not to memorize formats, but to learn how to recognize patterns and standardize them.
Transformation
Let’s create an object with the cleaned dates:
dat_dates_clean<-dat_dates|>mutate( date_clean =parse_date_time(incident_date, orders =c("ymd", "mdy", "B d Y")))
Now that our date variable is clean, we can actually use it to make some other useful variables:
dat_dates_clean<-dat_dates_clean|>mutate( year =year(date_clean), month =month(date_clean, label =TRUE), day =day(date_clean))
In the statement above we use mutate() to create several variables: “year”, “month”, and “day”. When creating “month” we see the argument label = to TRUE to keep the labels for the months (otherwise it defaults to a numeric value such as “1” for “Jan”).
Now that are data are cleaned, we can use dplyr to answer questions like: Which month has the most incidents?
# A tibble: 9 × 2
month `n()`
<ord> <int>
1 Jan 2
2 Mar 1
3 Apr 1
4 May 1
5 Jun 1
6 Jul 1
7 Aug 1
8 Sep 1
9 Oct 1
Example
Let’s pull this together by working with an example. As we have seen in prior chapters, the DWVpack package contains a data file on crimes in Phoenix, AZ called phx_crime. Take a look at the time_date variable for that object:
# load the librarylibrary(DWVpack)# print out the first few cases using the head() functionhead(phx_crime$time_date)
We can see that the time_date variable contains the date of the incident as well as the time and looks like this: 11/01/2015 00:00. There is a lot of information here. What we want to do is pull it apart and get several variables:
Year
Month
Day
Time
We can use dplyr and lubridate to help us create these variables!
Let’s start by getting the date and time separated. If you take a look at the first entry, 11/01/2015 00:00, you will see that the format is: “month/day/year hour:minute”. There is a function in lubridate called mdy_hm() that is build specifically for this format and converts a character string into a true date-time object that R can recognize and work with.
For example, mdy_hm( "11/1/2015 14:30" ) becomes something like "2015-11-01 14:30:00 UTC" where 2015-11-01 is the date stored in standardized YYYY-MM-DD format, 14:30:00 is the time in 24-hour format, and UTC is the time zone (“Coordinated Universal Time”).
Take a look using the Phoenix data:
# create the objectdatetime<-mdy_hm(phx_crime$time_date)# take a look at what it createstail(datetime)
The formatting codes in format() indicate that we want time in 24-hour (%H) and minutes (%M).
As a final step, we want to pull out calendar components (i.e. year, month, day) as we did above:
year<-year(datetime)# here, we want to keep the label and don't want it abbreviatedmonth<-month(datetime, label =TRUE, abbr =FALSE)day<-day(datetime)
I have presented this in separate steps, but we can put this all together as a “data recipe” using the mutate() function:
phx_crime_clean_date_time<-phx_crime|>mutate(# Parse datetime datetime =mdy_hm(time_date),# Separate date and time date =as_date(datetime), time =format(datetime, "%H:%M"),# Create components year =year(datetime), month =month(datetime, label =TRUE, abbr =FALSE), day =day(datetime))
We will work with these data in subsequent chapters. To avoid having to create a tidy data set with the cleaned date variables, DWVpack includes a data object called tidy_phx_crime which has already cleaned up the date and time variables. I would encourage you to take a look through them and compare. You can do this easily with the following commands: head( tidy_phx_crime ) and head( phx_crime ).
Summary
In this chapter, you learned that getting data into a tidy structure is only part of the data wrangling process. Even when each variable has its own column and each observation has its own row, the values stored in those variables may not yet be useful for analysis. Data transformation allows us to take those existing values and clean or transform them into variables that better represent the questions we want to answer.
We began with logical operations, which allow us to ask questions of our data that return TRUE or FALSE. Using comparison operators such as ==, !=, >, and <, along with operators such as &, |, and %in%, we can define conditions that identify particular observations. These logical conditions become especially powerful when combined with tools such as filter(), mutate(), and case_when() because they allow us to select cases or construct new variables based on meaningful analytical categories.
We then turned to strings, or text data. Crime data frequently contain text fields with inconsistent capitalization, extra spaces, abbreviations, and different labels for the same thing. Using functions from stringr, such as str_to_title(), str_to_upper(), str_squish(), and str_replace_all(), we can standardize these values so that R treats equivalent information consistently. Importantly, these cleaning operations can be combined into a single reproducible workflow rather than making changes manually.
Finally, we examined dates and times, which are especially important in crime analysis. Using lubridate, we learned how to parse messy date and date-time values and extract useful components such as year, month, day, date, and time. The Phoenix crime data used as an example in this chapter demonstrated why this matters: a single date-time field can be transformed into several variables that allow us to investigate when incidents occur and eventually examine temporal patterns in crime.
As with the previous chapters, the goal is not to memorize every function or transformation. Instead, you should begin thinking in terms of a data recipe: identify what is messy or analytically unhelpful about the original variables, determine what you want those variables to look like, and then construct a reproducible sequence of transformations to get there. With our data now tidy, cleaned, and transformed, we are getting closer to the ultimate goal of data wrangling: creating data that allow us to identify and communicate meaningful patterns.
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 the difference between tidying data and transforming data. Why might a dataset be tidy but still require transformation before it can be analyzed?
What is a logical vector? What values would R return for the expression c( 5, 10, 15, 20 ) > 10? Try to answer before running the code.
Using the dat crime incident dataset from this chapter, write a logical statement that identifies incidents occurring at 8:00 PM or later. Remember that INC_HOUR is stored using a 24-hour clock.
Write a logical statement that identifies incidents occurring on Friday, Saturday, or Sunday. Use %in% rather than writing three separate conditions connected with |.
Suppose you want incidents that occurred at 8:00 PM or later and occurred on Friday, Saturday, or Sunday. Combine the previous two conditions and use them inside filter() to return only those incidents.
A city variable contains the values "PHOENIX", "Phoenix", "phoenix", and " Phoenix ". Explain why R does not initially treat all four strings as identical. Write a transformation using functions from stringr that would make these values consistent.
Suppose a location variable contains both "123 MAIN STREET" and "123 MAIN ST". Write a transformation using str_replace_all() that standardizes "STREET" as "ST".
What is the purpose of case_when()? Suppose a variable called offense contains "Auto Theft", "Vehicle Theft", and "Stolen Vehicle". Write a mutate() statement using case_when() that creates a new variable called offense_clean and classifies all three values as "Vehicle Theft". Make sure observations that do not meet these conditions retain their original offense value.
Suppose an incident date is stored as "08/15/2025". What information would you need to give parse_date_time() so that R can correctly interpret this value? Once it has been converted to a date object, identify the lubridate functions you could use to extract its year, month, and day.
Finally, imagine you receive a crime dataset containing a variable called incident_datetime with values such as "07/18/2025 22:35". You want to determine whether crime is more common at night and on weekends. Describe the data recipe you would use to transform this raw variable into the variables needed to answer the question. Identify the transformations you would make and the R functions or logical operations you could use at each step.