R training: Data wrangling with dplyr

3.1 select()

Consider the above example dataset (“data”) downloaded from KoBo, and let’s assume we only want to keep certain columns and drop all unnecessary ones. There are several ways to go about this.

data_select <- data %>%
  select(age_respondent, gender_respondent)

head(data_select)

##   age_respondent gender_respondent
## 1             60            female
## 2             35              male
## 3             33              male
## 4             32              male
## 5             51              male
## 6             24              male

head(data) %>%
  select(governorate:camp_out_of_camp)

##       governorate        district nationality camp_out_of_camp
## 1 al_sulaymaniyah al_sulaymaniyah        iran             <NA>
## 2 al_sulaymaniyah al_sulaymaniyah       syria             camp
## 3           erbil           erbil       syria             camp
## 4           duhok           duhok       syria      out_of_camp
## 5           erbil           erbil      turkey             <NA>
## 6 al_sulaymaniyah al_sulaymaniyah       syria      out_of_camp

data_select <- data %>%
  select(-start, -end, -X_uuid)

data_select <- data %>%
  select(-starts_with("primary_livelihood"))

data_select <- data %>%
  select(where(is.numeric))

data_select <- data %>%
  select(X_uuid, everything())

3.2 filter()

Suppose you have a dataset and you wish to exclude certain entries from your analysis. Let’s assume for instance, we select only the two columns “age_respondent” and “gender_respondent” (as in 3.1.1) but on top of that only keep records of respondents age 30 and below. We achieve this by simply piping a filter() function to the expression, like so:

data %>%
  select(age_respondent, gender_respondent) %>%
  filter(age_respondent <= 30) %>%
  head()

##   age_respondent gender_respondent
## 1             24              male
## 2             26              male
## 3             22              male
## 4             28              male
## 5             20              male
## 6             23              male

All the entries with respondents that are older than 30 years are now removed. Note how we combined the select() and filter() formulas.

Other examples of filter operators and functions you can use are:

• ==, >, >= etc.
• & (and), | (or), ! (not)
• is.na() (element is missing, or NA)
• between()

You can also combine multiple conditions in one statement. The following expression filter the data by out-of camp non-Syrians with a food security index of at least 3.

data_filter <- data %>%
  filter(camp_out_of_camp == "out_of_camp" & nationality != "syria" & food_security_index >= 3)

More examples and documentation on filter() are found here.

back to top

3.3 mutate()

Now that you know how to subset your dataset in two dimensions, let’s have a look at how you can modify values column-wise or create new variables, using the mutate() function.

Let’s say we wanted to convert the income (“calc_total_income”) and expenditure (“calc_total_expenditure”) variables from Iraqi dinar to USD using the exchange rate of 1,400 USD/IQD. Besides that we also create a new variable (“ratio”) showing the ratio of expenditure over income, which we round to one decimal using the round() function.

data %>%
  select(calc_total_income, calc_total_expenditure) %>%
  mutate(calc_total_income      = calc_total_income / 1400,
         calc_total_expenditure = calc_total_expenditure / 1400,
         ratio                  = round(calc_total_expenditure / calc_total_income, 1)) %>%
  head()

##   calc_total_income calc_total_expenditure ratio
## 1          250.0000               267.8571   1.1
## 2          364.2857               221.4286   0.6
## 3          165.7143               183.5714   1.1
## 4          142.8571               435.7143   3.0
## 5          300.0000               282.1429   0.9
## 6          214.2857               167.8571   0.8

In order to mutate multiple columns at once, refer to the following variations:

• mutate_all(): to mutate all columns in data frame
• mutate_if(): to mutate column meeting specified criteria
• mutate_at(): to mutate multiple columns specified in a vars() statement

More on mutate() can be found here.

back to top

3.4 case_when()

The ‘case_when()’ function allows you to modify data based on various possible conditions, and is particularly useful if you want to create new variables or recode existing ones. You can put multiple conditions (or if-else statements) in one expression, which are evaluated in order.

Say we wanted to recode our income variable, and define categories (i.e. “low”, “normal” and “high”) depending on total income (“calc_total_income”). We could specify a case_when() statement within mutate() like this:

data %>%
  select(calc_total_income) %>%
  mutate(income_category = case_when(calc_total_income > 500000 ~ "high",
                                     calc_total_income > 250000 ~ "normal",
                                     TRUE ~ "low")) %>%
  head()

##   calc_total_income income_category
## 1            350000          normal
## 2            510000            high
## 3            232000             low
## 4            200000             low
## 5            420000          normal
## 6            300000          normal

The function evaluates arguments one after another. This means that you must proceed from the most specific to the most general. In the above example, the last argument (TRUE ~) ensures that if none of the above cases match, “low” is returned. If none of the cases match, and TRUE is not specified as above, NA is returned.

More on case_when() can be found here.

back to top

3.5 summarise()

summarise() reduces multiple values down to a single value, and thus allows you to summarise your dataset. Let’s assume we wanted to know the mean of total income (“calc_total_income”) across all rows, as well as the number of entries in our dataset. We can do so by using the summarise() function as follows:

data %>%
  summarise(mean = mean(calc_total_income),
            n    = n())

##     mean    n
## 1 416134 1800

As you can see in this case, the summarise() statement reduces the dataset to one row and two columns (because we specified two variables).

In order to summarise multiple columns at once, refer to the following variations:

• summarise_all(): to summarise all columns in a data frame
• summarise_if(): to summarise columns meeting specified criteria
• summarise_at(): to summarise multiple columns specified in a vars() statement

Summarise() allows you to pass any function. Besides some basic ones, such as mean(), median() and max(), you can add any function inside the statement, including non-standard aggregation functions that you defined yourself. This make summarise() very versatile and a key component of your R repertoire.

back to top

3.6 group_by()

The above discussed functions get even more useful when combined with group_by(), which allows you to do group-wise operations. group_by() alone does not do too much by itself, but is incredibly useful when used together with other functions.

Let’s assume we want to once again calculate the mean of “calc_total_income”, but this time we want to get a separate result for each nationality (“nationality”). To do this, we simply pipe a group_by() statement to the expression:

data %>%
  group_by(nationality) %>%
  summarise(mean = mean(calc_total_income))

## # A tibble: 4 x 2
##   nationality                mean
##   <chr>                     <dbl>
## 1 iran                    330359.
## 2 palestinian_territories 518851.
## 3 syria                   405992.
## 4 turkey                  422514.

Using group_by() in tandem with summarise() might be its most straight-forward application. However, you can also do more complex operations with it using other functions. For instance, if we wanted to calculate the deviation of each households’ income from the respective nationality mean, we could do so by running the following code:

data %>%
  select(nationality, calc_total_income) %>%
  group_by(nationality) %>%
  mutate(deviation = calc_total_income - mean(calc_total_income)) %>%
  head()

## # A tibble: 6 x 3
## # Groups:   nationality [3]
##   nationality calc_total_income deviation
##   <chr>                   <int>     <dbl>
## 1 iran                   350000    19641.
## 2 syria                  510000   104008.
## 3 syria                  232000  -173992.
## 4 syria                  200000  -205992.
## 5 turkey                 420000    -2514.
## 6 syria                  300000  -105992.

Because the rows are grouped by nationality, the mean() statement only evaluates elements within each group.

If you wanted to pipe additional functions to the expression without the grouping dimension (or use another grouping dimension), add a line specifying ungroup() to clear previous grouping specifications. In the following, we are first calculating the mean income per governorate (“governorate”), and then use these means to calculate the country mean:

data %>%
  group_by(governorate) %>%
  summarise(mean_gov = mean(calc_total_income)) %>%
  ungroup() %>%
  summarise(mean_country = mean(mean_gov))

## # A tibble: 1 x 1
##   mean_country
##          <dbl>
## 1      484520.

More examples and documentation on group_by() can be found here.

back to top

3.7 join()

The ‘join()’ statement is the go-to function to merge two dataframes (x and y). Let’s consider the following example, where we have a sampling frame (y) with population estimates, which we want to join to our dataset (x).

sampling_frame %>%
  head()

##                        strata.names population
## 1                        iran_NA_NA       3915
## 2     palestinian_territories_NA_NA       2916
## 3                      turkey_NA_NA       2470
## 4        syria_al_sulaymaniyah_camp       2163
## 5 syria_al_sulaymaniyah_out_of_camp       7525
## 6    syria_centre_south_out_of_camp       1127

In this case, we aim to add the column “population” from the sampling frame to the dataset by calling a left_join():

data <- left_join(data, sampling_frame, by = c("strata" = "strata.names"))

The different join options are the following:

• left_join(): includes all rows in x.
• inner_join(): includes all rows in x and y.
• right_join(): includes all rows in y.
• full_join(): includes all rows in x or y.

back to top

4.1 rename()

As the name indicates, this function can be used to rename one or multiple columns in the dataset. If we wanted to clean up the name of the income variable (from “calc_total_income” to “income”), we could do so by calling the following:

data %>%
  rename(income = calc_total_income)

More documentation and examples are found here.

4.2 arrange()

Say we wanted to order the rows in our dataframe by values of selected columns. In the following example we first sort by governorate and then by age of the respondent:

data %>%
  arrange(governorate, age_respondent)

More documentation is found here.

4.3 distinct()

Use distinct() if you want to get rid of duplicate rows in your dataset:

data <- distinct(data)

4.4 bind_rows() / bind_cols()

If you want to combine multiple datasets, use bind_rows() / bind_cols().

Let’s assume you have two different datasets (“data_team_1” and “data_team_2”), which were collected and cleaned by different field teams. Before running analysis, you want to join them together like this;

data <- bind_rows(data_team_1, data_team_2)

A full list of additional dplyr functions can be found here.

back to top