Merging Panel data with two different splits. Is it possible?

Question

I have two data frames with panel data. The first contains data on companies on a yearly basis. The layout is as below:

ID Year x1  x2  x3
A1 2000 10  11  12
A2 2000 13  25  20
A1 2001 13  58  13

and so on. The second data set contains the same companies, but data that is split by state AND year.

ID  Year  State  xx1  xx2  xx3
A1  2000   AL    581  135  1.22
A1  2000   FL    584  51    1.5
A2  2000   AL    5138 84   1.7
...

Now, I am wondering if and how I can merge these two? Economically, it makes sense to have these different data panels as some data is only available on an overall company level (total assets, debt, income) and some data can be split for example: revenue in state; customers in state and so on...

I am very grateful for any idea on this!

p.s. using R

Answer 1

I would just merge them horizontally... so that the main dataset is the second one, and you include country-wide statistics into each row:

ID  Year  State  xx1  xx2  xx3   x1  x2  x3
A1  2000   AL    581  135  1.22  10  11  12
A1  2000   FL    584  51   1.5   10  11  12
A2  2000   AL    5138 84   1.7   13  25  20
...

Answer 2

Let the two dataframes be DF1 and DF2. To merge them into a company-state level panel data (repeated the variables in DF1) as Art's answer suggests:

idx <- match(paste(DF2$ID, DF2$Year), paste(DF1$ID, DF1$Year))
for (v in c('x1', 'x2', 'x3')) DF2[[v]] <- DF1[idx,v]

Full example:

DF1 <- data.frame(ID=c('A1','A2','A1'), Year=c(2000,2000,2001),
                  x1=c(10,13,13), x2=c(11,25,58), x3=c(12,20,13))
# DF1
#   ID Year x1 x2 x3
# 1 A1 2000 10 11 12
# 2 A2 2000 13 25 20
# 3 A1 2001 13 58 13

DF2 <- data.frame(ID=c('A1','A1','A2'), Year=c(2000,2000,2000),
                  State=c('AL','FL','AL'),
                  xx1=c(581,584,5138), xx2=c(135,51,84), xx3=c(1.22,1.5,1.7))
# DF2
#   ID Year State  xx1 xx2  xx3
# 1 A1 2000    AL  581 135 1.22
# 2 A1 2000    FL  584  51 1.50
# 3 A2 2000    AL 5138  84 1.70

idx <- match(paste(DF2$ID, DF2$Year), paste(DF1$ID, DF1$Year))
for (v in c('x1', 'x2', 'x3')) DF2[[v]] <- DF1[idx,v]

DF2
#   ID Year State  xx1 xx2  xx3 x1 x2 x3
# 1 A1 2000    AL  581 135 1.22 10 11 12
# 2 A1 2000    FL  584  51 1.50 10 11 12
# 3 A2 2000    AL 5138  84 1.70 13 25 20