0

I would like to merge two data using different years.

My data are like the below with more than 1,000 firms with 20 years span.

And I want to merge data to examine firm A's ratio at t's impact on firm A's count at t+1. 

Data A

firm   year   ratio
 A     1990    0.2
 A     1991    0.3
...
 B     1990    0.1

Data B
firm   tyear  count
 A     1990     2
 A     1991     6
...
 B     1990     4

Expected Output

firm year ratio count
 A   1990  0.2   6

Any suggestion for code to merge data? Thank you

Ravi Saroch
  • 934
  • 2
  • 13
  • 28
user12157566
  • 43
  • 4
  • Can you please show what you've tried so far. Also, please post a [reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) – Rohit Oct 16 '19 at 05:46
  • 1
    Use `lag()` with `merge()`. This link should help: https://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right – Kidae Kim Oct 16 '19 at 06:03
  • Does any of the two answers provided, solve the question for you? If yes, then please consider accepting one of them. – hannes101 Oct 17 '19 at 07:14

2 Answers2

1

This should get you started on the dataset, just make sure you do the right lag/lead transformation on the table.

library(data.table)
dt.a.years <- data.table(Year =seq(from = 1990, to = 2010, by = 1L))
dt.b.years <- data.table(Year =seq(from = 1990, to = 2010, by = 1L))

dt.merged <- merge(  x = dt.a.years
      , y = dt.b.years[, .(Year, lag.Year = shift(Year, n = 1, fill = NA))]
      , by.x = "Year"
      , by.y = "lag.Year")

>dt.merged
    Year Year.y
 1: 1990   1991
 2: 1991   1992
 3: 1992   1993
 4: 1993   1994
 5: 1994   1995
 6: 1995   1996
 7: 1996   1997
 8: 1997   1998
 9: 1998   1999
hannes101
  • 2,410
  • 1
  • 17
  • 40
0

How about like this:

A$tyear = A$year+1
AB = merge(A,B,by=c('firm','tyear'),all=F)
webb
  • 4,180
  • 1
  • 17
  • 26