 # How to average a subset of data based on other columns

Hi! I have a question about how to subset data to then achieve an average of a specific group of numbers.
For instance:

``````block<-c('T1', 'T1', 'T1', 'T1','T2', 'T2', 'T2', 'T2')
plot<-c('1', '1', '1', '1','1', '1', '1', '1')
subplot<-c('NO', 'NI', 'EO', 'EI','NO', 'NI', 'EO', 'EI')
data1<-c(0,2,4,2,3,5,2,4)
data2<-c(1,1,1,7,2,2,3,1)

orig.DF<-data.frame(block, plot, subplot, data1, data2)
``````

So I have block, plot, and subplot as my three levels. I want to have a single average for each plot. So I need to combine the data for each subplot to have a single average value for data1 and data2 that represent a block and plot.
I want a dataframe that looks like this:
block plot data1 data2
1 1 2 2.5
2 1 3.5 2

I can probably use the subset or aggregate functions but I'm not sure how to syntax this!
Please let me know if you need any additional information! Thank you!

You can do it with the `dplyr` package

``````orig.DF <- data.frame(stringsAsFactors = FALSE,
data1 = c(0, 2, 4, 2, 3, 5, 2, 4),
data2 = c(1, 1, 1, 7, 2, 2, 3, 1),
block = as.factor(c("T1", "T1", "T1", "T1", "T2", "T2", "T2", "T2")),
plot = as.factor(c("1", "1", "1", "1", "1", "1", "1", "1")),
subplot = as.factor(c("NO", "NI", "EO", "EI", "NO", "NI", "EO", "EI"))
)

library(dplyr)

orig.DF %>%
group_by(block, plot) %>%
summarise(mean_data1 = mean(data1), mean_data2 = mean(data2))
#> # A tibble: 2 x 4
#> # Groups:   block 
#>   block plot  mean_data1 mean_data2
#>   <fct> <fct>      <dbl>      <dbl>
#> 1 T1    1            2          2.5
#> 2 T2    1            3.5        2
``````

Created on 2019-03-06 by the reprex package (v0.2.1)

You can learn how to use `dplyr` and the rest of the `tidyverse` by reading this free ebook

1 Like

Thank you! That's exactly what I want. What if instead of just 'data1' and 'data2' I have like 30 different columns, is there a shorter way to do that? Like specifying column (4:34) or something?

You can use `summarise_if()` or `summarise_at()`, this is an example that applies `mean()` function to all the numeric variables.

``````orig.DF %>%
group_by(block, plot) %>%
summarise_if(is.numeric, mean)
#> # A tibble: 2 x 4
#> # Groups:   block 
#>   block plot  data1 data2
#>   <fct> <fct> <dbl> <dbl>
#> 1 T1    1       2     2.5
#> 2 T2    1       3.5   2
``````

Perfect! Worked like a charm. Thank you so much.

Eventually what I need to do is also average groups of columns. So say I have data1:data20. And then doing the same thing as above, I then need have average 1:4, 5:8, 9:12, and so on. Any ideas?

You can also perform rowwise operations but this is not the preferred method becuse it's "untidy".

``````orig.DF %>%
group_by(block, plot) %>%
summarise_if(is.numeric, mean) %>%
rowwise() %>%
mutate(mean_1_2 = mean(c(data1,data2)))
#> Source: local data frame [2 x 5]
#> Groups: <by row>
#>
#> # A tibble: 2 x 5
#>   block plot  data1 data2 mean_1_2
#>   <fct> <fct> <dbl> <dbl>    <dbl>
#> 1 T1    1       2     2.5     2.25
#> 2 T2    1       3.5   2       2.75
``````

I recommend you to open a new topic about this but providing a relevant REPRoducible EXample (reprex), that way we can better understand the structure of your dataset and provide more suitable help.

If you've never heard of a reprex before, you might want to start by reading this FAQ:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.