For future reference this is the correct way of sharing sample data (pasting screen captures is not a good thing to do here).
I think this is what you want
library(dplyr)
library(tidyr)
table_1 <- data.frame(stringsAsFactors = FALSE,
Plot = c('Plot1', 'Plot2', 'Plot3'),
ABCD = c(1, 3, 2),
EFGH = c(2, 3, 0),
IJKL = c(2, 3, 1),
MNOP = c(3, 2, 1)
)
table_2 <- data.frame(stringsAsFactors = FALSE,
Code = c('ABCD', 'EFGH', 'IJKL', 'MNOP'),
Size = c('Big', 'Small', 'Medium', 'Big')
)
table_1 %>%
gather(Code, Count, ABCD:MNOP) %>%
left_join(table_2, by = 'Code') %>%
group_by(Plot, Size) %>%
summarise(n = sum(Count))
#> # A tibble: 9 x 3
#> # Groups: Plot [?]
#> Plot Size n
#> <chr> <chr> <dbl>
#> 1 Plot1 Big 4
#> 2 Plot1 Medium 2
#> 3 Plot1 Small 2
#> 4 Plot2 Big 5
#> 5 Plot2 Medium 3
#> 6 Plot2 Small 3
#> 7 Plot3 Big 3
#> 8 Plot3 Medium 1
#> 9 Plot3 Small 0
Created on 2019-01-15 by the reprex package (v0.2.1)