Using Filter function in R. Need to assign NA and keep length of dataset the same for Horse Racing Database

user_13606753 0
Viewed 76

I'm still new to the group and R. I had some really helpful feedback on my last query so hoping I can get some more support with the following:

I am working on a horse racing database that at this stage has 4 variables: race horse number, race id, distance of race and the rating (DaH) assigned for the horses performance for the race.

The dataset:

horse_ratings <- tibble(
  horse=c(1,1,1,2,2,2,3,3,3),
  raceid=c(1,2,3,1,2,3,1,2,3),
  Dist=c(9.47,9.47,10,10.1,10.2,9,11,9.47,10.5),
  DaH=c(101,99,103,101,94,87,102,96,62)
)

Giving:

> horse_ratings
# A tibble: 9 x 4
  horse raceid  Dist   DaH
  <dbl>  <dbl> <dbl> <dbl>
1     1      1  9.47   101
2     1      2  9.47    99
3     1      3 10      103
4     2      1 10.1    101
5     2      2 10.2     94
6     2      3  9       87
7     3      1 11      102
8     3      2  9.47    96
9     3      3 10.5     62

I will perform a number of calculations on the dataset such as mean rating, max rating etc which id like to result in a number of vectors of equal length.

I'm using the filter function to look at the performance ratings achieved for different race distances (ie. Distance greater than 10 to begin). However, if one of the horses has not run a race for that distance then i've noticed that the result does not include that horse in the output. ie:

> horse_ratings %>% 
+   group_by(horse) %>% 
+   filter(Dist>10) %>%
+   summarise(mean_rating=mean(DaH))
`summarise()` ungrouping output (override with `.groups` argument)
# A tibble: 2 x 2
  horse mean_rating
  <dbl>       <dbl>
1     2        97.5
2     3        82

So horse 1 has disappeared as it has not run a race of distance greater than 10. I need to keep the output vector of length 3 ideally so I can put all the calculations in to a dataframe of same length (for my final data output/print out). I'm hoping there's a way of assigning an NA or similar to an output for horse 1 Giving:

# A tibble: 2 x 2
  horse mean_rating
  <dbl>       <dbl>
1     1        NA
2     2        97.5
3     3        82

Or a similar solution. Help would be much appreciated!!

filter r
3 Answers

You can use the .drop = FALSE parameter in group_by():

horse_ratings %>%
 group_by(horse, .drop = FALSE) %>% 
 filter(Dist > 10) %>%
 summarise(mean_rating = mean(DaH))

  horse mean_rating
  <dbl>       <dbl>
1     1       NaN  
2     2        97.5
3     3        82
user_59645570

Don't filter first, do it in summarise so you don't drop groups (horse). 

library(dplyr)

horse_ratings %>% 
     group_by(horse) %>% 
     summarise(mean_rating = mean(DaH[Dist>10], na.rm = TRUE))

# A tibble: 3 x 2
#  horse mean_rating
#  <dbl>       <dbl>
#1     1       NaN  
#2     2        97.5
#3     3        82
user_39629140 
library(tidyverse)

Method 1: 

horse_stats <- 
  horse_ratings %>% 
  mutate(raceid = as.factor(raceid)) %>% 
  filter(Dist > 10) %>% 
  group_by(horse) %>% 
  summarise_if(is.numeric, c("sum", "mean", "max", "min")) %>% 
  ungroup() %>% 
  left_join(horse_ratings %>% 
              select(horse) %>% 
              distinct(), 
            ., by = "horse", all.x = TRUE)

Method 2 :

horse_stats <- 
  horse_ratings %>% 
  mutate(raceid = factor(raceid),
         Dist = ifelse(Dist <= 10, 0, Dist), 
         DaH = ifelse(Dist == 0, 0, Dist)) %>% 
  group_by(horse) %>% 
  summarise_if(is.numeric, c("sum", "mean", "max", "min")) %>% 
  ungroup() %>% 
  mutate_if(is.numeric, list(~na_if(., 0)))
user_92031580
Related