I have found a lot of material about counting a value in table, but my goal is little different and I havent found any source.
This is my data
ID_1 ID_2 Date RESULT
1 12 3 2011-12-21 0
2 12 13 2011-12-22 0
3 3 12 2011-12-22 1
4 15 13 2011-12-23 0
5 12 13 2011-12-23 1
5 13 15 2011-12-23 1
6 3 12 2011-12-23 0
7 12 13 2011-12-23 0
TARGET
ID_1 ID_2 Date RESULT H2H_ID1 H2H_ID2
1 12 3 2011-12-21 0 0 0
2 12 13 2011-12-22 0 0 0
3 3 12 2011-12-22 1 1 0
4 15 13 2011-12-23 0 0 0
5 12 13 2011-12-23 1 0 1
5 13 15 2011-12-23 1 1 0
6 3 12 2011-12-23 0 2 0
7 12 13 2011-12-23 0 1 1
...
and so on
In RESULT, 0 is a match won by id2, 1 when is won by id1.
I need 2 columns (h2h_id1, h2h_id2) that count the match previously won by the same players (id1 and id2), the traditional head-to-head.
I'll make an example. Row 3.
ID1=3 and ID2=12.
The row where id 3 and id 12 had a previous match is row1, and the winner of the match is id2 (result=0).
So in the row 3 I want to read 1 in the H2H_ID1.
In row 6, same conditions, 2 matches with same ids and same result.
In another post, for a similiar tastk, (column with previous result) they gave me this code to find only 1 match before (and without sum) but maybe could help.
# emulate the original dataframe
ID_1 <- c(12,12,3,15,16,3)
ID_2<-c(3,13,12,13,17,15)
ids <- cbind(ID_1, ID_2) # IDs columns
x1 <- c(15, 50, 20, 30, 51, 60)
y2 <- c(10, 40, 30, 20, 53, 62)
vars <- cbind(x1, y2) # x&y columns
FindPreviousIDsPair <- function(id_matrix, i_of_row) {
shorten_matrix <- id_matrix[1:(i_of_row - 1),,drop = FALSE]
string_to_search_for <- id_matrix[i_of_row, ]
string_to_search_for_sorted <-
string_to_search_for[order(string_to_search_for)]
found_rows_boolean <- sapply(FUN = function(i) all(shorten_matrix[i,
order(shorten_matrix[i, ])] ==
string_to_search_for_sorted), X = 1:(i_of_row - 1))
found_row_n <- ifelse(any(found_rows_boolean),
max(which(found_rows_boolean)), NA_real_)
found_col_of_DI1 <- ifelse(any(found_rows_boolean),
match(string_to_search_for[1], shorten_matrix[found_row_n, ]), NA_real_)
found_col_of_DI2 <- ifelse(any(found_rows_boolean),
match(string_to_search_for[2], shorten_matrix[found_row_n, ]), NA_real_)
return(c(found_row_n, found_col_of_DI1, found_col_of_DI2))
}
Thanks for your help.
