Group Over Multiple Rows and Columns

[omario]

I am working with the R programming language. I have the following data set:

set.seed(123)

Game = c(1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10,11,11,12,12,13,13,14,14,15,15,16,16,17,17,18,18,19,19,20,20)

id = c(3,4,3,4,3,4,3,4,3,4,3,4, 3,4,3,4,3,4,3,4)

c <- c("1", "2")    
coin <- sample(c, 20, replace=TRUE, prob=c(0.5,0.5))

  
winner <- c("win", "win", "lose", "lose", "tie", "tie", "lose", "lose", "win", "win", "win", "win", "lose", "lose", "tie", "tie", "lose", "lose", "win", "win", "win", "win", "lose", "lose", "tie", "tie", "lose", "lose", "win", "win", "win", "win", "lose", "lose", "tie", "tie", "lose", "lose", "win", "win")

my_data = data.frame(Game, id, coin, winner)

The data ("my_data") looks something like this:

  Game id coin winner
1    1  3    2    win
2    1  4    1    win
3    2  3    2   lose
4    2  4    1   lose
5    3  3    1    tie
6    3  4    2    tie

For this dataset ("my_data"), I want to perform the following operations:

• For each unique value of the "Game" variable (e.g. Game = 1, Game = 2, etc.), find out the frequency for each unique "Coin" combinations. For example, perhaps Coin = (2,1) happens 5 times , Coin = (2,2) happens 11 times, etc.
• Next, for each of these unique "Coin" combinations - find the breakdowns of "win", "lose" and "tie". For example, for Coin = (2,2) there might be 5/11 wins, 3/11 loss and 3/11 tie.

I tried to accomplish this with the following code :

Part 1: (Manually) Find out unique coin combinations per Game (e.g, 1,1 OR 1,2 OR 2,1 OR 2,2)

for (i in 1:19) {
for (j in 2:20) {

my_data$comb = ifelse(my_data[i,3] == "1" & my_data[j,3] == "1", "one,one", ifelse(my_data[i,3] == "2" & my_data[j,3] == "1", "two, one", ifelse(my_data[i,3] == "1" & my_data[j,3] == "2", "one,two", "two,two)))

}
}

Part 2: (Had this worked) Find out Win/Tie/Loss Breakdown for each unique combination from Part 1:

library(dplyr)

my_data %>% group_by(comb) %>% summarise(percent = n() )

The desired output should look something like this ( note : 1,2 = 2,1):

Currently, I am importing "my_data" into Microsoft Excel - but can someone please show me how to do this in R?

Can someone please show me how to obtain the above table?

Thanks!

[pieterjanvc]

Hi there,

Just a question first: Is this part of a course assignment? The type of question looks really like something you'd use in class
The reason I'm asking is that we're still happy to help you out, but in case of a homework assignment we do not provide solutions but only pointer as per our policy.

FAQ: Homework Policy

Can I ask questions from a course I am taking here? General questions are always welcome! Please do ask general questions about things like: How to use R How to use the RStudio IDE or RStudio Cloud How to work with tidyverse packages Where to find resources to help you learn or solve problems Specific questions can be OK, if you follow these rules: Never copy-paste instructions from an assignment (even for online courses). Explicitly mention the course you are taking and use the #homework tag. Ask your question as a reproducible example (reprex), preferably prepared for posting using the reprex package. Want to maximize your chances of getting the help you need? Keep readin…

Please add the homework tag to this topic should this be the case and we'll take it from there.

Thanks,
PJ

[andresrcs]

I don't think I clearly understand what you are trying to do but this could work as a starting point (and a proper reprex)

library(tidyverse)

my_data = data.frame(
  stringsAsFactors = FALSE,
              Game = c(1,1,2,2,3,3,4,4,5,5,6,
                       6,7,7,8,8,9,9,10,10,11,11,12,12,13,13,14,
                       14,15,15,16,16,17,17,18,18,19,19,20,20),
                id = c(3,4,3,4,3,4,3,4,3,4,3,
                       4,3,4,3,4,3,4,3,4,3,4,3,4,3,4,3,4,3,4,
                       3,4,3,4,3,4,3,4,3,4),
              coin = c("2","1","2","1","1","2",
                       "1","1","1","2","1","2","1","1","2","1","2","2",
                       "2","1","2","1","2","1","1","2","1","1","1",
                       "2","1","2","1","1","2","1","2","2","2","1"),
            winner = c("win","win","lose","lose",
                       "tie","tie","lose","lose","win","win","win","win",
                       "lose","lose","tie","tie","lose","lose","win",
                       "win","win","win","lose","lose","tie","tie","lose",
                       "lose","win","win","win","win","lose","lose","tie",
                       "tie","lose","lose","win","win")
)

my_data_comb <- my_data %>% 
    group_by(Game) %>% 
    mutate(coin_comb = paste(coin, collapse = ",")) %>% 
    ungroup()

# Part 1
my_data_comb %>% 
    count(Game, coin_comb, name = "count")
#> # A tibble: 20 × 3
#>     Game coin_comb count
#>    <dbl> <chr>     <int>
#>  1     1 2,1           2
#>  2     2 2,1           2
#>  3     3 1,2           2
#>  4     4 1,1           2
#>  5     5 1,2           2
#>  6     6 1,2           2
#>  7     7 1,1           2
#>  8     8 2,1           2
#>  9     9 2,2           2
#> 10    10 2,1           2
#> 11    11 2,1           2
#> 12    12 2,1           2
#> 13    13 1,2           2
#> 14    14 1,1           2
#> 15    15 1,2           2
#> 16    16 1,2           2
#> 17    17 1,1           2
#> 18    18 2,1           2
#> 19    19 2,2           2
#> 20    20 2,1           2

# Part 2
my_data_comb %>%
    count(coin_comb, winner, name = "percent") %>% 
    complete(coin_comb,nesting(winner), fill = list(percent = 0)) %>% 
    group_by(coin_comb) %>% 
    mutate(percent = scales::percent(percent/sum(percent)))
#> # A tibble: 12 × 3
#> # Groups:   coin_comb [4]
#>    coin_comb winner percent
#>    <chr>     <chr>  <chr>  
#>  1 1,1       lose   100%   
#>  2 1,1       tie    0%     
#>  3 1,1       win    0%     
#>  4 1,2       lose   0%     
#>  5 1,2       tie    33%    
#>  6 1,2       win    67%    
#>  7 2,1       lose   25%    
#>  8 2,1       tie    25%    
#>  9 2,1       win    50%    
#> 10 2,2       lose   100%   
#> 11 2,2       tie    0%     
#> 12 2,2       win    0%

^{Created on 2022-03-06 by the reprex package (v2.0.1)}

Note I don't think this is homework because of the previous related topics by the same user

[omario]

Hello! I guess this can be interpreted as my "own" homework - I spend the day imagining these "games and situations" and then try to code them myself in R ... both out of interest and as an attempt to better learn programming and about the R language.

Thank you so much!

[omario]

Thank you so much - I think this works!