Using pivot_wider or other solution to output my dataset in R

Question

I'm still new to R but making progress with the help of this group. I'm struggling with what I consider to be a problem that can be solved with pivot_wider, though i've not succeeded yet. Thanks in advance for any assistance!

I have a dataset as below:

    RaceNum Horse             DrawA row_c
     <int> <chr>              <dbl> <int>
 1   21127 A'Ali            -0.242      1
 2   20260 A'Ali             0.128      2
 3   17889 A'Ali            NA          3
 4   21712 A'Shamardi       -0.519      1
 5   20340 A'Shamardi        0.767      2
 6    5285 A Bit Of A Touch NA          1
 7    4825 A Bit Of A Touch -0.0256     2
 8    4207 A Bit Of A Touch  0.139      3
 9    3397 A Bit Of Ginger   0.704      1
10    3206 A Bit Of Ginger   0.152      2
# ... with 64,290 more rows

My goal is to create an output that gives:

Horse           DrawA_1     DrawA_2     DrawA_3
A'Ali           -0.242      0.128       NA
A'Shamardi      -0.519      0.767       NA
A Bit of a Touch    NA      -0.0256     0.139
etc

Basically for every Horse, I need to output the DrawA value for the 3 instances. Perhaps pivot_wider is not the optimal way to do this? I was trying to output what I needed using group_by(Horse) and then using summarise to display the output in a dataset 4 columns wide. Hope someone can help!

Could you provide your sample data via `dput()`? – Humpelstielzchen Aug 19 '20 at 09:45 — Humpelstielzchen, Aug 19 '20 at 09:45

score 2 · Answer 1 · answered Aug 19 '20 at 09:51

You can use pivot_wider on your data quite easily if you use row_c as the names. Here's an example with some simple data.

library(tidyr)

df <- data.frame(Horse = c("A", "A", "A", "B", "B"),
                 DrawA = runif(5),
                 row_c = c(1:3, 1:2))

pivot_wider(df,
            names_from = row_c,
            names_prefix = "DrawA_",
            values_from = DrawA)
#> # A tibble: 2 x 4
#>   Horse DrawA_1 DrawA_2 DrawA_3
#>   <chr>   <dbl>   <dbl>   <dbl>
#> 1 A       0.979   0.153   0.427
#> 2 B       0.853   0.210  NA

score 0 · Answer 2 · answered Aug 19 '20 at 11:23

0

Base R solution:

df1 <- as.data.frame.matrix(xtabs(DrawA~Horse+row_c, df))
within(setNames(df1, paste0("DrawA_", names(df1))), {horse <- row.names(df1)})

answered Aug 19 '20 at 11:23

hello_friend

5,682
1
11
15

@Bookie no worries. Did it work as required ? – hello_friend Aug 23 '20 at 10:46
All cool. Thanks – Bookie Sep 04 '20 at 16:06

Using pivot_wider or other solution to output my dataset in R

2 Answers2