Tracking Individual Observations Moving Between Groups Over Time

[Adrian Pietrzak]

Hi all,

I am working with a panel dataset which describes buildings zoning categories. Here's a simplified overview of the data for illustrative purposes:

building_id	zoning	zoning_category	rezoned	year
001	R6	54	0	2010
001	R6	54	0	2011
001	R6	54	0	2012
001	R7	58	1	2013
001	R7	58	0	2014
002	R6	54	0	2010
002	R6	54	0	2011
002	R6	54	0	2012
002	R6	54	0	2013

My zoning_category variable is just the result of STATA's encode command - which helped me create the dummy rezoned to track changes in the panel overtime. However, I would also like to try to track changes in individuals between zoning categories to understand what types of individual buildings are being moved to which types of zoning groups. In reality, I have 134 groups of zoning categories and 9 million observations over 10 years. Taking the difference between zoning_category groups (the only thing I could think of) won't work since the differences won't be unique. In the end, I'm looking for create a table which lists the frequency of individual observations moving between groups for every possible combination of group to group movements which actually occurred, for example:

zoning initial	zoning post	no. of observations
R6	R7	142
R6	R8	3
R6	R2	2
R6	R3	42

Does anyone have any suggestions on how I could tackle this? Apologies if something like this has been asked before.

[Clyde Schechter]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3 building_id str2 zoning byte(zoning_category rezoned) int year
"001" "R6" 54 0 2010
"001" "R6" 54 0 2011
"001" "R6" 54 0 2012
"001" "R7" 58 1 2013
"001" "R7" 58 0 2014
"002" "R6" 54 0 2010
"002" "R6" 54 0 2011
"002" "R6" 54 0 2012
"002" "R6" 54 0 2013
end

encode building_id, gen(id)
xtset id year

gen zoning_post = F1.zoning_category
contract zoning_category zoning_post
drop if inlist(zoning_post, zoning_category, .)
rename zoning_category zoning_initial

In the future, when showing data examples, please use the -dataex- command to do so, as I have in this response. If you are running version 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Zahid Khan]

input float new_id byte time float(mw_worker_below mw_worker mw_worker_above employed)
1 1 0 0 1 1
1 2 0 0 1 0
1 3 0 0 1 1
2 1 0 0 1 1
2 2 0 0 1 0
2 3 0 0 1 1
3 2 0 0 1 0
4 2 0 0 1 1
5 2 0 0 1 0
6 2 0 0 1 1
6 3 0 0 1 0
7 1 0 0 1 1
7 2 0 0 1 0
7 3 0 0 1 1
8 1 0 0 1 1
8 2 0 0 1 1
8 3 0 0 1 1
9 2 0 0 1 0
10 2 0 0 1 1
11 1 0 0 1 1
11 2 0 0 1 0
11 3 0 0 1 1
12 2 0 0 1 0
13 2 0 0 1 0
14 1 0 0 1 1
14 2 0 0 1 0
14 3 0 0 1 1
15 1 0 0 1 1
15 2 0 0 1 0
15 3 0 0 1 0
16 1 0 0 1 1
16 2 0 0 1 0
16 3 0 0 1 1
17 2 0 0 1 0
18 1 0 0 1 1
18 2 0 0 1 0
18 3 0 0 1 1
19 1 0 0 1 1
19 3 0 0 1 1
20 2 0 0 1 0
21 1 0 0 1 1
21 2 0 0 1 0
21 3 0 0 1 1
22 2 0 0 1 0
23 2 0 0 1 0
24 2 0 0 1 0
25 1 0 0 1 1
25 2 0 0 1 1
25 3 0 0 1 1
26 2 0 0 1 1
27 2 0 0 1 0
28 2 0 0 1 0
29 2 0 0 1 1
30 2 0 0 1 0
31 1 0 0 1 1
31 2 0 0 1 1
31 3 0 0 1 1
32 1 0 0 1 1
32 2 0 0 1 0
32 3 0 0 1 0
33 2 0 0 1 0
34 2 0 0 1 0
35 2 0 0 1 0
36 1 0 0 1 1
36 2 0 0 1 1
36 3 0 0 1 1
37 1 0 0 1 1
37 3 0 0 1 1
38 1 0 0 1 1
38 2 0 0 1 0
38 3 0 0 1 1
39 1 0 0 1 1
39 2 0 0 1 1
40 3 0 0 1 1
41 1 0 0 1 1
41 3 0 0 1 1
42 1 0 0 1 1
42 2 0 0 1 0
42 3 0 0 1 1
43 1 0 0 1 1
43 3 0 0 1 1
44 1 0 0 1 1
44 3 0 0 1 1
45 1 0 0 1 1
45 2 0 0 1 1
45 3 0 0 1 1
48 1 0 0 1 1
48 3 0 0 1 1
49 1 0 0 1 1
49 2 0 0 1 1
49 3 0 0 1 1
50 1 0 0 1 1
50 3 0 0 1 1
51 1 0 0 1 1
51 2 0 0 1 0
51 3 0 0 1 1
52 1 0 0 1 1
52 2 0 0 1 1
52 3 0 0 1 1
54 1 0 0 1 1
end
[/CODE]
------------------

Dear all, I want to do something similar to #1, However, I cannot apply what has been shared in #2. I want to know how many observations in the three dummy variables mw_worker_below mw_worker mw_worker_above in time==1 are employed==1 in time==2. I can find total of these three categories if employed==1 in time 2 but i want to know how many of those who were in mw_worker category==1 in time=1 are in employed=1 in time==2

[Carlo Lazzaro]

Zahid:
you may want to try:

. bysort new_id (time): gen counter=sum( employed) if time<=2


. replace counter=. if counter<2

. list if counter==2

     +---------------------------------------------------------------------+
     | new_id   time   mw_wor~w   mw_wor~r   mw_wor~e   employed   counter |
     |---------------------------------------------------------------------|
 16. |      8      2          0          0          1          1         2 |
 48. |     25      2          0          0          1          1         2 |
 56. |     31      2          0          0          1          1         2 |
 65. |     36      2          0          0          1          1         2 |
 73. |     39      2          0          0          1          1         2 |
     |---------------------------------------------------------------------|
 85. |     45      2          0          0          1          1         2 |
 90. |     49      2          0          0          1          1         2 |
 98. |     52      2          0          0          1          1         2 |
     +---------------------------------------------------------------------+

. 

.