Group_by

Grouping data in R with dplyr is very simple.

You use the group_by function to create implicit groupings within your data.

Lets first look at grouping our Plot data by the farmers who tend them.

We simply provide the name of the variable we want to use to define our groups.

Plot_data <- Plot_data %>%
  group_by(farmer_id)

Plot_data

Looking at the data, it will look like nothing has changed. This is because the groups are implicit, it is in essence now part of the metadata of the data. But R will recognise that this grouping exists within the dataset and perform operations accordingly.

Indeed if we take a look at the structure of our data using str, which displays the structure of an R object, we will see we will see that rather than our data being a data.frame (the standard data format in R), it is now a grouped_df (grouped data frame). From this we can see that we have created 19 groupings in our data and we can see for each group which rows belong to it.

So our first group (farmer 1) has 3 rows in the data, and they are rows 6,10 and 38.

str(Plot_data)

If for any reason you want to then get rid of this grouping, perhaps at a later point you want to summarise across your whole data and not groups, or by a different grouping variable entirely.

Then we can just add in ungroup and our data will be ungrouped.

Plot_data <- Plot_data %>%
  ungroup()

Summarise / grouped mutates

Now that we know how to group our variables, let's look at how we can start aggregating our data.

The simplest way to start creating data summaries is to use the summarise function from the dplyr package.

This will generate summary statistics that you define for each of the groups in your data, or for your whole dataset if your data is not grouped.

In this case we have decided to calculate the total area of all of the farmers plots. As we want a total, we use the function sum to sum up all the area sizes. Note that we have added the argument na.rm = TRUE this makes sure that any missing values are removed from the calculation. If this is not included and there is missing data, then our result would just read NA and not give an actual number.

Sum1 <- Plot_data %>%
  group_by(farmer_id) %>%
  summarise(total_area = sum(size, na.rm = TRUE))

Sum1

When using summarise, the number of rows returned will be equal to the number of groups in your data. So we had 19 groups, so 19 rows. If our data was not grouped then we would have had only 1 row returned. Also when summarising data, the resulting data will only contain the variables used to group your data and the summary variables you have created. All other variables will be dropped.

Our resulting data has moved data from the plot level and summarised it up to the farmer level.

Just like mutate we can start creating many different summary variables by separating the calculations with a comma.

In this example, we have additionally created summaries for the average size of a farmer's plots, the standard deviation of plot size and finally we have used n() to generate a variable counting how many rows are in each group. Or in other words, how many plots each farmer has. Note that we have NA values for sd as a this has happened where the farmer has only 1 plot, therefore a standard deviation cannot be calculated.

Sum2 <- Plot_data %>%
  group_by(farmer_id) %>%
  summarise(total_area = sum(size, na.rm = TRUE),
            avg_area = mean(size, na.rm = TRUE),
            sd = sd(size, na.rm = TRUE),
            nplots = n())

Sum2

There are a number of other summarise functions that can be used to apply the same function to multiple columns rather than go through one by one. For more on this, please follow this link

You can also use mutate to generate these same variables, while keeping your data set at the plot level.

We keep everything within the function the exact same.

All we do is swap summarise for mutate

We have added in an arrange to sort the data now on the farmer_id rather than plot_id, just to make this demonstration a little clearer.

Note that we have kept all of our plot specific data and we still have 50 rows. But now we also have variables for total_area, avg_area, sd and nplots. The results of which are the same as what was calculated by summarise, but instead we see these results replicated for each row within a group.

Sum3 <- Plot_data %>%
  group_by(farmer_id) %>%
  mutate(total_area = sum(size, na.rm = TRUE),
            avg_area = mean(size, na.rm = TRUE),
            sd = sd(size, na.rm = TRUE),
            nplots = n()) %>%
  arrange(farmer_id)

Sum3 

This could be useful for creating a new variable that is at one level, but is also dependent on a variable from a level higher up. For example, by keeping our data at plot level we could generate a new variable that is the proportion of the total area that each plot represents across each farmer.

Sum3 <- Sum3 %>%
  mutate(plot_area_prop = size/total_area)

Sum3 

Row binding

Row binding would be used when you have two or more datasets containing the same variables, the difference is that they contain separate sets of observations. For example, you could have data collected in one location and data collected in another but this data has been stored apart. They contain the same variables as the data collection tool was identical but different observations. You can bind these datasets together by their rows.

Let's pretend that our farmer data was originally stored into different data sets and now we want to combine them together

First we have farmers 1 through 12

Then farmers 13 through 20.

It is thankfully very simple to bind these two datasets as we have the same number of columns in our data, and they have the same names.

We can use the base r function rbind to simply achieve this binding. All we need to do is add the names of the data sets we want to bind.

rbind(Farmer_dataA, Farmer_dataB)

Now with rbind there is a little issue that occurs if there are variables in one dataset that aren't in the other. Let's add an age variable to our first dataset but not the second and see what happens when we try to merge them

rbind(Farmer_dataA_Age, Farmer_dataB)

We get an error instead. This is because rbind requires that there are exactly the same number of columns in both data sets.

To get around this issue, there is the bind_rows function from dyplr. This matches the columns by their names (so make sure those are the same in both datasets still), and if there are any column not present in both datasets, it will just fill this with NA in the data set where it is not present.

bind_rows(Farmer_dataA_Age, Farmer_dataB)

Column binding

Column binding on the other hand would be utilised when we have the same set of observations, but the variables are different. Perhaps we have taken additional measurements at a later point and want to bring this together with the original data.

For example, let's look to our plot data and bring in data on the yield of the crops grown on each plot. Data we have collected at a later point.

For column binding, as you may expect the base r function is cbind and the dplyr alternative is bind_cols. Unlike with row binding, there is not really any difference between how the functions operate.

Both require the same number of rows in order to operate. These should also be in the same order, if they are not you could use arrange first to make sure that they are.

cbind(Plot_data, Plot_data2)

and with bind_cols

bind_cols(Plot_data, Plot_data2)

Now you will notice that because both datasets contained plot_id, our resulting data table has two id columns unhelpfully names "plot_id...1" and "plot_id...6". This is because column binding will not merge information from columns that have the same name, rather they will just change the names or in the case of cbind, simply repeat them.

Therefore, if we were to use column binding it would be a good idea to drop the plot_id from one dataset and then perform the bind.

We can use select and then put a - before the name of the variable to remove it from the data

Though of course this stresses the importance that these ids MUST be identical in both data sets. The same numbers, and those numbers mean the same thing.

Plot_data3 <- Plot_data2 %>%
  select(-plot_id)

Now when we bind the data sets. We keep plot_id as normal

Plot_data <- bind_cols(Plot_data, Plot_data3)

Plot_data

This binding may have been more smoothly achieved if we had actually done a join instead as we shall see later in the workbook.

Example 1

First let's bring some data down a level. For instance let's say we want to analyse some data at the plot level but we want to group this data by village to generate a village level average plot size.

But we do not know which farmers lives in which village just by looking at this plot level data.

But we do have farmer_id which can link us up with the farmer data. A data set which does include the farmer data, which includes the village id as a variable.

So we need to merge information from the farmer data and the plot data using this link. Here our foreign key is farmer_id as it does not uniquely identify our rows in the plot data. But it is identifying a particular unit of observation, the farmer. This foreign key (farmer_id) links up to the primary key of the farmer data (farmer_id).

This is an example of a 1 to Many relationship as for each individual farmer there can be many plots.

Now you may have noticed that we have 20 farmers in our data, but when we have grouped our plots by farmer, we had only 19 groups. That is because we have one farmer without any plots.

This helps inform our decision on what type of join we need to use.

We are primarily concerned with our plot data, so those are the rows we want to keep.We don't want additional rows with little information of use. So we do not want to use a full join because we would get a row for this additional farmer despite them having no plots in our data. This row would be useless.

In this example we don't have any plots belonging to farmers not in our data, but if this were the case, again we would have rows with unhelpful information as we would not be able to identify which village they lived in because they are not in the records.

So in this case, we need an inner join. Because we only want to keep the rows that have a match, because then we will have both plot size and village information.

Plot_data <- Plot_data %>%
  inner_join(Farmer_data, by = "farmer_id")

Plot_data

So we have successfully brought down the farmer data, including both the village_id and the farmers names.

We could extend this further and bring down the village level information as well.

Plot_data <- Plot_data %>%
  inner_join(Village_data, by = "village_id")

Plot_data

Now we can use group_by and summarise to calculate that village level plot average

Plot_data %>%
  group_by(village_name) %>%
  summarise(avg_plot_size = mean(size, na.rm = TRUE))

Example 2

Now lets start moving data the other way. In the village level data, I want to calculate the average total expenditure on fertilisers. So I have to start with fertiliser data and bring this all the way up to the village level.

Firstly I of course need to match fertilisers and plots. This is actually an example of a many to many relationship because a plot can have up to two fertilisers and a fertiliser can be used on many different plots.

Now as our plot data is at that plot level we actually have the names of the two fertilisers in separate columns. Therefore we cannot match them straight away, We could do it in staggered steps, first bring in the costs for fertiliser 1 and then the costs for fertiliser 2. Though we would then have to reshape the data to long so we could join those two costs columns together.

It would be simpler if we actually reshaped our plot data first and then merged it with the fertiliser data.

So using what we learned in the previous tutorial Let's create a long version of our plot data.

Long_plots <- Plot_data %>%
  pivot_longer(
    cols = fertiliser_1:fertiliser_2,
    names_to = "Fertiliser_no",
    values_to = "fertiliser_id"
  )

Long_plots

Now we can bring in the fertiliser data. Again using left join, as we want to prioritise the plot information.

Long_plots <- Long_plots %>%
  left_join(Fertiliser_data, by = "fertiliser_id")

Long_plots

Okay so we now have our price information in the data. However there is one further step we need to make before we can go further up the levels of this data. Price is calculated at per ha, so to get total expenditure we need to multiply this by the size of the plot. we can do this using mutate

Long_plots <- Long_plots %>%
  mutate(price  = price_per_ha * size)

Long_plots

Now we can start summarising and bring the data up in sequential steps.

Starting with going from that long plot data, technically at the fertiliser level, back to normal plot level.

We can do this all in one pipe.

Starting with long plots we group by plot_id.

Then we summarise to calculate a plot level total expenditure on fertilisers.

Finally we right join onto our plot data. We use a right join because we start with long plots and pipe through therefore it will always be the first (left) argument in that join. But we only want the rows from the plot data so we want to join to the right.

Plot_data <- Long_plots %>%
  group_by(plot_id) %>%
  summarise(price_total = sum(price, na.rm = T)) %>%
  right_join(Plot_data, by = "plot_id")

Plot_data

We now simply repeat the process to bring this up to farmer level. Simply switching out our data arguments and change to use the appropriate keys that link the data sets.

Farmer_data <- Plot_data %>%
  group_by(farmer_id) %>%
  summarise(price_total = sum(price_total, na.rm = T)) %>%
  right_join(Farmer_data, by = "farmer_id")

Farmer_data

Finally, we repeat this one more time to bring it all up to the village level.

Village_data <- Farmer_data %>%
  group_by(village_id) %>%
  summarise(price_average = mean(price_total, na.rm = T)) %>%
  right_join(Village_data, by = "village_id")

Village_data

If we wanted to get fancy this could have all been done in the one pipe.

Note that as we start with Plot_data we do not need to join on to it like we did before, and because plot data already has the farmer_id we can go straight to summarising the data at that level first before moving it upwards to village.

Plot_data %>%
  pivot_longer(
    cols = fertiliser_1:fertiliser_2,
    names_to = "Fertiliser_no",
    values_to = "fertiliser_id"
  ) %>%
  left_join(Fertiliser_data, by = "fertiliser_id") %>%
  mutate(price = price_per_ha*size) %>%
  group_by(farmer_id) %>%
  summarise(price_total = sum(price, na.rm = TRUE)) %>%
  right_join(Farmer_data, by = "farmer_id") %>%
  group_by(village_id) %>%
  summarise(price_average = mean(price_total, na.rm = TRUE)) %>%
  right_join(Village_data, by = "village_id")