Generating value for variable if equal to condition

[Adam Gallaher]

Hello, apologies if something is done incorrectly, this is my first time posting to STATALIST.

I am looking to create a new variable based on the condition of other variables. For example, I have an ID which equals 280. That ID has four neighbors, which all also have their own unique IDs. Let's say 281, 282, 283, and 284. Now we can expand this, to another ID lets say 284. It has neighbors of 280, 285, 286, and 287. Further, let's say we have another variable called x which has the value of 2 at ID 280. What I am looking to do is create a value for another variable called y which is equal to 1 if variable x is greater than 0 and my IDs match. Below is a table to help visualize this.

ID	var_x	neighbor1	neighbor2	neighbor3	neighbor4	var_y
280		281	282	283	284	1
284	2	280	285	286	287	0

As you can see, because var_x is greater than 0 and ID 284 and 280 are neighbors, var_y is given a value of 1. This is a much simpler example of what I am looking to do but the logic is the same. So at ID 280 var_x is greater than 0 and neighbor4 equals 284. Thus at ID 284 var_y should equal 1 as seen in the table above. The issue I am having is that I cannot input numbers like one would replace var_y = 1 if ID == 280 because the data set is very large and these values are not consistent.

If anyone has some ideas on how to implement this that would be extremely helpful!

Thank you and all the best,

Adam

[Clyde Schechter]

The question, as stated, is ill-posed. Suppose that ID 282 has var_x = -1. Then ID 280 has this other neighbor with var_x < 0. So then what would var_y be? How will you reconcile situations like this where different neighbors "point in different directions?"

[Adam Gallaher]

Hello Clyde, yes that is a good point and would be valid in other situations. However, for my example I can assure you that var_x will never be -1. This is a counts type data variable. I also understand that the phrasing of my question is not the best, it is difficult to convey in words what I am looking to accomplish without a lengthy post.

[Clyde Schechter]

All right, but suppose var_x = 0, the same difficulty arises.

[Adam Gallaher]

Okay fair, than let say I am looking for when var_x >= 1

[Clyde Schechter]

You are clearly not understanding my question, and I don't know how to ask it any more clearly. Let me see if this restatement will get my point across:

Each ID in your data set has (up to) 4 neighbors. You want to set var_y = 1 if and only if the value of var_x in one of those neighbors is >= 1. But what if there are two neighbors, one of which has var_x >= 1 and the other of which has var_x < 1. One of those neighbors is saying var_y should be 1, and the other is saying var_y should be 0. What do you want to do with that conflict? Changing the condition doesn't solve the problem: the problem will arise with any condition if there are two neighbors, one of which meets the condition and the other does not.

[Adam Gallaher]

I see, I understand what you are saying now and agree that would cause an issue. Maybe if I explain more clearly the structure of var_x you will understand what I am asking. var_x is comprised of counts data, 0,1,2,3,4, etc. It will never be 1.5 or 0.5 or anything in between, which is why I felt comfortable with the var_x >= 1 condition because it will not capture a value that is var_x < 1.

[Clyde Schechter]

I see, I understand what you are saying now and agree that would cause an issue.

Good, so how do you want to deal with that issue when (if) it arises?

[Adam Gallaher]

Okay, if it arises, I would want give var_y a value of 1 when var_x is >= 1, if there is a conflict than var_y will be based on the higher value of var_x. Syntactically, I am not sure how this would be accomplished.

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input int id byte var_x int(neighbor1 neighbor2 neighbor3 neighbor4) byte var_y
280 . 281 282 283 284 1
284 2 280 285 286 287 0
end

reshape long neighbor, i(id) j(which)
expand 2, g(new)
replace neighbor= id if new
gen vx=cond(new, var_x, .)
bys neighbor: egen wanted= max(vx>0 & vx<.)
drop if new
bys id (wanted): replace wanted= wanted[_N]
drop vx new
reshape wide neighbor, i(id) j(which)

If reshape proves to be slow, consider tolong from SSC. The code assigns a value of 1 if any neighbor has a value greater than 0 and a value of 0 if all neighbors have a value of 0, missing values, or a combination of the two.

Res.:

. l

     +--------------------------------------------------------------------------+
     |  id   neighb~1   neighb~2   neighb~3   neighb~4   var_x   var_y   wanted |
     |--------------------------------------------------------------------------|
  1. | 280        281        282        283        284       .       1        1 |
  2. | 284        280        285        286        287       2       0        0 |
     +--------------------------------------------------------------------------+

.

[Adam Gallaher]

Hello Andrew, thank you for offering up your assistance. I believe what you gave will get me close. Below is an example of my data, what I have done is replicated what you did, where I said replace indirect_effects =1 if pointid_sw == 280. From the table you can see that pointid_sw 280 is equal to neighbor1 at pointid_sw 372 when direct impacts is equal to 2. What I want is to give the value of 1 in indirect impacts to each value in the four neighbors that match pointid_sw when direct impacts equals 2, if that makes sense. I tried to use what you wrote, but ran into issues reshaping long, I think because the data is already long when I imported it using the import csv wizard.

---------------------------------------------------------------------------+
| pointi~w neighb~1 neighb~2 neighb~3 neighb~4 direct~s indire~s |
|----------------------------------------------------------------------------|
1. | 280 281 371 372 . 0 1 |
2. | 280 281 371 372 . 0 1 |
3. | 280 281 371 372 . 0 1 |
4. | 280 281 371 372 . 0 1 |
5. | 280 281 371 372 . 0 1 |
|----------------------------------------------------------------------------|
6. | 280 281 371 372 . 0 1 |
7. | 280 281 371 372 . 0 1 |
8. | 281 280 371 372 373 0 . |
9. | 281 280 371 372 373 0 . |
10. | 281 280 371 372 373 0 . |
|----------------------------------------------------------------------------|
11. | 281 280 371 372 373 0 . |
12. | 281 280 371 372 373 0 . |
13. | 281 280 371 372 373 0 . |
14. | 281 280 371 372 373 0 . |
15. | 371 280 281 372 461 0 . |
|----------------------------------------------------------------------------|
16. | 371 280 281 372 461 0 . |
17. | 371 280 281 372 461 0 . |
18. | 371 280 281 372 461 0 . |
19. | 371 280 281 372 461 0 . |
20. | 371 280 281 372 461 0 . |
|----------------------------------------------------------------------------|
21. | 371 280 281 372 461 0 . |
22. | 372 280 281 371 373 0 . |
23. | 372 280 281 371 373 0 . |
24. | 372 280 281 371 373 0 . |
25. | 372 280 281 371 373 0 . |
|----------------------------------------------------------------------------|
26. | 372 280 281 371 373 0 . |
27. | 372 280 281 371 373 2 . |
28. | 372 280 281 371 373 0 . |
29. | 373 281 372 374 463 0 . |
30. | 373 281 372 374 463 0 . |
|----------------------------------------------------------------------------|
31. | 373 281 372 374 463 0 . |
32. | 373 281 372 374 463 0 . |
33. | 373 281 372 374 463 2 . |

[Andrew Musau]

Your data is not usable. Copy and paste the result of

dataex pointi*w neighb* direct*s indire*s in 1/33

[Adam Gallaher]

My apologies, below is the output from the line of code you asked to run.
. dataex pointi*w neighb* direct*s indire*s in 1/33

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(pointid_sw neighbor1 neighbor2 neighbor3 neighbor4 neighbor5 neighbor6 neighbor7 neighbor8) byte(direct_effects indirect_effects)
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 2 .
372 280 281 371 373 462 463 464 . 0 .
373 281 372 374 463 464 465   . . 0 .
373 281 372 374 463 464 465   . . 0 .
373 281 372 374 463 464 465   . . 0 .
373 281 372 374 463 464 465   . . 0 .
373 281 372 374 463 464 465   . . 2 .
end

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

Listed 33 out of 447 observations

[Andrew Musau]

Thanks. No change except that now you have multiple pointid_sw observations. Create a unique id for the reshape.

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(pointid_sw neighbor1 neighbor2 neighbor3 neighbor4 neighbor5 neighbor6 neighbor7 neighbor8) byte(direct_effects indirect_effects)
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
280 281 371 372   .   .   .   . . 0 1
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
281 280 371 372 373   .   .   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
371 280 281 372 461 462 463   . . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 0 .
372 280 281 371 373 462 463 464 . 2 .
372 280 281 371 373 462 463 464 . 0 .
373 281 372 374 463 464 465   . . 0 .
373 281 372 374 463 464 465   . . 0 .
373 281 372 374 463 464 465   . . 0 .
373 281 372 374 463 464 465   . . 0 .
373 281 372 374 463 464 465   . . 2 .
end

gen id=_n
reshape long neighbor, i(id) j(which)
expand 2, g(new)
replace neighbor= pointid_sw if new
gen vx=cond(new, direct_effects, .)
bys neighbor: egen wanted= max(vx>0 & vx<.)
drop if new
bys pointid_sw (wanted): replace wanted= wanted[_N]
drop vx new
reshape wide neighbor, i(id) j(which)

All values of the wanted variable are one because either 372 or 373 is a neighbor for each observation.

Res.:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id int(neighbor1 neighbor2 neighbor3 neighbor4 neighbor5 neighbor6 neighbor7 neighbor8 pointid_sw) byte(direct_effects indirect_effects) float wanted
 1 281 371 372   .   .   .   . . 280 0 1 1
 2 281 371 372   .   .   .   . . 280 0 1 1
 3 281 371 372   .   .   .   . . 280 0 1 1
 4 281 371 372   .   .   .   . . 280 0 1 1
 5 281 371 372   .   .   .   . . 280 0 1 1
 6 281 371 372   .   .   .   . . 280 0 1 1
 7 281 371 372   .   .   .   . . 280 0 1 1
 8 280 371 372 373   .   .   . . 281 0 . 1
 9 280 371 372 373   .   .   . . 281 0 . 1
10 280 371 372 373   .   .   . . 281 0 . 1
11 280 371 372 373   .   .   . . 281 0 . 1
12 280 371 372 373   .   .   . . 281 0 . 1
13 280 371 372 373   .   .   . . 281 0 . 1
14 280 371 372 373   .   .   . . 281 0 . 1
15 280 281 372 461 462 463   . . 371 0 . 1
16 280 281 372 461 462 463   . . 371 0 . 1
17 280 281 372 461 462 463   . . 371 0 . 1
18 280 281 372 461 462 463   . . 371 0 . 1
19 280 281 372 461 462 463   . . 371 0 . 1
20 280 281 372 461 462 463   . . 371 0 . 1
21 280 281 372 461 462 463   . . 371 0 . 1
22 280 281 371 373 462 463 464 . 372 0 . 1
23 280 281 371 373 462 463 464 . 372 0 . 1
24 280 281 371 373 462 463 464 . 372 0 . 1
25 280 281 371 373 462 463 464 . 372 0 . 1
26 280 281 371 373 462 463 464 . 372 0 . 1
27 280 281 371 373 462 463 464 . 372 2 . 1
28 280 281 371 373 462 463 464 . 372 0 . 1
29 281 372 374 463 464 465   . . 373 0 . 1
30 281 372 374 463 464 465   . . 373 0 . 1
31 281 372 374 463 464 465   . . 373 0 . 1
32 281 372 374 463 464 465   . . 373 0 . 1
33 281 372 374 463 464 465   . . 373 2 . 1
end

[Adam Gallaher]

Wonderful! I believe that this worked just how I wanted it to. Thank you very much!!