Collapse by what?

[Federico Nutarelli]

Hi everyone,

I have the following database which I describe quickly. Basically it is a escalation of each Year according to a certain criterion which is not of interest in this context. As you can see, it can happen that a single year is associated with more than one rescaled value (e.g. see 0 -1 -9 . . . . . 2004, 3 2 -6 . . . . 3.367997 2007 and others):

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(rescaled1 rescaled2 rescaled3 rescaled4 rescaled5 rescaled6 rescaled7 av_t Year)
-11  .  . . . . .         . 2004
  0  .  . . . . .         . 2015
 -5  .  . . . . .         . 2004
 -4  .  . . . . .         . 2005
 -3  .  . . . . .         . 2006
 -2  .  . . . . .  2.610031 2007
 -1  .  . . . . .         . 2008
  0  .  . . . . .         . 2009
  1  .  . . . . .         . 2010
  2  .  . . . . .         . 2011
  0 -1 -9 . . . .         . 2004
  1  0 -8 . . . .         . 2005
  2  1 -7 . . . .         . 2006
  3  2 -6 . . . .  3.367997 2007
  4  3 -5 . . . . 2.9179316 2008
  5  4 -4 . . . .  3.071436 2009
  6  5 -3 . . . .  3.307787 2010
  7  6 -2 . . . .  2.887359 2011
  8  7 -1 . . . . 2.2057533 2012
  9  8  0 . . . .  3.188269 2013
 10  9  1 . . . .  3.572203 2014
 11 10  2 . . . .         . 2015
 -4  .  . . . . .         . 2005
 -3  .  . . . . .         . 2006
 -2  .  . . . . .         . 2007
 -1  .  . . . . . -.2077017 2008
  0  .  . . . . .  .9114361 2009
  1  .  . . . . . .19138622 2010
  2  .  . . . . .         . 2011
  3  .  . . . . . -.6401815 2012
  4  .  . . . . .  2.249567 2013
  5  .  . . . . . 2.2605314 2014
  6  .  . . . . .  .7698421 2015
 -5  .  . . . . .         . 2004
 -4  .  . . . . .         . 2005
 -3  .  . . . . .         . 2006
 -2  .  . . . . .         . 2007
 -1  .  . . . . .         . 2008
  0  .  . . . . .         . 2009
  1  .  . . . . .         . 2010
  2  .  . . . . .         . 2011
  3  .  . . . . .         . 2012
  4  .  . . . . .         . 2013
  5  .  . . . . .         . 2014
  6  .  . . . . . -.1879759 2015
 -5  .  . . . . .         . 2004
 -4  .  . . . . .         . 2005
 -3  .  . . . . .         . 2006
 -2  .  . . . . .         . 2007
 -1  .  . . . . .         . 2008
  0  .  . . . . .         . 2009
  1  .  . . . . .         . 2010
 -7  .  . . . . .         . 2004
 -6  .  . . . . .         . 2005
 -5  .  . . . . .         . 2006
 -4  .  . . . . .         . 2007
 -3  .  . . . . .         . 2008
 -2  .  . . . . .  .9470272 2009
 -1  .  . . . . .         . 2010
  0  .  . . . . .         . 2011
  1  .  . . . . .         . 2012
  2  .  . . . . .         . 2013
  3  .  . . . . .         . 2014
  4  .  . . . . . -3.317956 2015
 -5 -6  . . . . .         . 2004
 -4 -5  . . . . .         . 2005
 -3 -4  . . . . .         . 2006
 -2 -3  . . . . .         . 2007
 -1 -2  . . . . .         . 2008
  0 -1  . . . . .         . 2009
  1  0  . . . . .         . 2010
  2  1  . . . . .         . 2011
  3  2  . . . . .         . 2012
  4  3  . . . . .         . 2013
  5  4  . . . . .         . 2014
  6  5  . . . . .         . 2015
 -6 -7  . . . . .         . 2004
 -5 -6  . . . . .         . 2005
 -4 -5  . . . . .         . 2006
 -3 -4  . . . . . 2.4716835 2007
 -2 -3  . . . . .  3.514191 2008
 -1 -2  . . . . .  3.426614 2009
  0 -1  . . . . .  3.675565 2010
  1  0  . . . . .  3.166685 2011
  2  1  . . . . .         . 2012
  3  2  . . . . .         . 2013
  4  3  . . . . .         . 2014
  5  4  . . . . .         . 2015
-11  .  . . . . .         . 2004
-10  .  . . . . .         . 2005
 -9  .  . . . . .         . 2006
 -8  .  . . . . .  .6483145 2007
 -7  .  . . . . . -.6747503 2008
 -6  .  . . . . .  .6400528 2009
 -5  .  . . . . .         . 2010
 -4  .  . . . . . -.4608803 2011
 -3  .  . . . . .  -.381546 2012
 -2  .  . . . . .         . 2013
 -1  .  . . . . .         . 2014
  0  .  . . . . .         . 2015
end

I am trying to collapse (mean) the variable av_t and specifically I would like to perform a mean by rescaled year, thus ending up with 22 values (a mean for rescaled year -11, another for -10...finally a mean for rescaled year 11). The problem is the "by" option basically. Since I have rescaled the years, to a single year can correspond several rescaled years (e.g. 2011 is rescaled both as 4 and as -1 for idpr 15 in #2). Hence when performing the mean, I would like the collapse to include an av_t in the numerator of the mean of each rescaled year corresponding to it. So for instance, for idpr 15 in issue 2 if av_t in 2011 = 3.4, since 2011 is rescaled both as 4 and -1, 3.4 should be in the numerator (and also counted in the denominator) of both the mean in -1 and in the rescaled year 4.

I am trying to end up with a graphic that looks like the one I attach below.

Can someone please help?

[Federico Nutarelli]

UPDATE: some progress I made is by doing:

stack rescaled1 rescaled2 rescaled3 rescaled4 rescaled5 rescaled6 rescaled7, into(rescaled_year) clear

This gives me the rescaled time value over which I can do the collapse (mean) av_t, by(rescaled_year). Unfortunately I have not yet found a way to properly take the av_t variable into the new stacked dataset (several values should be repeated). Of course if I create 7 av_t variables which are one the copy of the other like this:

su ritiri, meanonly 
quietly forval j = 1/`r(max)' { 
 gen av_t`j' = av_t
}

and then separately stack av_t*:

stack av_t1 av_t2 av_t3 av_t4 av_t5 av_t6 av_t7, into(mean_av_t) clear

by creating a second DB it works: indeed I can merge the two databases together and then collapse by(rescaled_year). But I would like to do it all in one command. I mean, is there an option of stack that allows me to do that?