Complicated Reshape from Wide to Long

[Josh Schwartz]

Here peacescale is the variable I want, and the time periods are in YYYYMMDD format. What I want to do is have each row correspond to the value of peacescale for each dyad year. So, for example, dyad 2020 has a peacescale value of 0.75 for 1920-1987 and 1 from 1987-2015. So, I'd want the data to look something like this:

Dyad Year Peacescale
2020 1920 0.75
2020 1921 0.75
...
2020 1987 1
...


Thanks!

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(dyad begperiod1 endperiod1) float peacescale1 long(begperiod2 endperiod2) float peacescale2 long(begperiod3 endperiod3) float peacescale3
2020 19200110 19871002 .75 19871003 20159999   1        .        .   .
2031 19730710 20159999  .5        .        .   .        .        .   .
2040 19020520 19590113  .5 19590114 20141216   0 20141217 20159999 .25
2041 19009999 19150909   0 19150910 19610529  .5 19610530 19640115 .25
2042 19009999 19160504 .25 19160505 20159999  .5        .        .   .
2051 19620806 20159999  .5        .        .   .        .        .   .
2052 19620831 20159999  .5        .        .   .        .        .   .
2053 19661130 20159999  .5        .        .   .        .        .   .
2054 19781103 20159999  .5        .        .   .        .        .   .
2055 19740207 20159999  .5        .        .   .        .        .   .
2056 19790222 20159999  .5        .        .   .        .        .   .
2057 19791027 20159999  .5        .        .   .        .        .   .
2058 19811101 20159999  .5        .        .   .        .        .   .
2060 19830919 20159999  .5        .        .   .        .        .   .
2070 19940101 20159999 .75        .        .   .        .        .   .
2071 19009999 19230923   0 19230924 19931231  .5 19940101 20159999 .75
2080 19810921 20159999  .5        .        .   .        .        .   .
2090 19009999 20159999  .5        .        .   .        .        .   .
2091 19009999 20159999  .5        .        .   .        .        .   .
2092 19009999 20159999  .5        .        .   .        .        .   .
2093 19000101 19090102  .5 19090103 19260923   0 19260924 19330101  -9
2094 19200101 20159999  .5        .        .   .        .        .   .
2095 19031103 19870928  .5 19870929 19891222 .25 19891223  2015999  .5
2100 19009999 20159999  .5        .        .   .        .        .   .
2101 19009999 20020513  .5 20020514 20159999 .25        .        .   .
2110 19660526 20159999  .5        .        .   .        .        .   .
2115 19751125 20159999  .5        .        .   .        .        .   .
2130 19009999 19520815  .5 19520816 19821209 .25 19821210 19851011  -9
2135 19009999 19520824  .5 19520825 20001112 .25 20001113 20159999  .5
2140 19009999 20159999  .5        .        .   .        .        .   .
2145 19009999 20159999  .5        .        .   .        .        .   .
2150 19009999 20159999  .5        .        .   .        .        .   .
2155 19009999 20159999  .5        .        .   .        .        .   .
2160 19009999 20159999  .5        .        .   .        .        .   .
2165 19009999 20159999  .5        .        .   .        .        .   .
2200 19009999 19020631  .5 19020701 19031020 .25 19031021 19411206  .5
2205 19221206 20159999  .5        .        .   .        .        .   .
2210 19170417 20159999  .5        .        .   .        .        .   .
2211 19170417 20159999  .5        .        .   .        .        .   .
2212 19201115 20159999  .5        .        .   .        .        .   .
2220 19009999 20159999  .5        .        .   .        .        .   .
2221 19930528 20159999  .5        .        .   .        .        .   .
2223 19900918 20159999  .5        .        .   .        .        .   .
2225 19170417 20159999  .5        .        .   .        .        .   .
2230 19009999 20159999  .5        .        .   .        .        .   .
2232 19930728 20159999  .5        .        .   .        .        .   .
2235 19170417 20159999  .5        .        .   .        .        .   .
2255 19009999 19150209  .5 19150210 19210702   0 19210703 19391023  .5
2265 19491007 19901002 .25        .        .   .        .        .   .
2290 19170417 20159999  .5        .        .   .        .        .   .
2300 19009999 19171206  .5 19171207 19181112 .25        .        .   .
2305 19190910 20159999  .5        .        .   .        .        .   .
2310 19170417 20159999  .5        .        .   .        .        .   .
2315 19181028 19451130  .5 19451201 19480224  -9 19480225 19891226 .25
2316 19930101 20159999  .5        .        .   .        .        .   .
2317 19930101 20159999  .5        .        .   .        .        .   .
2325 19009999 19411210  .5 19411211 19430903   0 19430904 20159999  .5
2331 19920302 20159999  .5        .        .   .        .        .   .
2338 19640921 20159999  .5        .        .   .        .        .   .
2339 19170417 20159999  .5        .        .   .        .        .   .
2341 20060612 20159999  .5        .        .   .        .        .   .
2343 19930408 20159999  .5        .        .   .        .        .   .
2344 19920115 20159999  .5        .        .   .        .        .   .
2345 19009999 19920522  .5 19920523 20010119   0 20010120 20159999  .5
2346 19920407 20159999  .5        .        .   .        .        .   .
2349 19920115 20159999  .5        .        .   .        .        .   .
2350 19170417 20159999  .5        .        .   .        .        .   .
2352 19600816 20159999  .5        .        .   .        .        .   .
2355 19401213 19440908   0 19440909 19511124  -9 19511125 19891107 .25
2359 19911226 20159999  .5        .        .   .        .        .   .
2360 19009999 19401212  .5 19401213 19440823   0 19440824 19511124  -9
2365 19009999 19170103  .5 19170104 19230701   0 19230702 19410930 .25
2366 19180224 19400616  .5 19910906 20159999  .5        .        .   .
2367 19181118 19400616  .5 19910906 20159999  .5        .        .   .
2368 19180216 19400615  .5 19910906 20159999  .5        .        .   .
2369 19911226 20159999  .5        .        .   .        .        .   .
2370 19911226 20159999  .5        .        .   .        .        .   .
2371 19911226 20159999  .5        .        .   .        .        .   .
2372 19911226 20159999  .5        .        .   .        .        .   .
2373 19911226 20159999  .5        .        .   .        .        .   .
2375 19171206 20159999  .5        .        .   .        .        .   .
2380 19170417 20159999  .5        .        .   .        .        .   .
2385 19170417 20159999  .5        .        .   .        .        .   .
2390 19170417 20159999  .5        .        .   .        .        .   .
2395 19440617 20159999  .5        .        .   .        .        .   .
2402 19750705 20159999  .5        .        .   .        .        .   .
2403 19750712 20159999  .5        .        .   .        .        .   .
2404 19740910 20159999  .5        .        .   .        .        .   .
2411 19681012 20159999  .5        .        .   .        .        .   .
2420 19650218 20159999  .5        .        .   .        .        .   .
2432 19600620 20159999  .5        .        .   .        .        .   .
2433 19600820 20159999  .5        .        .   .        .        .   .
2434 19600801 20159999  .5        .        .   .        .        .   .
2435 19601128 20159999  .5        .        .   .        .        .   .
2436 19601013 20159999  .5        .        .   .        .        .   .
2437 19600807 20159999  .5        .        .   .        .        .   .
2438 19581002 20159999  .5        .        .   .        .        .   .
2439 19600805 20159999  .5        .        .   .        .        .   .
2450 19200630 20159999  .5        .        .   .        .        .   .
2451 19610427 20159999  .5        .        .   .        .        .   .
end

[William Lisowski]

What is your rule for deciding, in your example of dyad 2020, that 1987 is assigned to the interval starting at 19871003 rather than the interval ending at 19871002? Please state this as a general rule applicable to all such cases. You seem to suggest that you want two observations for the same dyad and year, this seems unusual and a potential source of problems in your analysis.

Do I correctly infer that your data run through the end of 2015, and that 20159999 is a code indicating that the final value of peacescale was in effect through the end of 2015 and beyond?

[Clyde Schechter]

So it's a bit more than a -reshape long-. It's a -reshape long- followed by an expand.

First, I note there's an error in your data example. For dyad 2095, the variable endperiod3 is given as 2015999: it is missing a digit. I have corrected that below. But it is likely that there are other similar errors elsewhere in your real data, so you should look for them. I have included a check in the code below to verify that begperiod* and endperiod* are at least 8 digits and the first four digits are a number > 1900.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(dyad begperiod1 endperiod1) float peacescale1 long(begperiod2 endperiod2) float peacescale2 long(begperiod3 endperiod3) float peacescale3
2020 19200110 19871002 .75 19871003 20159999   1        .        .   .
2031 19730710 20159999  .5        .        .   .        .        .   .
2040 19020520 19590113  .5 19590114 20141216   0 20141217 20159999 .25
2041 19009999 19150909   0 19150910 19610529  .5 19610530 19640115 .25
2042 19009999 19160504 .25 19160505 20159999  .5        .        .   .
2051 19620806 20159999  .5        .        .   .        .        .   .
2052 19620831 20159999  .5        .        .   .        .        .   .
2053 19661130 20159999  .5        .        .   .        .        .   .
2054 19781103 20159999  .5        .        .   .        .        .   .
2055 19740207 20159999  .5        .        .   .        .        .   .
2056 19790222 20159999  .5        .        .   .        .        .   .
2057 19791027 20159999  .5        .        .   .        .        .   .
2058 19811101 20159999  .5        .        .   .        .        .   .
2060 19830919 20159999  .5        .        .   .        .        .   .
2070 19940101 20159999 .75        .        .   .        .        .   .
2071 19009999 19230923   0 19230924 19931231  .5 19940101 20159999 .75
2080 19810921 20159999  .5        .        .   .        .        .   .
2090 19009999 20159999  .5        .        .   .        .        .   .
2091 19009999 20159999  .5        .        .   .        .        .   .
2092 19009999 20159999  .5        .        .   .        .        .   .
2093 19000101 19090102  .5 19090103 19260923   0 19260924 19330101  -9
2094 19200101 20159999  .5        .        .   .        .        .   .
2095 19031103 19870928  .5 19870929 19891222 .25 19891223 20159999  .5
2100 19009999 20159999  .5        .        .   .        .        .   .
2101 19009999 20020513  .5 20020514 20159999 .25        .        .   .
2110 19660526 20159999  .5        .        .   .        .        .   .
2115 19751125 20159999  .5        .        .   .        .        .   .
2130 19009999 19520815  .5 19520816 19821209 .25 19821210 19851011  -9
2135 19009999 19520824  .5 19520825 20001112 .25 20001113 20159999  .5
2140 19009999 20159999  .5        .        .   .        .        .   .
2145 19009999 20159999  .5        .        .   .        .        .   .
2150 19009999 20159999  .5        .        .   .        .        .   .
2155 19009999 20159999  .5        .        .   .        .        .   .
2160 19009999 20159999  .5        .        .   .        .        .   .
2165 19009999 20159999  .5        .        .   .        .        .   .
2200 19009999 19020631  .5 19020701 19031020 .25 19031021 19411206  .5
2205 19221206 20159999  .5        .        .   .        .        .   .
2210 19170417 20159999  .5        .        .   .        .        .   .
2211 19170417 20159999  .5        .        .   .        .        .   .
2212 19201115 20159999  .5        .        .   .        .        .   .
2220 19009999 20159999  .5        .        .   .        .        .   .
2221 19930528 20159999  .5        .        .   .        .        .   .
2223 19900918 20159999  .5        .        .   .        .        .   .
2225 19170417 20159999  .5        .        .   .        .        .   .
2230 19009999 20159999  .5        .        .   .        .        .   .
2232 19930728 20159999  .5        .        .   .        .        .   .
2235 19170417 20159999  .5        .        .   .        .        .   .
2255 19009999 19150209  .5 19150210 19210702   0 19210703 19391023  .5
2265 19491007 19901002 .25        .        .   .        .        .   .
2290 19170417 20159999  .5        .        .   .        .        .   .
2300 19009999 19171206  .5 19171207 19181112 .25        .        .   .
2305 19190910 20159999  .5        .        .   .        .        .   .
2310 19170417 20159999  .5        .        .   .        .        .   .
2315 19181028 19451130  .5 19451201 19480224  -9 19480225 19891226 .25
2316 19930101 20159999  .5        .        .   .        .        .   .
2317 19930101 20159999  .5        .        .   .        .        .   .
2325 19009999 19411210  .5 19411211 19430903   0 19430904 20159999  .5
2331 19920302 20159999  .5        .        .   .        .        .   .
2338 19640921 20159999  .5        .        .   .        .        .   .
2339 19170417 20159999  .5        .        .   .        .        .   .
2341 20060612 20159999  .5        .        .   .        .        .   .
2343 19930408 20159999  .5        .        .   .        .        .   .
2344 19920115 20159999  .5        .        .   .        .        .   .
2345 19009999 19920522  .5 19920523 20010119   0 20010120 20159999  .5
2346 19920407 20159999  .5        .        .   .        .        .   .
2349 19920115 20159999  .5        .        .   .        .        .   .
2350 19170417 20159999  .5        .        .   .        .        .   .
2352 19600816 20159999  .5        .        .   .        .        .   .
2355 19401213 19440908   0 19440909 19511124  -9 19511125 19891107 .25
2359 19911226 20159999  .5        .        .   .        .        .   .
2360 19009999 19401212  .5 19401213 19440823   0 19440824 19511124  -9
2365 19009999 19170103  .5 19170104 19230701   0 19230702 19410930 .25
2366 19180224 19400616  .5 19910906 20159999  .5        .        .   .
2367 19181118 19400616  .5 19910906 20159999  .5        .        .   .
2368 19180216 19400615  .5 19910906 20159999  .5        .        .   .
2369 19911226 20159999  .5        .        .   .        .        .   .
2370 19911226 20159999  .5        .        .   .        .        .   .
2371 19911226 20159999  .5        .        .   .        .        .   .
2372 19911226 20159999  .5        .        .   .        .        .   .
2373 19911226 20159999  .5        .        .   .        .        .   .
2375 19171206 20159999  .5        .        .   .        .        .   .
2380 19170417 20159999  .5        .        .   .        .        .   .
2385 19170417 20159999  .5        .        .   .        .        .   .
2390 19170417 20159999  .5        .        .   .        .        .   .
2395 19440617 20159999  .5        .        .   .        .        .   .
2402 19750705 20159999  .5        .        .   .        .        .   .
2403 19750712 20159999  .5        .        .   .        .        .   .
2404 19740910 20159999  .5        .        .   .        .        .   .
2411 19681012 20159999  .5        .        .   .        .        .   .
2420 19650218 20159999  .5        .        .   .        .        .   .
2432 19600620 20159999  .5        .        .   .        .        .   .
2433 19600820 20159999  .5        .        .   .        .        .   .
2434 19600801 20159999  .5        .        .   .        .        .   .
2435 19601128 20159999  .5        .        .   .        .        .   .
2436 19601013 20159999  .5        .        .   .        .        .   .
2437 19600807 20159999  .5        .        .   .        .        .   .
2438 19581002 20159999  .5        .        .   .        .        .   .
2439 19600805 20159999  .5        .        .   .        .        .   .
2450 19200630 20159999  .5        .        .   .        .        .   .
2451 19610427 20159999  .5        .        .   .        .        .   .
end

//    RESHAPE TO LONG
reshape long begperiod endperiod peacescale, i(dyad) j(_j)
drop if missing(peacescale)

//    EXTRACT YEAR AS LEADING 4 DIGITS FROM BEGPERIOD AND ENDPERIOD
foreach x in beg end {
    assert `x'period > 19000000
    gen `x'_year = floor(`x'period/10000)
}
drop begperiod endperiod
order *_year, after(dyad)

//    VERIFY OVERLAPS ARE OK (i.e. JUST THE EDGE YEAR)
by dyad (beg_year), sort: assert beg_year >= beg_year[_n-1] if _n > 1

//    NOW EXPAND TO CREATE ONE OBSERVATION PER YEAR
expand end_year-beg_year
by dyad beg_year, sort: gen year = beg_year + _n - 1
assert year >= beg_year & year < end_year

drop beg_year end_year _j
order year, after(dyad)
isid dyad year, sort

Added: Crossed with #2. I had noted the same issue, and neglected to comment on it, although the code I show offers a check to make sure that the intervals provided do not overlap except for perhaps one endperiod being the same as the next begperiod. I have assumed that when this overlap occurs, the value from the later period applies for the overlap year.

[Nick Cox]

I replied to your earlier version at https://www.statalist.org/forums/for...ls-in-the-rows

Clyde, William and I were working on answers here and in the other thread at about the same time. Some of that duplicated effort could have been spared if you had followed this advice which you were asked to read before posting:

https://www.statalist.org/forums/help#adviceextras gives advice on bumping and related matters.