Handling bilateral panel data

Johanna Krenz

Join Date: Oct 2022
Posts: 33

Handling bilateral panel data

13 Oct 2022, 06:37

Hi,

I have two quite basic questions, I guess. My panel dataset is on bilateral asset and debt holdings. It is of the following form

issuer	holder	debt	equity	year	GDP_iss
Germany	France	100	40	2001	100
Germany	France	30	30	2002	300
Germany	Spain	20	50	2001	200
Germany	Spain	100	30	2002	100
Germany	Bulgaria	20	40	2001	255
Germany	Bulgaria	30	30	2002	103
France	Germany	20	30	2001	230
France	Germany	100	120	2002	340
France	Spain	234	320	2001	340
France	Spain	30	30	2002	320
Spain	Germany	34	90	2001	455
Spain	Germany	33	20	2002	342
Spain	France	20	330	2001	454
Spain	France	30	23	2002	304
Italy	Germany	34	23	2001	232
Italy	Spain	20	30	2002	402

1. I would like to kick out all observations where I don't have the same observations on both sides, that is in my example, I have data on the country pair Germany - Bulgaria only with Germany being the issuer. On the pairs Italy - Germany and Italy - Spain, I only have information where Italy is the issuer. So I would like to kick out these observations.

2. I want to construct a variable which for each country pair, for each year contains debt and equity from the issuer and the holder side, i.e.. for the Germany France pair, I would like to construct a variable which would be 100+40+20+30 for the year 2001 and 30+30+100+120 for the year 2002.

As I'm new with such kind of analysis, I would very much appreciate your help. Best, Johanna

Tags: None

Andrew Musau

Join Date: Oct 2014
Posts: 10482

13 Oct 2022, 13:10

I assume that you want the condition in #1 to hold on a year to year basis. Otherwise, modify the code.

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 issuer str8 holder int(debt equity year gdp_iss)
"Germany" "France"   100  40 2001 100
"Germany" "France"    30  30 2002 300
"Germany" "Spain"     20  50 2001 200
"Germany" "Spain"    100  30 2002 100
"Germany" "Bulgaria"  20  40 2001 255
"Germany" "Bulgaria"  30  30 2002 103
"France"  "Germany"   20  30 2001 230
"France"  "Germany"  100 120 2002 340
"France"  "Spain"    234 320 2001 340
"France"  "Spain"     30  30 2002 320
"Spain"   "Germany"   34  90 2001 455
"Spain"   "Germany"   33  20 2002 342
"Spain"   "France"    20 330 2001 454
"Spain"   "France"    30  23 2002 304
"Italy"   "Germany"   34  23 2001 232
"Italy"   "Spain"     20  30 2002 402
end

*#1
frame put issuer holder year, into(keep)
frame keep: rename (issuer holder) (holder issuer)
frlink 1:1 issuer holder year, frame(keep)
drop if missing(keep)
frame drop keep
*#2
bys issuer holder: egen tot_debt= total(debt)
bys issuer holder: egen tot_equity= total(equity)
g wanted= tot_debt + tot_equity

Res.:

Code:

. l, sepby(issuer holder)

     +------------------------------------------------------------------------------------------+
     |  issuer    holder   debt   equity   year   gdp_iss   keep   tot_debt   tot_eq~y   wanted |
     |------------------------------------------------------------------------------------------|
  1. |  France   Germany     20       30   2001       230      3        120        150      270 |
  2. |  France   Germany    100      120   2002       340      4        120        150      270 |
     |------------------------------------------------------------------------------------------|
  3. |  France     Spain    234      320   2001       340      5        264        350      614 |
  4. |  France     Spain     30       30   2002       320      6        264        350      614 |
     |------------------------------------------------------------------------------------------|
  5. | Germany    France     30       30   2002       300      8        130         70      200 |
  6. | Germany    France    100       40   2001       100      7        130         70      200 |
     |------------------------------------------------------------------------------------------|
  7. | Germany     Spain     20       50   2001       200     10        120         80      200 |
  8. | Germany     Spain    100       30   2002       100     11        120         80      200 |
     |------------------------------------------------------------------------------------------|
  9. |   Spain    France     30       23   2002       304     13         50        353      403 |
 10. |   Spain    France     20      330   2001       454     12         50        353      403 |
     |------------------------------------------------------------------------------------------|
 11. |   Spain   Germany     34       90   2001       455     14         67        110      177 |
 12. |   Spain   Germany     33       20   2002       342     15         67        110      177 |
     +------------------------------------------------------------------------------------------+

.

Comment

Andrew Musau

Join Date: Oct 2014
Posts: 10482

13 Oct 2022, 14:22

Originally posted by Johanna Krenz View Post

2. I want to construct a variable which for each country pair, for each year contains debt and equity from the issuer and the holder side, i.e.. for the Germany France pair, I would like to construct a variable which would be 100+40+20+30 for the year 2001 and 30+30+100+120 for the year 2002.

#2 asks for totals across pairs in a year, not importer-exporter groups. Here is one way with panel data.

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 issuer str8 holder int(debt equity year gdp_iss)
"Germany" "France"   100  40 2001 100
"Germany" "France"    30  30 2002 300
"Germany" "Spain"     20  50 2001 200
"Germany" "Spain"    100  30 2002 100
"Germany" "Bulgaria"  20  40 2001 255
"Germany" "Bulgaria"  30  30 2002 103
"France"  "Germany"   20  30 2001 230
"France"  "Germany"  100 120 2002 340
"France"  "Spain"    234 320 2001 340
"France"  "Spain"     30  30 2002 320
"Spain"   "Germany"   34  90 2001 455
"Spain"   "Germany"   33  20 2002 342
"Spain"   "France"    20 330 2001 454
"Spain"   "France"    30  23 2002 304
"Italy"   "Germany"   34  23 2001 232
"Italy"   "Spain"     20  30 2002 402
end

 *#1
frame put issuer holder year, into(keep)
frame keep: rename (issuer holder) (holder issuer)
frlink 1:1 issuer holder year, frame(keep)
drop if missing(keep)
*#2
egen gr1= group(issuer holder year)
egen gr2= group(holder issuer year)
egen pair= rowmean(gr1 gr2)
bys pair: g wanted= debt[1]+debt[2]+equity[1]+equity[2]
drop keep gr1 gr2

Res.:

Code:

. l, sepby(pair)

     +--------------------------------------------------------------------+
     |  issuer    holder   debt   equity   year   gdp_iss   pair   wanted |
     |--------------------------------------------------------------------|
  1. | Germany    France    100       40   2001       100      3      190 |
  2. |  France   Germany     20       30   2001       230      3      190 |
     |--------------------------------------------------------------------|
  3. | Germany    France     30       30   2002       300      4      280 |
  4. |  France   Germany    100      120   2002       340      4      280 |
     |--------------------------------------------------------------------|
  5. |   Spain    France     20      330   2001       454      6      904 |
  6. |  France     Spain    234      320   2001       340      6      904 |
     |--------------------------------------------------------------------|
  7. |   Spain    France     30       23   2002       304      7      113 |
  8. |  France     Spain     30       30   2002       320      7      113 |
     |--------------------------------------------------------------------|
  9. | Germany     Spain     20       50   2001       200      9      194 |
 10. |   Spain   Germany     34       90   2001       455      9      194 |
     |--------------------------------------------------------------------|
 11. | Germany     Spain    100       30   2002       100     10      183 |
 12. |   Spain   Germany     33       20   2002       342     10      183 |
     +--------------------------------------------------------------------+

.

Comment

Johanna Krenz

Join Date: Oct 2022

Posts: 33
#4

14 Oct 2022, 04:26

Thank you very much for your prompt help!

I have one more question related to my first question. In my large dataset, for some years, some country-pairs are missing and I only want to keep the country pairs for which I have data for all years. So in the example above suppose that the line
"Germany" "France" 100 40 2001 100 would be missing. Therefore, I would like to kick out the country pair Germany-France alltogether. How can I do that?

With respect to your answer to my second question, I'm afraid that the solution does not work in my large sample (approx. 30 countries over 20 years) as the rowmean(gr1, gr2) is not unique, i.e., in many cases different country-pairs have the same mean. However, I found another way of doing so, using the frame-command:

frame put issuer holder year debt equity, into(keepnew)

frame keepnew: rename (issuer holder debt equity) (holder issuer debt_hold equity_hold)

frlink 1:1 issuer holder year, frame(keepnew) generate(linkkeepnew)

frget (debt_hold equity_hold), from(linkkeepnew)

gen wanted=debt+equity+debt_hold+equity_hold

drop linkkeepnew

This gives me:
issuer holder debt equity year gdp_iss debt_hold equity_hold wanted
Germany France 100 40 2001 100 20 30 190
Germany France 30 30 2002 300 100 120 280
Germany Spain 20 50 2001 200 34 90 194
Germany Spain 100 30 2002 100 33 20 183
France Germany 20 30 2001 230 100 40 190
France Germany 100 120 2002 340 30 30 280
France Spain 234 320 2001 340 20 330 904
France Spain 30 30 2002 320 30 23 113
Spain Germany 34 90 2001 455 20 50 194
Spain Germany 33 20 2002 342 100 30 183
Spain France 20 330 2001 454 234 320 904
Spain France 30 23 2002 304 30 30 113

Sorry, I don't know how you can display the dataset so nicely as you did above.
Comment

Andrew Musau

Join Date: Oct 2014
Posts: 10482

14 Oct 2022, 05:45

Originally posted by Johanna Krenz View Post

Thank you very much for your prompt help!

I have one more question related to my first question. In my large dataset, for some years, some country-pairs are missing and I only want to keep the country pairs for which I have data for all years. So in the example above suppose that the line
"Germany" "France" 100 40 2001 100 would be missing. Therefore, I would like to kick out the country pair Germany-France altogether. How can I do that

Just count within groups.

With respect to your answer to my second question, I'm afraid that the solution does not work in my large sample (approx. 30 countries over 20 years) as the rowmean(gr1, gr2) is not unique, i.e., in many cases different country-pairs have the same mean. However, I found another way of doing so, using the frame-command:

The longer way is to sort the pairs alphabetically, and this should result in a unique combination.

Sorry, I don't know how you can display the dataset so nicely as you did above.

Use CODE delimiters (Click on # in the toolbar).

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 issuer str8 holder int(debt equity year gdp_iss)
"Germany" "France"    30  30 2002 300
"Germany" "Spain"     20  50 2001 200
"Germany" "Spain"    100  30 2002 100
"Germany" "Bulgaria"  20  40 2001 255
"Germany" "Bulgaria"  30  30 2002 103
"France"  "Germany"   20  30 2001 230
"France"  "Germany"  100 120 2002 340
"France"  "Spain"    234 320 2001 340
"France"  "Spain"     30  30 2002 320
"Spain"   "Germany"   34  90 2001 455
"Spain"   "Germany"   33  20 2002 342
"Spain"   "France"    20 330 2001 454
"Spain"   "France"    30  23 2002 304
"Italy"   "Germany"   34  23 2001 232
"Italy"   "Spain"     20  30 2002 402
end

frame put issuer holder year, into(keep)
frame keep: rename (issuer holder) (holder issuer)
frlink 1:1 issuer holder year, frame(keep)
drop if missing(keep)
frame drop keep
*#2
egen gr1= group(issuer holder)
egen gr2= group(holder issuer)
egen pair1= rowmin(gr1 gr2)
egen pair2= rowmax(gr1 gr2)
egen pair= group(pair1 pair2 year), label
*HERE A COMPLETE CASE HAS 4 OBSERVATIONS
bys pair1 pair2: drop if _N!=4
drop gr? pair? keep

Res.:

Code:

. sort pair year

. l, sepby(pair)

     +---------------------------------------------------------------+
     |  issuer    holder   debt   equity   year   gdp_iss       pair |
     |---------------------------------------------------------------|
  1. |   Spain    France     20      330   2001       454   2 5 2001 |
  2. |  France     Spain    234      320   2001       340   2 5 2001 |
     |---------------------------------------------------------------|
  3. |   Spain    France     30       23   2002       304   2 5 2002 |
  4. |  France     Spain     30       30   2002       320   2 5 2002 |
     |---------------------------------------------------------------|
  5. |   Spain   Germany     34       90   2001       455   4 6 2001 |
  6. | Germany     Spain     20       50   2001       200   4 6 2001 |
     |---------------------------------------------------------------|
  7. | Germany     Spain    100       30   2002       100   4 6 2002 |
  8. |   Spain   Germany     33       20   2002       342   4 6 2002 |
     +---------------------------------------------------------------+

Last edited by Andrew Musau; 14 Oct 2022, 06:30.

Comment

Johanna Krenz

Join Date: Oct 2022

Posts: 33
#6

14 Oct 2022, 07:36

Great! Thanks again!

And, as I'm working with the dataset, more questions keep coming up... If I now only want to keep one observation per pair -- doesn't matter which one --, how can I do that? So basically I want to tell Stata, if pair == pair[_n-1] drop the first or second obervation?
Comment
Andrew Musau

Join Date: Oct 2014

Posts: 10482
#7

14 Oct 2022, 07:45

See the tag function of egen.

Code:

h egen

However, the observation index is _n, so

Code:

*keep first bys pair: keep if _n==1 *keep last bys pair: keep if _n==_N
Comment
Johanna Krenz

Join Date: Oct 2022

Posts: 33
#8

21 Oct 2022, 08:02

Another, related question. Let's say, I start with a dataset of the form

Code:

+----------------------+ | country year gdp | |----------------------| 1. | France 2000 59 | 2. | France 2001 55 | 3. | France 2002 80 | |----------------------| 4. | Germany 2000 45 | 5. | Germany 2001 44 | 6. | Germany 2002 47 | |----------------------| 7. | Spain 2000 34 | 8. | Spain 2001 45 | 9. | Spain 2002 49 | +----------------------+

But as I want to calculate GDP correlations I want to turn it into a bilateral dataset, which contains country pairs, i.e.

input str7 country_1 str7 country_2 int(year gdp_1 gdp_2)
"Germany" "France" 2000 45 59
"Germany" "France" 2001 44 55
"Germany" "France" 2002 47 80
"Germany" "Spain" 2000 45 34
end
... and so on

How is that possible?
Comment

Andrew Musau

Join Date: Oct 2014
Posts: 10482

21 Oct 2022, 08:30

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 country int year byte gdp
"France"  2000 59
"France"  2001 55
"France"  2002 80
"Germany" 2000 45
"Germany" 2001 44
"Germany" 2002 47
"Spain"   2000 34
"Spain"   2001 45
"Spain"   2002 49
end

preserve
rename (country gdp) (country2 gdp2)
tempfile two
save `two'
restore
joinby year using `two'
keep if country<country2

Res.:

Code:

. sort country year

. l, sep(0)

     +----------------------------------------+
     | country   year   gdp   country2   gdp2 |
     |----------------------------------------|
  1. |  France   2000    59    Germany     45 |
  2. |  France   2000    59      Spain     34 |
  3. |  France   2001    55    Germany     44 |
  4. |  France   2001    55      Spain     45 |
  5. |  France   2002    80    Germany     47 |
  6. |  France   2002    80      Spain     49 |
  7. | Germany   2000    45      Spain     34 |
  8. | Germany   2001    44      Spain     45 |
  9. | Germany   2002    47      Spain     49 |
     +----------------------------------------+

Comment

Johanna Krenz

Join Date: Oct 2022

Posts: 33
#10

25 Oct 2022, 08:44

Thank you!
Comment

Johanna Krenz

Join Date: Oct 2022
Posts: 33

#11

16 Feb 2023, 03:12

Originally posted by Andrew Musau View Post

Just count within groups.

The longer way is to sort the pairs alphabetically, and this should result in a unique combination.

Use CODE delimiters (Click on # in the toolbar).

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 issuer str8 holder int(debt equity year gdp_iss)
"Germany" "France" 30 30 2002 300
"Germany" "Spain" 20 50 2001 200
"Germany" "Spain" 100 30 2002 100
"Germany" "Bulgaria" 20 40 2001 255
"Germany" "Bulgaria" 30 30 2002 103
"France" "Germany" 20 30 2001 230
"France" "Germany" 100 120 2002 340
"France" "Spain" 234 320 2001 340
"France" "Spain" 30 30 2002 320
"Spain" "Germany" 34 90 2001 455
"Spain" "Germany" 33 20 2002 342
"Spain" "France" 20 330 2001 454
"Spain" "France" 30 23 2002 304
"Italy" "Germany" 34 23 2001 232
"Italy" "Spain" 20 30 2002 402
end

frame put issuer holder year, into(keep)
frame keep: rename (issuer holder) (holder issuer)
frlink 1:1 issuer holder year, frame(keep)
drop if missing(keep)
frame drop keep
*#2
egen gr1= group(issuer holder)
egen gr2= group(holder issuer)
egen pair1= rowmin(gr1 gr2)
egen pair2= rowmax(gr1 gr2)
egen pair= group(pair1 pair2 year), label
*HERE A COMPLETE CASE HAS 4 OBSERVATIONS
bys pair1 pair2: drop if _N!=4
drop gr? pair? keep

Res.:

Code:

. sort pair year

. l, sepby(pair)

+---------------------------------------------------------------+
| issuer holder debt equity year gdp_iss pair |
|---------------------------------------------------------------|
1. | Spain France 20 330 2001 454 2 5 2001 |
2. | France Spain 234 320 2001 340 2 5 2001 |
|---------------------------------------------------------------|
3. | Spain France 30 23 2002 304 2 5 2002 |
4. | France Spain 30 30 2002 320 2 5 2002 |
|---------------------------------------------------------------|
5. | Spain Germany 34 90 2001 455 4 6 2001 |
6. | Germany Spain 20 50 2001 200 4 6 2001 |
|---------------------------------------------------------------|
7. | Germany Spain 100 30 2002 100 4 6 2002 |
8. | Spain Germany 33 20 2002 342 4 6 2002 |
+---------------------------------------------------------------+

Hi,

my dataset is very large now. So when I try to construct the variable "pair" as described above, I get the following error message: too many values for label definer(134);

Anyone knows an alternative or has a solution for that?

Best, Johanna

Comment

Andrew Musau

Join Date: Oct 2014

Posts: 10482
#12

16 Feb 2023, 05:06

Just drop the label option. It is just for your convenience and not needed for the effectiveness of the code.

egen pair= group(pair1 pair2 year), label

to

Code:

egen pair= group(pair1 pair2 year)
Comment

Announcement

Handling bilateral panel data

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment