How to match large dataframe and rbind in R

user_13199674 0
Viewed 55

I am working on following dataframe.

File1:

UniqueId     Match_ID               Parameters     Value
UID-245       xty-tt09-23-ert        Age            28
UID-245       xty-tt09-23-ert        Sex            Male
UID-245       xty-tt09-23-ert        Height         5.7
UID-245       xty-tt09-23-ert        Balance        200
UID-246       xty-tt09-03-ert        Balance        303
UID-246       xty-tt09-03-ert        Weight         89

File2:

UniqueId     Match_ID               Parameters     Value
UID-245       xty-tt09-23-ert        Age            29
UID-245       xty-tt09-23-ert        Sex            Female
UID-245       xty-tt09-23-ert        Height         5.7
UID-245       xty-tt09-23-ert        Balance        200
UID-246       xty-tt09-03-ert        Balance        300

I need to compare both the datafrme and required the following output on distinct UniqueId basis.

Where, We need to compare File1 Parameters Value with File2 to check if both are exact match or not. Also, We need to create a comma separated string of Mismatched Parameters in output dataframe as Variable_Mismatched.

UniqueID   Count_File1  Count_File2 Matched_Parameters   Mismatched_Parameters   Variable_Mismatched
UID-245     4            4            2                   2                      Age,Sex
UID-246     2            1            0                   1                      Balance
dplyr dataframe r
1 Answers

Here is a data.table approach. explanation is in the code's comments

Make sure your File1 and File2 are in data.table-format. This can be done by reading the fils in using data.table::fread(), or by using data.table::setDT() on existing data.frames.

library(data.table)
library(tibble) #for lst-function
# Sample data -----
File1 <- fread("UniqueId     Match_ID               Parameters     Value
UID-245       xty-tt09-23-ert        Age            28
UID-245       xty-tt09-23-ert        Sex            Male
UID-245       xty-tt09-23-ert        Height         5.7
UID-245       xty-tt09-23-ert        Balance        200
UID-246       xty-tt09-03-ert        Balance        303
UID-246       xty-tt09-03-ert        Weight         89")
File2 <- fread("UniqueId     Match_ID               Parameters     Value
UID-245       xty-tt09-23-ert        Age            29
UID-245       xty-tt09-23-ert        Sex            Female
UID-245       xty-tt09-23-ert        Height         5.7
UID-245       xty-tt09-23-ert        Balance        200
UID-246       xty-tt09-03-ert        Balance        300")

# Part 1: get counts per file & UniqueId -----
L <- tibble::lst(File1, File2)
DT <- rbindlist(L, use.names = TRUE, fill = TRUE, idcol = "File")
# Summarise by UniqueId to get file-count, the first part of the answer
ans1 <- dcast(DT[, .N, by = .(File, UniqueId)], 
             UniqueId ~ paste0("Count_", File),
             value.var = "N")
# Part 2: Find matches and mismatches -----
ans2 <- copy(File1)[ File2, Value2 := i.Value, on = .(UniqueId, Parameters)]
# Only keep complete cases
ans2 <- ans2[!is.na(Value) & !is.na(Value2), ]
ans2 <- ans2[, .(Matched_Parameters    = sum(Value == Value2),
                 Mismatched_Parameters = sum(!Value == Value2),
                 Variable_Mismatched   = paste0(Parameters[!Value == Value2], 
                                                collapse = ","),
                 Variable_Matched      = paste0(Parameters[Value == Value2], 
                                                collapse = ",")), 
             by = .(UniqueId)]
# Part 3: Join together -----
ans1[ans2, on = .(UniqueId)]
#    UniqueId Count_File1 Count_File2 Matched_Parameters Mismatched_Parameters Variable_Mismatched Variable_Matched
# 1:  UID-245           4           4                  2                     2             Age,Sex   Height,Balance
# 2:  UID-246           2           1                  0                     1             Balance
user_63562780
Related