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

<pre><code>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
...
</code></pre>

CC BY-SA 4.0
2019-10-29T13:18:07.047Z
Yeah, Thanks. That is what i just came up with too. So thanks for confirming this.
Jjakob
CC BY-SA 4.0
2019-10-28T03:30:11.137Z
2019-10-28T03:30:11.137Z
Art
<p>Let the two dataframes be <code>DF1</code> and <code>DF2</code>. To merge them into a company-state level panel data (repeated the variables in <code>DF1</code>) as Art's answer suggests:</p>

<pre><code>idx &lt;- match(paste(DF2<span class="math-container">$ID, DF2$</span>Year), paste(DF1<span class="math-container">$ID, DF1$</span>Year))
for (v in c('x1', 'x2', 'x3')) DF2[[v]] &lt;- DF1[idx,v]
</code></pre>

<p>Full example:</p>

<pre><code>DF1 &lt;- 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 &lt;- 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 &lt;- match(paste(DF2<span class="math-container">$ID, DF2$</span>Year), paste(DF1<span class="math-container">$ID, DF1$</span>Year))
for (v in c('x1', 'x2', 'x3')) DF2[[v]] &lt;- 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
</code></pre>

CC BY-SA 4.0
2019-10-29T13:18:38.137Z
Will try to run the code and come back if and how it works. Thanks!
Jjakob
CC BY-SA 4.0
2019-10-29T13:51:17.213Z
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"))
```
Jjakob
CC BY-SA 4.0
2019-10-29T22:20:52.203Z
Your codes are much simpler. Thanks.
chan1142
CC BY-SA 4.0
2019-10-29T00:51:51.380Z
2019-10-29T00:51:51.380Z
chan1142
<p>I have two data frames with panel data. The first contains data on companies on a yearly basis. The layout is as below:</p>

<pre><code>ID Year x1  x2  x3
A1 2000 10  11  12
A2 2000 13  25  20
A1 2001 13  58  13
</code></pre>

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

<pre><code>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
...
</code></pre>

<p>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...</p>

<p>I am very grateful for any idea on this!</p>

<p>p.s. using R</p>

<paneldata><r>
Merging Panel data with two different splits. Is it possible?
Jjakob
# Merging Panel data with two different splits. Is it possible?

(Asked by: username_0 on 2019-10-27)

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

**Tags:** paneldata, r


---

## 2 answers

### Answer by username_1 on 2019-10-28. Score: 0 | Accepted answer

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
...

```

> **Comments:**
> **username_0**: Yeah, Thanks. That is what i just came up with too. So thanks for confirming this.  

---

### Answer by username_2 on 2019-10-29. Score: 0

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

```

> **Comments:**
> **username_0**: Will try to run the code and come back if and how it works. Thanks!  
> **username_0**: 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")) ```  
> **username_2**: Your codes are much simpler. Thanks.  

---

