Select a percentage of rows for each group and calculate a stastistics for a specific column

[piravi80]

H everyone, I have a little problem, and I hope you can help me.

I have a dataset:

value <- c(3,4,5,4,3,4,5,4,3,4,3,4,6,5,4,5,6,7,5,4,6,4,6,3,2,6,7,4,7,8,5,6,7,5,5,7,8,7,45,3,4,3,4,6,4,3,2,1,2,3,4)
Sub <- c(rep("sub1", 10), rep("sub2",25), rep("sub3", 16))

dat <-as.data.frame(Sub, value)

It'a only an example, but it is useful to give you an idea of my purposes.

What I need to do is:

1. to divide for each subject the variable value in 10 part and to calculate a statistics, such as the mean
2. I should create a dataframe like this:

  p1    p2    p3    p4    p5    p6    p7   p8    p9   p10
Sub1   
Sub2

Sub...

I used a function like this to calculate a percentage of rows to analyze.

subset_percent <- function(x, start=0, end=100) {
  stopifnot(
    is.numeric(start), is.numeric(end),
    start < end, start >= 0, end <= 100
  )
  nr <- nrow(x)
  rows <- 1:nr
  srt <- ceiling(start*nr/100)
  end <-floor(end*nr/100)
  x[srt:end,]
}

My intent was to use dplyr to apply this fu> nction to each group:

newdat <- dat %>% 
  group_by(Sub) %>% 
  summarise(
p10 = mean(subset_percent(dat,0,10)$value),
p20 = mean(subset_percent(dat,10,20)$value),
p30 = mean(subset_percent(dat,20,30)$value),
p40 = mean(subset_percent(dat,30,40)$value),
p50 = mean(subset_percent(dat,40,50)$value),
p60 = mean(subset_percent(dat,50,60)$value),
p70 = mean(subset_percent(dat,60,70)$value),
p80 = mean(subset_percent(dat,70,80)$value),
p90 = mean(subset_percent(dat,80,90)$value),
p100 = mean(subset_percent(dat,90,100)$value))

However the output was not correct, because I got this erroneous result:

Sub     p10   p20   p30   p40   p50   p60   p70   p80   p90  p100
  <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 sub1    3.8   3.9  4.07   4.4  4.36   4.7  4.83  5.98  5.78  5.39
2 sub2    3.8   3.9  4.07   4.4  4.36   4.7  4.83  5.98  5.78  5.39
3 sub3    3.8   3.9  4.07   4.4  4.36   4.7  4.83  5.98  5.78  5.39

Do you have any advice?

Thank you

[pieterjanvc]

Hi,

Welcome to the RStudio community!

Thanks for creating a nice reproducible example, that's making this much easier. If I understand you correctly, you can do what you want to achieve in just a few lines:

library(dplyr)
library(tidyr)

value <- c(3,4,5,4,3,4,5,4,3,4,3,4,6,5,4,5,6,7,5,4,6,4,6,3,2,
          6,7,4,7,8,5,6,7,5,5,7,8,7,45,3,4,3,4,6,4,3,2,1,2,3,4)
Sub <- c(rep("sub1", 10), rep("sub2",25), rep("sub3", 16))

dat <- data.frame(sub = Sub, value = value)

#Set number of groups
nGroups = 10

#Add groups column
dat = dat %>% group_by(sub) %>% 
 mutate(group = 0:(n()-1) %% nGroups + 1)
dat
#> # A tibble: 51 x 3
#> # Groups:   sub [3]
#>    sub   value group
#>    <chr> <dbl> <dbl>
#>  1 sub1      3     1
#>  2 sub1      4     2
#>  3 sub1      5     3
#>  4 sub1      4     4
#>  5 sub1      3     5
#>  6 sub1      4     6
#>  7 sub1      5     7
#>  8 sub1      4     8
#>  9 sub1      3     9
#> 10 sub1      4    10
#> # ... with 41 more rows

#Summarise the groups per subject
dat = dat %>% 
 group_by(sub, group) %>% 
 #Replace 'mean' by any summary function you like
 summarise(value = mean(value), .groups = "drop")
dat
#> # A tibble: 30 x 3
#>    sub   group value
#>    <chr> <dbl> <dbl>
#>  1 sub1      1     3
#>  2 sub1      2     4
#>  3 sub1      3     5
#>  4 sub1      4     4
#>  5 sub1      5     3
#>  6 sub1      6     4
#>  7 sub1      7     5
#>  8 sub1      8     4
#>  9 sub1      9     3
#> 10 sub1     10     4
#> # ... with 20 more rows

#Convert to wide format
dat = dat %>% 
 pivot_wider(sub, names_from = group, values_from = value)
dat
#> # A tibble: 3 x 11
#>   sub     `1`   `2`   `3`   `4`   `5`   `6`   `7`   `8`   `9`  `10`
#>   <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 sub1   3     4     5     4     3      4     5     4       3     4
#> 2 sub2   4.67  4.67  6.33  4.33  3.67   5.5   6.5   5.5     6     6
#> 3 sub3   5     5     4    23.5   3      4     3     4       6     4

^{Created on 2021-12-12 by the reprex package (v2.0.1)}

Here is all the functions combined into one:

dat = dat %>% group_by(sub) %>% 
  mutate(group = 0:(n()-1) %% 10 + 1) %>% 
  group_by(sub, group) %>% 
  summarise(value = mean(value), .groups = "drop") %>% 
  pivot_wider(sub, names_from = group, values_from = value)

We are now creating 10 groups by consecutively taking samples from each subject. Should you rather create random groups (still respecting the subjects of course), you can add one more line

dat = dat %>% group_by(sub) %>% 
  mutate(
    group = 0:(n()-1) %% 10 + 1,
    #Shuffle the groups within subject to make them random
    group = sample(group, n())) %>% 
  group_by(sub, group) %>% 
  summarise(value = mean(value), .groups = "drop") %>% 
  pivot_wider(sub, names_from = group, values_from = value)

The results will now differ depending on the randomness of the groups

Hope this helps,
PJ

[piravi80]

Dear pieterjanvc,
thank you for your support. Your script works perfectly and it is well written!
I learnt something new.

Bets regards
Francesco