I have below-mentioned dataframe in R:
DF <- tibble::tribble(
~ID, ~Check,
"I-1", "A1",
"I-2", "A2",
"I-2", "OT",
"I-2", "LP",
"I-3", "A1",
"I-3", "A2",
"I-4", NA,
"I-5", NA,
"I-6", "A1",
"I-6", "OT",
"I-7", "A2"
)
DF2 <- tibble::tribble(
~ID, ~Remarks,
"I-1", "{X1,XR,XT}",
"I-2", "{X2,XR}",
"I-3", NA,
"I-4", "{X1,XR,X2}",
"I-5", "{X1}",
"I-6", "{XT}",
"I-7", "{X1,X2}"
)
Using the above mentioned two dataframe, I need the output in the following format:
Where I want to identify the exclusive count of Check for each unique ID and Identify what is the Remark value for that unique ID in 2x2 Matrix format.
Group Variable is where multiple values hit for a unique ID
Note - The order of rows should be highest to lowest based on Exclusive_Count of Check. It is quite possible that the number of unique Check and Remark may differ in my actual dataframe. (i.e 10 unique Remark and 5 Check, something like this)
It would be helpful if we can fix the value of Remark and Check as constant so that if that particular value is not available then the value for that will be 0.
DF_Output<-
Remark Exclusive_Count % Blank A1 A2 OT LP
Blank 1 14.29% 1 0 0 0 0
X1 1 14.29% 2 0 1 1 0
X2 0 0.00% 1 1 0 1 1
XR 0 0.00% 0 1 1 0 1
XT 1 14.29% 0 0 1 1 0
Group 4 57.14% 1 2 2 2 1
Total 7 100.00% 4 2 3 3 2