Averaging values in a column based on variable in another for line graph

Question

I'd like to be able to average the values across Small.area for every given Big.area. I then want to plot a line graph with year on the X and the mean of values for small.area on the Y colored by the big.area - i.e. a line for every value of the averaged small.area colored by Big.area

Year	Big.area	Small.area	Value
2011	A	a	10
2011	A	b	24
2011	B	c	32
2011	B	d	22
2012	A	a	20
2012	A	b	30
2012	B	c	40
2012	B	d	10
2013	A	a	15
2013	A	b	34
2013	B	c	10
2013	B	d	30
2014	A	a	15
2014	A	b	35
2014	B	c	25
2014	B	d	35

so I'd end up with a table like

Year	Big.area	mean.small.areas
2011	A	17
2011	B	27
2012	A	25
2012	B	25
2013	A	24.5
2013	B	20
2014	A	25
2014	B	30

which I can then plot using ggplot(df, aes(x= Year, y =mean.small.area, color = Big.area))+geom_line()

Any ideas? Thanks for any suggestions

Are those mean values based on the same example input. I get different means based on your data with(df1, mean(Value[Big.area == "A" & Year == 2011])) [1] 17 — akrun
If the below solution works, please consider to accept solution. thanks — akrun

akrun akrun · Accepted Answer · 2021-07-27T00:50:36

We could use a group by summarise approach

library(ggplot2)
library(dplyr)
out <- df1 %>% 
    group_by(Year, Big.area) %>% 
    summarise(mean.small.area = mean(Value, na.rm = TRUE), 
      .groups = 'drop')

-output

out
# A tibble: 8 x 3
   Year Big.area mean.small.area
  <int> <chr>              <dbl>
1  2011 A                   17  
2  2011 B                   27  
3  2012 A                   25  
4  2012 B                   25  
5  2013 A                   24.5
6  2013 B                   20  
7  2014 A                   25  
8  2014 B                   30

and use the summarised data in ggplot

ggplot(out, aes(Year, y = mean.small.area, color = Big.area))+
       geom_line() + 
       theme_bw()

-output

data

df1 <- structure(list(Year = c(2011L, 2011L, 2011L, 2011L, 2012L, 2012L, 
2012L, 2012L, 2013L, 2013L, 2013L, 2013L, 2014L, 2014L, 2014L, 
2014L), Big.area = c("A", "A", "B", "B", "A", "A", "B", "B", 
"A", "A", "B", "B", "A", "A", "B", "B"), Small.area = c("a", 
"b", "c", "d", "a", "b", "c", "d", "a", "b", "c", "d", "a", "b", 
"c", "d"), Value = c(10L, 24L, 32L, 22L, 20L, 30L, 40L, 10L, 
15L, 34L, 10L, 30L, 15L, 35L, 25L, 35L)), class = "data.frame", row.names = c(NA, 
-16L))

Averaging values in a column based on variable in another for line graph

1 Answers

data