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

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!

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)

rownames_to_column(var = "rn")

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.

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

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

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))``````

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.