Counting other similar group members with specific characteristics by member and group

[Erik Aadland]

Dear Statalist.
I have panel data of individuals in projects. I am trying to generate a variable that for each individual_id and project_id counts the number of other individual_id that are of the same type as the focal individual_id and in addition has a characteristic x. For instance, for a focal individual_id with type_a = 1, how many other individual_id are there in the same project_id that are also type_a = 1 but also has x = 1? It is not required that the focal individual_id has x = 1. This count value is then recorded for focal individual_id and project_id in question in the variable similar_coll. The same logic applies to individual_id and type_b.

Here is a small toy dataset that shows the structure of the data and the wanted count variable similar_coll.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(individual_id project_id type_a type_b x similar_coll)
 1 1 1 0 1 0
 2 1 0 1 0 1
 3 1 0 1 1 0
 4 2 1 0 0 0
 5 2 1 0 0 0
 3 2 0 1 0 0
 1 3 0 1 1 0
 2 3 1 0 0 1
 9 3 1 0 1 0
10 3 1 0 0 1
11 4 0 1 0 0
12 4 0 1 0 0
 2 4 1 0 1 1
 9 4 1 0 1 1
10 4 1 0 0 2
end

The code suggested to me so far generates only 0 values. Here is the suggested (wrong) code:

sort project_id individual_id 

gen similar_coll = 0 

* Count for type_a
bysort project_id (individual_id): replace similar_coll = ///
    sum((type_a == type_a[_n] & x == 1) * (individual_id != individual_id[_n])) ///
    if type_a == 1 

* Count for type_b
bysort project_id (individual_id): replace similar_coll = ///
    sum((type_b == type_b[_n] & x == 1) * (individual_id != individual_id[_n])) ///
    if type_b == 1 

* Clean similar_coll to ensure it only contains relevant counts
bysort project_id individual_id: replace similar_coll = similar_coll - 1 if similar_coll > 0

Any help with this code would be greatly appreciated.
Thanks!

[Clyde Schechter]

In words, you ask for two results, one concerning a match on type_a, and the other on type_b. But somehow you have reduced it to a single result in the variable similar_coll. It looks to me as if similar_coll gives the results for type_a and you have ignored type_b here. Anyway, the following code gives two results, wanted_a and wanted_b, that deal with the two types separately, and my wanted_a variable matches your similar_coll variable.

isid project_id individual_id

preserve
rename (individual_id type_a type_b x) =_U
tempfile copy
save `copy'

restore
joinby project_id using `copy'
drop if individual_id == individual_id_U

foreach z in a b {
    by project_id, sort: gen similar_`z' = (type_`z' == type_`z'_U) & x_U == 1
    by project_id individual_id, sort:egen wanted_`z' = total(similar_`z')
}
drop *_U similar_a similar_b

[Erik Aadland]

Dear Clyde.
Thank you so much for this.
The code works very well. It also seems to be quite flexible. I added another data column with type_c, added c to your code, and it works too. The wanted_a (similar_coll) variable is still correct. So the code appears to scale well with more types added. This extra flexibility is great.

Here is the modified data structure:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(individual_id project_id type_a type_b type_c x similar_coll)
 1 1 1 0 0 1 0
 2 1 0 1 0 0 1
 3 1 0 1 0 1 0
 3 2 0 1 0 0 0
 4 2 1 0 0 0 0
 5 2 1 0 0 0 0
 1 3 0 1 0 1 0
 2 3 1 0 0 0 1
 9 3 1 0 0 1 0
10 3 1 0 0 0 1
 2 4 1 0 0 1 1
 9 4 1 0 0 1 1
10 4 1 0 0 0 2
11 4 0 1 0 0 0
12 4 0 1 0 0 0
13 5 0 0 1 0 1
14 5 0 0 1 1 0
end

Here is the tweaked code:

isid project_id individual_id

preserve
rename (individual_id type_a type_b type_c x) =_U
tempfile copy
save `copy'

restore
joinby project_id using `copy'
drop if individual_id == individual_id_U

foreach z in a b c {
    by project_id, sort: gen similar_`z' = (type_`z' == type_`z'_U) & x_U == 1
    by project_id individual_id, sort:egen wanted_`z' = total(similar_`z')
}
drop *_U similar_a similar_b similar_c

egen tag = tag(individual_id project_id) 
keep if tag == 1

Just one follow up question. You mentioned that the code gives two results, wanted_a and wanted_b, that deal with the two types separately, but when I look at the output the values for wanted_a and wanted_b they appear to be identical in their separate columns? The values in the wanted_c column I don't understand except for in project 5 where type_c is observed (and the wanted_c value is correct).

Thanks again, Clyde. This is very helpful.

Kind Regards,
Erik

[Clyde Schechter]

when I look at the output the values for wanted_a and wanted_b they appear to be identical in their separate columns?

In the example data, type_a and type_b are mutually exclusive and exhaustive categories. More simply put, a person is type_a if and only if they are not type_b. Consequently, in that data, if two people are of the same value of type_a, then they will always be of the same value of type_b. And, of course, x == 1 will be the same regardless of whether the person is type_a or type_b. So, wanted_a and wanted_b will always be the same in this data. I had noticed this when I developed the code, but decided that I would still do the separate calculations for type a and type b because I thought that perhaps the example data having this type_a == !type_b relationship might not be representative of the entire data set.

The values in the wanted_c column I don't understand except for in project 5 where type_c is observed (and the wanted_c value is correct).

In the projects other than 5, type_c is zero for all people. Therefore each person on that project has the same value of type_c, namely 0, as every other person on the project. Consequently a person is designated as similar in these projects if and only if x == 1. So the results in those projects is nothing other than the total number of other people in the same project, if the focal individual has x = 1, and 0 otherwise.

[Erik Aadland]

Dear Clyde.
Thank you for the additional explanation. I appreciate it very much.

Best regards,
Erik