CODEWITHSUNDEEP
rdataframetidyversetibble
ACH
ACH

Reputation: 369

How to count the values in a tibble or data frame to produce a new output table?

I have the following code:

gm <- c("Alex", "Ashton", "Brett", "Chris", "Dave", "Ian", "Jack", "Jon", "Katz", "Mikulich", "Nate", "Todd")

year <- (2012:2019)

# podium placement, by year
first <- c("Mikulich", "Nate", "Nate", "Chris", "Nate", "Nate", "Dave", "Dave")
second <- c("Jon", "Alex", "Ian", "Todd", "Jon", "Chris", "Jon", "Jon")
third <- c("Katz", "Katz", "Jack", "Dave", "Katz", "Katz", "Chris", "Todd")

podium <- tibble(year, first, second, third)

producing the following tibble, podium:

year    first       second    third
<int>   <chr>       <chr>     <chr>

2012    Mikulich    Jon       Katz  
2013    Nate        Alex      Katz  
2014    Nate        Ian       Jack  
2015    Chris       Ross      Dave  
2016    Nate        Jon       Katz  
2017    Nate        Chris     Katz  
2018    Dave        Jon       Chris 
2019    Dave        Jon       Ross

I would like to have a tibble or data frame (12x4) that lists each GM and counts the number of top 3 finishes into an output that looks something like this:

GM         first    second    third
<chr>      <int>    <int>     <int>

Alex       0        1         0
Ashton     0        0         0
Brett      0        0         0
Chris      1        1         1
Dave       2        0         1
Ian        0        1         0
Jack       0        0         1
Jon        0        4         0
Katz       0        0         4
Mikulich   1        0         0
Nate       4        0         0
Todd       0        1         1

I can use podium %>% count(first) to get an individual distribution, but I'm wondering if there a more efficient way to create the entire table.

podium %>% count(first):

first      n
<chr>      <int>

Chris      1
Dave       2
Mikulich   1
Nate       4

Upvotes: 1

Views: 167

Answers (1)

akrun
akrun

Reputation: 887048

We could use pivot_longer to reshape to 'long' format, then do the count and reshape it back to 'wide'

library(dplyr)
library(tidyr)
podium %>%
    pivot_longer(cols = -year, values_to = 'GM') %>%
    count(name, GM) %>% 
    pivot_wider(names_from = name, values_from = n, values_fill = list(n = 0))
# A tibble: 10 x 4
#   GM       first second third
#   <chr>    <int>  <int> <int>
# 1 Chris        1      1     1
# 2 Dave         2      0     1
# 3 Mikulich     1      0     0
# 4 Nate         4      0     0
# 5 Alex         0      1     0
# 6 Ian          0      1     0
# 7 Jon          0      4     0
# 8 Todd         0      1     1
# 9 Jack         0      0     1
#10 Katz         0      0     4

Upvotes: 1

Related Questions