Subsetting and Summarizing
Much of this lesson was copied or adapted from Jeff Hollister’s materials
Learning Objectives
- Understand bracket subsetting in R.
- Learn how to use key
dplyrverbs to summarize data:select(),filter(),mutate(),group_by(), andsummarize(). - Create analysis pipelines with
%>% - Write data using the
write.tablecommand
Bracket Subsetting
To pull out one or multiple values from an object, we’ll often use square brackets. For subsetting a vector, you place the brackets right next to the name of the object, and inside the brackets type the indices you want to extract. Indexing begins at 1 in R, so weights[1] will give you the first element of weights. You can also specify a range of values, such as weights[1:3].
The same approach can be applied to data frames. Data frames have two dimensions (rows and columns), so the subsetting follows a slightly different pattern: dataframe[rows, columns]. For example:
(The “142 Levels” that appear mean this is a categorical variable and those are the categories.)
gapminder[1, 1] ## First row, first column.[1] Afghanistan
142 Levels: Afghanistan Albania Algeria Angola Argentina ... Zimbabwe
gapminder[1, 3] ## First row, third column[1] 8425333
gapminder[500, 5:6] ## 500th row, 5th and 6th columns lifeExp gdpPercap
500 46.453 521.1341
To pull out single columns you can also use the $ sign. gapminder$pop will give you a vector of all values in the pop column. This is equivalent to doing gapminder[, 5] or gapminder[, "pop"].
Finally, you can set conditions for which data to return. For example:
### Countries and years when populations were less than or equal to 100000
gapminder[gapminder$pop <= 100000, c("country", "year")] country year
421 Djibouti 1952
422 Djibouti 1957
423 Djibouti 1962
1297 Sao Tome and Principe 1952
1298 Sao Tome and Principe 1957
1299 Sao Tome and Principe 1962
1300 Sao Tome and Principe 1967
1301 Sao Tome and Principe 1972
1302 Sao Tome and Principe 1977
1303 Sao Tome and Principe 1982
### All data for Finland
gapminder[gapminder$country == "Finland", ] country year pop continent lifeExp gdpPercap
517 Finland 1952 4090500 Europe 66.550 6424.519
518 Finland 1957 4324000 Europe 67.490 7545.415
519 Finland 1962 4491443 Europe 68.750 9371.843
520 Finland 1967 4605744 Europe 69.830 10921.636
521 Finland 1972 4639657 Europe 70.870 14358.876
522 Finland 1977 4738902 Europe 72.520 15605.423
523 Finland 1982 4826933 Europe 74.550 18533.158
524 Finland 1987 4931729 Europe 74.830 21141.012
525 Finland 1992 5041039 Europe 75.700 20647.165
526 Finland 1997 5134406 Europe 77.130 23723.950
527 Finland 2002 5193039 Europe 78.370 28204.591
528 Finland 2007 5238460 Europe 79.313 33207.084
Challenge
Which of the following are NOT equivalent?
gapminder[50, 4]andgapminder[50, "lifeExp"]gapminder[50, 4]andgapminder[4, 50]gapminder[50, 4]andgapminder$lifeExp[50]
Challenge
Which countries in the data have life expectancies greater than 80?
Data manipulation using dplyr
Bracket subsetting is handy, but it can be cumbersome and difficult to read, especially for complicated operations. Enter dplyr. dplyr is a package for making data manipulation easier.
Packages in R are basically sets of additional functions that let you do more stuff in R. The functions we’ve been using, like str(), come built into R; packages give you access to more functions. You need to install a package and then load it to be able to use it.
install.packages("dplyr") ## installYou might get asked to choose a CRAN mirror – this is basically asking you to choose a site to download the package from. The choice doesn’t matter too much; I’d recommend choosing the RStudio mirror or one of the mirrors located in WA.
library("dplyr") ## loadYou only need to install a package once per computer, but you need to load it every time you open a new R session and want to use that package.
What is dplyr?
The package dplyr is a fairly new (2014) package that tries to provide easy tools for the most common data manipulation tasks. It is built to work directly with data frames. The thinking behind it was largely inspired by the package plyr which has been in use for some time but suffered from being slow in some cases.dplyr addresses this by porting much of the computation to C++. An additional feature is the ability to work with data stored directly in an external database. The benefits of doing this are that the data can be managed natively in a relational database, queries can be conducted on that database, and only the results of the query returned.
This addresses a common problem with R in that all operations are conducted in memory and thus the amount of data you can work with is limited by available memory. The database connections essentially remove that limitation in that you can have a database of many 100s GB, conduct queries on it directly and pull back just what you need for analysis in R. There is a lot of great info on dplyr. If you have an interest, I’d encourage you to look more. The vignettes are particularly good.
Selecting columns and filtering rows
We’re going to learn some of the most common dplyr functions: select(), filter(), mutate(), group_by(), and summarize(). To select columns of a data frame, use select(). The first argument to this function is the data frame (gapminder), and the subsequent arguments are the columns to keep.
## Keep columns "country", "year", and "pop"
select(gapminder, country, year, pop)To choose rows, use filter():
## All data for Finland
filter(gapminder, country == "Finland") country year pop continent lifeExp gdpPercap
1 Finland 1952 4090500 Europe 66.550 6424.519
2 Finland 1957 4324000 Europe 67.490 7545.415
3 Finland 1962 4491443 Europe 68.750 9371.843
4 Finland 1967 4605744 Europe 69.830 10921.636
5 Finland 1972 4639657 Europe 70.870 14358.876
6 Finland 1977 4738902 Europe 72.520 15605.423
7 Finland 1982 4826933 Europe 74.550 18533.158
8 Finland 1987 4931729 Europe 74.830 21141.012
9 Finland 1992 5041039 Europe 75.700 20647.165
10 Finland 1997 5134406 Europe 77.130 23723.950
11 Finland 2002 5193039 Europe 78.370 28204.591
12 Finland 2007 5238460 Europe 79.313 33207.084
Pipes
But what if you wanted to select and filter? There are three ways to do this: use intermediate steps, nested functions, or pipes. With the intermediate steps, you essentially create a temporary data frame and use that as input to the next function. This can clutter up your workspace with lots of objects. You can also nest functions (i.e. one function inside of another). This is handy, but can be difficult to read if too many functions are nested as the process from inside out. The last option, pipes, are a fairly recent addition to R. Pipes let you take the output of one function and send it directly to the next, which is useful when you need to many things to the same data set. Pipes in R look like %>% and are made available via the magrittr package installed as part of dplyr.
### Countries and years when populations were less than or equal to 10000
gapminder %>%
filter(pop <= 100000) %>%
select(country, year) country year
1 Djibouti 1952
2 Djibouti 1957
3 Djibouti 1962
4 Sao Tome and Principe 1952
5 Sao Tome and Principe 1957
6 Sao Tome and Principe 1962
7 Sao Tome and Principe 1967
8 Sao Tome and Principe 1972
9 Sao Tome and Principe 1977
10 Sao Tome and Principe 1982
In the above we use the pipe to send the gapminder data set first through filter, to keep rows where pop was less than 100000, and then through select to keep the country and year columns. When the data frame is being passed to the filter() and select() functions through a pipe, we don’t need to include it as an argument to these functions anymore.
If we wanted to create a new object with this smaller version of the data we could do so by assigning it a new name:
gapminder_sml <- gapminder %>%
filter(pop <= 100000) %>%
select(country, year)
gapminder_sml country year
1 Djibouti 1952
2 Djibouti 1957
3 Djibouti 1962
4 Sao Tome and Principe 1952
5 Sao Tome and Principe 1957
6 Sao Tome and Principe 1962
7 Sao Tome and Principe 1967
8 Sao Tome and Principe 1972
9 Sao Tome and Principe 1977
10 Sao Tome and Principe 1982
Challenge
Using pipes, subset the gapminder data to include rows where gdpPercap was greater than or equal to 35,000. Retain columns country, year, and gdpPercap.
Mutate
Frequently you’ll want to create new columns based on the values in existing columns, for example to do unit conversions or find the ratio of values in two columns. For this we’ll use mutate().
To create a new column of gdpPercap * pop:
mutate(gapminder, totalgdp = gdpPercap * pop)If this runs off your screen and you just want to see the first few rows, you can use a pipe to view the head() of the data (pipes work with non-dplyr functions too, as long as the dplyr or magrittr packages are loaded).
mutate(gapminder, totalgdp = gdpPercap * pop) %>%
head country year pop continent lifeExp gdpPercap totalgdp
1 Afghanistan 1952 8425333 Asia 28.801 779.4453 6567086330
2 Afghanistan 1957 9240934 Asia 30.332 820.8530 7585448670
3 Afghanistan 1962 10267083 Asia 31.997 853.1007 8758855797
4 Afghanistan 1967 11537966 Asia 34.020 836.1971 9648014150
5 Afghanistan 1972 13079460 Asia 36.088 739.9811 9678553274
6 Afghanistan 1977 14880372 Asia 38.438 786.1134 11697659231
Split-apply-combine data analysis and the summarize() function
Many data analysis tasks can be approached using the “split-apply-combine” paradigm: split the data into groups, apply some analysis to each group, and then combine the results. dplyr makes this very easy through the use of the group_by() and summarize() functions. group_by() splits the data into groups, and summarize() collapses each group into a single-row summary of that group. For example, to view mean totalgdp by continent:
gapminder %>%
mutate(totalgdp = gdpPercap * pop) %>%
group_by(continent) %>%
summarize(meangdp = mean(totalgdp))Source: local data frame [5 x 2]
continent meangdp
1 Africa 20904782844
2 Americas 379262350210
3 Asia 227233738153
4 Europe 269442085301
5 Oceania 188187105354
You can group by multiple columns too:
gapminder %>%
mutate(totalgdp = gdpPercap * pop) %>%
group_by(continent, year) %>%
summarize(meangdp = mean(totalgdp))Source: local data frame [60 x 3]
Groups: continent
continent year meangdp
1 Africa 1952 5992294608
2 Africa 1957 7359188796
3 Africa 1962 8784876958
4 Africa 1967 11443994101
5 Africa 1972 15072241974
6 Africa 1977 18694898732
7 Africa 1982 22040401045
8 Africa 1987 24107264108
9 Africa 1992 26256977719
10 Africa 1997 30023173824
.. ... ... ...
And summarize multiple variables at the same time:
gapminder %>%
mutate(totalgdp = gdpPercap * pop) %>%
group_by(continent, year) %>%
summarize(meangdp = mean(totalgdp),
mingdp = min(totalgdp))Source: local data frame [60 x 4]
Groups: continent
continent year meangdp mingdp
1 Africa 1952 5992294608 52784691
2 Africa 1957 7359188796 52784691
3 Africa 1962 8784876958 70020508
4 Africa 1967 11443994101 98028711
5 Africa 1972 15072241974 117419006
6 Africa 1977 18694898732 150813402
7 Africa 1982 22040401045 186362275
8 Africa 1987 24107264108 168049219
9 Africa 1992 26256977719 179898843
10 Africa 1997 30023173824 194980183
.. ... ... ... ...
Challenge
Use group_by() and summarize() to find the maximum life expectancy for each continent. Hint: there is a max() function.
Challenge
Use group_by() and summarize() to find the mean, min, and max life expectancy for each year.
Challenge
Pick a country and find the population for each year in the data prior to 1982. Return a data frame with the columns country, year, and pop.
Writing data
At some point, you’ll also want to write out data from R.
We can use the write.table function for this, which is very similar to read.table from before.
Let’s create a data-cleaning script, for this analysis, we only want to focus on the gapminder data for Australia:
aust_subset <- filter(gapminder, country == "Australia")
write.table(aust_subset,
file="gapminder-aus.csv",
sep=","
)Let’s take a look at the data to make sure it looks OK:
head(read.table('gapminder-aus.csv',header=T)) country X..year...pop...continent...lifeExp...gdpPercap.
1 1 ,"Australia",1952,8691212,"Oceania",69.12,10039.59564
2 2 ,"Australia",1957,9712569,"Oceania",70.33,10949.64959
3 3 ,"Australia",1962,10794968,"Oceania",70.93,12217.22686
4 4 ,"Australia",1967,11872264,"Oceania",71.1,14526.12465
5 5 ,"Australia",1972,13177000,"Oceania",71.93,16788.62948
6 6 ,"Australia",1977,14074100,"Oceania",73.49,18334.19751
Hmm, that’s not quite what we wanted. Where did all these quotation marks come from? Also the row numbers are meaningless.
Let’s look at the help file to work out how to change this behaviour.
?write.tableBy default R will wrap character vectors with quotation marks when writing out to file. It will also write out the row and column names.
Let’s fix this:
write.table(
gapminder[gapminder$country == "Australia",],
file="gapminder-aus.csv",
sep=",", quote=FALSE, row.names=FALSE
)Now lets look at the data again:
head(read.table('gapminder-aus.csv',header=T)) country.year.pop.continent.lifeExp.gdpPercap
1 Australia,1952,8691212,Oceania,69.12,10039.59564
2 Australia,1957,9712569,Oceania,70.33,10949.64959
3 Australia,1962,10794968,Oceania,70.93,12217.22686
4 Australia,1967,11872264,Oceania,71.1,14526.12465
5 Australia,1972,13177000,Oceania,71.93,16788.62948
6 Australia,1977,14074100,Oceania,73.49,18334.19751
That looks better!
Challenge 2
Write a data-cleaning script file that subsets the gapminder data to include only data points collected since 1990.
Use this script to write out the new subset to a file in the cleaned-data/ directory.