合并数据框时,多个匹配项可能存在时,不重复数据。

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

Merging data frames without duplicating data when several matches are possible

问题

我需要合并两个数据框,如果有多个匹配项,就不要有重复的行/重复的数据。基本上,如果我的数据框之间的匹配是模糊的,我希望模糊的行不要匹配,而是每行保持分开。

我一直在使用merge函数,但它说如果有多个匹配项,所有可能的匹配都会贡献一行,而我使用join/inner_join函数得到了相同的结果。有没有办法解决这个问题?

示例:

df1

ID 问题 1 问题 2
A1 1 2
B1 3 4
C1 5 6
C1 7 8

df2

ID 问题 3 问题 4
A1 a b
B1 c d
C1 e f
C1 g h

使用ID合并后的结果

ID 问题 1 问题 2 问题 3 问题 4
A1 1 2 a b
B1 3 4 c d
C1 5 6 e f
C1 7 8 g h
C1 5 6 g h
C1 7 8 e f

我想要的结果

ID 问题 1 问题 2 问题 3 问题 4
A1 1 2 a b
B1 3 4 c d
C1 5 6 NA NA
C1 7 8 NA NA
C1 NA NA e f
C1 NA NA g h

感谢您的帮助!

英文:

I need merge two data frames, without having duplicate rows/duplicated data if there is more than one match. Basically, if the matching between my dataframes is ambiguous, I would like the ambiguous rows to NOT be matched, and each row to remain separate instead.
I've been using the merge function, but it states that If there is more than one match, all possible matches contribute one row each, and I got to the same result using join/inner_join functions. Is there a way to fix this ?

Example:

df1

ID Question 1 Question 2
A1 1 2
B1 3 4
C1 5 6
C1 7 8

df2

ID Question 3 Question 4
A1 a b
B1 c d
C1 e f
C1 g h

What I get using merge by ID

ID Question 1 Question 2 Question 3 Question 4
A1 1 2 a b
B1 3 4 c d
C1 5 6 e f
C1 7 8 g h
C1 5 6 g h
C1 7 8 e f

What I want

ID Question 1 Question 2 Question 3 Question 4
A1 1 2 a b
B1 3 4 c d
C1 5 6 NA NA
C1 7 8 NA NA
C1 NA NA e f
C1 NA NA g h

Thank you for your help !

答案1

得分: 2

你可以尝试识别duplicated的id,然后在使用merge时排除它们,最后再使用rbind将它们合并。

X <- unique(c(df1$ID[duplicated(df1$ID)], df2$ID[duplicated(df2$ID)]))
plyr::rbind.fill(merge(df1[!df1$ID %in% X,], df2[!df2$ID %in% X,]),
                 df1[df1$ID %in% X,],
                 df2[df2$ID %in% X,])
#  ID Question1 Question2 Question3 Question4
#1 A1         1         2         a         b
#2 B1         3         4         c         d
#3 C1         5         6      &lt;NA&gt;      &lt;NA&gt;
#4 C1         7         8      &lt;NA&gt;      &lt;NA&gt;
#5 C1        NA        NA         e         f
#6 C1        NA        NA         g         h

数据

df2 <- read.table(header=TRUE, text="ID 	Question3 	Question4
A1 	a 	b
B1 	c 	d
C1 	e 	f
C1 	g 	h")

df1 <- read.table(header=TRUE, text="
ID 	Question1 	Question2
A1 	1 	2
B1 	3 	4
C1 	5 	6
C1 	7 	8")
英文:

You can try to identify the duplicated id's, exclude them when using merge and rbind them afterwards.

X &lt;- unique(c(df1$ID[duplicated(df1$ID)], df2$ID[duplicated(df2$ID)]))
plyr::rbind.fill(merge(df1[!df1$ID %in% X,], df2[!df2$ID %in% X,]),
                 df1[df1$ID %in% X,],
                 df2[df2$ID %in% X,])
#  ID Question1 Question2 Question3 Question4
#1 A1         1         2         a         b
#2 B1         3         4         c         d
#3 C1         5         6      &lt;NA&gt;      &lt;NA&gt;
#4 C1         7         8      &lt;NA&gt;      &lt;NA&gt;
#5 C1        NA        NA         e         f
#6 C1        NA        NA         g         h

Data

df2 &lt;- read.table(header=TRUE, text=&quot;ID 	Question3 	Question4
A1 	a 	b
B1 	c 	d
C1 	e 	f
C1 	g 	h&quot;)

df1 &lt;- read.table(header=TRUE, text=&quot;
ID 	Question1 	Question2
A1 	1 	2
B1 	3 	4
C1 	5 	6
C1 	7 	8&quot;)

答案2

得分: 1

I always seek to avoid many to many merges by creating a case variable for each duplicate id and then merge by ID case.

Like below:

df1 <- data.frame(ID = c("A1", "B1", "C1", "C1"), Q1 = c(1, 3, 4, 7), Q2 = c(2, 4, 6, 8))
df2 <- data.frame(ID = c("A1", "B1", "C1", "C1"), Q3 = c("a", "b", "c", "d"), Q4 = c("b", "d", "f", "h"))
library(dplyr)

df3 <- full_join(df1, df2, by = "ID")

df3

df11 <- df1 %>%
  group_by(ID) %>%
  mutate(case = row_number())

df22 <- df2 %>%
  group_by(ID) %>%
  mutate(case = row_number())

tmp <- df22 %>% summarise(count = n())

df22 <- merge(df22, tmp, by = c("ID"))

df22 <- df22 %>% mutate (case = ifelse(count==2,case+2,case))
df22

df33 <- full_join(df11, df22, by = c("ID", "case"))
df33

which returns:
合并数据框时,多个匹配项可能存在时,不重复数据。

英文:

I always seek to avoid many to many merges by creating a case variable for each duplicate id and then merge by ID case.

Like below:

df1 &lt;- data.frame(ID = c(&quot;A1&quot;, &quot;B1&quot;, &quot;C1&quot;, &quot;C1&quot;), Q1 = c(1, 3, 4, 7), Q2 = c(2, 4, 6, 8))
df2 &lt;- data.frame(ID = c(&quot;A1&quot;, &quot;B1&quot;, &quot;C1&quot;, &quot;C1&quot;), Q3 = c(&quot;a&quot;, &quot;b&quot;, &quot;c&quot;, &quot;d&quot;), Q4 = c(&quot;b&quot;, &quot;d&quot;, &quot;f&quot;, &quot;h&quot;))
library(dplyr)

df3 &lt;- full_join(df1, df2, by = &quot;ID&quot;)

df3

df11 &lt;- df1 %&gt;%
  group_by(ID) %&gt;%
  mutate(case = row_number())

df22 &lt;- df2 %&gt;%
  group_by(ID) %&gt;%
  mutate(case = row_number())

tmp &lt;- df22 %&gt;% summarise(count = n())

df22 &lt;- merge(df22, tmp, by = c(&quot;ID&quot;))

df22 &lt;- df22 %&gt;% mutate (case = ifelse(count==2,case+2,case))
df22

df33 &lt;- full_join(df11, df22, by = c(&quot;ID&quot;, &quot;case&quot;))
df33

which returns:
合并数据框时,多个匹配项可能存在时,不重复数据。

huangapple
  • 本文由 发表于 2023年5月22日 22:22:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/76307179.html
匿名

发表评论

匿名网友

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

确定