<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/useful-dplyr-functions-wexamples/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://sw23993.wordpress.com/2017/07/10/useful-dplyr-functions-wexamples/"> Environmental Science and Data Analytics</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>The R package<strong><em> dplyr</em></strong> is an extremely useful resource for data cleaning, manipulation, visualisation and analysis. It contains a large number of very useful functions and is, without doubt, one of my top 3 R packages today (<em><strong>ggplot2</strong> </em>and<em><strong> reshape2</strong></em> being the others). When I was learning how to use dplyr for the first time, I used <a rel="nofollow" href="https://www.datacamp.com/?tap_a=5644-dce66f&tap_s=10907-287229" target="_blank">DataCamp</a> which offers some fantastic interactive courses on R. I also made use of  a decent data wrangling cheat sheet which can be found <a rel="nofollow" href="https://www.rstudio.com/resources/cheatsheets/" target="_blank">here</a>.</p>
<p>There are many useful functions contained within the dplyr package. This post does not attempt to cover them all but does look at the major functions that are commonly used in data manipulation tasks. These are:</p>
<pre>select() 
filter()
mutate() 
group_by() 
summarise()
arrange() 
join()</pre>
<p>The data used in this post are taken from the UCI Machine Learning Repository and contain census information from 1994 for the USA. The dataset can be used for classification of income class in a machine learning setting and can be obtained<a rel="nofollow" href="https://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.data" target="_blank"> here</a>.</p>
<pre>require(dplyr)<em>
# Data file</em>
file &lt;- &quot;https://archive.ics.uci.edu/ml/machine-learning-databases/adult/adult.data&quot;

<em># Some sensible variable names</em>
df_names &lt;- c(&quot;age&quot;, &quot;wrkclass&quot;, &quot;fnlweight&quot;, &quot;education_lvl&quot;, &quot;edu_score&quot;,
 &quot;marital_status&quot;, &quot;occupation&quot;, &quot;relationship&quot;, &quot;ethnic&quot;, &quot;gender&quot;,
 &quot;cap_gain&quot;, &quot;cap_loss&quot;, &quot;hrs_wk&quot;, &quot;nationality&quot;, &quot;income&quot;)

<em># Import the data</em>
df &lt;- read.csv(file, header = F,
 sep = &quot;,&quot;,
 na.strings = c(&quot; ?&quot;, &quot; &quot;, &quot;&quot;),
 row.names = NULL,
 col.names = df_names)</pre>
<p>Many data manipulation tasks in dplyr can be performed with the assistance of the forward-pipe operator (%>%). The pipe was first introduced in the <em><strong>magrittr</strong> </em>package and has since been included in the dplyr package. It is an incredibly useful tool for fluid data manipulation and results in highly readable code.</p>
<p>The census dataset requires a bit of preprocessing to get it ready for classification algorithms. This post does not cover preprocessing nor does it include predictive modelling.</p>
<p>The first function I would like to introduce removes duplicate entries which, in fact, <em>is</em> a preprocessing step one may carry out in a data analysis. It is so useful that it must be included.</p>
<pre><em># Remove duplicate rows and check number of rows</em>
df %>% distinct() %>% nrow()

<em># Drop duplicate rows and assign to new dataframe object</em>
df_clean &lt;- df %>% distinct()

<em># Drop duplicates based on one or more variables</em>
df %>% distinct(gender, .keep_all = T)
df %>% distinct(gender, education_lvl, .keep_all =  T)</pre>
<p>Taking random samples of data is easy with dplyr.</p>
<pre><em># Sample random rows with or without replacement</em>
sample_n(df, size = nrow(df) * 0.7, replace = F)
sample_n(df, size = 20, replace = T)

<em># Sample a proportion of rows with or without replacement</em>
sample_frac(df, size = 0.7, replace = F)
sample_frac(df, size = 0.8, replace = T</pre>
<p>Renaming variables is also easy with dplyr.</p>
<pre><em># Rename one or more variables in a dataframe</em>
df &lt;- df %>%
 rename(&quot;INCOME&quot; = &quot;income&quot;)

df &lt;- df %>%
 rename(&quot;INCOME&quot; = &quot;income&quot;, &quot;AGE&quot; = &quot;age&quot;)</pre>
<p>The main “verbs” of dplyr are now introduced. Let’s begin with the <strong>select()</strong> verb which filters a dataframe by column.</p>
<pre><em># Select specific columns (note that INCOME is the new name from earlier)</em>
df %>%
 select(education_lvl, INCOME)
 
<em># With dplyr 0.7.0 the pull() function extracts a variable as a vector</em>
df %>%
 pull(age)

<em># Drop a column using the - operator (variable can be referenced by name or column position)</em>
df %>%
 select(-edu_score)

df %>%
 select(-1, -4)

df %>%
 select(-c(2:6))</pre>
<p>Some useful helper functions are available in dplyr and can be used in conjunction with the select() verb. Here are some quick examples.</p>
<pre><em># Select columns with their names starting with &quot;e&quot;</em>
df %>%
 select(starts_with(&quot;e&quot;))

<em># The negative sign works for dropping here too</em>
df %>%
 select(-starts_with(&quot;e&quot;))

<em># Select columns with some pattern in the column name</em>
df %>%
 select(contains(&quot;edu&quot;))

<em># Reorder data to place a particular column at the start followed by all others </em><em>using everything()</em>
df %>%
 select(INCOME, everything())

<em># Select columns ending with a pattern</em>
df %>%
 select(ends_with(&quot;e&quot;))

df %>%
 select(ends_with(&quot;_loss&quot;))</pre>
<p>The next major verb we look at is <strong>filter() </strong>which, surprisingly enough, filters a dataframe by row based on one or more conditions.</p>
<pre><em># Filter rows to retain observations where age is greater than 30</em>
df %>%
 filter(age > 30)

<em># Filter by multiple conditions using the %in% operator (make sure strings match)</em>
df %>%
 filter(relationship %in% c(&quot; Unmarried&quot;, &quot; Wife&quot;))

<em># You can also use the OR operator (|)</em>
df %>%
 filter(relationship == &quot; Husband&quot; | relationship == &quot; Wife&quot;)

<em># Filter using the AND operator</em>
df %>%
 filter(age > 30 &amp; INCOME == &quot; >50K&quot;)

<em># Combine them too</em>
df %>%
 filter(education_lvl %in% c(&quot; Doctorate&quot;, &quot; Masters&quot;) &amp; age > 30)

<em># The NOT condition (filter out doctorate holders)</em>
df %>%
 filter(education_lvl != &quot; Doctorate&quot;)

<em># The grepl() function can be conveniently used with filter()</em>
df %>%
 filter(grepl(&quot; Wi&quot;, relationship))</pre>
<p>Next, we look at the <strong>summarise()</strong> verb which allows one to dynamically summarise groups of data and even pipe groups to ggplot data visualisations.</p>
<pre><em># The summarise() verb in dplyr is useful for summarising grouped data</em>
df %>%
 filter(INCOME == &quot; >50K&quot;) %>%
 summarise(mean_age = mean(age),
           median_age = median(age),
           sd_age = sd(age))

<em># Summarise multiple variables using summarise_at()</em>
df %>%
 filter(INCOME == &quot; >50K&quot;) %>%
 summarise_at(vars(age, hrs_wk),
 funs(n(), 
      mean, 
      median))

<em># We can also summarise with custom functions</em>
<em># The . in parentheses represents all called variables</em>
df %>%
 summarise_at(vars(age, hrs_wk),
 funs(n(),
      missing = sum(is.na(.)),
      mean = mean(., na.rm = T)))

<em># Create a new summary statistic with an anonymous function</em>
df %>%
 summarise_at(vars(age),
 function(x) { sum((x - mean(x)) / sd(x)) })

<em># Summarise conditionally using summarise_if()</em>
df %>%
 filter(INCOME == &quot; >50K&quot;) %>%
 summarise_if(is.numeric,
              funs(n(),
                   mean,
                   median))
 
<em># Subset numeric variables and use summarise_all() to get summary statistics</em>
ints &lt;- df[sapply(df, is.numeric)]
summarise_all(ints,
              funs(mean, 
                   median, 
                   sd, 
                   var))</pre>
<p>Next up is the <strong>arrange()</strong> verb which is useful for sorting data in ascending or descending order (ascending is default).</p>
<pre><em># Sort by ascending age and print top 10</em>
df %>%
 arrange(age) %>%
 head(10)

<em># Sort by descending age and print top 10</em>
df %>%
 arrange(desc(age)) %>%
 head(10)</pre>
<p>The<strong> group_by()</strong> verb is useful for grouping together observations which share common characteristics.</p>
<pre><em># The group_by verb is extremely useful for data analysis</em>
df %>%
 group_by(gender) %>%
 summarise(Mean = mean(age))

df %>%
 group_by(relationship) %>%
 summarise(total = n())

df %>%
 group_by(relationship) %>%
 summarise(total = n(),
           mean_age = mean(age))</pre>
<p>The<strong> mutate()</strong> verb is used to create new variables from existing local variables or global objects. New variables, such as sequences, can be also specified within mutate().</p>
<pre><em># Create new variables from existing or global variables</em>
df %>% 
 mutate(norm_age = (age - mean(age)) / sd(age))
 

<em># Multiply each numeric element by 1000 (the name &quot;new&quot; is added to the original variable name)</em>
df %>%
 mutate_if(is.numeric,
           funs(new = (. * 1000))) %>%
           head()</pre>
<p>The<strong> join()</strong> verb is used to merge rows from disjoint tables which share a primary key ID  or some other common variable. There are many join variants but I will consider just left, right, inner and full joins.</p>
<pre><em># Create ID variable which will be used as the primary key</em>
df &lt;- df %>%
 mutate(ID = seq(1:nrow(df))) %>%
 select(ID, everything())

<em># Create two tables (purposely overlap to facilitate joins)</em>
table_1 &lt;- df[1:50 , ] %>%
 select(ID, age, education_lvl)

table_2 &lt;- df[26:75 , ] %>%
 select(ID, gender, INCOME)

<em># Left join joins rows from table 2 to table 1 (the direction is implicit in the argument order)</em>
left_join(table_1, table_2, by = &quot;ID&quot;)

<em># Right join joins rows from table 1 to table 2</em>
right_join(table_1, table_2, by = &quot;ID&quot;)

<em># Inner join joins and retains only complete cases</em>
inner_join(table_1, table_2, by = &quot;ID&quot;)

<em># Full join joins and retains all values</em>
full_join(table_1, table_2, by = &quot;ID&quot;</pre>
<p>That wraps up a brief demonstration of some of dplyr’s excellent functions. For additional information on the functions and their arguments, check out the help documentation using the template: ?