Update Cell Based on Corresponding Matrix Row/Col Names-CodePudding

I am trying to figure out a way to update a matrix based on what the row and column names of that matrix are. For example say if, while reading in data, the 4th row name and 3rd column name are on the same line of data, to add a value into the cell at [4,3]. I have tried to do this using the code below:

dfList <- c("P1", "P2", "P3", "P4", "P5")

emptyMatrix <- data.frame()
myMatrix <- data.frame(matrix(ncol = length(dfList), nrow = length(dfList)))
colnames(myMatrix) <- dfList
rownames(myMatrix) <- dfList
myMatrix[is.na(myMatrix)] <- 0
myMatrix
  
dtMatrix <- structure(list(category = c("Opponent", "Opponent", "Opponent", 
                                        "Opponent", "P1", "P2", "P3", "P4", "P2", "Opponent", "Opponent", 
                                        "P1"), Event = c("Good Pass", "Good Pass", "Good Pass", "Turnover", 
                                                         "Good Pass", "Good Pass", "Good Pass", "Good Pass", "Good Pass", 
                                                         "Intercepted Pass", "Bad Pass", "Good Pass"), Receiver = c(NA, 
                                                                                                                    NA, NA, NA, "P2", "P3", "P4", "P5", "P1", NA, NA, "P2")), row.names = c(NA, 
                                                                                                                                                                                            -12L), class = c("tbl_df", "tbl", "data.frame"))
  
myMatrix[rownames(myMatrix), colnames(myMatrix)] <- 0
for (i in 1:nrow(dtMatrix)) {
  if (dtMatrix$category[i] %in% dfList && dtMatrix$Event == "Good Pass" && dtMatrix$Receiver[i] %in% dfList) {
    for (j in 1:nrow(myMatrix)) {
      # print(paste("passer = ", dtMatrix$category[i]))
      # print(paste("rownames = ", rownames(myMatrix)))
      # print(paste("receiver = ", dtMatrix$Receiver[i]))
      # print(paste("colnames = ", colnames(myMatrix)))
        print(paste(rownames(myMatrix[j,])))
      # if (rownames(myMatrix[j,]) %in% dtMatrix$category[i] &
      #     colnames(myMatrix[j]) %in% dtMatrix$Receiver[i]) {
      if (dtMatrix$category[i] == rownames(myMatrix[j,]) & 
          dtMatrix$Receiver[i] == colnames(myMatrix[j,])) {
          
        print(paste("myMatrixUpdate is", myMatrix[rownames(myMatrix[j,]) ,colnames(myMatrix[j])]))
        myMatrix[rownames(myMatrix), colnames(myMatrix)] <- 1   myMatrix[rownames(myMatrix), colnames(myMatrix)]
      }
    }
  }
}
myMatrix
dtMatrix

expectedOutput <- structure(list(P1 = c(0, 1, 0, 0, 0), P2 = c(2, 0, 0, 0, 0), 
                                 P3 = c(0, 1, 0, 0, 0), P4 = c(0, 0, 1, 0, 0), P5 = c(0, 0, 
                                                                                      0, 1, 0)), row.names = c("P1", "P2", "P3", "P4", "P5"), class = "data.frame")

Using the data in dtMatrix, I am trying to take each time a player (P1-P5, in this case) in the category column makes a good pass to another player (1-5) in the receiver column, and add and update that into the variable called myMatrix. I have tried to do this using that code, but this only creates a matrix in myMatrix filled with all 1's. I have also tried using which(myMatrix == "P1", arr.ind = T) to get the specific row/col of each player, but this returns blank. I thought about trying to build a matrix as the data is being read in from dtMatrix, but wasn't sure how to go about that, so I built it pre-reading in instead.

The expected output matrix is at the bottom of the code in a data frame called expectedOutput. Is there an easier way to go about this by grouping the names or some sort like that? Thanks.

CodePudding user response：

Keep the rows with 'Good Pass' and drop rows with NA value in Receiver. Count the number of rows for each category and Receiver values, if certain combinations are missing from dfList in the output we complete them and get the data in wide format.

library(tidyverse)

dtMatrix %>%
  filter(Event == 'Good Pass' & !is.na(Receiver)) %>%
  count(category, Receiver) %>%
  complete(category = dfList, Receiver = dfList, fill = list(n = 0)) %>%
  pivot_wider(names_from = Receiver, values_from = n) %>%
  column_to_rownames('category')

#   P1 P2 P3 P4 P5
#P1  0  2  0  0  0
#P2  1  0  1  0  0
#P3  0  0  0  1  0
#P4  0  0  0  0  1
#P5  0  0  0  0  0