Generating a variable using two-way data for flows from ID i to ID j and ID j to ID i

[David Heidtman]

Hello all,

I've been using Statalist for about a year to get help on Stata related coding, and this is the first time I had a question which I could not find already answered. I apologize if this has already been addressed elsewhere. What I am trying to do is generate an "industry vertical relatedness" variable for the average commodity flows between two industries. I have data on aggregate flows between 48 industries (see a random sample below). There are 2200 combinations of industries for which I have non-missing values. The first two variables are industry i and industry j, and the variable I am working with is dependence, which is the flow from industry j to industry i divided by total inflows for industry i. The issue I am struggling with is I need to take the average of the flow from industry i to industry j and the flow from industry j to industry i to create my measure of relatedness. So, for example, I would like to take the value of dependence for ff48_1=3 and ff48_2=7 and combine this with the value of dependence for ff48_1=7 and ff48_2=3 to generate V=1/2(dependence_i_j+dependence_j_i). This seems like something that I should be able to accomplish but I can't think of how to set this up. Any help would be greatly appreciated.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(ff48_1 ff48_2) float(agg_flows totalinput dependence)
 7 16     2.5  30679.9 .00008148658
 8 26    14.4  18417.2  .0007818778
11 20       0   7505.2            0
13 18       0  11672.4            0
13 23       0  11672.4            0
20 22  1278.7  47920.3    .02668389
23 33 11745.2  81691.3    .14377542
30  3    45.3 157098.3  .0002883545
30 20   134.9 157098.3   .000858698
34 13  2999.2 416184.9   .007206412
34 32  5030.9 416184.9   .012088137
35 29       0  18598.4            0
35 47    47.2  18598.4  .0025378526
40 24   861.8 165737.9   .005199776
45 21   265.3  83341.9   .003183273
end

[Clyde Schechter]

So, if I understand what you want here, the variables agg_flows and total_input are not relevant for present purposes: they were used to calculate dependence, but right now you just want to average the dependence of each pair with its reversed-order pair. Since your data don't actually contain any pairs where the order is reversed, I took the liberty of creating a data set somewhat resembling the original and adding in some random dependence values for reversed pairs. From there, it's this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(ff48_1 ff48_2) float dependence
16  7    .05252917
26  8    .03891877
20 11    .14250039
18 13    .09850481
23 13    .07228551
22 20    .09925994
33 23    .12416463
 3 30   .005817628
20 30    .02648658
13 34    .06036852
32 34    .09310175
29 35    .13088085
47 35    .09789986
24 40    .12319867
21 45   .009230639
 7 16 .00008148658
 8 26  .0007818778
11 20            0
13 18            0
13 23            0
20 22    .02668389
23 33    .14377542
30  3  .0002883545
30 20   .000858698
34 13   .007206412
34 32   .012088137
35 29            0
35 47  .0025378526
40 24   .005199776
45 21   .003183273
end

gen byte n1 = min(ff48_1, ff48_2)
gen byte n2 = max(ff48_1, ff48_2)
by n1 n2, sort: egen V = mean(dependence)

So the trick is to just realize that if you put ff48_1 and ff48_2 into new variables in row-sorted order, those new variables will uniquely identify each pair with its reverse. Then it's just our old friend -egen, mean()-.

[David Heidtman]

Wow. That is so simple. That's exactly what I needed. Thank you, Clyde, for your timely and helpful response.