test if strings from two datasets match using subject id columns from twi datasets by subject id

huangapple
go评论87阅读模式
英文:

test if strings from two datasets match using subject id columns from twi datasets by subject id

问题

以下是您要翻译的内容：

  1. 我正在尝试对两列进行简单匹配，但我需要按subject id进行匹配，并且输出应该是两个数据集中不匹配的subject id。
  3. 数据：

df1 <- structure(list(
subject_id = c("191-5467", "191-6784", "191-3457", "191-0987", "191-1245", "191-1945"),
edta_code = c("EDTA45", "EDTA79", "EDTA20", "EDTA66", "EDTA12", "EDTA74"),
ipv = c("0", "3", "2", "2", "1", "2")),
class = "data.frame", row.names = c(NA, -6L))

df2 <- structure(list(
subject_id = c("191-5467", "191-6784", "191-3457", "191-0987",
"191-1245", "191-1947"),
edta_code_df2 = c("EDTA45", "EDTA78", "EDTA20", "EDTA66", "EDTA14", "EDTA74")), class = "data.frame", row.names = c(NA, -6L))

  2. 我尝试过的内容：

df3 <- df1 %>% select(subject_id, edta_code)

df3 <- full_join(df3, df2, by = "subject_id")
df$all_matching <- df3$edta_code == df3$edta_code_df2

  2. 上面的代码不起作用，它会产生错误：

Error in $<-.data.frame(*tmp*, all_matching, value = c(TRUE, FALSE, :
replacement has 7 rows, data has 5

  2. 期望的输出：
  4. [![enter image description here][1]][1]
  6. 其中0表示否，1表示是。
  7. 然后在那之后，我提取了subject id的值为0的记录。
  9. |subject_id|
  10. |--------|
  11. |191-6784|
  12. |191-1245|
  13. |191-1947|
  14. |191-1945|
  16. 注意：尽管最后两个subject_id具有正确的代码，但subject_id是不正确的。
  17. [1]: https://i.stack.imgur.com/NFeTV.png
  19. <details>
  20. <summary>英文:</summary>
  22. I am trying to do a simple matching of two columns, but I need it to match by subject id and the output should be the subject id&#39;s which do not match in the two datasets. 
  24. data:

df1 <- structure (list(
subject_id = c("191-5467", "191-6784", "191-3457", "191-0987", "191-1245", "191-1945"),
edta_code = c("EDTA45", "EDTA79", "EDTA20", "EDTA66", "EDTA12", "EDTA74"),
ipv = c("0", "3", "2", "2", "1", "2")),
class = "data.frame", row.names = c(NA, -6L))

df2 <- structure (list(
subject_id = c("191-5467", "191-6784", "191-3457", "191-0987",
"191-1245", "191-1947"),
edta_code_df2 = c("EDTA45", "EDTA78", "EDTA20", "EDTA66", "EDTA14", "EDTA74")), class = "data.frame", row.names = c(NA, -6L))

  1. what I have tried

df3 <- df1 %>% select(subject_id, edta_code)

df3 <- full_join(df3, df2, by = "subject_id")
df$all_matching <- df3$edta_code == df3$edta_code_df2

  1. The above code doesn&#39;t work, it gives error:

Error in $&lt;-.data.frame(*tmp*, all_matching, value = c(TRUE, FALSE, :
replacement has 7 rows, data has 5

  2. desired output: 
  4. [![enter image description here][1]][1]
  6. where 0 is no and 1 is yes. 
  7. Then after that I pull the subject id&#39;s with 0. 
  9. |subject_id|
  10. |--------|
  11. |191-6784|
  12. |191-1245|
  13. |191-1947|
  14. |191-1945|
  16. Note: although the last two subject_ids have the correct codes the subject_ids are incorrect. 
  19.   [1]: https://i.stack.imgur.com/NFeTV.png
  21. </details>
  24. # 答案1
  25. **得分**: 1
  27. ```R
  28. library(tidyverse)
  30. df1 %>%
  31.   select(-ipv) %>%
  32.   full_join(df2) %>%
  33.   mutate(matching = if_else(edta_code == edta_code_df2, 1, 0)) %>%
  34.   replace_na(list(matching = 0))
  1. Joining with `by = join_by(subject_id)`
  2. # A tibble: 7 × 4
  3.   subject_id edta_code edta_code_df2 matching
  4.   <chr>      <chr>     <chr>            <dbl>
  5. 1 191-5467   EDTA45    EDTA45               1
  6. 2 191-6784   EDTA79    EDTA78               0
  7. 3 191-3457   EDTA20    EDTA20               1
  8. 4 191-0987   EDTA66    EDTA66               1
  9. 5 191-1245   EDTA12    EDTA14               0
  10. 6 191-1945   EDTA74    NA                   0
  11. 7 191-1947   NA        EDTA74               0
  1. Pull the IDs
  3. df1 %>%
  4.   select(-ipv) %>%
  5.   full_join(df2) %>%
  6.   mutate(matching = if_else(edta_code == edta_code_df2, 1, 0)) %>%
  7.   filter(!matching %in% c(1))
  8.   
  9. # A tibble: 4 × 4
  10.   subject_id edta_code edta_code_df2 matching
  11.   <chr>      <chr>     <chr>            <dbl>
  12. 1 191-6784   EDTA79    EDTA78               0
  13. 2 191-1245   EDTA12    EDTA14               0
  14. 3 191-1945   EDTA74    NA                  NA
  15. 4 191-1947   NA        EDTA74              NA
英文: 
  1. library(tidyverse)
  3. df1 %&gt;% 
  4.   select(-ipv) %&gt;% 
  5.   full_join(df2) %&gt;%  
  6.   mutate(matching = if_else(edta_code == edta_code_df2, 1, 0)) %&gt;% 
  7.   replace_na(list(matching = 0))
  9. Joining with `by = join_by(subject_id)`
  10. # A tibble: 7 &#215; 4
  11.   subject_id edta_code edta_code_df2 matching
  12.   &lt;chr&gt;      &lt;chr&gt;     &lt;chr&gt;            &lt;dbl&gt;
  13. 1 191-5467   EDTA45    EDTA45               1
  14. 2 191-6784   EDTA79    EDTA78               0
  15. 3 191-3457   EDTA20    EDTA20               1
  16. 4 191-0987   EDTA66    EDTA66               1
  17. 5 191-1245   EDTA12    EDTA14               0
  18. 6 191-1945   EDTA74    NA                   0
  19. 7 191-1947   NA        EDTA74               0

Pull the IDs

  1. df1 %&gt;% 
  2.   select(-ipv) %&gt;% 
  3.   full_join(df2) %&gt;%  
  4.   mutate(matching = if_else(edta_code == edta_code_df2, 1, 0)) %&gt;% 
  5.   filter(!matching %in% c(1))
  7. # A tibble: 4 &#215; 4
  8.   subject_id edta_code edta_code_df2 matching
  9.   &lt;chr&gt;      &lt;chr&gt;     &lt;chr&gt;            &lt;dbl&gt;
  10. 1 191-6784   EDTA79    EDTA78               0
  11. 2 191-1245   EDTA12    EDTA14               0
  12. 3 191-1945   EDTA74    NA                  NA
  13. 4 191-1947   NA        EDTA74              NA

答案2

得分: 1

在你的full_join之后，你需要处理edta_code或edta_code_df2列中的缺失值。在使用==进行相等性检查时，你可以添加!is.na()条件，这样如果这两列中的任何一列或两列都缺失，matching列将会得到FALSE或0。

  1. library(tidyverse)
  3. df3 <- full_join(df1, df2, by = "subject_id") %>%
  4.   select(-ipv) %>%
  5.   mutate(matching = +(edta_code == edta_code_df2 & !is.na(edta_code) & !is.na(edta_code_df2)))
  7. df3
  9.   subject_id edta_code edta_code_df2 matching
  10. 1   191-5467    EDTA45        EDTA45        1
  11. 2   191-6784    EDTA79        EDTA78        0
  12. 3   191-3457    EDTA20        EDTA20        1
  13. 4   191-0987    EDTA66        EDTA66        1
  14. 5   191-1245    EDTA12        EDTA14        0
  15. 6   191-1945    EDTA74          <NA>        0
  16. 7   191-1947      <NA>        EDTA74        0
  18. df3 %>%
  19.   filter(matching == 0) %>%
  20.   pull(subject_id)
  22. [1] "191-6784" "191-1245" "191-1945" "191-1947"
英文:

After your full_join, you will need to deal with missing in either edta_code or edta_code_df2 columns. When checking for equality with == you can add !is.na() so that if either or both of those two columns is missing you will get FALSE or 0 for the matching column.

  1. library(tidyverse)
  3. df3 &lt;- full_join(df1, df2, by = &quot;subject_id&quot;) %&gt;%
  4.   select(-ipv) %&gt;%
  5.   mutate(matching = +(edta_code == edta_code_df2 &amp; !is.na(edta_code) &amp; !is.na(edta_code_df2)))
  7. df3
  9.   subject_id edta_code edta_code_df2 matching
  10. 1   191-5467    EDTA45        EDTA45        1
  11. 2   191-6784    EDTA79        EDTA78        0
  12. 3   191-3457    EDTA20        EDTA20        1
  13. 4   191-0987    EDTA66        EDTA66        1
  14. 5   191-1245    EDTA12        EDTA14        0
  15. 6   191-1945    EDTA74          &lt;NA&gt;        0
  16. 7   191-1947      &lt;NA&gt;        EDTA74        0
  18. df3 %&gt;%
  19.   filter(matching == 0) %&gt;%
  20.   pull(subject_id)
  22. [1] &quot;191-6784&quot; &quot;191-1245&quot; &quot;191-1945&quot; &quot;191-1947&quot;

huangapple
  • 本文由 发表于 2023年6月8日 17:53:40
  • 转载请务必保留本文链接：https://go.coder-hub.com/76430642.html
  • dplyr
  • r
  • tidyverse
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定