CODEWITHSUNDEEP
rmerge
Dale Wu
Dale Wu

Reputation: 199

Merging datasets by columns that have different names

I want to merge datasets by columns that have different names

For example, for the data frames, df and df1

df <- data.frame(ID = c(1,2,3), Day = c(1,2,3), mean = c(2,3,4))
df1 <- data.frame(ID = c(1,2,3), Day = c(1,2,3), median = c(5,6,7))

I want to merge df and df1 so that I get

ID  Day  Measure  Value
1   1    Mean      2
2   2    Mean      3
3   3    Mean      4
1   1    Median    5    
2   2    Median    6    
3   3    Median    7

Any ideas how? I tried using 

merge(df,df1, by=c("ID","Day"))   and
rbind.fill(df,df1)  from the plyr package

but they each only do half of what I want.

Upvotes: 2

Views: 125

Answers (3)

Pierre L
Pierre L

Reputation: 28441

library(tidyr)
m <- merge(df, df1, c("ID", "Day"))
gather(m, measure, value, mean:median)
#  ID Day measure value
#1  1   1    mean     2
#2  2   2    mean     3
#3  3   3    mean     4
#4  1   1  median     5
#5  2   2  median     6
#6  3   3  median     7

And with reshape2:

melt(m, id=c("ID", "Day"))

Or with data.table:

setDT(df, df1)
setkey(df, ID, Day)
melt(df[df1], c("ID", "Day"))
# 1:  1   1     mean     2
# 2:  2   2     mean     3
# 3:  3   3     mean     4
# 4:  1   1   median     5
# 5:  2   2   median     6
# 6:  3   3   median     7

Upvotes: 4

thelatemail
thelatemail

Reputation: 93803

In base R:

vars <- c("ID","Day")
m <- merge(df, df1, by=vars)
cbind(m[vars], stack(m[setdiff(names(m),vars)]) )

#  ID Day values    ind
#1  1   1      2   mean
#2  2   2      3   mean
#3  3   3      4   mean
#4  1   1      5 median
#5  2   2      6 median
#6  3   3      7 median

Upvotes: 1

William Gearty
William Gearty

Reputation: 165

You could add a new column to your two original data.frames called "Measure", then set the entire column to "Mean" in your first data.frame and "Median" in your second data.frame. Then set the colname of mean and median in both of your data.frames to "Value". Then combine using rbind.

Upvotes: -1

Related Questions