2

I have two data.frames that i would like to merge and turn into a panel. the first data.frame is a balanced panel, the second is an unbalanced one:

the first data.frame looks like this:

date1 <- c("2012-01-01","2012-01-02","2012-01-03","2012-01-04","2012-01-05","2012-01-01","2012-01-02","2012-01-03","2012-01-04","2012-01-05")
company1 <- c("A","A","A","A","A","B","B","B","B","B")
ret1 <- c(-0.01, -0.013, 0.02, 0.032, -0.002, 0.022, 0.012, 0.031, -0.018, -0.034)

mydf1 <- data.frame(date1, company1, ret1)
mydf1

        date1 company1   ret1
1  2012-01-01        A -0.010
2  2012-01-02        A -0.013
3  2012-01-03        A  0.020
4  2012-01-04        A  0.032
5  2012-01-05        A -0.002
6  2012-01-01        B  0.022
7  2012-01-02        B  0.012
8  2012-01-03        B  0.031
9  2012-01-04        B -0.018
10 2012-01-05        B -0.034

the second data.frame looks like this:

date2 <- c("2012-01-02","2012-01-04","2012-01-05","2012-01-01","2012-01-04")
company2 <- c("A","A","A","B","B")
class2 <- c("p", "p", "x", "n", "x")

mydf2 <- data.frame(date2, company2, class2)
mydf2

       date2 company2 class2
1 2012-01-02        A      p
2 2012-01-04        A      p
3 2012-01-05        A      x
4 2012-01-01        B      n
5 2012-01-04        B      x

so the first and second row is actually the same thing: date and company name. now i want to get the remaining columns of the first data frame (here: ret1) into the second data frame. of course i want them to be in the correct row. how can i do that? the panel should look like this:

        date  company  class     ret
1 2012-01-02        A      p  -0.013
2 2012-01-04        A      p   0.032
3 2012-01-05        A      x  -0.002
4 2012-01-01        B      n   0.022
5 2012-01-04        B      x  -0.018
cptn
  • 693
  • 2
  • 8
  • 28

1 Answers1

2

I think you can use merge. But maybe change the column names first to obtain the output you want.

names(mydf1)=c("date","company","ret")
names(mydf2)=c("date","company","class")
merge(mydf1, mydf2, by.x=intersect(names(mydf1), names(mydf2)))

see also: Matching multiple columns on different data frames and getting other column as result

Community
  • 1
  • 1
Wave
  • 1,216
  • 1
  • 9
  • 22
  • You can also do it without changing column names with `merge(mydf2,mydf1,by.x=c("date2","company2"),by.y=c("date1","company1"),sort=F)`. To get the columns in the order OP wants, you need to put mydf2 first in the call to `merge(...)`. – jlhoward Mar 07 '14 at 08:09
  • Thank you very much it works!! how can i do the same thing, but keep mydf1 as it is and just attach the class vector from mydf2 (which is shorter) to mydf1? – cptn Mar 09 '14 at 10:20
  • You can do e.g. a match mydf1$class=mydf2$class2[match(paste(mydf1$date1,mydf1$company1,sep=""),paste(mydf2$date2,mydf2$company2,sep=""))] – Wave Mar 12 '14 at 13:27