CODEWITHSUNDEEP
rmergedataframe
rp1
rp1

Reputation: 371

Merging two data frames

I have the following data frame:

Date,Year,Austria,Germany,...
1969-12-31,1969,96.743,95.768,...
1970-01-30,1970,95.515,95.091,...
1970-02-27,1970,95.075,95.235,...

Ultimately, I would like to merge this data frame with another one that looks like this:

Year,Country,Exp,...
1969,Austria,1,...
1970,Austria,0,...
1969,Germany,0,...
1970,Germany,1,...

The way I see it, I would have to change the first data frame to the following format:

Date,Year,Country,Exp,…
1969-12-31,1969,Austria,96.743,...
1970-01-30,1970,Austria,95.515,...
1970-02-27,1970,Austria,95.075,...
1969-12-31,1969,Germany,95.768,...
1970-01-30,1970,Germany,95.091,...
1970-02-27,1970,Germany,95.235,...

Then, I can just use the merge function and merge them (one-to-many) using Year and Country.

I have tried to transform the data frame as suggest above. However, the only way I can think of is to use a couple of complicated "for" loops. It would be greatly appreciated if someone had an easier approach. Also, if you think that merging those two data frames can be done in an easier fashion that would be great too.

Upvotes: 6

Views: 2924

Answers (1)

Brandon Bertelsen
Brandon Bertelsen

Reputation: 44638

The first data frame you need to melt. 

library(reshape)
melt(dat, id.vars="Date,Year") # may need to add ...,c())

Rename the new columns to match your other data.frame.

Then merge (or you might prefer to join, using the plyr package) 

merge(dat,dat2, by=c("Date","Country"))

or: 

library(plyr)
join(dat,dat2, by=c("Date","Country"))

I prefer the join function, because it acts much more intuitively than merge, especially in the case where there are NA values.

Upvotes: 5

Related Questions