1. Introduction

This is the third notebook which Geomatica Basica students have to write to get started with R & RStudio. It aims at learning how to use the dplyr package for data “editing”. Editing refers to choosing a subset of the variables and/or observations in a dataset, as well as filtering (selecting observations based on their variables values), creating new variables, and more. For such a purpose, we use here the Evaluaciones Agropecuarias Municipales (EVA), a 2007-2018 agriculture statistics data set provided by the Ministerio de Agricultura y Desarrollo Rural.

2. Loading the required libraries

Let’s start loading several libraries, in particular dplyr:

# install packages before anything
library(tidyverse)
library(dplyr)
library(ggplot2)

3. Reading a table with agrarian statistics

Once we have downloaded the EVA dataset, we need to “clean” it, to be able to work with in R. This comprises two main steps: - to remove strange symbols and spaces in column’s names - to select only the rows which belong to the department we are interested in. In my case, it is Boyacá.

The two tasks needs to be done in excel.

The next figure shows the columns heading in the original file: The next figure shows the columns heading in the cleaned file: Now, let’s read the cleaned file of EVA statistics for Boyaca. We will read the csv delimited fileinto a tibble:

eva_boyaca <- read_csv(file = "../../datos/EVA_Boyaca_2019.csv")
## 
## ── Column specification ────────────────────────────────────────────────────────
## cols(
##   COD_DPTO = col_double(),
##   DPTO = col_character(),
##   COD_MUN = col_double(),
##   MUNICIPIO = col_character(),
##   GRUPO = col_character(),
##   SUBGRUPO = col_character(),
##   CULTIVO = col_character(),
##   DESAGREG = col_character(),
##   YEAR = col_double(),
##   PERIODO = col_character(),
##   HA_SIEMBRA = col_double(),
##   HA_COSECHA = col_double(),
##   TON_PROD = col_double(),
##   RENDIM = col_double(),
##   CICLO = col_character()
## )
class(eva_boyaca)
## [1] "spec_tbl_df" "tbl_df"      "tbl"         "data.frame"
head(eva_boyaca)

4. 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.

4.1 Selecting columns and filtering rows

We are 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 (eva_boyaca), and the subsequent arguments are the columns to keep.

evita  <-   select(eva_boyaca, MUNICIPIO, CULTIVO, YEAR, TON_PROD,RENDIM)
evita

To choose rows, for example the statistics corresponding to a single crop, we can use filter():

evita_papa <- filter(evita, CULTIVO == "PAPA")
evita_papa

In case we are interested in a single municipality:

evita_paipa <- filter(evita, MUNICIPIO == "PAIPA")
evita_paipa

4.2 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.

evita_paipa_2017 <- evita %>%
  filter(MUNICIPIO == "PAIPA") %>%
  filter(YEAR == 2018) %>%
  select(CULTIVO, RENDIM)
evita_paipa_2017

4.3 Mutate

Frequently we 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().

eva_boyaca %>%
  select(MUNICIPIO, CULTIVO, HA_SIEMBRA, TON_PROD, RENDIM) %>%
  filter(HA_SIEMBRA!=0) %>%
  mutate(RENDIM_SIEMBRA = TON_PROD/HA_SIEMBRA)

4.4 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() function, which splits the data into groups. When the data is grouped in this way summarize() can be used to collapse each group into a single-row summary. summarize() does this by applying an aggregating or summary function to each group.

For example, if we wanted to group by crop and find the mean productivy (Ton/ha) in Boyaca’s municipalities, we would do:

evita %>%
  group_by(CULTIVO) %>%
  summarize(mean_rend = mean(RENDIM, na.rm = TRUE))

In case, we want to find the municipalities with higher productivity for every crop for any year:

eva_boyaca %>%
  group_by(CULTIVO, MUNICIPIO) %>%
  summarize(max_rend = max(RENDIM, na.rm = TRUE)) %>%
  slice(which.max(max_rend))

To find the municipalities with higher productivity for groups of crops for any year:

eva_boyaca %>%
  group_by(GRUPO, MUNICIPIO) %>%
  summarize(max_rend = max(RENDIM, na.rm = TRUE)) %>%
  slice(which.max(max_rend))

Now, let’s find what are the municipalities with largest harvested area for every group of crops in 2018:

eva_boyaca %>% 
  filter(YEAR==2018) %>% 
  group_by(GRUPO, MUNICIPIO) %>%
  summarize(max_area_cosecha = max(HA_COSECHA, na.rm = TRUE)) %>%
    slice(which.max(max_area_cosecha)) %>%
    arrange(desc(max_area_cosecha)) -> area_cosecha_max

area_cosecha_max

Now, let’s find the biggest production for every crop in any year:

eva_boyaca %>% 
  group_by(GRUPO, MUNICIPIO, YEAR) %>%
  summarize(max_prod = max(TON_PROD, na.rm = TRUE)) %>%
    slice(which.max(max_prod)) %>%
    arrange(desc(max_prod)) -> ton_prod_max

ton_prod_max

Note that the largest production amounts to 95450 Ton of TUBERCULOS in Tunja for 2009.

Let’s select tubérculos’ production (Tons) in Tunja for every year:

eva_boyaca %>% 
  filter(MUNICIPIO=="TUNJA" & CULTIVO=="PAPA") %>% 
  group_by(YEAR, CULTIVO) %>%
  select(MUNICIPIO, CULTIVO, TON_PROD, YEAR) ->  tunja_papa

tunja_papa

5. Exploratory graphics:

Let’s do a quick plot of potato production in Tunja for the whole time period covered by the EVA dataset:

# we use the ggplot 2 library
g <- ggplot(aes(x=YEAR, y=TON_PROD/1000), data = tunja_papa) + geom_bar(stat='identity') + labs(y='Produccion de Papa [Ton x 1000]')
g + ggtitle("Evolution of Potato Crop Production in Tunja from 2007 to 2018") + labs(caption= "Based on EVA data (Minagricultura, 2020)")

This graphics suggest a big loss of the potato crop production in Tunja in the last ten years. What happened?

Now, let’s try a pie chart with the names of municipalities with higher potato crop production in a single year:

# Prepare data
data <- eva_boyaca %>%
       filter(CULTIVO=="PAPA" & YEAR==2018 & TON_PROD>25000) %>% 
       select(MUNICIPIO, TON_PROD)
  
  
# Basic piechart
ggplot(data, aes(x="", y=TON_PROD/1000, fill=MUNICIPIO)) +
  geom_bar(stat="identity", width=1) +
  coord_polar("y", start=0)

6. Further tasks

Feel free to explore more functionalities of the packages used here to examine agriculture trends in your department.

7. Reproducibility

sessionInfo()
## R version 4.0.4 (2021-02-15)
## Platform: x86_64-apple-darwin17.0 (64-bit)
## Running under: macOS Mojave 10.14.6
## 
## Matrix products: default
## BLAS:   /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRblas.dylib
## LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib
## 
## locale:
## [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
## 
## attached base packages:
## [1] stats     graphics  grDevices utils     datasets  methods   base     
## 
## other attached packages:
## [1] forcats_0.5.1   stringr_1.4.0   dplyr_1.0.4     purrr_0.3.4    
## [5] readr_1.4.0     tidyr_1.1.2     tibble_3.0.6    ggplot2_3.3.3  
## [9] tidyverse_1.3.0
## 
## loaded via a namespace (and not attached):
##  [1] tidyselect_1.1.0  xfun_0.21         bslib_0.2.4       haven_2.3.1      
##  [5] colorspace_2.0-0  vctrs_0.3.6       generics_0.1.0    htmltools_0.5.1.1
##  [9] yaml_2.2.1        rlang_0.4.10      jquerylib_0.1.3   pillar_1.4.7     
## [13] glue_1.4.2        withr_2.4.1       DBI_1.1.1         dbplyr_2.1.0     
## [17] modelr_0.1.8      readxl_1.3.1      lifecycle_1.0.0   munsell_0.5.0    
## [21] gtable_0.3.0      cellranger_1.1.0  rvest_0.3.6       evaluate_0.14    
## [25] labeling_0.4.2    knitr_1.31        highr_0.8         broom_0.7.5      
## [29] Rcpp_1.0.6        scales_1.1.1      backports_1.2.1   jsonlite_1.7.2   
## [33] farver_2.0.3      fs_1.5.0          hms_1.0.0         digest_0.6.27    
## [37] stringi_1.5.3     grid_4.0.4        cli_2.3.0         tools_4.0.4      
## [41] magrittr_2.0.1    sass_0.3.1        crayon_1.4.1      pkgconfig_2.0.3  
## [45] ellipsis_0.3.1    xml2_1.3.2        reprex_1.0.0      lubridate_1.7.9.2
## [49] assertthat_0.2.1  rmarkdown_2.7     httr_1.4.2        rstudioapi_0.13  
## [53] R6_2.5.0          compiler_4.0.4