R 数据的预处理：数据集的合并

合并数据集
很多情况下，数据集都不会只有一个文件，但是为了方便后续的处理和分析，在预处理的阶段就会将各个数据集进行合并。

1. Inner Join内连接

# 数据集A
A = data.frame(id=c(1,3,5), A_val=c('a','x','c')) 
   id A_val 
1  1  a
2  3 x
3  5 c
# 数据集B
B = data.frame(id=c(3,5,6), B_val=c('x','y','z')) 
   id B_val 
1  3  x
2  5  y
3  6  z
# 函数merge( )通过“id”取交集，所以只剩下 id为3 5的
A_B = merge(x=A, y=B, by.x='id', by.y='id') A_B
   id A_val B_val 
1  3    x     x 
2  5    c     y

2. Out Join 外连接

# 通过“id”将B外接导A上
left_A_B = merge(x=A, y=B, by.x='id', by.y='id', all.x=T)
left_A_B # A left join B
    id A_val B_val 
1   1    a    <NA>
2   3    x     x 
3   5    c     y
# 通过“id”将A外接导B上
right_A_B = merge(x=A, y=B, by.x='id', by.y='id', all.y=T) 
right_A_B # A right join B
    id A_val B_val 
1   3    x     x 
2   5    c     y
3   6   <NA>   z
# 通过“id”将AB都保留
full_A_B = merge(x=A, y=B, by.x='id', by.y='id', all=T) 
full_A_B # A full outer join B
    id A_val B_val 
1   1    a    <NA> 
2   3    x     x 
3   5    c     y
4   6   <NA>   z

3. Concatenation

# 将A B数据集columns的名字改成统一的
colnames(A) = colnames(B) = c('id','val');
# 然后用rbind()将A B两组数据集通过row合并，B接在A的下面(需要相同的column)
rbind(A,B) # Concatenate vertically
  id val
1 1   a
2 3   x
3 5   c 
4 3   x 
5 5   y 
6 6   z

# 将A B数据集通过column合并，B接在A的后面
cbind(A,B) # Concatenate horizontally 
    id val  id val
1   1   a   3   x 
2   3   x   5   y 
3   5   c   6   z

4. Set Operation交集

# A intersect B
 subset(A, (A$id %in% B$id & A$val %in% B$val))
    id val
2   3   x
# A except B
 subset(A, ! (A$id %in% B$id & A$val %in% B$val))
    id val
1   1   a
3   5   c
# B except A
 subset(B, ! (B$id %in% A$id & B$val %in% A$val))
    id val 
2   5   y 
3   6   z

5. 用dplyr包来查找交集

# More efficient row/column binding
dplyr::bind_rows(A, B); dplyr::bind_cols(A, B)
# A intersect/union B
dplyr::intersect(A, B); dplyr::union(A, B);
# A except B; B except A
dplyr::setdiff(A, B); dplyr::setdiff(B, A)
# Observation-level set comparison
dplyr::setequal(A, B)

6. 用SQL语法来合并数据集（sqldf包）

library(sqldf)
# Inner join
sqldf('select A.id, A.val as A_val, B.val as B_val from A inner join B on A.id = B.id')
# Left outer join
sqldf('select A.id, A.val as A_val, B.val as B_val from A left join B on A.id = B.id')
# Union
sqldf('select * from A union select * from B')

sqldf('select * from A except select * from B')
#     id val 
# 1   1   a
# 2   5   c
 sqldf('select * from A intersect select * from B')
#     id val 
# 1   3   x
sqldf('select * from
(select * from A union all select * from B) where id > 5')
#     id val 
# 1   6   z
mtcars = data.frame(mtcars);
sqldf('select row_names, mpg, cyl, wt from mtcars
where row_names like "%Toyota%" ', row.names=T)
#                 mpg cyl wt 
# Toyota Corolla 33.9 4 1.835 
# Toyota Corona 21.5 4 2.465

欢迎关注【数据小盐罐儿】一个很“咸”的数据科学公众号，不定期分享有趣好玩的项目以及大量的学习资源