合并所有组合的两个数据框 [英] Merge two data frames with all combinations
问题描述
我遇到了一个复杂的问题: 我有两个数据帧，它们分别具有4个不同机器的no和规格(颜色，零件)的组合.每台机器总共有5种规格(实际上甚至更多的机器和规格). 这是两个示例数据帧:
I got a complicated problem: I have two data frames which have one combination of no and specification (Color, Part) each for 4 different machines. There is a total count of 5 specifications per machine (in reality even more machines and specifications). Here are two sample data frames:
df1 <- data.frame(
nr=c("000", "000", "000", "001", "002",
"002", "003", "004", "004", "004", "005"),
Color=c("Red", "Cyan", "Brown", "Blue", "Red",
"Green", "DeepBlue", "Orange", "Cyan", "Grey", "Magenta"),
mach1=c(1, NA, NA, 1, NA, 1, 1, NA, 1, NA, 1),
mach2=c(1, NA, NA, 1, NA, 1, 1, 1, NA, NA, 1),
mach3=c(NA, 1, NA, 1, 1, NA, 1, NA, NA, 1, 1),
mach4=c(NA, NA, 1, 1, NA, 1, 1, NA, NA, 1, 1))
df2 <- data.frame(
nr=c("000", "000", "001", "002", "002",
"003", "003", "004", "005", "005"),
Part=c("Car", "Tree", "Flag", "Tree", "Road",
"Road", "House", "Plane", "House", "Car"),
mach1=c(NA, 1, 1, NA, 1, NA, 1, 1, NA, 1),
mach2=c(1, NA, 1, NA, 1, NA, 1, 1, 1, NA),
mach3=c(NA, 1, 1, 1, NA, 1, NA, 1, 1, NA),
mach4=c(NA, 1, 1, 1, NA, 1, NA, 1, 1, NA))
所以我有这些输出:
> df1
nr Color mach1 mach2 mach3 mach4
1 000 Red 1 1 NA NA
2 000 Cyan NA NA 1 NA
3 000 Brown NA NA NA 1
4 001 Blue 1 1 1 1
5 002 Red NA NA 1 NA
6 002 Green 1 1 NA 1
7 003 DeepBlue 1 1 1 1
8 004 Orange NA 1 NA NA
9 004 Cyan 1 NA NA NA
10 004 Grey NA NA 1 1
11 005 Magenta 1 1 1 1
> df2
nr Part mach1 mach2 mach3 mach4
1 000 Car NA 1 NA NA
2 000 Tree 1 NA 1 1
3 001 Flag 1 1 1 1
4 002 Tree NA NA 1 1
5 002 Road 1 1 NA NA
6 003 Road NA NA 1 1
7 003 House 1 1 NA NA
8 004 Plane 1 1 1 1
9 005 House NA 1 1 1
10 005 Car 1 NA NA NA
现在，我想将这两个数据帧合并为一个新的数据帧df3，该数据帧显示特定机器的Color和Part的所有组合，如下所示:
Now I would like to combine those two data frames into one new data frame df3 that shows all combinations of Color and Part for the specific machines like this:
> df3
nr Color Part mach1 mach2 mach3 mach4
1 000 Red Tree 1 NA NA NA
2 000 Red Car NA 1 NA NA
3 000 Cyan Tree NA NA 1 NA
4 000 Brown Tree NA NA NA 1
5 001 Blue Flag 1 1 1 1
6 002 Green Road 1 1 NA NA
7 002 Red Tree NA NA 1 NA
8 002 Green Tree NA NA NA 1
9 003 Deepblue House 1 1 NA NA
10 003 Deepblue Road NA NA 1 1
11 004 Cyan Plane 1 NA NA NA
12 004 Orange Plane NA 1 NA NA
13 004 Grey Plane NA NA 1 1
14 005 Magenta Car 1 NA NA NA
15 005 Magenta House NA 1 1 1
>
有什么建议吗?
推荐答案
您可以首先重塑两个数据框的形状(使用reshape2中的melt)，然后合并它们(使用dplyr中的full_join)并将其重塑为原始格式:
You can first reshape the two data frames (using melt from reshape2), then merge them (using full_join from dplyr) and reshape that back into the original format:
library(dplyr)
library(reshape2)
df1.b <- melt(df1)
df2.b <- melt(df2)
df3 <- full_join(df1.b, df2.b)
df3 <- na.omit(df3)
df3.b <- dcast(df3, nr + Color + Part ~ variable)
nr Color Part mach1 mach2 mach3 mach4
1 000 Brown Tree NA NA NA 1
2 000 Cyan Tree NA NA 1 NA
3 000 Red Car NA 1 NA NA
4 000 Red Tree 1 NA NA NA
5 001 Blue Flag 1 1 1 1
6 002 Green Road 1 1 NA NA
7 002 Green Tree NA NA NA 1
8 002 Red Tree NA NA 1 NA
9 003 DeepBlue House 1 1 NA NA
10 003 DeepBlue Road NA NA 1 1
11 004 Cyan Plane 1 NA NA NA
12 004 Grey Plane NA NA 1 1
13 004 Orange Plane NA 1 NA NA
14 005 Magenta Car 1 NA NA NA
15 005 Magenta House NA 1 1 1
这篇关于合并所有组合的两个数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
