Calculate average for a sequence of strings, then remove anything greater than 2SD of the average in R

user_5942100 0
Viewed 62

I have a large dataset of over 10,000 rows: df:

  User              duration

  amy                582         
  amy                27
  amy                592
  amy                16
  amy                250
  tom                33
  tom                10
  tom                40
  tom                100

Desired output:

User               duration

amy                 582
amy                 592
amy                 250
tom                 33
tom                 10
tom                 40

Essentially, this will be removing any outliers that are 2SD from each unique User mean. The code would take the mean of each unique user, determine its mean and standard deviation and then remove values that are > 2SD of the mean.

dput:

structure(list(User = structure(c(1L, 1L, 1L, 1L, 1L, 2L, 2L, 
2L, 2L), .Label = c("amy", "tom"), class = "factor"), duration = c(582L, 
27L, 592L, 16L, 250L, 33L, 10L, 40L, 100L)), class = "data.frame", row.names = c(NA, 
-9L))

This is what I have tried:

first define average and standard deviation


      ave = ave(df$duration)
      sd =  sd(df$duration)

And then set some sort of parameter to this:

     for i in df {
     remove all if > 2*sd}

I am not sure and would like some suggestions.

stringr dplyr lubridate r
4 Answers

Here's a data.table approach, which may be faster for many rows.

library(data.table)
df <- structure(list(User = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 
2L, 2L, 2L), .Label = c("amy", "tom"), class = "factor"), duration = c(50000, 
582, 27, 592, 16, 250, 33, 10, 40, 100)), row.names = c(NA, -10L
), class = "data.frame")
df
   User duration
1   amy    50000
2   amy      582
3   amy       27
4   amy      592
5   amy       16
6   amy      250
7   tom       33
8   tom       10
9   tom       40
10  tom      100

Code

setDT(df)[,.SD[duration <= mean(duration) + (2 * sd(duration)) &
               duration >= mean(duration) - (2 * sd(duration)),]
          ,by=User]
   User duration
1:  amy      582
2:  amy       27
3:  amy      592
4:  amy       16
5:  amy      250
6:  tom       33
7:  tom       10
8:  tom       40
9:  tom      100
user_130953260

We can use dplyr, which would be much concise when used with between

library(dplyr)
df %>% 
   group_by(User) %>%
   filter(between(duration, mean(duration) -  sd(duration), 
                           mean(duration) +   sd(duration)))
user_37322710

You can use scale() to find the z score and keep absolute values less than 2:

library(dplyr)

df %>%
  group_by(User) %>%
  filter(abs(scale(duration)) < 2)

# A tibble: 9 x 2
# Groups:   User [2]
  User  duration
  <fct>    <int>
1 amy        582
2 amy         27
3 amy        592
4 amy         16
5 amy        250
6 tom         33
7 tom         10
8 tom         40
9 tom        100
user_28352610

We can try using the mutate and filter functions in dplyr

library(dplyr)
df %>% group_by(User) %>% mutate(ave_plus2sd=ave(duration)+2*sd(duration)) %>% 
filter(duration < ave_plus2sd)

This will give you the following output which allows comparison of each entry with average + 2*sd for the user.

# Groups:   User [2]
  User  duration ave_plus2sd
  <fct>    <int>       <dbl>
1 amy        582        861.
2 amy         27        861.
3 amy        592        861.
4 amy         16        861.
5 amy        250        861.
6 tom         33        122.
7 tom         10        122.
8 tom         40        122.
9 tom        100        122.

We can further add %>% select (User,duration) to select the columns of interest User and duration.

user_131015480
Related