Data Transformation and Manipulation
Bocheng Zhang
Data Transformation with Transportation Data
Visualization and modelling are important tools for insight generation, but it is rare that you get the data in exactly the right form you need. Therefore, the topic of this R lecture is Data Transformation and Manipulation. This handout is a summary and re-write of Chapter 5 in “R for Data Science.”
Introduction
When conducting research and before analyzing data, often you’ll need to create some new variables, or maybe you just want to rename the variables or reorder the observations in order to make the data a little easier to work with. The most important thing is that you always need to make sure your data frame is at least suitable for your research questions.
Here, we will mainly focus on 4 types of data manipulation:
Pick rows/observations
Reorder the rows/observations
Pick columns/variables
Create new columns/variables
Prerequisites
In this handout, we are going to focus on how to use the dplyr package, another core member of the big tidyverse package. We will illustrate the key ideas using data from the nycflights13 package, a built-in collection of data frames that contain a data frame about flights departing NYC in the year of 2013.
To load our package and the data frame collection into R, run:
To load the data into our environment, run:
flights<-nycflights13::flightsThis data frame contains all 336,776 flights that departed from New York City (i.e. JFK, LGA, or EWR) in 2013. The data comes from the US Bureau of Transportation Statistics, and is documented in ?flights. There are 19 columns in this data frame so it has 19 variables.
Please also remember to take a look at other variables in this data frame.
dplyr Basics
As aforementioned, we will mainly focus on 4 types of data manipulation (pick rows/observations, reorder the rows/observations, pick columns/variables, create new columns/variables).
These 4 types are the vast majority of data manipulation challenges you can encounter. All these 4 types of manipulation can be completed using dplyr functions. Therefore, we will learn 4 key dplyr functions that allow your to solve the 4 types of data manipulation challenges:
Pick rows/observations (
filter())Reorder the rows/observations (
arrange())Pick columns/variables (
select())Create new columns/variables (
mutate())
All verbs work similarly:
The first argument is a data frame.
The subsequent arguments describe what to do with the data frame, using the variable names (without quotes).
The result is a new data frame.
Let’s dive in and see how these verbs work!
Filter Rows with filter()
filter() allows you to subset observations based on their values. The first argument is the name of the data frame. The second and subsequent arguments are the expressions that filter the data frame. For example, we can pick all flights on January with:
filter(flights, month == 1)## # A tibble: 27,004 x 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ... with 26,994 more rows, and 11 more variables: arr_delay <dbl>,
## # carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## # air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>When you run that line of code, dplyr executes the filtering operation and returns a new data frame. dplyr functions never modify their inputs, so if you want to save the result, you’ll need to use the assignment operator, <-. In this way, you will get a new data frame in your environment:
January<-filter(flights, month == 1)Comparisons
To use filtering effectively, you have to know how to select the observations that you want using the comparison operators. R provides the standard suite: >, >=, <, <=, != (not equal), and == (equal).
When you’re starting out with R, the easiest most common to make is to use = instead of == when testing for equality. When this happens you’ll get an informative error:
Consider the following examples and write the codes:
Pick observations from the first half of the year
Pick observations from the second half of the year
Pick observations that does not belong to September
Logical Operators
A logical operator helps to combine multiple arguments together (adding more requirements on filter()). There are three basic Boolean operators, AND, OR, and NOT. In R, they are represented by:
AND (
&or,)OR (
|)NOT (
!)
Look at the Venn diagrams below and the complete set of Boolean operations, where x is the left-hand circle, y is the right-hand circle, and the shaded region show which parts each operator selects:
Consider the following examples:
Pick observations from January and February
Pick observations from January to April
Pick observations NOT from January and February
Pick observations on January 1st
These are just some simple examples, but once you understand these basics, you can easily execute complicated filter tasks to meet your research purposes in your research project.
Missing Values
Missing value, or NAs (not available), can be common when carrying out a research project. If you want to decide if a value is missing, use is.na(). Another way (also an easier way) to find out if your dataset contain any missing value is using the summary function.
You can also filter out observations with missing values. For example, if you want to drop observations with departure time (dep_time) as missing values, run:
filter(flights,is.na(dep_time)==FALSE)## # A tibble: 328,521 x 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ... with 328,511 more rows, and 11 more variables: arr_delay <dbl>,
## # carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## # air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>Arrange rows with arrange()
arrange() works similarly to filter() except that instead of selecting rows, it changes their order. It takes a data frame and a set of column names (or more complicated expressions) to order by. You can choose to rearrange rows by either ascending or descending order, and you can also reorder variables of different types, including numerical and categorical.
For example, if you want to re-order by departure delay in ascending order, run:
arrange(flights,dep_delay)## # A tibble: 336,776 x 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 12 7 2040 2123 -43 40 2352
## 2 2013 2 3 2022 2055 -33 2240 2338
## 3 2013 11 10 1408 1440 -32 1549 1559
## 4 2013 1 11 1900 1930 -30 2233 2243
## 5 2013 1 29 1703 1730 -27 1947 1957
## 6 2013 8 9 729 755 -26 1002 955
## 7 2013 10 23 1907 1932 -25 2143 2143
## 8 2013 3 30 2030 2055 -25 2213 2250
## 9 2013 3 2 1431 1455 -24 1601 1631
## 10 2013 5 5 934 958 -24 1225 1309
## # ... with 336,766 more rows, and 11 more variables: arr_delay <dbl>,
## # carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## # air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>Similarly, if you want to re-order by departure delay in descending order, run:
arrange(flights,desc(dep_delay))## # A tibble: 336,776 x 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 9 641 900 1301 1242 1530
## 2 2013 6 15 1432 1935 1137 1607 2120
## 3 2013 1 10 1121 1635 1126 1239 1810
## 4 2013 9 20 1139 1845 1014 1457 2210
## 5 2013 7 22 845 1600 1005 1044 1815
## 6 2013 4 10 1100 1900 960 1342 2211
## 7 2013 3 17 2321 810 911 135 1020
## 8 2013 6 27 959 1900 899 1236 2226
## 9 2013 7 22 2257 759 898 121 1026
## 10 2013 12 5 756 1700 896 1058 2020
## # ... with 336,766 more rows, and 11 more variables: arr_delay <dbl>,
## # carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## # air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>If you provide more than one column name, each additional column will be used to break ties in the values of preceding columns:
arrange(flights,dep_delay,arr_delay)## # A tibble: 336,776 x 19
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 12 7 2040 2123 -43 40 2352
## 2 2013 2 3 2022 2055 -33 2240 2338
## 3 2013 11 10 1408 1440 -32 1549 1559
## 4 2013 1 11 1900 1930 -30 2233 2243
## 5 2013 1 29 1703 1730 -27 1947 1957
## 6 2013 8 9 729 755 -26 1002 955
## 7 2013 3 30 2030 2055 -25 2213 2250
## 8 2013 10 23 1907 1932 -25 2143 2143
## 9 2013 5 5 934 958 -24 1225 1309
## 10 2013 9 18 1631 1655 -24 1812 1845
## # ... with 336,766 more rows, and 11 more variables: arr_delay <dbl>,
## # carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## # air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>Select Columns with select()
In a research project, it’s not uncommon to get a dataset with plenty of variables. However, due to your research purposes, you are usually only interested in a few subset of them.
In this case, the first challenge is often narrowing in on the variables you’re actually interested in. select() allows you to rapidly zoom in on a useful subset using operations based on the names of the variables.
select() is not terribly useful with our flights data because we only have 19 variables, but you can still get the general idea.
- Select columns by the names of the variables (
year,month, andday):
select(flights,year,month,day)## # A tibble: 336,776 x 3
## year month day
## <int> <int> <int>
## 1 2013 1 1
## 2 2013 1 1
## 3 2013 1 1
## 4 2013 1 1
## 5 2013 1 1
## 6 2013 1 1
## 7 2013 1 1
## 8 2013 1 1
## 9 2013 1 1
## 10 2013 1 1
## # ... with 336,766 more rows- Select columns between variables
yearandsched_dep_time
select(flights,year:sched_dep_time)## # A tibble: 336,776 x 5
## year month day dep_time sched_dep_time
## <int> <int> <int> <int> <int>
## 1 2013 1 1 517 515
## 2 2013 1 1 533 529
## 3 2013 1 1 542 540
## 4 2013 1 1 544 545
## 5 2013 1 1 554 600
## 6 2013 1 1 554 558
## 7 2013 1 1 555 600
## 8 2013 1 1 557 600
## 9 2013 1 1 557 600
## 10 2013 1 1 558 600
## # ... with 336,766 more rows- Select columns except those between
yearandsched_dep_time
select(flights,!(year:sched_dep_time))## # A tibble: 336,776 x 14
## dep_delay arr_time sched_arr_time arr_delay carrier flight tailnum origin
## <dbl> <int> <int> <dbl> <chr> <int> <chr> <chr>
## 1 2 830 819 11 UA 1545 N14228 EWR
## 2 4 850 830 20 UA 1714 N24211 LGA
## 3 2 923 850 33 AA 1141 N619AA JFK
## 4 -1 1004 1022 -18 B6 725 N804JB JFK
## 5 -6 812 837 -25 DL 461 N668DN LGA
## 6 -4 740 728 12 UA 1696 N39463 EWR
## 7 -5 913 854 19 B6 507 N516JB EWR
## 8 -3 709 723 -14 EV 5708 N829AS LGA
## 9 -3 838 846 -8 B6 79 N593JB JFK
## 10 -2 753 745 8 AA 301 N3ALAA LGA
## # ... with 336,766 more rows, and 6 more variables: dest <chr>, air_time <dbl>,
## # distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>Rename a variable’s name
select() can also be used to rename variables. For instance:
select(flights, Year=year)## # A tibble: 336,776 x 1
## Year
## <int>
## 1 2013
## 2 2013
## 3 2013
## 4 2013
## 5 2013
## 6 2013
## 7 2013
## 8 2013
## 9 2013
## 10 2013
## # ... with 336,766 more rowsHowever, it is rarely useful because select() drops all of the variables not explicitly mentioned.
Instead, use rename(), which is a variant of select() that keeps all the variables that aren’t explicitly mentioned:
rename(flights,Year=year)## # A tibble: 336,776 x 19
## Year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ... with 336,766 more rows, and 11 more variables: arr_delay <dbl>,
## # carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## # air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>The everything() Helper
Another option is to use select() in conjunction with the everything() helper. This is useful if you have a handful of variables you’d like to move to the start of the data frame. For example:
select(flights, carrier, air_time, everything())## # A tibble: 336,776 x 19
## carrier air_time year month day dep_time sched_dep_time dep_delay arr_time
## <chr> <dbl> <int> <int> <int> <int> <int> <dbl> <int>
## 1 UA 227 2013 1 1 517 515 2 830
## 2 UA 227 2013 1 1 533 529 4 850
## 3 AA 160 2013 1 1 542 540 2 923
## 4 B6 183 2013 1 1 544 545 -1 1004
## 5 DL 116 2013 1 1 554 600 -6 812
## 6 UA 150 2013 1 1 554 558 -4 740
## 7 B6 158 2013 1 1 555 600 -5 913
## 8 EV 53 2013 1 1 557 600 -3 709
## 9 B6 140 2013 1 1 557 600 -3 838
## 10 AA 138 2013 1 1 558 600 -2 753
## # ... with 336,766 more rows, and 10 more variables: sched_arr_time <int>,
## # arr_delay <dbl>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## # distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>Add New Variables with mutate()
In a research project, besides selecting sets of existing columns with select(), it’s often useful to add new columns that are functions of existing columns. That’s the job of mutate(). mutate() always adds new columns at the end of your dataset.
Suppose we want to create a new variable the represents the difference between dep_delay and arr_delay called gain and another new variable that indicates the speed of the flight in miles per hour called speed, run:
mutate(flights, gain = dep_delay - arr_delay,
speed = (distance / air_time)*60)## # A tibble: 336,776 x 21
## year month day dep_time sched_dep_time dep_delay arr_time sched_arr_time
## <int> <int> <int> <int> <int> <dbl> <int> <int>
## 1 2013 1 1 517 515 2 830 819
## 2 2013 1 1 533 529 4 850 830
## 3 2013 1 1 542 540 2 923 850
## 4 2013 1 1 544 545 -1 1004 1022
## 5 2013 1 1 554 600 -6 812 837
## 6 2013 1 1 554 558 -4 740 728
## 7 2013 1 1 555 600 -5 913 854
## 8 2013 1 1 557 600 -3 709 723
## 9 2013 1 1 557 600 -3 838 846
## 10 2013 1 1 558 600 -2 753 745
## # ... with 336,766 more rows, and 13 more variables: arr_delay <dbl>,
## # carrier <chr>, flight <int>, tailnum <chr>, origin <chr>, dest <chr>,
## # air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>, time_hour <dttm>,
## # gain <dbl>, speed <dbl>If you only want to keep the new variables, use transmute(), a variant of mutate() instead of mutate():
transmute(flights, gain = dep_delay - arr_delay,
speed = (distance / air_time)*60)## # A tibble: 336,776 x 2
## gain speed
## <dbl> <dbl>
## 1 -9 370.
## 2 -16 374.
## 3 -31 408.
## 4 17 517.
## 5 19 394.
## 6 -16 288.
## 7 -24 404.
## 8 11 259.
## 9 5 405.
## 10 -10 319.
## # ... with 336,766 more rowsConsidering the following example:
Create a new variable called
hoursto representair_timein hourCreate a new variable called
gain_per_hourwithhoursandgainto represent a flight’s gain or loss in time per hour
Useful Creation Functions
There are many functions for creating new variables that you can use with mutate().
There is no way to list every possible function that you might use, but here is two functions that are frequently useful:
Arithmetic operators:
+,-,*,/,^. Arithmetic operators are also useful in conjunction with the aggregate functions. For example,x / sum(x)calculates the proportion of a total, andy - mean(y)computes the difference from the mean.Logs:
log(),log2(),log10(). Logarithms are an incredibly useful transformation for dealing with data that ranges across multiple orders of magnitude.
Note that different creation functions can always be combined together.