Average of all rows minus row 1 (2,3,4) ?

[cwright1]

Say we have a few values in one column:

mtcars <- data.frame("mpg"=mtcars[1:10,1])

mtcars
    mpg
1  21.0
2  21.0
3  22.8
4  21.4
5  18.7
6  18.1
7  14.3
8  24.4
9  22.8
10 19.2

How can I make a new column where I can calculate the average of all rows minus row1? Then row2, ... row 10?

So in the new column:
row 1 = (average of all rows except row1) - 21.0
row 2 = (average of all rows except row2) - 21.0
row 3 = (average of all rows except row3) - 22.8

I thought I could use sapply but I'm not getting anything to work

Thanks for any help!

[EeethB]

I love this question! It hit on a couple techniques that I felt like I knew-ish, but wasn't totally comfortable with them, and now I feel better about them. So thank you for your nerd sniping!

Here's what I came up with:

library(fuzzyjoin)
library(tidyverse)

mtcars_head <- data.frame("mpg"=mtcars[1:10, 1]) %>% 
  rownames_to_column(var = "rn")

mh_join <- fuzzy_left_join(mtcars_head, mtcars_head,
                      by = c("rn" = "rn"),
                      match_fun = `!=`)

mh_nest <- mh_join %>% 
  group_by(rn.x, mpg.x) %>% 
  nest()

mh_nest %>% 
  mutate(avg = map_dbl(data, ~mean(.x$mpg.y)))
#> # A tibble: 10 x 4
#> # Groups:   rn.x, mpg.x [10]
#>    rn.x  mpg.x data                   avg
#>    <chr> <dbl> <list>               <dbl>
#>  1 1      21   <tibble[,2] [9 x 2]>  20.3
#>  2 2      21   <tibble[,2] [9 x 2]>  20.3
#>  3 3      22.8 <tibble[,2] [9 x 2]>  20.1
#>  4 4      21.4 <tibble[,2] [9 x 2]>  20.3
#>  5 5      18.7 <tibble[,2] [9 x 2]>  20.6
#>  6 6      18.1 <tibble[,2] [9 x 2]>  20.6
#>  7 7      14.3 <tibble[,2] [9 x 2]>  21.0
#>  8 8      24.4 <tibble[,2] [9 x 2]>  19.9
#>  9 9      22.8 <tibble[,2] [9 x 2]>  20.1
#> 10 10     19.2 <tibble[,2] [9 x 2]>  20.5

^{Created on 2021-05-17 by the reprex package (v2.0.0)}

Essentially, this joins the data to itself where the rownames are not equal, then calculates the mean for each group.

[startz]

I think this particular one is easy. (Famous last words.)

(sum(mtcars$mpg)-mtcars$mpg)/(nrow(mtcars)-1)

[nirgrahamuk]

taking startz's idea and putting in dplyr context.

data.frame("mpg"=mtcars[1:10,1]) %>%
  mutate(avg_not_self=
           (sum(mpg)-mpg)/(nrow(.)-1))