Weighted adjacency matrix with two columns

[Aria Mac]

Dear Statalists,

I have 2 columns (popid and forumid)

I would like to create one type of adjacency matrix to look like this:

1) Both column headings and row headings for the ids, the values inside the matrix equals the number of forums co-attended, zero otherwise. The diagonal equals zero (do not take into account the id itself).

	1	3	8	18	........
1	0	2	0	3	........
3	2	0	6	1	........
8	0	6	0	0	.........
18	3	1	0	0	.......

Can anyone help me with this, please?

I really appreciate your help.

Kind regards,
Aria

* Example generated by -dataex-. To install: ssc install dataex
clear
input double popid float forumid
 1 452
 1 455
 1 431
 1  33
 3  85
 8 455
 8 247
 8  36
18 452
23 443
23   4
23  35
23 455
23 312
26  53
29  39
29 489
36 460
38 232
38  63
42 452
42 357
42 271
46 206
46 310
46 198
60  85
60   9
65 218
65 139
65 468
65 455
65 391
71   4
71 455
71 447
71 437
79 443
79 442
85 394
85 447
86 340
86 327
86 151
86 272
86 259
88 353
90 244
90 503
92 361
end

------------------ copy up to and including the previous line ------------------

Listed 50 out of 1350 observations

[Andrew Musau]

Thanks for the data example. You can loop painfully!

levelsof popid, local(pid)
local n: word count `pid'
mat A= J(`n', `n', 0)
mat colnames A= `pid'
mat rownames A= `pid'
egen gr= group(popid)
tempfile data
save `data'

forval i=1/`n'{
forval j=1/`n'{
use `data', clear
keep if inlist(gr, `i', `j')
sort forumid
gen total= sum(forumid== forumid[_n+1])
local t= total[_N]
mat A[`i',`j'] = `t'
}
}

Res.:

. mat list A

symmetric A[20,20]
     1   3   8  18  23  26  29  36  38  42  46  60  65  71  79  85  86  88  90  92
 1   0
 3   0   0
 8   1   0   0
18   1   0   0   0
23   1   0   1   0   0
26   0   0   0   0   0   0
29   0   0   0   0   0   0   0
36   0   0   0   0   0   0   0   0
38   0   0   0   0   0   0   0   0   0
42   1   0   0   1   0   0   0   0   0   0
46   0   0   0   0   0   0   0   0   0   0   0
60   0   1   0   0   0   0   0   0   0   0   0   0
65   1   0   1   0   1   0   0   0   0   0   0   0   0
71   1   0   1   0   2   0   0   0   0   0   0   0   1   0
79   0   0   0   0   1   0   0   0   0   0   0   0   0   0   0
85   0   0   0   0   0   0   0   0   0   0   0   0   0   1   0   0
86   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
88   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
90   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0
92   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0   0

[Aria Mac]

wow, thank you so much Andrew. Final question, how do I actually view the matrix or print to excel somehow?

[Andrew Musau]

putexcel set matresults
putexcel A1 = matrix(A), names

will export to Excel. You can also save this matrix as a Stata data set, see

help svmat

However, you will need to add a prefix to the column names of the matrix as Stata does not allow numbers as variable names.

[Mike Lacy]

Here's another approach, with no loops and no intermediate matrices. I'd also note that this is fundamentally a social network problem, and so something in the user-written package -nwcommands- (-findit nwcommands-ado-) would probably do this and related things more elegantly. I'm an admirer but not a user of -nwcommands-, so I didn't try it.

// Make a data set of pairs that share forumid
preserve
rename popid popid2
tempfile temp
save `temp'
restore
joinby forumid using `temp'
gen byte attend = 1
// Count co-attendances
collapse (sum) attend , by(popid popid2)
replace attend = 0 if (popid == popid2) // self
// Adjacency format
reshape wide attend , i(popid) j(popid2)
recode attend* (.=0)
// Export
export excel using "YourExcel.xlsx", firstrow(variables) replace

[Aria Mac]

thank you so much Andrew and Mike. Both of these methods worked. I am very grateful