Looping over an iteratively changing subset of grouped rows in R

Hello team,

I have a mind numbing loop to write and your help would be greatly appreciated!

I am trying to summarise grouped data by excluding each row iteratively and summing the mean of the remaining group, then rejoining to form a new data frame.

So, for instance with this mockup data...

x <- c("A","A", "B", "B")
y <- c(10:13)

xy <- data.frame(x,y) %>% 
      group_by(x) %>% 
      mutate(index = row_number(x)) %>% 
      ungroup() 

xy$uniq_id<- with(xy, paste0(index, x))

Heres an image of the mockup data frame.

For 1A I the output value would be 11 because without row 1A, the sum of y for group A is 11. So, the value for 1B would be 13, and the value for 2B would be 12 ect.

Here is how I would do it manually for all the rows with index 1

xynew <- xy %>% 
  group_by(x) %>% 
  filter(index != "1") %>% 
  mutate(sum_Sepal = sum(y))
  
  full_join(xy, xynew, by = "uniq_id")

My data frame has 38 groups with 1-70 rows per group so I cant do this manually!

Your help in trying to writing a loop for this would be amazing.

Please let me know if you have any questions:)

Looking forward to hearing from you.

Is this what you want?

library(dplyr)
DF <- data.frame(x=c("A","A","A","B","B","C","C","C"),
                 y=1:8)
DF |> group_by(x) |> 
  mutate(Total=sum(y), Spec_Sum=Total-y)
#> # A tibble: 8 × 4
#> # Groups:   x [3]
#>   x         y Total Spec_Sum
#>   <chr> <int> <int>    <int>
#> 1 A         1     6        5
#> 2 A         2     6        4
#> 3 A         3     6        3
#> 4 B         4     9        5
#> 5 B         5     9        4
#> 6 C         6    21       15
#> 7 C         7    21       14
#> 8 C         8    21       13

Created on 2022-10-06 with reprex v2.0.2

2 Likes

Yes!! Thank you heaps:) you are amazing.

This topic was automatically closed 7 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.