Group Over Multiple Rows and Columns

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):

enter image description here

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!

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 :slight_smile:
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.

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

Thanks,
PJ

1 Like

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

1 Like

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!

1 Like

Thank you so much - I think this works!

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

If you have a query related to it or one of the replies, start a new topic and refer back with a link.