1

I am trying to obtain the weighted mean of some soil properties, weighted by their surface but aggregated by their owner and other levels. My dataset looks like this full_dataset and i would like to obtain the mean like thismean_dataset. My code right now is:

results<- original_df %>% 
group_by(owner,cultivar) %>%                
summarise(across(.cols= where(is.numeric),fns= weighted.mean(.,w= surface)))

but at the moment is not working, because the output as the same number of rows of the original dataset. What am I missing?

data:

structure(list(town = c("a", "a", "a", "a", "b", "b", "b", "b", 
"b", "C", "C", "C", "C", "C", "C"), cultivar = c(1L, 1L, 2L, 
2L, 1L, 2L, 3L, 4L, 4L, 3L, 3L, 2L, 2L, 1L, 1L), owner = c("A", 
"A", "B", "C", "A", "B", "B", "C", "C", "B", "B", "C", "C", "A", 
"A"), surface = c(456L, 446L, 462L, 120L, 204L, 250L, 642L, 466L, 
580L, 258L, 146L, 617L, 79L, 304L, 48L), x1 = c(202L, 647L, 525L, 
536L, 563L, 269L, 376L, 492L, 229L, 177L, 413L, 156L, 77L, 79L, 
609L), x2 = c(91L, 334L, 110L, 533L, 161L, 605L, 344L, 380L, 
221L, 368L, 179L, 531L, 357L, 66L, 157L), x3 = c(300L, 90L, 570L, 
43L, 403L, 245L, 640L, 344L, 371L, 70L, 546L, 400L, 255L, 176L, 
336L)), class = "data.frame", row.names = c(NA, -15L))
TarJae
  • 72,363
  • 6
  • 19
  • 66
Marco Prandi
  • 15
  • 5

1 Answers1

3

If we use ., we need the lambda (~) i..e. the ~ is equivalent to function(.).

library(dplyr)
original_df %>% 
 group_by(owner,cultivar) %>%                
  dplyr::summarise(across(.cols= starts_with('x'),
     ~ weighted.mean(.,w= surface)), .groups = 'drop')

This may also work by just removing the () and the . as the w is already specified as 'surface' and thus it implicitly take the x column looped

original_df %>% 
 group_by(owner,cultivar) %>%                
 dplyr::summarise(across(.cols= starts_with('x'),
     weighted.mean, w= surface), .groups = 'drop')

-output

# A tibble: 5 × 5
  owner cultivar    x1    x2    x3
  <chr>    <int> <dbl> <dbl> <dbl>
1 A            1  376.  172.  226.
2 B            2  435.  284.  456.
3 B            3  332.  327.  486.
4 C            2  204.  514.  333.
5 C            4  346.  292.  359.
akrun
  • 874,273
  • 37
  • 540
  • 662
  • I copy-pasted your answer to be sure to not change anything, and I still have the same problem, rows seem are not grouping together. I think I will have to code that portion for each colum i want to weight – Marco Prandi Oct 29 '21 at 18:15
  • @MarcoPrandi you may have `plyr` loaded which masked `summarise` Use `dplyr::summarise(across(` – akrun Oct 29 '21 at 18:20
  • @MarcoPrandi can you try the update – akrun Oct 29 '21 at 18:26
  • aaa<-stampa_risultati %>% group_by(comune) %>% dplyr::summarise(across(.cols= where(is.numeric), fns= ~ weighted.mean,w= superficie)) View(aaa) – Marco Prandi Oct 29 '21 at 18:46
  • this is exactly the code I'm running, It's not working again, i also tryed to unistall and install dplyr, and I paid attention to not load any package that required plyr – Marco Prandi Oct 29 '21 at 18:49
  • 1
    @akrun: I edited OP's question and put in the data. Maybe it helps to solve! – TarJae Oct 29 '21 at 18:50
  • where(is.numeric) also selects the surface. Check the update. Instead, we want to use `'x' columns selected – akrun Oct 29 '21 at 19:04
  • many tanks @akrun for your help. I think that ".group" was the solution, together with the right order and sintax like ~ etc. My finale code is now: – Marco Prandi Oct 29 '21 at 21:21
  • aaa<-stampa_risultati %>% group_by(azienda,vitigno) %>% dplyr::summarise(across(.cols= where(is.numeric), ~weighted.mean(.,w= superficie)), .groups = 'drop') View(aaa) – Marco Prandi Oct 29 '21 at 21:22
  • 1
    Now is working perfectly as it should do. even with ".cols= where(is.numeric)". Thanks also to @TarJae for the edit that helped to understand and solve the problem. – Marco Prandi Oct 29 '21 at 21:24