weighted average of a subset of rows

[yoyong]

Hi everyone.

I need to do the following: get a weighted average of a subset in a dataset, relabel the rows and use weighted average. I think it is best to explain using the tables below.

In the new dataset, I got the weighted average [total sum of the products of n and ave columns of rows 4 to 6 divided by total sum of n column of rows 4 to 6]. Then call rows 4 to 6 as 11 and use the weighted average as entry in the new dataset.

Thanks heaps for the help.

Yoyong

Original data

Codes	n	ave
1	25	80
2	10	75
3	15	65
4	25	80
5	30	90
6	30	85
7	40	75
8	60	70
9	10	80

New dataset

Codes	ave
1	80
2	75
3	65
11	85.29
7	75
8	70
9	80

[bustosmiguel]

Yourdataframe %>% mutate(Mean = Ave/245)

And you’ll get a Mean column

I have a video that can help you, sorry my english ehh! 5:47

- YouTube

I hope this is usefull, if you want to add more columns continuous piping : %>% mutate()…

Greetings!

[HanOostdijk]

If you more than one group to recode you could something like:

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

df1 <- tibble::tribble(
  ~Codes, ~n, ~ ave, 
1,  25,     80,
2,  10,     75,
3,  15,     65,
4,  25,     80,
5,  30,     90,
6,  30,     85,
7,  40,     75,
8,  60,     70,
9,  10,     80
)

df2 <- df1 %>%
  mutate(Codes2 = case_when(
    Codes %in% c(4,5,6) ~ 11 , 
    # optionally more recodings
    # others remain the same
    TRUE ~ Codes
    ),
    nave = n * ave
  ) %>% 
  group_by(Codes2) %>%
  summarise(n=sum(n), nave=sum(nave)) %>%
  rename(Codes=Codes2) %>%
  mutate (ave = nave / n) %>%
  select(-c(n,nave))
print(df2)
#> # A tibble: 7 x 2
#>   Codes   ave
#>   <dbl> <dbl>
#> 1     1  80  
#> 2     2  75  
#> 3     3  65  
#> 4     7  75  
#> 5     8  70  
#> 6     9  80  
#> 7    11  85.3
Created on 2021-12-02 by the reprex package (v2.0.1)