Creating Variable Using Average Value of Other Variables

[Chirag Yadav]

I need to create a variable for rainfall deviation from average (for the time period 2005-2010). I have monthly rainfall data for the region for 40 years. So, for any year I need to take the total rainfall in that year and subtract the average rainfall for the past 30 years to create the deviation variable. I have been trying to figure out how to get that average for the last 30 years for each year and each district.

Another problem is that the data is missing for some months. If I have data only for July-Aug in 2005, I would need a 30-year average for July-Aug for the last 30 years. But if I have data for all months in the year, I need the 30 year average for the entire year. So, I can't have one column for annual rainfall and do this calculation. This is what the data looks like:

* Example generated by -dataex-. For more info, type help dataex
clear
input str20 STATE str16 DISTRICT str5 YEAR double(JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC)
"Andhra Pradesh" "ANANTAPUR" "1970"    .4    .8    0   11 102.4  20.6  25.1  66.9 143.5 102.8   1.6     0
"Andhra Pradesh" "ANANTAPUR" "1971"   1.3     2  3.2   12  45.7  16.8  17.4  88.9 113.8 152.7   6.9   1.2
"Andhra Pradesh" "ANANTAPUR" "1972"     0     0    0 10.6  93.6  42.7  10.8   6.8   136 144.2  37.2   9.6
"Andhra Pradesh" "ANANTAPUR" "1973"     0     0    0  2.3    17  67.2    27  86.8 181.3 191.1  26.5   2.4
"Andhra Pradesh" "ANANTAPUR" "1974"     0     0    0  9.6 108.8  44.1  52.6  21.9 207.4 142.4   3.1     0
"Andhra Pradesh" "ANANTAPUR" "1975"     0     0  9.9  2.3  43.5  24.6   129  79.4 129.7 248.7  72.5   1.3
"Andhra Pradesh" "ANANTAPUR" "1976"     0     0    0 22.5  12.1  45.6  31.3 116.5  25.5  39.1  26.6     0
"Andhra Pradesh" "ANANTAPUR" "1977"     0     0  4.4 22.3 126.1    74    62  94.4  97.4 103.2  59.3     0
"Andhra Pradesh" "ANANTAPUR" "1978"     0   4.8  1.2 19.5  41.8  28.8  74.6  35.6 158.5  50.9  63.5  22.8
"Andhra Pradesh" "ANANTAPUR" "1979"     0  12.7   .3  6.8  36.2  42.2  45.3  57.5 242.9  45.2 110.2     0
"Andhra Pradesh" "ANANTAPUR" "1980"     0     0    3 13.8    47  48.9  32.7  36.2  96.6  54.4  46.2   4.7
"Andhra Pradesh" "ANANTAPUR" "1981"     2     0 12.1  7.5    40  26.6  64.4  79.9 268.2  91.4  31.3   3.7
"Andhra Pradesh" "ANANTAPUR" "1982"     0     0   .1 10.5  30.9  68.8  44.3  10.1 136.2  70.2 118.6     0
"Andhra Pradesh" "ANANTAPUR" "1983"     0     0    0   .1  54.8  80.6    22 102.4 179.6  44.5   2.2  20.8
"Andhra Pradesh" "ANANTAPUR" "1984"     0   2.9 37.4 14.9  10.6   8.6 107.8  11.3  77.8  67.8   5.4   6.5
"Andhra Pradesh" "ANANTAPUR" "1985"    .9     0  3.3 13.9  28.9  30.1  56.8  21.3  52.9  75.6    15   2.1
"Andhra Pradesh" "ANANTAPUR" "1986"  10.2  12.6    0  6.9  15.8  62.5  18.3  42.2 131.3  44.6  39.1   4.2
"Andhra Pradesh" "ANANTAPUR" "1987"     0     0    1   .4  20.9  72.3   7.5  94.4  66.8 177.5  32.7  13.8
"Andhra Pradesh" "ANANTAPUR" "1988"     0    .6  9.1 37.4  98.3  14.4 141.9 210.6 272.9  20.3   1.9  18.4
"Andhra Pradesh" "ANANTAPUR" "1989"     0     0 10.9  7.6  10.8  46.1 265.1  22.3 201.3  21.5   4.3   2.1
"Andhra Pradesh" "ANANTAPUR" "1990"    .1     0    5  1.1 101.4    49  30.9  44.9  44.1 108.7  62.3   6.4
"Andhra Pradesh" "ANANTAPUR" "1991"   9.6     0  8.1 26.9  53.1 120.5    20  26.8  82.2 173.9    58     1
"Andhra Pradesh" "ANANTAPUR" "1992"     0     0    0 20.2  51.9  61.9  44.1    63    76  78.8  82.6     0
"Andhra Pradesh" "ANANTAPUR" "1993"     0     0 10.1 13.7    30  67.4  42.7  88.5  91.6 148.5  37.5  54.4
"Andhra Pradesh" "ANANTAPUR" "1994"  19.9   3.4    0 23.8  38.4  22.9  46.9  35.7  32.3 173.4  26.9   2.3
"Andhra Pradesh" "ANANTAPUR" "1995"   6.2   5.8  3.9  9.6  43.5  33.9 108.2 131.4  95.3  82.6  12.2     0
"Andhra Pradesh" "ANANTAPUR" "1996"     0     0    0 33.1    28 166.2  47.7 112.1 185.6 184.1  11.2  22.6
"Andhra Pradesh" "ANANTAPUR" "1997"  14.8     0 21.7 23.5    17  74.2  13.6  64.2 175.3  46.4  50.3    30
"Andhra Pradesh" "ANANTAPUR" "1998"     0    .7  2.6 19.9  32.2  30.2 112.7 167.5   114   131  38.3   9.6
"Andhra Pradesh" "ANANTAPUR" "1999"     0   9.9    3 10.1  59.8  30.5  42.8  85.7 126.4 104.5    25    .8
"Andhra Pradesh" "ANANTAPUR" "2000"     0    53    0 17.5  55.8  76.9  51.4 173.8  76.2 157.4   8.4   7.2
"Andhra Pradesh" "ANANTAPUR" "2001"   9.6     0 13.1 54.7  15.8  19.8  21.5  68.5 242.2   231  15.8   6.4
"Andhra Pradesh" "ANANTAPUR" "2002"  17.1   6.2  2.3 30.9  84.6  33.9  20.6  36.7  60.2 107.6  14.4   5.5
"Andhra Pradesh" "ANANTAPUR" "2003"     0   3.7 14.5   15   2.6  23.1  44.9  61.4  42.2 171.5   8.1    .7
"Andhra Pradesh" "ANANTAPUR" "2004"  10.7     0   19 32.5   129  20.2 102.2  13.7 113.6  45.9   3.9     0
"Andhra Pradesh" "ANANTAPUR" "2005"   5.3   8.3  7.9  8.2  52.6  46.9 143.7  99.7 136.3 191.3  51.1  16.4
"Andhra Pradesh" "ANANTAPUR" "2006"     0     0 40.4 11.6  62.2    79    21    16  87.2  44.7  76.6    .9
"Andhra Pradesh" "ANANTAPUR" "2007"     0     0    0   .1   1.8 154.3  69.5 107.6 198.1  95.4    10  17.3
"Andhra Pradesh" "ANANTAPUR" "2008"     0  21.1   85   .4  57.9  63.1  82.2 110.5 176.8   112  51.3    .6
"Andhra Pradesh" "ANANTAPUR" "2009"    .4     0  6.5  4.9  73.6  55.3   9.3 103.5 189.7  62.2  69.9   4.1
"Andhra Pradesh" "ANANTAPUR" "2010"   5.1    .9  1.9 34.3  85.2  75.7 106.5   149  66.7  72.8 150.4   3.7
"Andhra Pradesh" "CHITTOR"   "1970"    .3  32.6   .9 10.2  70.7  53.9 143.4 167.8 117.9 124.1   148   1.3
"Andhra Pradesh" "CHITTOR"   "1971"     8   8.3   21 36.8  64.9  29.5    79 100.1 132.8 192.5  80.7  47.5
"Andhra Pradesh" "CHITTOR"   "1972"   7.3    .3    0 10.6 117.3 112.8  17.7    44 125.5 333.2 166.9 153.5
"Andhra Pradesh" "CHITTOR"   "1973"     0     0   .2    1  47.7  49.1  85.6 113.4   141 164.8  59.1  83.1
"Andhra Pradesh" "CHITTOR"   "1974"     0     0    0 10.4  51.2  68.5 139.9  53.2 183.5 217.4  44.2     0
"Andhra Pradesh" "CHITTOR"   "1975"   1.9     0    7  2.1    65  56.7 172.1 123.9 164.3 262.9 200.8   6.1
"Andhra Pradesh" "CHITTOR"   "1976"   2.2     0    0  6.2  17.9  68.8   143 220.6  42.6 238.5 367.2   7.7
"Andhra Pradesh" "CHITTOR"   "1977"     1    .9  1.5 32.2    59  78.6    76 132.7  61.4 313.8 272.6   1.6
"Andhra Pradesh" "CHITTOR"   "1978"   2.8    11   .4 42.2  34.2  43.9  98.3  54.8 195.6 113.1 204.4 168.1
"Andhra Pradesh" "CHITTOR"   "1979"   6.4  33.7    0 10.9  69.5  41.4  99.1  82.3 151.3  72.2 387.4    25
"Andhra Pradesh" "CHITTOR"   "1980"    .1     0    2  6.9  47.7  32.9  70.3 122.7  42.5  68.4 269.2  49.6
"Andhra Pradesh" "CHITTOR"   "1981"  12.2     0 24.1 10.1  64.3  70.9 116.9 114.1 183.9 190.4  49.4  81.9
"Andhra Pradesh" "CHITTOR"   "1982"    .3     0  1.3 13.2  34.3    68  71.7  44.4   117 156.7 180.3    .1
"Andhra Pradesh" "CHITTOR"   "1983"     0    .4  7.7  7.7  50.1  63.7 131.1 178.2 289.2 169.8  56.1 186.9
"Andhra Pradesh" "CHITTOR"   "1984"  15.4 156.9 54.4 16.8  14.4  24.5 150.6  17.5 181.1 109.8 242.9  79.7
"Andhra Pradesh" "CHITTOR"   "1985"  29.9     0  3.6 12.7  15.3  62.9 175.4  90.6   108 102.9 262.9  68.4
"Andhra Pradesh" "CHITTOR"   "1986" 117.9  25.6    0  8.7  27.4  54.6  58.5  62.7 128.5 129.2   127  13.5
"Andhra Pradesh" "CHITTOR"   "1987"     4     0   22 17.3  31.7  57.3  45.2 127.6  96.5 210.3 140.5 159.5
"Andhra Pradesh" "CHITTOR"   "1988"   1.5     0  6.7 59.9  69.8  17.8 161.4 216.5 217.2  67.5 124.1    59
"Andhra Pradesh" "CHITTOR"   "1989"     0     0 15.8  3.2  26.1  52.3   235  25.8 132.4  80.2 116.4  92.3
"Andhra Pradesh" "CHITTOR"   "1990"   3.2   9.5 25.2 16.8 175.2    23  65.2 108.4 211.4 192.6 218.2  16.6
"Andhra Pradesh" "CHITTOR"   "1991"  18.7     0  5.8 30.8  30.9 178.9  79.7 141.1 114.8 303.2 331.7  14.4
"Andhra Pradesh" "CHITTOR"   "1992"  15.7     0    0 17.9  54.8  44.4    72  49.6   100 131.8 202.2     9
"Andhra Pradesh" "CHITTOR"   "1993"     .    16 15.9 13.4  50.4  65.5 103.2 129.2 178.5 203.4 216.1 123.3
"Andhra Pradesh" "CHITTOR"   "1994"   4.1   9.1    5   20  63.7    39 114.2 121.9  59.5 180.6 180.3  37.5
"Andhra Pradesh" "CHITTOR"   "1995"  50.1  10.2 18.3 14.4 182.7  91.8   156 195.3  75.5 149.3  71.8  10.2
"Andhra Pradesh" "CHITTOR"   "1996"     0   1.6  5.7 47.5  45.5 291.4  88.6   154 249.3 241.1  76.8 260.9
"Andhra Pradesh" "CHITTOR"   "1997"  24.6     0 23.1 46.6  43.8  98.4  46.4  76.4 183.2 142.9 240.4 133.4
"Andhra Pradesh" "CHITTOR"   "1998"    .3    14    4 23.1  29.1  45.9 143.2 196.3   167 124.4 223.6  93.2
"Andhra Pradesh" "CHITTOR"   "1999"   1.7   4.6    0 18.4  90.6  58.8  47.6  73.2  79.5 144.4 112.6  27.1
"Andhra Pradesh" "CHITTOR"   "2000"     0  51.6    0 14.6  88.5  62.7  68.3 190.5 137.7 158.1  66.7  58.1
"Andhra Pradesh" "CHITTOR"   "2001"    13     0  6.3   88    35  24.7 101.4  84.4 196.1   353  71.8  69.5
"Andhra Pradesh" "CHITTOR"   "2002"  40.3   3.9 15.4 13.7  53.2 111.9    35    55 105.3 175.8  79.6  24.9
"Andhra Pradesh" "CHITTOR"   "2003"     0     0 36.7 18.4   9.7  77.2 220.2   121 124.2 162.9  17.9  15.6
"Andhra Pradesh" "CHITTOR"   "2004"  19.1     8  9.5 35.2 197.8  42.7 117.6  18.6   163 172.2 134.4    .1
"Andhra Pradesh" "CHITTOR"   "2005"     0  18.6 19.6 52.8  60.6  59.6 126.3 106.5 151.5 346.3 258.4 154.4
"Andhra Pradesh" "CHITTOR"   "2006"    .4     0   36 10.6  68.8  97.2  18.5  97.4 136.2 148.5 100.1  33.4
"Andhra Pradesh" "CHITTOR"   "2007"     0     0    0    0   2.3   101 110.4 192.6 138.9   215  46.6 172.3
"Andhra Pradesh" "CHITTOR"   "2008"  15.3   9.5 55.9  6.5  64.4  44.2  99.3  89.6 131.6 165.3   271  11.4
"Andhra Pradesh" "CHITTOR"   "2009"    .8     0  4.3  9.5  45.3  67.6  42.9 132.2 127.1  35.8 184.1  60.4
"Andhra Pradesh" "CHITTOR"   "2010"   1.7    .3  1.3 13.3  95.7 103.1 180.8 155.7 135.7  98.3 230.7  68.5
"Andhra Pradesh" "CUDDAPAH"  "1970"     0   1.1   .6  9.3  82.7  68.6  71.8 221.3 136.7 119.3   6.5     0
"Andhra Pradesh" "CUDDAPAH"  "1971"     0   3.5 18.8 29.2  47.2  33.1  44.9  93.1  66.5 132.1  20.5  11.7
"Andhra Pradesh" "CUDDAPAH"  "1972"    .4    .2    0 18.3  80.5   118  28.2  11.5 151.9 179.2  87.7    78
"Andhra Pradesh" "CUDDAPAH"  "1973"     0     0    0   .4  21.9  33.7  94.3 168.7 154.8 171.3  24.5   2.5
"Andhra Pradesh" "CUDDAPAH"  "1974"     0     0    0  4.1  68.7  68.2  74.6  68.2 246.4 186.6  29.8     0
"Andhra Pradesh" "CUDDAPAH"  "1975"     0     0   .9    4  29.8    29 168.7 136.5 146.4 413.7    95     8
"Andhra Pradesh" "CUDDAPAH"  "1976"     0     0    0 13.2   9.3  76.3 153.1 191.4  34.4  98.4 118.4    .8
"Andhra Pradesh" "CUDDAPAH"  "1977"     0     0  3.4   11  69.3 110.3  84.5 141.1  46.2   165 124.5   1.1
"Andhra Pradesh" "CUDDAPAH"  "1978"   1.4   5.2    0 18.2  30.3  75.8 144.7  81.5 162.6  64.3 152.5  42.1
"Andhra Pradesh" "CUDDAPAH"  "1979"     0   6.4    0  3.9   103  40.4    37  98.3 132.7  63.4 219.2   5.6
"Andhra Pradesh" "CUDDAPAH"  "1980"     0     0    0   .9  16.3  69.1  69.8  82.8  60.3  27.1 123.3    18
"Andhra Pradesh" "CUDDAPAH"  "1981"   1.4     0 20.3  5.5    24  44.4    86 112.4 214.4 127.8  40.3  18.4
"Andhra Pradesh" "CUDDAPAH"  "1982"     0     0   .1  3.5  27.3  57.3  51.7   9.4  78.7 103.7 120.8     0
"Andhra Pradesh" "CUDDAPAH"  "1983"     0   2.6  1.6  4.2  62.5  33.1  77.2 188.4 269.4 102.6  30.9  25.5
"Andhra Pradesh" "CUDDAPAH"  "1984"     0  43.3 14.5  7.5   4.2  19.5 131.8  17.7 116.2  59.6  74.2  35.5
"Andhra Pradesh" "CUDDAPAH"  "1985"  15.5     0  2.2 12.3   3.3  54.3 113.8  55.8  46.6 119.7  83.8  31.7
"Andhra Pradesh" "CUDDAPAH"  "1986"    48   6.5    0  7.9   8.5  50.3  42.7  55.8    94 119.7  50.6  11.2
"Andhra Pradesh" "CUDDAPAH"  "1987"   3.3     0  6.6  1.4   5.2  44.9  11.7 164.8  65.1 126.3   104  44.1
end

To begin with I know I should start by using bysort STATE DISTRICT. But I don't know how to get the average of the past 30 years for every district.

[Clyde Schechter]

As with so many things in Stata, what is mind-bendingly complicated with wide data can be quite simple with long data.

rename (JAN-DEC) rainfall#, addnumber
destring YEAR, replace
reshape long rainfall, i(STATE DISTRICT YEAR) j(month)
rangestat (mean) monthly_avg_prior_30y = rainfall, ///
    by(STATE DISTRICT month) interval(YEAR -30 -1)
gen monthly_deviation = rainfall - monthly_avg_prior_30y
by STATE DISTRICT YEAR (month), sort: egen yearly_deviation = total(monthly_deviation)

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

The example data does not even extend into the 2005-2010 era, but there is no reason it shouldn't work just as well there as anywhere else. Please note that the "30 year" average here means the average of all years in the data set that are within the preceding 30: for the earlier years this will be fewer then 30 years.

Finally, you do not say whether "the past 30 years" means starting from the preceding year and extending back to 30 years before or starting from the current year and going back to 29 years before. This code assumes the former, but is easily modified to accommodate the latter if that was intended.

[Chirag Yadav]

This worked perfectly. Thank you so much!