Error in Weighted.Mean. Why can't it find column?

user_14332320 0
Viewed 72

I have a dataframe that looks like this

df <- data.frame(Region = c("Asia","Asia","Africa","Europe","Europe"),
Emp = c(120,40,10,67,110),
Sales18 = c(12310, 4510, 1140, 5310, 16435),
Sales19 = c(15670, 6730, 1605, 6120, 1755))

I am running a code where I group by region and then take average and weighted average for all 'sales' columns by 'Emp'

Result <- df %>% group_by(Region) %>% 
summarise(sales18 = mean(Sales18, na.rm = T),
sales19 = mean(Sales19, na.rm = T),
weightedsales18 = weighted.mean(Sales18, .data[[Emp]], na.rm = T),
weightedsales19 = weighted.mean(Sales19, .data[[Emp]], na.rm = T))

However, I get the following err

Error in splice(dot_call(capture_dots, frame_env = frame_env, named = named,  : 
  object 'Emp' not found

Can't figure out what I am doing wrong

dplyr r
3 Answers

This works. The data masking already takes place, you don't need the .data pronoun.

library(tidyverse)
df <- data.frame(Region = c("Asia","Asia","Africa","Europe","Europe"),
                 Emp = c(120,40,10,67,110),
                 Sales18 = c(12310, 4510, 1140, 5310, 16435),
                 Sales19 = c(15670, 6730, 1605, 6120, 1755))

Result <- df %>% group_by(Region) %>% 
  summarise(sales18 = mean(Sales18, na.rm = T),
            sales19 = mean(Sales19, na.rm = T),
            weightedsales18 = weighted.mean(Sales18, Emp, na.rm = T),
            weightedsales19 = weighted.mean(Sales19, Emp, na.rm = T))
Result
#> # A tibble: 3 x 5
#>   Region sales18 sales19 weightedsales18 weightedsales19
#>   <chr>    <dbl>   <dbl>           <dbl>           <dbl>
#> 1 Africa   1140    1605            1140            1605 
#> 2 Asia     8410   11200           10360           13435 
#> 3 Europe  10872.   3938.          12224.           3407.

Created on 2021-05-25 by the reprex package (v2.0.0)

user_54597290

An option could be:

library(tidyverse)
df <- data.frame(Region = c("Asia","Asia","Africa","Europe","Europe"),
                 Emp = c(120,40,10,67,110),
                 Sales18 = c(12310, 4510, 1140, 5310, 16435),
                 Sales19 = c(15670, 6730, 1605, 6120, 1755))


df %>%
  group_by(Region) %>%
  summarise(across(
    .cols = starts_with("Sales"),
    .fns = list(w_mean = ~ weighted.mean(.x, w = Emp), mean = ~ mean(.x)), 
    .names = "{.col}_{.fn}")
  )
#> # A tibble: 3 x 5
#>   Region Sales18_w_mean Sales18_mean Sales19_w_mean Sales19_mean
#>   <chr>           <dbl>        <dbl>          <dbl>        <dbl>
#> 1 Africa          1140         1140           1605         1605 
#> 2 Asia           10360         8410          13435        11200 
#> 3 Europe         12224.       10872.          3407.        3938.

Created on 2021-05-25 by the reprex package (v2.0.0)

user_120254830

Unquoted Emp inside [[ tells R to search for string variable called Emp that presumably contains name of other variable that contains weights, like here:

df <- data.frame(Region = c("Asia","Asia","Africa","Europe","Europe"),
             x = c(120,40,10,67,110),
             Sales18 = c(12310, 4510, 1140, 5310, 16435),
             Sales19 = c(15670, 6730, 1605, 6120, 1755))

Emp <- 'x'

df %>% group_by(Region) %>% 
  summarise(sales18 = mean(Sales18, na.rm = T),
            sales19 = mean(Sales19, na.rm = T),
            weightedsales18 = weighted.mean(Sales18, .data[[Emp]], na.rm = T),
            weightedsales19 = weighted.mean(Sales19, .data[[Emp]], na.rm = T))

# A tibble: 3 x 5
  Region sales18 sales19 weightedsales18 weightedsales19
  <chr>    <dbl>   <dbl>           <dbl>           <dbl>
1 Africa   1140    1605            1140            1605 
2 Asia     8410   11200           10360           13435 
3 Europe  10872.   3938.          12224.           3407.

Since, you do not have this kind of Emp, R throws an error.

What to do? Just quote Emp inside [[:

df <- data.frame(Region = c("Asia","Asia","Africa","Europe","Europe"),
                 Emp = c(120,40,10,67,110),
                 Sales18 = c(12310, 4510, 1140, 5310, 16435),
                 Sales19 = c(15670, 6730, 1605, 6120, 1755))


df %>% group_by(Region) %>% 
  summarise(sales18 = mean(Sales18, na.rm = T),
            sales19 = mean(Sales19, na.rm = T),
            weightedsales18 = weighted.mean(Sales18, .data[['Emp']], na.rm = T),
            weightedsales19 = weighted.mean(Sales19, .data[['Emp']], na.rm = T))

# A tibble: 3 x 5
  Region sales18 sales19 weightedsales18 weightedsales19
  <chr>    <dbl>   <dbl>           <dbl>           <dbl>
1 Africa   1140    1605            1140            1605 
2 Asia     8410   11200           10360           13435 
3 Europe  10872.   3938.          12224.           3407.
user_80309300
Related