I have a dataset that contains lists for values in two columns, and I want to be able to compare the lists with each other, for each row. For example, I have 3 groups, in which certain numbers are expected, and other numbers are observed. I want to compare the Observed column with the Expected column to see which numbers were expected, but not observed.
| Group | Expected | Observed |
|---|---|---|
| A | 4:8 | c(4, 5, 7) |
| B | 7:12 | c(7, 8, 9, 10, 12) |
| C | 6:10 | c(6, 8, 10) |
I want an extra column called Missing, that contains all the values that are in Expected, but not Observed.
| Group | Expected | Observed | Missing |
|---|---|---|---|
| A | 4:8 | c(4, 5, 7) | c(6, 8) |
| B | 7:12 | c(7, 8, 9, 10, 12) | 11 |
| C | 6:11 | c(6, 8, 11) | c(7, 9, 10) |
I have tried to use setdiff() and base R, as it can find the differing values between two lists. However, I cannot get it to work in the way that it compares the lists per row.
df$Missing <- setdiff(df$Expected, df$Observed)
df$Missing <- df$Expected[!(df$Expected %in% df$Observed)]
Both these options result in the full list of Expected. This is the output that I get:
| Group | Expected | Observed | Missing |
|---|---|---|---|
| A | 4:8 | c(4, 5, 7) | 4:8 |
| B | 7:12 | c(7, 8, 9, 10, 12) | 7:12 |
| C | 6:11 | c(6, 8, 11) | 6:11 |
Is there any way that I can compare the two lists (Observed vs. Expected) per group, so I can see which values are missing per group? Thank you in advance for any help!
Since you have lists, use Map with setdiff -
df$Missing <- Map(setdiff, df$Expected, df$Observed)
df
# Group Expected Observed Missing
#1 A 4, 5, 6, 7, 8 4, 5, 7 6, 8
#2 B 7, 8, 9, 10, 11, 12 7, 8, 9, 10, 12 11
#3 C 6, 7, 8, 9, 10 6, 8, 10 7, 9
data
It is easier to help if you provide data in reproducible format.
df <- structure(list(Group = c("A", "B", "C"), Expected = list(4:8,
7:12, 6:10), Observed = list(c(4, 5, 7), c(7, 8, 9, 10, 12
), c(6, 8, 10))), row.names = c(NA, -3L), class = "data.frame")