Merging two data frames

Question

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 combine this data frame with another 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 in 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 simply use the merge function and combine them (one-to-many) with Year and Country.

I tried to convert the data frame as above. However, the only way I can come up with is to use some complex "for" loops. It would be very helpful if someone had an easier approach. Also, if you think that merging these two frames of data can be made easier, that's great too.

Answer 1

The first data frame to be melted.

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

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

Then merge (or you can join it using 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 merging, especially when there are NA values.