Copy latitude and longitude information for a number of events into another frame containing individuals location

[Felix Kaysers]

Crosspost from Stackoverflow.
I am using Stata 17 on a Windows. I have data in two different datasets. The default dataset is in the .dta format and is cross-section of a sample in year X. The event dataset is imported through an excel file and is a series of events in the same year. I am just interested in the location information of the events.

set obs 10
gen iid = _n
gen latitude_i = runiform()
gen longitude_i = runiform()

frame create events
frame change events
set obs 5
gen eventid = _n
gen latitude = runiform()
gen longitude = runiform()

For each event in the events frame I want to create a two new variables for the location variables (latitudeevent1, longitudeevent1, latitudevent2, longitutdeevent2, etc.) in my default frame. Because the list of events is long, I would like to automate this and not do it manually. The ultimate goal is to compute the distance for each event from the location of the person.

 frame change default
gen latitude1 = latitude from eventid = 1 in frame events
gen longitude1 = longitutde from eventid = 1 in frame events
gen latitude2 = latitude from eventid = 2 in frame events
gen longitude2 = longitude from eventid = 2 in frame events

My general idea is to reshape the events data from long to wide leaving me with just one row. The problem is that reshape needs a unique identifier i which leaves me with a table with more than one row. The second problem is accessing the data in the events frame from the default frame. This is problematic because frval needs a linked dataframe which is not possible and frame put [varlist] in [if], into(*newframename*) only works if you put the data into a new frame.

[William Lisowski]

If you expand your sample data so each individual has an observation for each event, you can then use frlink to access the event latitudes and longitudes in the events frame. In your example data

set seed 666 // make this code reproducible

set obs 10
gen iid = _n
gen latitude_i = runiform()
gen longitude_i = runiform()

frame create events
frame change events
set obs 5
gen eventid = 100+_n
gen latitude = runiform()
gen longitude = runiform()

levelsof eventid, local(events)
local n_events `r(r)'
list, noobs abbreviate(20)

frame change default

expand `n_events'
bysort iid: gen eventid = real(word("`events'",_n))
list if iid==1, noobs abbreviate(20)


frlink m:1 eventid, frame(events) 
frget latitude longitude, from(events)
list if iid==1, noobs abbreviate(20)

. set seed 666 // make this code reproducible

. 
. set obs 10
Number of observations (_N) was 0, now 10.

. gen iid = _n

. gen latitude_i = runiform()

. gen longitude_i = runiform()

. 
. frame create events

. frame change events

. set obs 5
Number of observations (_N) was 0, now 5.

. gen eventid = 100+_n

. gen latitude = runiform()

. gen longitude = runiform()

. 
. levelsof eventid, local(events)
101 102 103 104 105

. local n_events `r(r)'

. list, noobs abbreviate(20)

  +--------------------------------+
  | eventid   latitude   longitude |
  |--------------------------------|
  |     101   .5341839    .7930499 |
  |     102   .8674982    .5709443 |
  |     103    .984127    .0892013 |
  |     104   .4414154    .3871417 |
  |     105   .1817127    .3958606 |
  +--------------------------------+

. 
. frame change default

. 
. expand `n_events'
(40 observations created)

. bysort iid: gen eventid = real(word("`events'",_n))

. list if iid==1, noobs abbreviate(20)

  +------------------------------------------+
  | iid   latitude_i   longitude_i   eventid |
  |------------------------------------------|
  |   1     .1498635      .4745677       101 |
  |   1     .1498635      .4745677       102 |
  |   1     .1498635      .4745677       103 |
  |   1     .1498635      .4745677       104 |
  |   1     .1498635      .4745677       105 |
  +------------------------------------------+

. 
. 
. frlink m:1 eventid, frame(events) 
  (all observations in frame default matched)

. frget latitude longitude, from(events)
  (2 variables copied from linked frame)

. list if iid==1, noobs abbreviate(20)

  +--------------------------------------------------------------------------+
  | iid   latitude_i   longitude_i   eventid   events   latitude   longitude |
  |--------------------------------------------------------------------------|
  |   1     .1498635      .4745677       101        1   .5341839    .7930499 |
  |   1     .1498635      .4745677       102        2   .8674982    .5709443 |
  |   1     .1498635      .4745677       103        3    .984127    .0892013 |
  |   1     .1498635      .4745677       104        4   .4414154    .3871417 |
  |   1     .1498635      .4745677       105        5   .1817127    .3958606 |
  +--------------------------------------------------------------------------+

[Felix Kaysers]

Thank you William Lisowski. While your solution was not right for my case, I am sure it will help other people in the future!

In the end, I just used macros (i.e.

help global

to store the information in a global variable, changed the frames and then generate the latitude and longitutde variables and assigned the respective global macros to them.

global latitude1 = latitude[1]
global latitude2 = latitude[2]
global latitude3 = latitude[3]
global latitude4 = latitude[4]
global latitude5 = latitude[5]

global longitude1 = longitude[1]
global longitude2 = longitude[2]
global longitude3 = longitude[3]
global longitude4 = longitude[4]
global longitude5 = longitude[5]

frame change default

gen latitude1 = $latitude1
gen latitude2 = $latitude2
gen latitude3 = $latitude3
gen latitude4 = $latitude4
gen latitude5 = $latitude5

gen longitude1 = $longitude1
gen longitude2 = $longitude2
gen longitude3 = $longitude3
gen longitude4 = $longitude4
gen longitude5 = $longitude5