New to R. New to group. Using loop on a horse racing database.

Bookie · May 26, 2020, 10:34pm

HI All
I'm new to the group and to R language.
I've been studying basic R language using 'for dummies' books and I've recently started using RStudio.
I've written some code (below) that achieves the desired result.
However, i'm aware that i'm reproducing lines of the same code which would surely be more efficiently coded using a for loop.
Also, there will be races with large numbers of horses so I really need to be able to run a for loop that runs through each horse.
ie.

num_runners = NROW(my_new_data)

my_new_data holds data on horses previous races.
DaH is a numeric rating that is attached to each of a horse's previous runs with DaH1 being the most recent and DaH6 is six races back etc
Code, a character, signifies the type of race that the horse competed in. ie. Flat, Fences.
I have played with using for loops, ie. for(i in 1:6) without success.
Since I am assigning to a new horse each time I would hope something such as the following would work:

horse(i) = c(my_new_data$DaH1[i],my_new_data$DaH2[i],my_new_data$DaH3[i],my_new_data$DaH4[i],my_new_data$DaH5[i],my_new_data$DaH6[i])

But I know that horse(i) is not allowed.
Would my best strategy be to pre-define a dataframe of size: 6 rows and 6 columns
and use 2 for loops to populate [row][column]? Something like:

final_data[i,j]

Here is the code I am presently using which creates the dataframe racetest:

horse1 = c(my_new_data$DaH1[1],my_new_data$DaH2[1],my_new_data$DaH3[1],my_new_data$DaH4[1],my_new_data$DaH5[1],my_new_data$DaH6[1])
horse2 = c(my_new_data$DaH1[2],my_new_data$DaH2[2],my_new_data$DaH3[2],my_new_data$DaH4[2],my_new_data$DaH5[2],my_new_data$DaH6[2])
horse3 = c(my_new_data$DaH1[3],my_new_data$DaH2[3],my_new_data$DaH3[3],my_new_data$DaH4[3],my_new_data$DaH5[3],my_new_data$DaH6[3])
horse4 = c(my_new_data$DaH1[4],my_new_data$DaH2[4],my_new_data$DaH3[4],my_new_data$DaH4[4],my_new_data$DaH5[4],my_new_data$DaH6[4])
horse5 = c(my_new_data$DaH1[5],my_new_data$DaH2[5],my_new_data$DaH3[5],my_new_data$DaH4[5],my_new_data$DaH5[5],my_new_data$DaH6[5])
horse6 = c(my_new_data$DaH1[6],my_new_data$DaH2[6],my_new_data$DaH3[6],my_new_data$DaH4[6],my_new_data$DaH5[6],my_new_data$DaH6[6])
horse1.code = c(my_new_data$Code1[1],my_new_data$Code2[1],my_new_data$Code3[1],my_new_data$Code4[1],my_new_data$Code5[1],my_new_data$Code6[1])
horse2.code = c(my_new_data$Code1[2],my_new_data$Code2[2],my_new_data$Code3[2],my_new_data$Code4[2],my_new_data$Code5[2],my_new_data$Code6[2])
horse3.code = c(my_new_data$Code1[3],my_new_data$Code2[3],my_new_data$Code3[3],my_new_data$Code4[3],my_new_data$Code5[3],my_new_data$Code6[3])
horse4.code = c(my_new_data$Code1[4],my_new_data$Code2[4],my_new_data$Code3[4],my_new_data$Code4[4],my_new_data$Code5[4],my_new_data$Code6[4])
horse5.code = c(my_new_data$Code1[5],my_new_data$Code2[5],my_new_data$Code3[5],my_new_data$Code4[5],my_new_data$Code5[5],my_new_data$Code6[5])
horse6.code = c(my_new_data$Code1[6],my_new_data$Code2[6],my_new_data$Code3[6],my_new_data$Code4[6],my_new_data$Code5[6],my_new_data$Code6[6])
racetest = data.frame(horse1,horse1.code,horse2,horse2.code, horse3, horse3.code,
horse4,horse4.code,horse5,horse5.code, horse6, horse6.code)

Data below:

The data

> dput(head(my_new_data))
structure(list(Date = c(20200302L, 20200302L, 20200302L, 20200302L, 
20200302L, 20200302L), OffTime = c(15.4, 15.4, 15.4, 15.4, 15.4, 
15.4), Course = c("WETH", "WETH", "WETH", "WETH", "WETH", "WETH"
), Distance = c(19.38, 19.38, 19.38, 19.38, 19.38, 19.38), Going = c("H", 
"H", "H", "H", "H", "H"), Prize = c(5159L, 5159L, 5159L, 5159L, 
5159L, 5159L), RCode = c("C", "C", "C", "C", "C", "C"), Type = c("H", 
"H", "H", "H", "H", "H"), Class = c(4L, 4L, 4L, 4L, 4L, 4L), 
    SpecTyp = c(NA, NA, NA, NA, NA, NA), Ran = c(7L, 7L, 7L, 
    7L, 7L, 7L), CardNo = 1:6, Name = c("MORTENS LEAM", "FRANKIE BALLOU", 
    "MAXED OUT KING", "CASUAL CAVALIER", "WELL SMITTEN", "ENVOL DE LA COUR"
    ), Age = c(8L, 11L, 12L, 12L, 8L, 6L), Weight = c(166L, 140L, 
    153L, 161L, 163L, 154L), Gender = c(NA, NA, NA, NA, NA, NA
    ), Trainer = c("Mike Hawker             ", "Miss J E Foster         ", 
    "Mrs S J Smith           ", "Tristan Davidson        ", "Sam England             ", 
    "M Scudamore             "), Jockey = c("P K Donovan             ", 
    "Sean Quinlan            ", "Mr D R Cook             ", "Harry Reed              ", 
    "Jonathan England        ", "Brendan Powell          "), 
    Claim = c(0L, 0L, 0L, 0L, 0L, 0L), Draw = c(0L, 0L, 0L, 0L, 
    0L, 0L), FSP = c(7, 8, 5.5, 8, 6, 1.5), ISP = c(NA, NA, NA, 
    NA, NA, NA), Headgear = c(NA, NA, NA, NA, NA, NA), Code1 = c("C", 
    "C", "C", "C", "C", "H"), DaH1 = c(0L, 124L, 121L, 123L, 
    0L, 122L), Code2 = c("C", "C", "C", "C", "C", "C"), DaH2 = c(124L, 
    117L, 125L, 120L, 125L, 0L), Code3 = c("C", "C", "C", "C", 
    "H", "C"), DaH3 = c(121L, 119L, 123L, 119L, 82L, 0L), Code4 = c("C", 
    "C", "C", "C", "C", "H"), DaH4 = c(107L, 115L, 125L, 119L, 
    0L, 132L), Code5 = c("C", "C", "C", "C", "C", "H"), DaH5 = c(129L, 
    119L, 119L, 108L, 118L, 141L), Code6 = c("C", "C", "C", "C", 
    "C", "H"), DaH6 = c(127L, 0L, 0L, 124L, 122L, 137L), Code7 = c("C", 
    "C", "C", "C", "C", ""), DaH7 = c(128L, 0L, 128L, 122L, 0L, 
    0L), Code8 = c("C", "C", "C", "C", "C", ""), DaH8 = c(129L, 
    97L, 130L, 123L, 125L, 0L), Code9 = c("C", "C", "C", "C", 
    "C", ""), DaH9 = c(115L, 0L, 101L, 111L, 117L, 0L), Code10 = c("C", 
    "C", "C", "C", "C", ""), DaH10 = c(119L, 114L, 137L, 120L, 
    0L, 0L), Code11 = c("C", "C", "C", "C", "C", ""), DaH11 = c(0L, 
    0L, 134L, 113L, 131L, 0L), Code12 = c("H", "C", "C", "C", 
    "H", ""), DaH12 = c(98L, 0L, 129L, 92L, 0L, 0L), Code13 = c("H", 
    "C", "C", "C", "H", ""), DaH13 = c(0L, 135L, 123L, 122L, 
    124L, 0L), Code14 = c("H", "C", "C", "C", "H", ""), DaH14 = c(95L, 
    134L, 123L, 128L, 70L, 0L), Code15 = c("", "H", "H", "H", 
    "C", ""), DaH15 = c(0L, 0L, 0L, 118L, 106L, 0L), Code16 = c("", 
    "H", "H", "H", "C", ""), DaH16 = c(0L, 120L, 96L, 0L, 100L, 
    0L), Code17 = c("", "H", "H", "C", "C", ""), DaH17 = c(0L, 
    0L, 152L, 133L, 0L, 0L), Code18 = c("", "H", "H", "C", "C", 
    ""), DaH18 = c(0L, 103L, 114L, 126L, 101L, 0L), Code19 = c("", 
    "H", "", "C", "H", ""), DaH19 = c(0L, 0L, 0L, 123L, 81L, 
    0L), Code20 = c("", "H", "", "C", "C", ""), DaH20 = c(0L, 
    135L, 0L, 144L, 95L, 0L), Code21 = c("", "H", "", "H", "C", 
    ""), DaH21 = c(0L, 138L, 0L, 119L, 0L, 0L), Code22 = c("", 
    "H", "", "H", "C", ""), DaH22 = c(0L, 130L, 0L, 115L, 102L, 
    0L), Code23 = c("", "", "", "H", "H", ""), DaH23 = c(0L, 
    0L, 0L, 122L, 0L, 0L), Code24 = c("", "", "", "H", "H", ""
    ), DaH24 = c(0L, 0L, 0L, 127L, 91L, 0L), Code25 = c("", "", 
    "", "H", "H", ""), DaH25 = c(0L, 0L, 0L, 98L, 86L, 0L), Code26 = c("", 
    "", "", "H", "H", ""), DaH26 = c(0L, 0L, 0L, 86L, 73L, 0L
    ), Code27 = c("", "", "", "H", "H", ""), DaH27 = c(0L, 0L, 
    0L, 102L, 75L, 0L), Code28 = c("", "", "", "H", "H", ""), 
    DaH28 = c(0L, 0L, 0L, 88L, 89L, 0L), Code29 = c("", "", "", 
    "H", "H", ""), DaH29 = c(0L, 0L, 0L, 100L, 62L, 0L), Code30 = c("", 
    "", "", "H", "H", ""), DaH30 = c(0L, 0L, 0L, 94L, 0L, 0L), 
    Code31 = c("", "", "", "H", "H", ""), DaH31 = c(0L, 0L, 0L, 
    92L, 118L, 0L), Code32 = c("", "", "", "", "H", ""), DaH32 = c(0L, 
    0L, 0L, 0L, 102L, 0L), Code33 = c("", "", "", "", "H", ""
    ), DaH33 = c(0L, 0L, 0L, 0L, 102L, 0L), Code34 = c("", "", 
    "", "", "H", ""), DaH34 = c(0L, 0L, 0L, 0L, 116L, 0L), Code35 = c("", 
    "", "", "", "H", ""), DaH35 = c(0L, 0L, 0L, 0L, 101L, 0L), 
    Code36 = c("", "", "", "", "H", ""), DaH36 = c(0L, 0L, 0L, 
    0L, 90L, 0L), Code37 = c("", "", "", "", "H", ""), DaH37 = c(0L, 
    0L, 0L, 0L, 80L, 0L), Code38 = c(NA, NA, NA, NA, NA, NA), 
    DaH38 = c(0L, 0L, 0L, 0L, 0L, 0L), Code39 = c(NA, NA, NA, 
    NA, NA, NA), DaH39 = c(0L, 0L, 0L, 0L, 0L, 0L), Code40 = c(NA, 
    NA, NA, NA, NA, NA), DaH40 = c(0L, 0L, 0L, 0L, 0L, 0L), Code41 = c(NA, 
    NA, NA, NA, NA, NA), DaH41 = c(0L, 0L, 0L, 0L, 0L, 0L), Code42 = c(NA, 
    NA, NA, NA, NA, NA), DaH42 = c(0L, 0L, 0L, 0L, 0L, 0L), Code43 = c(NA, 
    NA, NA, NA, NA, NA), DaH43 = c(0L, 0L, 0L, 0L, 0L, 0L), Code44 = c(NA, 
    NA, NA, NA, NA, NA), DaH44 = c(0L, 0L, 0L, 0L, 0L, 0L), Code45 = c(NA, 
    NA, NA, NA, NA, NA), DaH45 = c(0L, 0L, 0L, 0L, 0L, 0L), Code46 = c(NA, 
    NA, NA, NA, NA, NA), DaH46 = c(0L, 0L, 0L, 0L, 0L, 0L), Code47 = c(NA, 
    NA, NA, NA, NA, NA), DaH47 = c(0L, 0L, 0L, 0L, 0L, 0L), Code48 = c(NA, 
    NA, NA, NA, NA, NA), DaH48 = c(0L, 0L, 0L, 0L, 0L, 0L), Code49 = c(NA, 
    NA, NA, NA, NA, NA), DaH49 = c(0L, 0L, 0L, 0L, 0L, 0L), Code50 = c(NA, 
    NA, NA, NA, NA, NA), DaH50 = c(0L, 0L, 0L, 0L, 0L, 0L), Code51 = c(NA, 
    NA, NA, NA, NA, NA), DaH51 = c(0L, 0L, 0L, 0L, 0L, 0L), Code52 = c(NA, 
    NA, NA, NA, NA, NA), DaH52 = c(0L, 0L, 0L, 0L, 0L, 0L), Code53 = c(NA, 
    NA, NA, NA, NA, NA), DaH53 = c(0L, 0L, 0L, 0L, 0L, 0L), Code54 = c(NA, 
    NA, NA, NA, NA, NA), DaH54 = c(0L, 0L, 0L, 0L, 0L, 0L), Code55 = c(NA, 
    NA, NA, NA, NA, NA), DaH55 = c(0L, 0L, 0L, 0L, 0L, 0L), Code56 = c(NA, 
    NA, NA, NA, NA, NA), DaH56 = c(0L, 0L, 0L, 0L, 0L, 0L), Code57 = c(NA, 
    NA, NA, NA, NA, NA), DaH57 = c(0L, 0L, 0L, 0L, 0L, 0L), Code58 = c(NA, 
    NA, NA, NA, NA, NA), DaH58 = c(0L, 0L, 0L, 0L, 0L, 0L), Code59 = c(NA, 
    NA, NA, NA, NA, NA), DaH59 = c(0L, 0L, 0L, 0L, 0L, 0L), Code60 = c(NA, 
    NA, NA, NA, NA, NA), DaH60 = c(0L, 0L, 0L, 0L, 0L, 0L), Code61 = c(NA, 
    NA, NA, NA, NA, NA), DaH61 = c(0L, 0L, 0L, 0L, 0L, 0L), Code62 = c(NA, 
    NA, NA, NA, NA, NA), DaH62 = c(0L, 0L, 0L, 0L, 0L, 0L), Code63 = c(NA, 
    NA, NA, NA, NA, NA), DaH63 = c(0L, 0L, 0L, 0L, 0L, 0L), Code64 = c(NA, 
    NA, NA, NA, NA, NA), DaH64 = c(0L, 0L, 0L, 0L, 0L, 0L), Code65 = c(NA, 
    NA, NA, NA, NA, NA), DaH65 = c(0L, 0L, 0L, 0L, 0L, 0L), Code66 = c(NA, 
    NA, NA, NA, NA, NA), DaH66 = c(0L, 0L, 0L, 0L, 0L, 0L), Code67 = c(NA, 
    NA, NA, NA, NA, NA), DaH67 = c(0L, 0L, 0L, 0L, 0L, 0L), Code68 = c(NA, 
    NA, NA, NA, NA, NA), DaH68 = c(0L, 0L, 0L, 0L, 0L, 0L), Code69 = c(NA, 
    NA, NA, NA, NA, NA), DaH69 = c(0L, 0L, 0L, 0L, 0L, 0L), Code70 = c(NA, 
    NA, NA, NA, NA, NA), DaH70 = c(0L, 0L, 0L, 0L, 0L, 0L), Code71 = c(NA, 
    NA, NA, NA, NA, NA), DaH71 = c(0L, 0L, 0L, 0L, 0L, 0L), Code72 = c(NA, 
    NA, NA, NA, NA, NA), DaH72 = c(0L, 0L, 0L, 0L, 0L, 0L), Code73 = c(NA, 
    NA, NA, NA, NA, NA), DaH73 = c(0L, 0L, 0L, 0L, 0L, 0L), Code74 = c(NA, 
    NA, NA, NA, NA, NA), DaH74 = c(0L, 0L, 0L, 0L, 0L, 0L), Code75 = c(NA, 
    NA, NA, NA, NA, NA), DaH75 = c(0L, 0L, 0L, 0L, 0L, 0L), Code76 = c(NA, 
    NA, NA, NA, NA, NA), DaH76 = c(0L, 0L, 0L, 0L, 0L, 0L), Code77 = c(NA, 
    NA, NA, NA, NA, NA), DaH77 = c(0L, 0L, 0L, 0L, 0L, 0L), Code78 = c(NA, 
    NA, NA, NA, NA, NA), DaH78 = c(0L, 0L, 0L, 0L, 0L, 0L), Code79 = c(NA, 
    NA, NA, NA, NA, NA), DaH79 = c(0L, 0L, 0L, 0L, 0L, 0L), Code80 = c(NA, 
    NA, NA, NA, NA, NA), DaH80 = c(0L, 0L, 0L, 0L, 0L, 0L), X = c(NA, 
    NA, NA, NA, NA, NA)), row.names = c(NA, 6L), class = "data.frame")
>

Thanks in advance for any help that can be offered!
Graham

EconomiCurtis · May 27, 2020, 1:38am

It's not clear to me what you're trying to do?

Also, I see this is a continuation of this thread: New to R. New to group. Using for loop on a horse racing database.

FJCC · May 27, 2020, 2:24am

Is this the sort of thing you want to do. I truncated the data set but the code does work on the full set.

my_new_data <- structure(list(Date = c(20200302L, 20200302L, 20200302L, 20200302L,  20200302L, 20200302L), 
                              OffTime = c(15.4, 15.4, 15.4, 15.4, 15.4, 15.4), 
                              Course = c("WETH", "WETH", "WETH", "WETH", "WETH", "WETH"), 
                              Distance = c(19.38, 19.38, 19.38, 19.38, 19.38, 19.38), 
                              Going = c("H","H", "H", "H", "H", "H"), 
                              Prize = c(5159L, 5159L, 5159L, 5159L, 5159L, 5159L), 
                              RCode = c("C", "C", "C", "C", "C", "C"), 
                              Type = c("H", "H", "H", "H", "H", "H"), 
                              Class = c(4L, 4L, 4L, 4L, 4L, 4L), 
                              SpecTyp = c(NA, NA, NA, NA, NA, NA), 
                              Ran = c(7L, 7L, 7L, 7L, 7L, 7L), 
                              CardNo = 1:6, Name = c("MORTENS LEAM", "FRANKIE BALLOU", "MAXED OUT KING", "CASUAL CAVALIER", "WELL SMITTEN", "ENVOL DE LA COUR"), 
                              Age = c(8L, 11L, 12L, 12L, 8L, 6L), 
                              Weight = c(166L, 140L,153L, 161L, 163L, 154L), 
                              Gender = c(NA, NA, NA, NA, NA, NA), 
                              Trainer = c("Mike Hawker             ", "Miss J E Foster         ", "Mrs S J Smith           ", "Tristan Davidson        ", 
                                          "Sam England             ", "M Scudamore             "), 
                              Jockey = c("P K Donovan             ", "Sean Quinlan            ", "Mr D R Cook             ", "Harry Reed              ", 
                                         "Jonathan England        ", "Brendan Powell          "), 
                              Claim = c(0L, 0L, 0L, 0L, 0L, 0L), 
                              Draw = c(0L, 0L, 0L, 0L, 0L, 0L), 
                              FSP = c(7, 8, 5.5, 8, 6, 1.5), 
                              ISP = c(NA, NA, NA, NA, NA, NA), 
                              Headgear = c(NA, NA, NA, NA, NA, NA), Code1 = c("C", "C", "C", "C", "C", "H"), 
                              DaH1 = c(0L, 124L, 121L, 123L, 0L, 122L), 
                              Code2 = c("C", "C", "C", "C", "C", "C"), 
                              DaH2 = c(124L, 117L, 125L, 120L, 125L, 0L), 
                              Code3 = c("C", "C", "C", "C", "H", "C"), 
                              DaH3 = c(121L, 119L, 123L, 119L, 82L, 0L), 
                              Code4 = c("C","C", "C", "C", "C", "H"), 
                              DaH4 = c(107L, 115L, 125L, 119L, 0L, 132L), 
                              Code5 = c("C", "C", "C", "C", "C", "H"), 
                              DaH5 = c(129L, 119L, 119L, 108L, 118L, 141L), 
                              Code6 = c("C", "C", "C", "C", "C", "H"), 
                              DaH6 = c(127L, 0L, 0L, 124L, 122L, 137L)), row.names = c(NA, 6L), class = "data.frame")
library(dplyr)

NewDaH <- select(my_new_data, DaH1, DaH2, DaH3, DaH4, DaH5, DaH6)
NewDaH
#>   DaH1 DaH2 DaH3 DaH4 DaH5 DaH6
#> 1    0  124  121  107  129  127
#> 2  124  117  119  115  119    0
#> 3  121  125  123  125  119    0
#> 4  123  120  119  119  108  124
#> 5    0  125   82    0  118  122
#> 6  122    0    0  132  141  137
NewDaH <- t(NewDaH)
dimnames(NewDaH) <- list(NULL, paste0("Horse", 1:6))
NewDaH
#>      Horse1 Horse2 Horse3 Horse4 Horse5 Horse6
#> [1,]      0    124    121    123      0    122
#> [2,]    124    117    125    120    125      0
#> [3,]    121    119    123    119     82      0
#> [4,]    107    115    125    119      0    132
#> [5,]    129    119    119    108    118    141
#> [6,]    127      0      0    124    122    137
NewCode <- select(my_new_data, Code1, Code2, Code3, Code4, Code5, Code6)
NewCode
#>   Code1 Code2 Code3 Code4 Code5 Code6
#> 1     C     C     C     C     C     C
#> 2     C     C     C     C     C     C
#> 3     C     C     C     C     C     C
#> 4     C     C     C     C     C     C
#> 5     C     C     H     C     C     C
#> 6     H     C     C     H     H     H
NewCode <- t(NewCode)
dimnames(NewCode) <- list(NULL, paste0("HorseCode", 1:6))
NewCode
#>      HorseCode1 HorseCode2 HorseCode3 HorseCode4 HorseCode5 HorseCode6
#> [1,] "C"        "C"        "C"        "C"        "C"        "H"       
#> [2,] "C"        "C"        "C"        "C"        "C"        "C"       
#> [3,] "C"        "C"        "C"        "C"        "H"        "C"       
#> [4,] "C"        "C"        "C"        "C"        "C"        "H"       
#> [5,] "C"        "C"        "C"        "C"        "C"        "H"       
#> [6,] "C"        "C"        "C"        "C"        "C"        "H"
racetest <- as.data.frame(cbind(NewDaH, NewCode)) %>% select(Horse1, HorseCode1, Horse2, HorseCode2, Horse3, HorseCode3,
                                  Horse4, HorseCode4, Horse5, HorseCode5, Horse6, HorseCode6,)
racetest
#>   Horse1 HorseCode1 Horse2 HorseCode2 Horse3 HorseCode3 Horse4 HorseCode4
#> 1      0          C    124          C    121          C    123          C
#> 2    124          C    117          C    125          C    120          C
#> 3    121          C    119          C    123          C    119          C
#> 4    107          C    115          C    125          C    119          C
#> 5    129          C    119          C    119          C    108          C
#> 6    127          C      0          C      0          C    124          C
#>   Horse5 HorseCode5 Horse6 HorseCode6
#> 1      0          C    122          H
#> 2    125          C      0          C
#> 3     82          H      0          C
#> 4      0          C    132          H
#> 5    118          C    141          H
#> 6    122          C    137          H

^{Created on 2020-05-26 by the reprex package (v0.2.1)}

Bookie · May 27, 2020, 10:51am

Thanks for your help.
I appreciate that I have not made myself very clear. Apologies.
I have tried to simplify the data and my request.
Will post a new Question to the group.

Bookie · May 27, 2020, 10:56am

The following is the new question that I would like to ask:
Hi.
I am new to the group and R.
I posted a question earlier, and am very grateful for the help provided, but realised that I was not very clear.
So here is hopefully a better attempt to clarify my problem.
Thanks in advance to anyone that can assist.

I have a dataset which contains racehorse names along with each racehorses
performance rating, 'DaH', (an integer) and the type of race the horses ran in 'Code',(character).

The dataset has 7 horses with performance ratings (DaH) for the last 3 races, DaH1 to DaH3,
where DaH1 is the performance rating for the last/most recent race and DaH3 was the horses
performance rating from 3 races ago.
Likewise the Codes, Code1 to Code3, provide the Code that the horse ran in.

So looking at the dataset below you can see that horse2 ran in a code C race in its last race
and achieved a performance rating DaH1 of 124.

I import the horserace data from a provider of horse racing data.

> racehorse_data
  Code1 DaH1 Code2 DaH2 Code3 DaH3
1     C    0     C  124     C  127
2     C  124     C  117     C    0
3     C  121     C  125     C    0
4     C  123     C  120     C  124
5     C    0     C  125     H  122
6     H  122     C    0     C  137
7     C  110     H  115     C  127

structure(list(Code1 = c("C", "C", "C", "C", "C", "H", "C"), 
    DaH1 = c(0L, 124L, 121L, 123L, 0L, 122L, 110L), Code2 = c("C", 
    "C", "C", "C", "C", "C", "H"), DaH2 = c(124L, 117L, 125L, 
    120L, 125L, 0L, 115L), Code3 = c("C", "C", "C", "C", "H", 
    "C", "C"), DaH6 = c(127L, 0L, 0L, 124L, 122L, 137L, 127L)), class = "data.frame", row.names = c(NA, 
-7L))

I need to calculate the mean DaH rating for each horse once I have stripped out any performance rating
that was achieved in a code that was not a 'C' code.
To clarify:
The problem i'm having is that I need to remove any performance ratings, DaH, where the
Code of the race was not a 'C' code race.

So for example you can see that horse 6 ran in a 'H' code race in its last race.
This needs to be removed from the dataset so that just the two races where horse 6 achieved
DaH2=0 and DaH3=137 remain to give a mean DaH rating of 68.5 for its two code C races.

I have been playing with the MELT command trying to achieve a reconfigured dataframe
that will enable me to filter out the DaH ratings where the code of the race was not equal
to 'C'. But i'm not managed to achieve this.

It occurred to me that I could isolate each horses DaH and Code as a separate data frame and
work through each horse separately but this seems more complicated than i'd like.

My ideal output would be a data frame such as:

> racehorse_data
   DaH1   DaH2  DaH3	Mean
1     0    124   127	83.7
2   124    117     0	80.3
3   121    125     0	82
4   123    120   124	122.3
5     0    125    na	62.5
6    na      0   137	68.5
7   110     na   127 	118.5

Or perhaps a separate vector for each horse

horse 1		0	124	127	83.7
horse 2		124	117	0	80.3
etc

Many Thanks
Graham

andresrcs · May 28, 2020, 12:50am

You can do something like this

library(tidyverse)

racehorse_data <- data.frame(
  stringsAsFactors = FALSE,
             Code1 = c("C", "C", "C", "C", "C", "H", "C"),
              DaH1 = c(0L, 124L, 121L, 123L, 0L, 122L, 110L),
             Code2 = c("C", "C", "C", "C", "C", "C", "H"),
              DaH2 = c(124L, 117L, 125L, 120L, 125L, 0L, 115L),
             Code3 = c("C", "C", "C", "C", "H", "C", "C"),
              DaH3 = c(127L, 0L, 0L, 124L, 122L, 137L, 127L)
)

racehorse_data %>%
    rownames_to_column(var = "Horse") %>%
    pivot_longer(-Horse,
                 names_to = c(".value", "set"),
                 names_pattern = "([a-zA-Z]+)(\\d)"
    ) %>% 
    filter(Code == "C") %>% 
    group_by(Horse) %>% 
    summarise(mean_DaH = mean(DaH))
#> # A tibble: 7 x 2
#>   Horse mean_DaH
#>   <chr>    <dbl>
#> 1 1         83.7
#> 2 2         80.3
#> 3 3         82  
#> 4 4        122. 
#> 5 5         62.5
#> 6 6         68.5
#> 7 7        118.

^{Created on 2020-05-28 by the reprex package (v0.3.0)}

Bookie · May 31, 2020, 7:43am

Thanks. Really appreciated the help!

system · June 21, 2020, 7:43am

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.