Combining Two Data Fames with Different Row/Col Names Together

Viewed 54

I have this data frame:

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

With this, I created a matrix

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

This goodMatrix stores the combinations of good passes between P1-P5. In the dtMatrix, it also has other values in the Event column such as turnover/intercepted pass, and also accounts for the opponent. I would like to create a similar matrix as goodMatrix but for the events and opponent previously mentioned.

countTypes <- dtMatrix %>% dplyr::count(category, Event) Grabs all the counts of the events based on the category column. With that, I then did:

secondMatrix <- data.frame(matrix(ncol = length(unique(countTypes$Event)), nrow = length(unique(countTypes$category))))
rownames(secondMatrix) <- unique(countTypes$category)
colnames(secondMatrix) <- unique(countTypes$Event)
secondMatrix

test <- merge(goodMatrix, secondMatrix, by = "row.names")

To try and combine the two separate matrices together.

anotherMatrix <- dtMatrix %>% 
  dplyr::count(category, Event) %>% 
  tidyr::complete(category = dfList, Event = dfList, fill = list(n = 0)) %>%
  pivot_wider(names_from = Event, values_from = n) %>%
  column_to_rownames('category')

This also adds them into one, but does not keep the values from dtMatrix and instead resets them to 0.

My expected result should look as such:

expectedOutput <- structure(list(P1 = c(0, 1, 0, 0, 0, 0), P2 = c(2, 0, 0, 0, 0, 
0), P3 = c(0, 1, 0, 0, 0, 0), P4 = c(0, 0, 1, 0, 0, 0), P5 = c(0, 
0, 0, 1, 0, 0), `Good Pass` = c(2, 2, 1, 1, 0, 3), `Bad Pass` = c(0, 
0, 0, 0, 0, 1), `Intercepted Pass` = c(0, 0, 0, 0, 0, 1), Turnover = c(0, 
0, 0, 0, 0, 1)), row.names = c("P1", "P2", "P3", "P4", "P5", 
"Opponent"), class = "data.frame")

And anotherMatrix does half of this, while dtMatrix does the other half, but I am struggling on merging them into what I would like my result to be.

Edit

newTest <- test[,-1]
rownames(newTest) <- test[,1]
newTry <- merge(anotherMatrix, newTest, by = "row.names")

Just as an extra attempted method - this also gets close to my expected output, but does not include the opponent row, and also doubles every column.

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

Edit 2

A quick follow up on combining 2 DF's with different row/col lengths, how would I go about combining passesComb + copyComb into gamesComb:

passesComb <- structure(list(P1_Good = c(0, 1, 0, 0, 0, 0, 1), P2_Good = c(2, 
0, 0, 0, 0, 0, 2), P3_Good = c(0, 1, 0, 0, 0, 0, 1), P4_Good = c(0, 
0, 1, 0, 0, 0, 1), P5_Good = c(0, 0, 0, 1, 0, 0, 1), P1_Bad = c(0, 
0, 0, 0, 0, 0, 0), P2_Bad = c(0, 0, 0, 0, 0, 0, 0), P3_Bad = c(0, 
0, 0, 0, 0, 0, 0), P4_Bad = c(0, 0, 1, 0, 0, 0, 1), P5_Bad = c(0, 
0, 0, 0, 0, 0, 0), `Bad Pass` = c(0, 0, 1, 0, 0, 1, 1), `Good Pass` = c(2, 
2, 1, 1, 0, 3, 6), `Intercepted Pass` = c(0, 0, 0, 0, 0, 1, 0
), Turnover = c(0, 0, 0, 0, 0, 1, 0), totalEvents = c(2, 2, 2, 
1, 0, 6, 7)), row.names = c("P1", "P2", "P3", "P4", "P5", "Opponent", 
"VT"), class = "data.frame")

and

copyComb <- structure(list(P1_Good = c(0, 1, 0, 0, 0, 1), P2_Good = c(2, 
0, 0, 0, 0, 2), P4_Good = c(0, 0, 0, 0, 0, 1), P5_Good = c(0, 
0, 1, 0, 0, 1), P1_Bad = c(0, 0, 0, 0, 0, 0), P2_Bad = c(0, 0, 
0, 0, 0, 0), P3_Bad = c(0, 0, 0, 0, 0, 0), P4_Bad = c(0, 0, 0, 
0, 0, 1), P5_Bad = c(0, 0, 0, 0, 0, 0), `Bad Pass` = c(0, 0, 
0, 0, 1, 1), `Good Pass` = c(2, 2, 1, 0, 3, 6), `Intercepted Pass` = c(0, 
0, 0, 0, 1, 0), Turnover = c(0, 0, 0, 0, 1, 0), totalEvents = c(2, 
2, 1, 0, 6, 7)), row.names = c("P1", "P2", "P4", "P5", "Opponent", 
"VT"), class = "data.frame")

copyComb is the same as passesComb just with row/column 3 removed. I tried adapting from the code for the original answer.

gamesComb <- data.frame(matrix(NA, nrow = ifelse(nrow(passesComb) >= nrow(copyComb), nrow(passesComb),nrow(copyComb)),
                               ncol = ifelse(ncol(passesComb) >= ncol(copyComb), ncol(passesComb),ncol(copyComb))))
                        
gamesComb[row.names(ifelse(nrow(passesComb) >= nrow(copyComb), passesComb, copyComb)),
                           colnames(ifelse(ncol(passesComb) >= ncol(copyComb), passesComb, copyComb))] <- passesComb

but this only creates a 7x15 df and doesn't add the row/column names for some reason, in addition to not adding the cell values.

1 Answers

If the intention is to update 'anotherMatrix' with goodMatrix, use the row.names and colnames from 'goodMatrix' to subset the 'anotherMatrix' and assign the 'goodMatrix' to 'anotherMatrix'

anotherMatrix[row.names(goodMatrix), colnames(goodMatrix)] <- goodMatrix

Then, we just replace the NA with 0

anotherMatrix[is.na(anotherMatrix)] <- 0

-checking with 'expectedOutput

> identical(expectedOutput, anotherMatrix[names(expectedOutput)])
[1] TRUE
Related