1. Introduction

This is our third R notebook :Evaluaciones Agropecuarias Municipales, written using R Markdown Notebook. 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 requiered libraries

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

library(tidyverse)
library(dplyr)
library(ggplot2)

Note: R for Windows installs the precompiled “binary packages” from CRAN. Maybe the previous libraries required a different compilation, for this reason we need install Rtools40. this page can help us to understand it https://cran.r-project.org/bin/windows/Rtools/.

3. Reading a table with agrarian statistics

We edited the .csv file in excel to erase the spaces and symbols that could produce “error” at the usage of the libraries.

EVA <- read.csv(file = "/Users/Acer/Documents/Materias U/Geomatica Basica/Evaluaciones_Agropecuarias_Municipales_EVA.csv", header=T, sep = ";");head(EVA); class(EVA) #Load the all file
## [1] "data.frame"
eva_sucre <- filter(EVA, DEPARTAMENTO == "SUCRE");eva_sucre; class(eva_sucre)#Muestra el archivo solo con el depto de SUCRE
## [1] "data.frame"

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

evita <- select(eva_sucre, MUNICIPIO, CULTIVO, YEAR, TON_PRODUCCION, RENDIMIENTO); evita

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

evita_berenjena <- filter(evita, CULTIVO =="BERENJENA");evita_berenjena

In case we are interested in a single municipality:

evita_sincelejo <- filter(evita, MUNICIPIO == "SINCELEJO");evita_sincelejo

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_sincelejo_2013 <- evita %>%
  filter(MUNICIPIO == "SINCELEJO") %>%
  filter(YEAR == 2013) %>%
  select(CULTIVO, RENDIMIENTO); evita_sincelejo_2013

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_sucre %>%
  select(MUNICIPIO, CULTIVO, HA_SEMBRADA, TON_PRODUCCION, RENDIMIENTO)%>%
  filter(HA_SEMBRADA !=0)%>%
  mutate(RENDIMISIEMBRA = TON_PRODUCCION/HA_SEMBRADA)

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 Sucre’s municipalities, we would do:

evita %>%
  group_by(CULTIVO, MUNICIPIO)%>%
  summarize(mean_rend = mean(RENDIMIENTO, na.rm =T))
## `summarise()` has grouped output by 'CULTIVO'. You can override using the `.groups` argument.

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

eva_sucre %>%
  group_by(CULTIVO, MUNICIPIO, YEAR)%>%
  summarize(max_rend = max(RENDIMIENTO, na.rm =T)) %>%
  slice(which.max(max_rend))
## `summarise()` has grouped output by 'CULTIVO', 'MUNICIPIO'. You can override using the `.groups` argument.

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

eva_sucre %>%
  group_by(GRUPO, MUNICIPIO, YEAR)%>%
  summarize(max_rend = max(RENDIMIENTO, na.rm =T)) %>%
  slice(which.max(max_rend))
## `summarise()` has grouped output by 'GRUPO', 'MUNICIPIO'. You can override using the `.groups` argument.

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

eva_sucre %>%
  filter(YEAR == 2018)%>%
  group_by(GRUPO, MUNICIPIO)%>%
  summarize(max_area_cosecha = max(HA._COSECHADA, na.rm =T)) %>%
    slice(which.max(max_area_cosecha))%>%
    arrange(desc(max_area_cosecha)) -> area_cosecha_max
## `summarise()` has grouped output by 'GRUPO'. You can override using the `.groups` argument.
area_cosecha_max

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

eva_sucre %>%
  group_by(GRUPO, MUNICIPIO, YEAR)%>%
  summarize(max_prod = max(TON_PRODUCCION, na.rm =T)) %>%
    slice(which.max(max_prod))%>%
    arrange(desc(max_prod)) -> ton_prod_max
## `summarise()` has grouped output by 'GRUPO', 'MUNICIPIO'. You can override using the `.groups` argument.
ton_prod_max

Note that the largest production amounts to 47018 Ton of CEREALES in MAJAGUAL for 2016.

Let’s select CEREALES production (Tons) in MAJAGUAL for every year:

eva_sucre %>%
  filter(MUNICIPIO == "MAJAGUAL" & GRUPO == "CEREALES")%>%
  group_by(YEAR,CULTIVO)%>%
  select(MUNICIPIO, CULTIVO, TON_PRODUCCION, YEAR) %>%
  arrange(-TON_PRODUCCION)-> majagual_cereales
  
majagual_cereales

Let’s select ARROZ production (Tons) in MAJAGUAL for every year:

eva_sucre %>%
  filter(MUNICIPIO == "MAJAGUAL" & CULTIVO == "ARROZ")%>%
  group_by(YEAR,CULTIVO)%>%
  select(MUNICIPIO, CULTIVO, TON_PRODUCCION, YEAR) %>%
  arrange(-TON_PRODUCCION)-> majagual_arroz
  
majagual_arroz

5. Exploratory graphics:

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

# we use the ggplot 2 library
g <- ggplot(aes(x = YEAR, y = TON_PRODUCCION/1000), data = majagual_arroz) + geom_bar(stat = 'identity') + labs(y='Produccion de arroz [TON x 1000]')

g + ggtitle("Evolution of rice crop production in malagual from 2007 to 2018") + labs(caption = "Based on EVA data (Minagricultura, 2020)")

This graphics suggest a big loss of the rise crop production in Majagual in the 2017. What happened?

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

# Prepare data
eva_sucre %>%
  filter(CULTIVO == "ARROZ" & YEAR == 2018 )%>%
  group_by(YEAR,CULTIVO)%>%
  select(MUNICIPIO, CULTIVO, TON_PRODUCCION, YEAR)-> sucre_arroz
  
# Basic piechart
ggplot(sucre_arroz, aes(x="", y= TON_PRODUCCION/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.

We are interest by the Eggplant crop in sucre, so we’ll select this crop on the whole time

sucre_berenjena <- eva_sucre %>%
  filter(CULTIVO == "BERENJENA")%>%
  select(MUNICIPIO, YEAR, HA_SEMBRADA, HA._COSECHADA, TON_PRODUCCION, RENDIMIENTO)%>%
  arrange(-TON_PRODUCCION);sucre_berenjena 
g <- ggplot(aes(x = YEAR, y = TON_PRODUCCION/1000), data = sucre_berenjena) + geom_bar(stat = 'identity') + labs(y='Produccion de Eggplant [TON x 1000]')

g + ggtitle("Evolution of Eggplant crop production in sucre from 2007 to 2018") + labs(caption = "Based on EVA data (Minagricultura, 2020)")

ggplot(sucre_berenjena, aes(x="", y= TON_PRODUCCION/1000, fill= MUNICIPIO)) +
  geom_bar(stat="identity", width=1) +
  coord_polar("y", start=0)

7. Reproducibility

sessionInfo()
## R version 4.0.4 (2021-02-15)
## Platform: x86_64-w64-mingw32/x64 (64-bit)
## Running under: Windows 10 x64 (build 18363)
## 
## Matrix products: default
## 
## locale:
## [1] LC_COLLATE=Spanish_Colombia.1252  LC_CTYPE=Spanish_Colombia.1252   
## [3] LC_MONETARY=Spanish_Colombia.1252 LC_NUMERIC=C                     
## [5] LC_TIME=Spanish_Colombia.1252    
## 
## 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.5     purrr_0.3.4    
## [5] readr_1.4.0     tidyr_1.1.3     tibble_3.1.0    ggplot2_3.3.3  
## [9] tidyverse_1.3.0
## 
## loaded via a namespace (and not attached):
##  [1] tidyselect_1.1.0  xfun_0.21         haven_2.3.1       colorspace_2.0-0 
##  [5] vctrs_0.3.6       generics_0.1.0    htmltools_0.5.1.1 yaml_2.2.1       
##  [9] utf8_1.1.4        rlang_0.4.10      pillar_1.5.1      glue_1.4.2       
## [13] withr_2.4.1       DBI_1.1.1         dbplyr_2.1.0      modelr_0.1.8     
## [17] readxl_1.3.1      lifecycle_1.0.0   munsell_0.5.0     gtable_0.3.0     
## [21] cellranger_1.1.0  rvest_1.0.0       evaluate_0.14     labeling_0.4.2   
## [25] knitr_1.31        fansi_0.4.2       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.1.0      fs_1.5.0          hms_1.0.0         digest_0.6.27    
## [37] stringi_1.5.3     grid_4.0.4        cli_2.3.1         tools_4.0.4      
## [41] magrittr_2.0.1    crayon_1.4.1      pkgconfig_2.0.3   ellipsis_0.3.1   
## [45] xml2_1.3.2        reprex_1.0.0      lubridate_1.7.10  assertthat_0.2.1 
## [49] rmarkdown_2.7     httr_1.4.2        rstudioapi_0.13   R6_2.5.0         
## [53] compiler_4.0.4