0
$\begingroup$

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

Improve this question
$\endgroup$

2 Answers 2

Reset to default
0
$\begingroup$

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
...
Improve this answer
$\endgroup$
1
  • $\begingroup$ Yeah, Thanks. That is what i just came up with too. So thanks for confirming this. $\endgroup$
    – Jjakob
    Oct 29, 2019 at 13:18
0
$\begingroup$

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
Improve this answer
$\endgroup$
3
  • $\begingroup$ Will try to run the code and come back if and how it works. Thanks! $\endgroup$
    – Jjakob
    Oct 29, 2019 at 13:18
  • $\begingroup$ The code you provided looks pretty complicated to me. At least for the test data this works, too: ``` df3<-merge(df2,df1,by=c("ID","Year")) ``` $\endgroup$
    – Jjakob
    Oct 29, 2019 at 13:51
  • $\begingroup$ Your codes are much simpler. Thanks. $\endgroup$
    – chan1142
    Oct 29, 2019 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.