Basic data manipulation with dplyr
Stephanie Walters
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
## .. ... ... ...methodists %>%
select(-conference, -district)## Source: local data frame [15,887 x 8]
##
## minutes_year meeting state members_general members_white
## (int) (chr) (chr) (int) (int)
## 1 1773 New York NA 180 NA
## 2 1773 Philadelphia NA 180 NA
## 3 1773 New Jersey NA 200 NA
## 4 1773 Maryland NA 500 NA
## 5 1773 Virginia NA 100 NA
## 6 1774 New York NA 222 NA
## 7 1774 Philadelphia NA 204 NA
## 8 1774 New Jersey NA 257 NA
## 9 1774 Chester NA 36 NA
## 10 1774 Baltimore NA 738 NA
## .. ... ... ... ... ...
## Variables not shown: members_colored (int), members_indian (chr), url
## (chr)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_", ignore.case = TRUE))## 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)methodists %>%
filter(meeting == "Fairfax", members_general >350)## Source: local data frame [2 x 10]
##
## minutes_year conference district meeting state members_general
## (int) (chr) (chr) (chr) (chr) (int)
## 1 1780 NA NA Fairfax NA 361
## 2 1782 NA NA Fairfax NA 362
## Variables not shown: members_white (int), members_colored (int),
## members_indian (chr), url (chr)methodists %>%
filter(members_white > 0) %>%
select (minutes_year, members_white)## Source: local data frame [15,575 x 2]
##
## minutes_year members_white
## (int) (int)
## 1 1786 330
## 2 1786 416
## 3 1786 305
## 4 1786 382
## 5 1786 392
## 6 1786 524
## 7 1786 374
## 8 1786 167
## 9 1786 350
## 10 1786 140
## .. ... ...- 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_white == 0)## 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 %>%
mutate(year = minutes_year -1) %>%
select(minutes_year, year, meeting) %>%
filter(str_detect(meeting, "mission"))## Source: local data frame [82 x 3]
##
## minutes_year year meeting
## (int) (dbl) (chr)
## 1 1823 1822 Early county mission
## 2 1823 1822 Grand River mission
## 3 1824 1823 Indian mission
## 4 1824 1823 Cumberland mission
## 5 1824 1823 Cherokee mission, Indiana
## 6 1824 1823 Monroe mission
## 7 1824 1823 Yellow River mission
## 8 1824 1823 Gwinnett mission
## 9 1824 1823 St. Augustine mission
## 10 1824 1823 Chatahoochee mission
## .. ... ... ...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 (members_total)## Source: local data frame [15,663 x 1]
##
## members_total
## (int)
## 1 356
## 2 488
## 3 364
## 4 412
## 5 429
## 6 540
## 7 449
## 8 178
## 9 368
## 10 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.)
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)) %>%
select(minutes_year, meeting, members_white)## Source: local data frame [15,887 x 3]
##
## minutes_year meeting members_white
## (int) (chr) (int)
## 1 1825 Muskingum 7775
## 2 1830 New York 3886
## 3 1829 New York 3783
## 4 1828 New York 3410
## 5 1830 Baltimore 3357
## 6 1825 Hockhocking 3335
## 7 1827 New York 3219
## 8 1829 Baltimore 3207
## 9 1828 Baltimore 2996
## 10 1826 New York 2867
## .. ... ... ...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 highest number of black members? Be sure to select only the necessary columns so that the results print in a meaningful way.
methodists %>%
arrange(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 1786 Redstone 0
## 2 1786 Fairfax 0
## 3 1786 Lancaster 0
## 4 1786 Little York 0
## 5 1786 Kent 0
## 6 1786 Dorchester 0
## 7 1786 Somerset 0
## 8 1786 Philadelphia 0
## 9 1786 Georgia 0
## 10 1786 Santee 0
## .. ... ... ...- 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 %>%
mutate(members_total = members_white + members_colored) %>%
arrange(members_total) %>%
select(minutes_year, meeting, members_total)## Source: local data frame [15,887 x 3]
##
## minutes_year meeting members_total
## (int) (chr) (int)
## 1 1825 Asbury 2
## 2 1812 Savannah 5
## 3 1829 Salem mission 6
## 4 1830 Galena 6
## 5 1803 Montreal 7
## 6 1828 Shawangunk mission 7
## 7 1810 Three Rivers 8
## 8 1812 Lancaster 8
## 9 1815 Hotchkisstown 8
## 10 1827 Grand River mission 8
## .. ... ... ...- Which circuits had the high percentage of black members without being entirely black?
methodists %>%
filter(minutes_year>=1786) %>%
mutate(perc_colored = members_colored / (members_white + members_colored)) %>%
arrange (desc(perc_colored)) %>%
filter (!members_white == 0) %>%
select(minutes_year, perc_colored, members_colored, members_white )## Source: local data frame [15,575 x 4]
##
## minutes_year perc_colored members_colored members_white
## (int) (dbl) (int) (int)
## 1 1830 0.9952153 208 1
## 2 1799 0.9627660 181 7
## 3 1798 0.9625668 180 7
## 4 1816 0.9581182 1670 73
## 5 1829 0.9574468 45 2
## 6 1800 0.9570815 223 10
## 7 1801 0.9503311 287 15
## 8 1815 0.9501823 1564 82
## 9 1805 0.9484778 405 22
## 10 1828 0.9478944 1328 73
## .. ... ... ... ...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(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 Redstone NA 523 0
## 2 1787 Bedford NA 252 0
## 3 1788 Burke NA 82 0
## 4 1789 Augusta NA 87 0
## 5 1790 Bottetourt NA 115 0
## 6 1791 Union NA 120 0
## 7 1792 Randolph NA 36 0
## 8 1793 Little York NA 156 0
## 9 1794 Mattamuskeet NA 198 0
## 10 1795 Salt River NA 269 0
## .. ... ... ... ... ...
## 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?
methodists %>%
mutate(members_total = members_white + members_colored) %>%
select(minutes_year, meeting, members_total) %>%
group_by(members_total) %>%
arrange(members_total) %>%
top_n(1, members_total)## Source: local data frame [15,583 x 3]
## Groups: members_total [1712]
##
## minutes_year meeting members_total
## (int) (chr) (int)
## 1 1825 Asbury 2
## 2 1812 Savannah 5
## 3 1829 Salem mission 6
## 4 1830 Galena 6
## 5 1803 Montreal 7
## 6 1828 Shawangunk mission 7
## 7 1810 Three Rivers 8
## 8 1812 Lancaster 8
## 9 1815 Hotchkisstown 8
## 10 1827 Grand River mission 8
## .. ... ... ...- For each year since 1786, which church had the biggest percentage of black members without being entirely black?
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(af_perc = members_colored / (members_white +members_colored)) %>%
select(minutes_year, meeting, af_perc, members_white, members_colored) %>%
group_by(minutes_year) %>%
arrange(af_perc) %>%
top_n(1, af_perc)## Source: local data frame [48 x 5]
## Groups: minutes_year [45]
##
## minutes_year meeting af_perc members_white members_colored
## (int) (chr) (dbl) (int) (int)
## 1 1786 Antigua 1.0000000 0 1000
## 2 1787 Bladen 1.0000000 0 30
## 3 1788 Calvert 0.6250928 505 842
## 4 1789 Mecklenburg 0.8759494 98 692
## 5 1790 Annapolis 0.6026059 122 185
## 6 1791 Charleston 0.6432432 66 119
## 7 1792 Georgetown 0.6711409 49 100
## 8 1793 Prince George's 0.7758621 65 225
## 9 1793 Georgetown 0.7758621 52 180
## 10 1794 Charleston 0.7857143 60 220
## .. ... ... ... ... ...- For the year 1825, what was the biggest meeting in each conference? In each district?
methodists %>%
filter(minutes_year == 1825) %>%
mutate(members_group = members_colored + members_white) %>%
select(minutes_year, conference, meeting, members_group) %>%
group_by(conference) %>%
top_n(1, meeting)## Source: local data frame [15 x 4]
## Groups: conference [15]
##
## minutes_year conference meeting members_group
## (int) (chr) (chr) (int)
## 1 1825 Ohio Zanesville station 187
## 2 1825 Kentucky Wayne 566
## 3 1825 Missouri White River 168
## 4 1825 Tennessee Wolfe 97
## 5 1825 Holston Tennessee Valley 641
## 6 1825 Mississippi Wilkinson 795
## 7 1825 South Carolina Yellow River mission 691
## 8 1825 Virginia Yadkin 408
## 9 1825 Baltimore York 109
## 10 1825 Philadelphia Wilmington 321
## 11 1825 New York Winstead 182
## 12 1825 New England Winchester 250
## 13 1825 Maine Vinalhaven 45
## 14 1825 Genesee Wyoming 461
## 15 1825 Canada Young Street and York 406- For each year, what were the three biggest churches in the Baltimore conference?
methodists %>%
filter(conference == "Baltimore") %>%
mutate(members_group = members_colored + members_white) %>%
select(minutes_year, conference, meeting, members_group) %>%
group_by(minutes_year) %>%
arrange(desc(conference))%>%
slice(1:3)## Source: local data frame [87 x 4]
## Groups: minutes_year [29]
##
## minutes_year conference meeting members_group
## (int) (chr) (chr) (int)
## 1 1802 Baltimore Muskingum 153
## 2 1802 Baltimore West Wheeling 266
## 3 1802 Baltimore Rockingham 280
## 4 1803 Baltimore Rockingham 617
## 5 1803 Baltimore Pendleton 176
## 6 1803 Baltimore Alleghany 454
## 7 1804 Baltimore Bottetourt 397
## 8 1804 Baltimore Greenbrier 643
## 9 1804 Baltimore Pendleton 349
## 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
## .. ... ...- 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(meeting) %>%
summarize(total_members_white = sum(members_white, na.rm = TRUE))## Source: local data frame [1,799 x 2]
##
## meeting total_members_white
## (chr) (int)
## 1 A Boeuf 125
## 2 Abbeville 6677
## 3 Abeff and Cold Water 176
## 4 Abindgon 955
## 5 Abingdon 7806
## 6 Abingdon cir. 1424
## 7 Abingdon town 70
## 8 Academy 102
## 9 Accomack 22640
## 10 Adams 961
## .. ... ...- 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(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
## .. ... ...- Make a plot of the average percentage of black members over time.
library(ggplot2)
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(perc_members_colored = members_colored / (members_colored + members_white)) %>%
group_by(minutes_year) %>%
summarize(total_members_colored = sum(perc_members_colored, na.rm = TRUE)/n()) %>%
ggplot(aes(x=minutes_year, y= total_members_colored)) +
geom_point() 
- Make a plot of the average and median number of members over time.
methodists %>%
filter(minutes_year >= 1786) %>%
mutate(members_av= members_colored + members_white) %>%
group_by(minutes_year) %>%
summarize(total_members = sum(members_av, na.rm = TRUE)/n()) %>%
ggplot(aes(x=minutes_year, y= total_members)) +
geom_point() 
What was the average number of members in each conference for each year? Can you also make a plot of this?
What was the average percentage of black members in each conference for each year? Can you also make a plot of this?