Stata: Count groups by individuals

Wednesday, April 25, 2012

One friend asked me the following question:

How can I transform the following format into:

id level
1 A
1 A
1 B
2 A
2 B
3 B

this one?

id level #ofA #ofB
1 A 2 1
1 A 2 1
1 B 2 1
2 A 1 1
2 B 1 1
3 B 0 1

Well, I do not think there is one command for this task. This is not very difficult if there are only two groups to count.

The way I achieve this task is:

use "", clear
egen acount = group(level)
gsort +id +acount
by id: egen acount2 = count(acount) if acount==1
bys id: replace acount2 = acount2[_n-1] if acount2==.
replace acount2=0 if acount2==.
bys id: egen bcount2 = count(acount) if acount==2
gsort +id -level
by id: replace bcount2 = bcount2[_n-1] if bcount2==.
replace bcount2=0 if bcount2==. 

Level variable is a string variable, so I use egen to get a group id. If you are interested in learning more details, you can check my previous post:Stata: Create id by group.

After creating a new group id, I sort id and level. How do I count how many As and Bs? I count # of As using egen, but  you may notice that if the value is B, # of A would be missing. So my next step is to fill up this missing with the value of previous record. This is why I sorted data at the beginning.

If there is no A, then replace the value from missing to zero.

The way I count # of Bs is similar. The only difference is sorting.

You may be curious: how about if I have more than three groups? Well, my code only works for two groups, and I have not found a way to count three groups by individuals.

If you have tips or code to achieve the task, please let me know!


One friend shared with me her code:
foreach i in A B C D E F G H I J K L N P Q R S T U V W X Y Z {
bys id: egen nof`i'=sum(level=="`i'")}
Related Posts Plugin for WordPress, Blogger...


No response to “Stata: Count groups by individuals”
Post a Comment | Post Comments (Atom)

Post a Comment