Reshape from Wide to Long w/ Time Intervals in the Rows

[Josh Schwartz]

Here peacescale is the variable I want, and the time periods are in YYYYMMDD format. How can I put the above table in long format where each row is the dyad-year?

Thanks!

[Nick Cox]

Please do read and act on https://www.statalist.org/forums/help#stata which spells out that screenshots are a bad idea. In this case, I can't even read your variable names easily and in general screenshots are not amenable to copy and paste or informative about variable types.

The section cited gives positive advice on what to do instead.

[William Lisowski]

I agree with Nick, your data leaves much to the imagination, as does your explanation of what you desire. With that said, it appears that the following may be a start at what you need.

reshape long beginperiod endperiod peacescale, i(dyad) j(period)

perhaps followed by

drop if peacescale==.

[Josh Schwartz]

Sorry about that! I think the data is in the proper format below. 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
...

* 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]

Reposted at https://www.statalist.org/forums/for...m-wide-to-long with followup there.

[Nick Cox]

You have several problems you don't discuss.

1. What is meant by 9999 as month and day.

2. Malformed dates such as 2015999 for 2095 and 19020631 for 2200.

3. What to do when there is a change within a year.

This code gets you some of the way.

reshape long begperiod endperiod peacescale, i(dyad) 
drop if missing(peacescale)
gen beg = real(substr(string(begperiod, "%8.0f"), 1, 4))  
gen end = real(substr(string(endperiod, "%8.0f"), 1, 4))   
list if missing(beg, end)
gen duration = end - beg + 1 
expand duration 
bysort dyad beg : gen year = beg + _n - 1 
list if inlist(dyad, 2020, 2031), sepby(dyad)

[Josh Schwartz]

This is really helpful; thank you Nick!

I'm not sure what's up with the malformed dates, but the month and day don't matter for my purposes, and so your code works fine for that.

When there's a change within a year my preference would be to use the lower value of peacescale. Is there a way to modify your code to do that?

[Nick Cox]

My guess is that you just add

bysort dyad year (peacescale) : drop if _n > 1

but I didn't test it.