Basic data manipulation with dplyr
Alyssa Fahringer
Aims of this worksheet
One of the key reasons to use R is to be able to manipulate data with ease. After completing this worksheet you will be able to work with the most commonly used data manipulation verbs provided by the dplyr package. In addition, you will learn how to read a CSV file into R, and learn how to use the pipe operator.
Since these packages were written by Hadley Wickham, you may find his articles on “Tidy Data” and “The Split-Apply-Combine Strategy for Data Analysis” to be useful. In addition, you should read the package documentation and vignettes for the dplyr package.
Loading CSV files
We will begin by loading the necessary packages:
library(dplyr)
library(historydata)So far we have worked almost exclusively with data which is contained in R packages, which we can install and then load. However, most data will come to you outside of an R package, so you must learn how to load that data.
Often tabular data will come to you in the form of a CSV (comma separated values) file. This is a text file where, as the name indicates, the columns in the data are separated with columns. This is a much better way of receiving data than say, an Excel spreadsheet.
R has a number of functions that can read in data in various formats. We will use one, read_csv(), from the readr package, which works nicely with dplyr and tidyr. But there is an equivalent function in base R called read.csv(). See ?readr::read_csv or ?read.csv for details.
Because R is awesome, we can pass it the URL to a file (in addition to the local path to a file) and it will be loaded. (You could also download the file with download.file().)
library(readr)
methodists <- read_csv("http://lincolnmullen.com/projects/worksheets/data/methodists.csv")The pipe (%>%)
The packages that we will be working with all take advantage of an operator provided by the magrittr package. Called the pipe, this operator %>% passes the output of one function to the first argument of a second function.
Consider this example from an earlier exercise, where we find the earliest and latest dates from a column in a data frame.
range(as.Date(naval_promotions$date), na.rm = TRUE)## [1] "1794-06-04" "1905-02-12"Here we have to read the expression from the inside out, from the naval_promotions$date, to the as.Date() function, to the range() function. The pipe operator lets us write an equivalent expression which is more legible.
naval_promotions$date %>%
as.Date() %>%
range(na.rm = TRUE)## [1] "1794-06-04" "1905-02-12"Selecting columns (select())
Now we can begin to use the Methodists data. This data contains membership statistics for different churches, circuits, and class meetings. (This data is transcribed from the Methodist annual minutes meeting, and so it uses the racial terminology from those primary sources.) First can get a sense of what the data looks like.
methodists## Source: local data frame [15,887 x 10]
##
## minutes_year conference district meeting state members_general
## (int) (chr) (chr) (chr) (chr) (int)
## 1 1773 NA NA New York NA 180
## 2 1773 NA NA Philadelphia NA 180
## 3 1773 NA NA New Jersey NA 200
## 4 1773 NA NA Maryland NA 500
## 5 1773 NA NA Virginia NA 100
## 6 1774 NA NA New York NA 222
## 7 1774 NA NA Philadelphia NA 204
## 8 1774 NA NA New Jersey NA 257
## 9 1774 NA NA Chester NA 36
## 10 1774 NA NA Baltimore NA 738
## .. ... ... ... ... ... ...
## Variables not shown: members_white (int), members_colored (int),
## members_indian (chr), url (chr)The first data manipulation verb that we are going to use is select(). This function lets us pass the names of the columns that we want to keep.
methodists %>%
select(minutes_year, meeting, members_general)## Source: local data frame [15,887 x 3]
##
## minutes_year meeting members_general
## (int) (chr) (int)
## 1 1773 New York 180
## 2 1773 Philadelphia 180
## 3 1773 New Jersey 200
## 4 1773 Maryland 500
## 5 1773 Virginia 100
## 6 1774 New York 222
## 7 1774 Philadelphia 204
## 8 1774 New Jersey 257
## 9 1774 Chester 36
## 10 1774 Baltimore 738
## .. ... ... ...Notice that we have not actually changed the data stored in methodists until we assign the changed data back to the variable.
Read the documentation for this function, ?select.
- Select the
minutes_year,meeting, and all the columns that begin with the wordmembers_.
methodists %>%
select(minutes_year, meeting, starts_with("members"))## Source: local data frame [15,887 x 6]
##
## minutes_year meeting members_general members_white members_colored
## (int) (chr) (int) (int) (int)
## 1 1773 New York 180 NA NA
## 2 1773 Philadelphia 180 NA NA
## 3 1773 New Jersey 200 NA NA
## 4 1773 Maryland 500 NA NA
## 5 1773 Virginia 100 NA NA
## 6 1774 New York 222 NA NA
## 7 1774 Philadelphia 204 NA NA
## 8 1774 New Jersey 257 NA NA
## 9 1774 Chester 36 NA NA
## 10 1774 Baltimore 738 NA NA
## .. ... ... ... ... ...
## Variables not shown: members_indian (chr)- Remove the column
url.
methodists %>%
select(-url)## Source: local data frame [15,887 x 9]
##
## minutes_year conference district meeting state members_general
## (int) (chr) (chr) (chr) (chr) (int)
## 1 1773 NA NA New York NA 180
## 2 1773 NA NA Philadelphia NA 180
## 3 1773 NA NA New Jersey NA 200
## 4 1773 NA NA Maryland NA 500
## 5 1773 NA NA Virginia NA 100
## 6 1774 NA NA New York NA 222
## 7 1774 NA NA Philadelphia NA 204
## 8 1774 NA NA New Jersey NA 257
## 9 1774 NA NA Chester NA 36
## 10 1774 NA NA Baltimore NA 738
## .. ... ... ... ... ... ...
## Variables not shown: members_white (int), members_colored (int),
## members_indian (chr)Filtering rows (filter())
The select() function lets us pick certain columns. The filter() function lets select certain rows based on logical conditions. For example, here we get the only the churches where the total number of members is at greater than 1,000.
methodists %>%
filter(members_general > 1000)## Source: local data frame [4 x 10]
##
## minutes_year conference district meeting state members_general
## (int) (chr) (chr) (chr) (chr) (int)
## 1 1776 NA NA Brunswick NA 1611
## 2 1777 NA NA Brunswick NA 1360
## 3 1781 NA NA Delaware NA 1052
## 4 1783 NA NA Dover NA 1017
## Variables not shown: members_white (int), members_colored (int),
## members_indian (chr), url (chr)- Get just the rows from New York in 1800.
methodists %>%
filter(state == "New York", minutes_year == "1800")## Source: local data frame [18 x 10]
##
## minutes_year conference district meeting state
## (int) (chr) (chr) (chr) (chr)
## 1 1800 NA NA Albany City New York
## 2 1800 NA NA Albany Circuit New York
## 3 1800 NA NA Brooklyn New York
## 4 1800 NA NA Cambridge New York
## 5 1800 NA NA Chenango New York
## 6 1800 NA NA Columbia New York
## 7 1800 NA NA Delaware New York
## 8 1800 NA NA Dutchess New York
## 9 1800 NA NA Herkimer New York
## 10 1800 NA NA Long Island New York
## 11 1800 NA NA Mohawk New York
## 12 1800 NA NA Newburg New York
## 13 1800 NA NA New Rochelle and Croton New York
## 14 1800 NA NA New York New York
## 15 1800 NA NA Oneida and Cayuga New York
## 16 1800 NA NA Plattsburg New York
## 17 1800 NA NA Saratoga New York
## 18 1800 NA NA Seneca New York
## Variables not shown: members_general (int), members_white (int),
## members_colored (int), members_indian (chr), url (chr)- Which Methodist meetings had only black members?
methodists %>%
filter(members_colored > 0, members_white < 1)## Source: local data frame [8 x 10]
##
## minutes_year conference district
## (int) (chr) (chr)
## 1 1786 NA NA
## 2 1787 NA NA
## 3 1819 New York New York
## 4 1820 New York New York
## 5 1823 New York New York
## 6 1830 South Carolina Augusta
## 7 1830 South Carolina Charleston
## 8 1830 South Carolina Charleston
## Variables not shown: meeting (chr), state (chr), members_general (int),
## members_white (int), members_colored (int), members_indian (chr), url
## (chr)The Methodists gradually kept more detailed records. They began by keeping track of the total number of members, then the number of white and black members, and eventually the number of white, black, and Indian members. But when they kept track of those racial divisions, they did not report the total number of members.
- In which year did the Methodists start keeping track of white and black members? (Hint: use
is.na()to find when the missing values begin.)
methodists %>%
filter(!is.na(members_white))## Source: local data frame [15,583 x 10]
##
## minutes_year conference district meeting state members_general
## (int) (chr) (chr) (chr) (chr) (int)
## 1 1786 NA NA Portsmouth NA NA
## 2 1786 NA NA Sussex NA NA
## 3 1786 NA NA Brunswick NA NA
## 4 1786 NA NA Amelia NA NA
## 5 1786 NA NA Mecklenburg NA NA
## 6 1786 NA NA Bedford NA NA
## 7 1786 NA NA Orange NA NA
## 8 1786 NA NA Williamsburg NA NA
## 9 1786 NA NA Alleghany NA NA
## 10 1786 NA NA Berkley NA NA
## .. ... ... ... ... ... ...
## Variables not shown: members_white (int), members_colored (int),
## members_indian (chr), url (chr)- Bonus: some of these Methodist meetings were “missions.” Load the
stringrpackage and look at the documentation for?str_detect. Can you find which rows represent missions?
library(stringr)
methodists %>%
filter(str_detect(meeting, "mission"))## Source: local data frame [82 x 10]
##
## minutes_year conference district meeting
## (int) (chr) (chr) (chr)
## 1 1823 South Carolina Catawba Early county mission
## 2 1823 Genesee Upper Canada Grand River mission
## 3 1824 Ohio Lebanon Indian mission
## 4 1824 Kentucky Cumberland Cumberland mission
## 5 1824 Tennessee Huntsville Cherokee mission, Indiana
## 6 1824 South Carolina Athens Monroe mission
## 7 1824 South Carolina Athens Yellow River mission
## 8 1824 South Carolina Athens Gwinnett mission
## 9 1824 South Carolina Oconee St. Augustine mission
## 10 1824 South Carolina Oconee Chatahoochee mission
## .. ... ... ... ...
## Variables not shown: state (chr), members_general (int), members_white
## (int), members_colored (int), members_indian (chr), url (chr)Creating new columns (mutate())
Very often one will want to create a new column based on other columns in the data. For instance, in our Methodist data, there is a column called minutes_year for the year that the minutes were kept. But the actual data was for the previous year. Here we create a new column called year, where each value is one less than in minutes_year.
methodists %>%
mutate(year = minutes_year - 1) %>%
select(minutes_year, year, meeting)## Source: local data frame [15,887 x 3]
##
## minutes_year year meeting
## (int) (dbl) (chr)
## 1 1773 1772 New York
## 2 1773 1772 Philadelphia
## 3 1773 1772 New Jersey
## 4 1773 1772 Maryland
## 5 1773 1772 Virginia
## 6 1774 1773 New York
## 7 1774 1773 Philadelphia
## 8 1774 1773 New Jersey
## 9 1774 1773 Chester
## 10 1774 1773 Baltimore
## .. ... ... ...Notice that we chained the data manipulation functions using the pipe (%>%). This lets us create a pipeline were we can do many different manipulations in a row.
- Filter the Methodists data to 1786 and later. All of the
members_totalvalues for those years areNA. Assign the the value ofmembers_whiteplusmembers_coloredto themembers_totalcolumn.
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(members_total = members_white + members_colored) %>%
select(minutes_year, conference, district, meeting, state, members_total)## Source: local data frame [15,663 x 6]
##
## minutes_year conference district meeting state members_total
## (int) (chr) (chr) (chr) (chr) (int)
## 1 1786 NA NA Portsmouth NA 356
## 2 1786 NA NA Sussex NA 488
## 3 1786 NA NA Brunswick NA 364
## 4 1786 NA NA Amelia NA 412
## 5 1786 NA NA Mecklenburg NA 429
## 6 1786 NA NA Bedford NA 540
## 7 1786 NA NA Orange NA 449
## 8 1786 NA NA Williamsburg NA 178
## 9 1786 NA NA Alleghany NA 368
## 10 1786 NA NA Berkley NA 166
## .. ... ... ... ... ... ...- Create two new columns, one with the percentage of white members, and one with the percentage of black members.
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(members_total = members_white + members_colored) %>%
mutate(white_percentage = members_white / members_total) %>%
mutate(black_percentage = members_colored / members_total) %>%
select(white_percentage, black_percentage)## Source: local data frame [15,663 x 2]
##
## white_percentage black_percentage
## (dbl) (dbl)
## 1 0.9269663 0.07303371
## 2 0.8524590 0.14754098
## 3 0.8379121 0.16208791
## 4 0.9271845 0.07281553
## 5 0.9137529 0.08624709
## 6 0.9703704 0.02962963
## 7 0.8329621 0.16703786
## 8 0.9382022 0.06179775
## 9 0.9510870 0.04891304
## 10 0.8433735 0.15662651
## .. ... ...- Bonus: We neglected the column
members_indianabove, which should be counted in themembers_totalcolumn. However, for some years the values of that column areNA, and in other years they have a numeric value. The difference is that the years where the value isNA, that column does not appear in the data because it was not tracked. How can you add that column to the total without running into problems with the missing values? (Hints: One way to do this is to use theifelse()function to conditionally assign a value tomembers_total. Another way to do this is to group the data frame usingrowwise()and usesum()with thena.rm = TRUEargument. A third, less elegant way, would be to split the data frame manually based on whether the value isNAor not, then recombine them.) did not finish
remove_na <- function(x) {
ifelse(is.na(x), 0, x)
}
methodists %>%
mutate(members_white = ifelse(is.na(members_white), 0, members_white)) %>%
mutate(members_indian = ifelse(is.na(members_indian), 0, members_indian)) %>%
mutate(members_indian = as.integer(members_indian)) %>%
mutate(members_colored = ifelse(is.na(members_colored), 0, members_colored)) %>%
mutate(members_general = ifelse(is.na(members_general), 0, members_general)) %>%
mutate(members_total = members_white + members_general + members_colored + members_indian)## Source: local data frame [15,887 x 11]
##
## minutes_year conference district meeting state members_general
## (int) (chr) (chr) (chr) (chr) (dbl)
## 1 1773 NA NA New York NA 180
## 2 1773 NA NA Philadelphia NA 180
## 3 1773 NA NA New Jersey NA 200
## 4 1773 NA NA Maryland NA 500
## 5 1773 NA NA Virginia NA 100
## 6 1774 NA NA New York NA 222
## 7 1774 NA NA Philadelphia NA 204
## 8 1774 NA NA New Jersey NA 257
## 9 1774 NA NA Chester NA 36
## 10 1774 NA NA Baltimore NA 738
## .. ... ... ... ... ... ...
## Variables not shown: members_white (dbl), members_colored (dbl),
## members_indian (int), url (chr), members_total (dbl)Sorting columns (arrange())
Often we want to sort a data frame by one of its columns. This can be done with the verb arrange(). By default arrange() will sort from least to greatest; we can use the function desc() to sort from greatest to least. In this example, we sort the data frame to get the circuits with the highest number of white members.
methodists %>%
arrange(desc(members_white))## Source: local data frame [15,887 x 10]
##
## minutes_year conference district meeting state members_general
## (int) (chr) (chr) (chr) (chr) (int)
## 1 1825 Ohio Lancaster Muskingum NA NA
## 2 1830 New York New York New York NA NA
## 3 1829 New York New York New York NA NA
## 4 1828 New York New York New York NA NA
## 5 1830 Baltimore Baltimore Baltimore NA NA
## 6 1825 Ohio Lancaster Hockhocking NA NA
## 7 1827 New York New York New York NA NA
## 8 1829 Baltimore Baltimore Baltimore NA NA
## 9 1828 Baltimore Baltimore Baltimore NA NA
## 10 1826 New York New York New York NA NA
## .. ... ... ... ... ... ...
## Variables not shown: members_white (int), members_colored (int),
## members_indian (chr), url (chr)- Which circuits had the highest number of black members? Be sure to select only the necessary columns so that the results print in a meaningful way.
methodists %>%
arrange(desc(members_colored)) %>%
select(minutes_year, meeting, members_colored) ## Source: local data frame [15,887 x 3]
##
## minutes_year meeting members_colored
## (int) (chr) (int)
## 1 1817 Charleston 5699
## 2 1816 Charleston 5313
## 3 1815 Charleston 3796
## 4 1813 Charleston 3604
## 5 1814 Charleston 3418
## 6 1830 Charleston 3163
## 7 1812 Charleston 3128
## 8 1829 Charleston 3000
## 9 1825 Charleston 2747
## 10 1826 Charleston 2712
## .. ... ... ...- Which circuits had the most members overall? Keep in mind that you will have to use your code from above to calculate the total members for years where that value is
NAbecause only the number of white and black members is supplied?
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(members_total = members_white + members_colored) %>%
arrange(desc(members_total))## Source: local data frame [15,663 x 11]
##
## minutes_year conference district meeting state
## (int) (chr) (chr) (chr) (chr)
## 1 1825 Ohio Lancaster Muskingum NA
## 2 1817 South Carolina Edisto Charleston NA
## 3 1816 South Carolina Edisto Charleston NA
## 4 1830 Baltimore Baltimore Baltimore NA
## 5 1829 Baltimore Baltimore Baltimore NA
## 6 1828 Baltimore Baltimore Baltimore NA
## 7 1826 Baltimore Baltimore Baltimore station NA
## 8 1827 Baltimore Baltimore Baltimore city NA
## 9 1815 South Carolina Edisto Charleston NA
## 10 1825 Baltimore Baltimore Baltimore city NA
## .. ... ... ... ... ...
## Variables not shown: members_general (int), members_white (int),
## members_colored (int), members_indian (chr), url (chr), members_total
## (int)- Which circuits had the high percentage of black members without being entirely black?
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(percentage_afam = members_colored / (members_white + members_colored)) %>%
filter(!members_white == 0) %>%
arrange(desc(percentage_afam)) %>%
select(minutes_year, meeting, state, percentage_afam, members_white, members_colored)## Source: local data frame [15,575 x 6]
##
## minutes_year meeting state percentage_afam
## (int) (chr) (chr) (dbl)
## 1 1830 Asbury mission NA 0.9952153
## 2 1799 Georgetown South Carolina 0.9627660
## 3 1798 Georgetown South Carolina 0.9625668
## 4 1816 Georgetown NA 0.9581182
## 5 1829 Asbury mission NA 0.9574468
## 6 1800 Georgetown South Carolina 0.9570815
## 7 1801 Georgetown South Carolina 0.9503311
## 8 1815 Georgetown NA 0.9501823
## 9 1805 Wilmington NA 0.9484778
## 10 1828 Georgetown NA 0.9478944
## .. ... ... ... ...
## Variables not shown: members_white (int), members_colored (int)Split-apply-combine (group_by())
Notice that in the example above the arrange() function sorted the entire data frame. So when we looked for the circuits with the largest number of members, we got rows from 1825, then 1830, then 1829, then 1830, and so on. What if we wanted to get the biggest circuit from each year?
We can solve this kind of problem with what Hadley Wickham calls the “split-apply-combine” pattern of data analysis. Think of it this way. First we can split the big data frame into separate data frames, one for each year. Then we can apply our logic to get the results we want; in this case, that means sorting the data frame. We might also want to get just the top one row with the biggest number of members. Then we can combine those split apart data frames into a new data frame.
Observe how this works. If we want to get circuit with the most black members in each year, we can use the following code:
methodists %>%
filter(minutes_year >= 1786) %>%
select(minutes_year, meeting, starts_with("members")) %>%
group_by(minutes_year) %>%
arrange(desc(members_colored)) %>%
slice(1) ## Source: local data frame [45 x 6]
## Groups: minutes_year [45]
##
## minutes_year meeting members_general members_white members_colored
## (int) (chr) (int) (int) (int)
## 1 1786 Antigua NA 0 1000
## 2 1787 Kent NA 607 604
## 3 1788 Calvert NA 505 842
## 4 1789 Calvert NA 943 909
## 5 1790 Calvert NA 814 1170
## 6 1791 Calvert NA 760 1329
## 7 1792 Calvert NA 700 1200
## 8 1793 Surry NA 814 955
## 9 1794 Calvert NA 682 1102
## 10 1795 Calvert NA 602 924
## .. ... ... ... ... ...
## Variables not shown: members_indian (chr)Let’s walk through that logic step by step.
- First we get only the years since 1786 with
filter(), since the data does not keep track of the number of white and black members until that year. - Then we select only the columns that we are interested in, namely, the column for the year, the name of the meeting, and the various values for membership. We do this just so that the results print out in a useful way: in a real analysis we might decide not to throw away the other columns.
- The crucial step is when we
group_by()theminutes_year. This creates a new data-frame (the split step) for each unique combination of values in the variables. Notice that the printed our result says that there are 45 groups, i.e., one for each year from 1786 to 1830. (Note that you can group by combinations of columns, so, one group for each combination of city and state, for instance.) - Next we apply our logic, in this case, sorting by the column
members_coloredin descending order. This puts the rows with the biggest value at the top. - Next we continue to apply our logic with
slice(). This function simply gives us the rows in each of the split-up data frames with that index. Soslice(1)gives us the first row,slice(5)gives us this fifth row, andslice(1:5)gives us the first through fifth rows. - The last step, combine, where the split-up data frames are brought back together, is done for us automatically. Note that the data frame is still grouped, however, so any subsequent data manipulation verbs will be applied to the groups rather than the whole data frame. If we wished, we could use
ungroup().
This particular operation, getting the top value in a split up data frame is so common that dplyr provides us with a top_n() function as a short cut. That function also handles ties better. (What if, for instance, two circuits both have the same biggest value?)
methodists %>%
filter(minutes_year >= 1786) %>%
select(minutes_year, meeting, starts_with("members")) %>%
group_by(minutes_year) %>%
top_n(1, members_colored)## Source: local data frame [45 x 6]
## Groups: minutes_year [45]
##
## minutes_year meeting members_general members_white members_colored
## (int) (chr) (int) (int) (int)
## 1 1786 Antigua NA 0 1000
## 2 1787 Kent NA 607 604
## 3 1788 Calvert NA 505 842
## 4 1789 Calvert NA 943 909
## 5 1790 Calvert NA 814 1170
## 6 1791 Calvert NA 760 1329
## 7 1792 Calvert NA 700 1200
## 8 1793 Surry NA 814 955
## 9 1794 Calvert NA 682 1102
## 10 1795 Calvert NA 602 924
## .. ... ... ... ... ...
## Variables not shown: members_indian (chr)We get the same results more concisely and reliably, though the steps of “split-apply-combine” are perhaps somewhat less easy to see.
- For each year, which was the biggest circuit? did not finish
methodists %>%
select(minutes_year, meeting, starts_with("members")) %>%
group_by(meeting) %>%
top_n(1, meeting)## Source: local data frame [15,886 x 6]
## Groups: meeting [1879]
##
## minutes_year meeting members_general members_white members_colored
## (int) (chr) (int) (int) (int)
## 1 1773 New York 180 NA NA
## 2 1773 Philadelphia 180 NA NA
## 3 1773 New Jersey 200 NA NA
## 4 1773 Maryland 500 NA NA
## 5 1773 Virginia 100 NA NA
## 6 1774 New York 222 NA NA
## 7 1774 Philadelphia 204 NA NA
## 8 1774 New Jersey 257 NA NA
## 9 1774 Chester 36 NA NA
## 10 1774 Baltimore 738 NA NA
## .. ... ... ... ... ...
## Variables not shown: members_indian (chr)- For each year since 1786, which church had the biggest percentage of black members without being entirely black?
methodists %>%
filter(minutes_year >= 1786, !members_white == 0) %>%
mutate(members_afam = members_colored / (members_white + members_colored)) %>%
select(minutes_year, meeting, members_afam, members_colored, members_white) %>%
group_by(minutes_year) %>%
top_n(1, members_afam)## Source: local data frame [46 x 5]
## Groups: minutes_year [45]
##
## minutes_year meeting members_afam members_colored members_white
## (int) (chr) (dbl) (int) (int)
## 1 1786 Calvert 0.5171849 316 295
## 2 1787 Charleston 0.6091954 53 34
## 3 1788 Calvert 0.6250928 842 505
## 4 1789 Mecklenburg 0.8759494 692 98
## 5 1790 Annapolis 0.6026059 185 122
## 6 1791 Charleston 0.6432432 119 66
## 7 1792 Georgetown 0.6711409 100 49
## 8 1793 Prince George's 0.7758621 225 65
## 9 1793 Georgetown 0.7758621 180 52
## 10 1794 Charleston 0.7857143 220 60
## .. ... ... ... ... ...- For the year 1825, what was the biggest meeting in each conference? In each district?
methodists %>%
filter(minutes_year == 1825) %>%
mutate(big_meeting = members_white + members_colored) %>%
select(minutes_year, meeting, conference, district, big_meeting) %>%
group_by(conference) %>%
top_n(1)## Selecting by big_meeting## Source: local data frame [15 x 5]
## Groups: conference [15]
##
## minutes_year meeting conference district
## (int) (chr) (chr) (chr)
## 1 1825 Muskingum Ohio Lancaster
## 2 1825 Lexington cir. Kentucky Kentucky
## 3 1825 Charleston Missouri Indiana
## 4 1825 Lebanon Tennessee Nashville
## 5 1825 Holston Holston Holston
## 6 1825 Tuscaloosa Mississippi Cahawba
## 7 1825 Charleston South Carolina Edisto
## 8 1825 Princess Ann Virginia Norfolk
## 9 1825 Baltimore city Baltimore Baltimore
## 10 1825 St. George's Philadelphia Philadelphia
## 11 1825 New York city New York New York
## 12 1825 Hebron and New London New England New London
## 13 1825 Livermore Maine Portland
## 14 1825 Lyons Genesee Ontario
## 15 1825 Augusta Canada Bay of Quinte
## Variables not shown: big_meeting (int)- For each year, what were the three biggest churches in the Baltimore conference?
methodists %>%
filter(conference == "Baltimore") %>%
mutate(biggest_church = members_colored + members_white) %>%
select(minutes_year, conference, meeting, biggest_church) %>%
group_by(minutes_year) %>%
top_n(3)## Selecting by biggest_church## Source: local data frame [87 x 4]
## Groups: minutes_year [29]
##
## minutes_year conference meeting biggest_church
## (int) (chr) (chr) (int)
## 1 1802 Baltimore Montgomery 1362
## 2 1802 Baltimore Calvert 1612
## 3 1802 Baltimore Baltimore circuit 1314
## 4 1803 Baltimore Baltimore cir. 1718
## 5 1803 Baltimore Montgomery 1376
## 6 1803 Baltimore Calvert 2052
## 7 1804 Baltimore Calvert 2457
## 8 1804 Baltimore Baltimore 1857
## 9 1804 Baltimore Baltimore circuit 1392
## 10 1805 Baltimore Federal 1267
## .. ... ... ... ...Summarizing or aggregating data (summarize())
In the examples using top_n() or slice() we performed a very simple kind of data summary, where we took the single row with the biggest value in a given column. This essentially boiled many rows of a data frame down into a single row. We would like to be able to summarize or aggregate a data frame in other ways as well. For instance, we often want to take the sum or the mean of a given column. We can do this using the summarize() function in conjunction with the group_by() function.
In this example, we group by the year the minutes were taken. Then we find the total number of white members for each year.
methodists %>%
filter(minutes_year >= 1786) %>%
group_by(minutes_year) %>%
summarize(total_members_white = sum(members_white, na.rm = TRUE))## Source: local data frame [45 x 2]
##
## minutes_year total_members_white
## (int) (int)
## 1 1786 18291
## 2 1787 21949
## 3 1788 30557
## 4 1789 34425
## 5 1790 45983
## 6 1791 50580
## 7 1792 52079
## 8 1793 51486
## 9 1794 52794
## 10 1795 48121
## .. ... ...Notice that we get one row in the recombined data frame for each group in the original data frame. The value in the new column is the result of a function (in this case, sum()) applied to the columns in each of the split apart data frames.
There is also a special case where we might want to know how many rows were in each of the split apart (or grouped) data frames. We can use the special n() function to get that count. (Just like the case of slice() and top_n(), this is such a common thing to do that dplyr provides the special functions count() and tally(). You can look up their documentation to see how they work.)
methodists %>%
group_by(minutes_year) %>%
summarize(total_meetings = n())## Source: local data frame [56 x 2]
##
## minutes_year total_meetings
## (int) (int)
## 1 1773 5
## 2 1774 9
## 3 1775 10
## 4 1776 12
## 5 1777 15
## 6 1779 22
## 7 1780 21
## 8 1781 24
## 9 1782 27
## 10 1783 37
## .. ... ...?count()- How many meetings (i.e., churches or circuits) were there in each conference in each year since 1802?
methodists %>%
filter(minutes_year >= 1802) %>%
group_by(minutes_year, conference) %>%
summarize(total_meetings = n())## Source: local data frame [310 x 3]
## Groups: minutes_year [?]
##
## minutes_year conference total_meetings
## (int) (chr) (int)
## 1 1802 Baltimore 30
## 2 1802 New England 20
## 3 1802 New York 36
## 4 1802 Philadelphia 43
## 5 1802 South Carolina 18
## 6 1802 Virginia 30
## 7 1802 Western 13
## 8 1803 Baltimore 30
## 9 1803 New England 22
## 10 1803 New York 39
## .. ... ... ...- What is the average number of white, black, and average number of total members for each year since 1786?
methodists %>% filter(minutes_year >= 1786) %>% group_by(minutes_year) %>% summarize(avg_white = mean(members_white, na.rm = TRUE), avg_black = mean(members_colored, na,rm = TRUE),
avg_indian = mean(as.integer(members_indian), na.rm = TRUE))
- Make a plot of the average percentage of black members over time.
library(ggplot2)
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(members_total = members_colored + members_white) %>%
mutate(perc_black = members_colored / members_total) %>%
group_by(minutes_year) %>%
summarize(black_average = mean(perc_black, na.rm = TRUE)) %>%
ggplot(aes(x = minutes_year, y = black_average)) +
geom_line()
- Make a plot of the average and median number of members over time.
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(members_total = members_colored + members_white) %>%
group_by(minutes_year) %>%
summarize(members_median = median(members_total, na.rm = TRUE), members_average = mean(members_total, na.rm = TRUE)) %>%
ggplot(aes(x = minutes_year)) +
geom_point(aes(y = members_median)) +
geom_point(aes(y = members_average))
- What was the average number of members in each conference for each year? Can you also make a plot of this?
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(members_total = members_white + members_colored) %>%
group_by(minutes_year, conference) %>%
summarize(members_average = mean(members_total, na.rm = TRUE)) %>%
ggplot(aes(x = minutes_year)) +
geom_point(aes(y = members_average)) +
facet_wrap(~conference)
- What was the average percentage of black members in each conference for each year? Can you also make a plot of this? did not finish
methodists %>%
group_by(members_colored, conference, minutes_year) %>%
summarize(average_blacks = mean(members_colored)) %>%
ggplot(aes(x = minutes_year)) +
geom_point(aes(y = ))