Panel Data: Table with ranks of medians over groups

[Lea Reifenroether]

Hi together,

i use stata 16.0 and have a panel dataset with monthly average temperature (tavg) from 1971-2019 for 48 states.

My goal is to create a table that ranks the states by its median average temperature over the period from 2000-2019 per quarter. The table should show the rank, the state, the median and mean average temperature of the rank of a quarter (for example quarter==2)

I already created a median of tavg by statecode and quarter as well as a rank variable

• bysort statecode quarter: egen mediantavg = median(tavg) if yearly>=2000 & yearly<=2019
• bysort yearly statecode: egen rank= rank(mediantavg) if yearly>=2000 & yearly<=2019 & quarter==2, unique
  tab rank

With that i want to create a table looking like that but with rank by median average temperature and for each quarter separatly

Soruce: Temperature and growth: A panel analysis of the United States, Colacito, Hoffmann & Phan Journal ofMoney, Credit and Banking, Vol. 51, Nos. 2–3 (March–April 2019)

Thank you very much for your help.
Greets, Lea

[Nick Cox]

Giving Fahrenheit temperatures and not stating the scale is not a good idea. (The same would be true of Celsius scale, or any other.) Many readers here would be puzzled by the information that my weight is 83. Units of measurement matter!

Still, that's a fault in your source, presumably.

I am a little fuzzy on what you want. The rank variable you create is helpful. So, you can try

tabdisp rank if quarter == 2, c(state medianavg) 

[Lea Reifenroether]

I'm Sorry, the scale of the monthly average temperature is Fahrenheit.

I couldn't even manage to get the right rank variable. I want to have a variable that ranks the states by the median of the different average temperatures (mediantavg) of for example all quarter==2 from 2000-2019. So that i can finally create a table which shows the 48 states ranked from hottest to coldest state by the use of mediantavg

Thus i want the following (see also the table in my previous post, only that it should be the medium instead of the mean of monthly average temperatues:

rank state quarter period median(tavg)
1 AL 1 2000-2019 70,3
2 CL 1 2000-2019 70,2

I only managed it by using the collapse command but i need the rank variable for further regressions thus this solution is not sufficient for me.

• preserve
• collapse (mean) tavg (median) median_tavg=tavg if yearly>=2000 & yearly<=2019 , by (State statecode quarter)
• egen rank= rank(median_tavg) if quarter==2, unique
• sort rank
• list state rank median_tavg if rank!=.
• 

I hope I could describe it understandably

[Lea Reifenroether]

I think i got it by replacing the duplicates

Code:

egen rank= rank(mediantavg) if yearly>=2000 & yearly<=2019 & quarter==2, unique

bysort statecode mediantavg: gen dup =cond(_N==1,0,_n)

browse statecode yearly quarter quarterly tavg mediantavg rank dup if

yearly>=2000

replace mediantavg=. if dup>1

list State statecode rank mediantavg if rank!=.

Stata Output: see attachement

But is there also a way by not replacing the duplicates?

[Nick Cox]

Please do read and act on FAQ Advice #12. https://www.statalist.org/forums/help#stata You're posting images which cannot be copied and pasted.by us for experiment.

collapse is indeed not a good idea for what you want and my suggestion of tabdisp was based partly on thinking otherwise. You have ignored that suggestion.

From the data of the form in #1 this kind of thing should work. I have fixed the different by: arguments in #1 which look wrong to me. Also, if you want hottest first, you need a minus sign in your call to rank().

gen tag = inrange(yearly, 2000, 2019)
bysort statecode quarter: egen mediantavg = median(tavg) if tag
format mediantavg %2.1f
bysort statecode quarter: egen rank = rank(-mediantavg) if tag, unique
tabdisp rank if quarter==1, c(state mediantavg)

[Lea Reifenroether]

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 State byte statecode float(yearly quarterly quarter monthly tavg)
"AL" 1 1971 44 1 132   45
"AL" 1 1971 44 1 133 45.8
"AL" 1 1971 44 1 134   50
"AL" 1 1971 45 2 135 61.2
"AL" 1 1971 45 2 136 66.5
"AL" 1 1971 45 2 137   78
"AL" 1 1971 46 3 138   78
"AL" 1 1971 46 3 139 77.7
"AL" 1 1971 46 3 140 75.2
"AL" 1 1971 47 4 141 67.7
"AL" 1 1971 47 4 142 52.2
"AL" 1 1971 47 4 143 54.9
"AL" 1 1972 48 1 144 49.1
"AL" 1 1972 48 1 145   47
"AL" 1 1972 48 1 146 55.1
"AL" 1 1972 49 2 147 63.5
"AL" 1 1972 49 2 148 68.7
"AL" 1 1972 49 2 149   75
"AL" 1 1972 50 3 150 77.6
"AL" 1 1972 50 3 151 79.1
"AL" 1 1972 50 3 152 77.2
"AL" 1 1972 51 4 153 63.4
"AL" 1 1972 51 4 154 50.7
"AL" 1 1972 51 4 155 49.1
"AL" 1 1973 52 1 156 43.2
"AL" 1 1973 52 1 157 44.6
"AL" 1 1973 52 1 158 59.9
"AL" 1 1973 53 2 159 60.1
"AL" 1 1973 53 2 160 67.7
"AL" 1 1973 53 2 161 76.9
"AL" 1 1973 54 3 162 79.9
"AL" 1 1973 54 3 163 77.6
"AL" 1 1973 54 3 164 76.5
"AL" 1 1973 55 4 165 66.7
"AL" 1 1973 55 4 166 57.6
"AL" 1 1973 55 4 167   46
"AL" 1 1974 56 1 168   55
"AL" 1 1974 56 1 169 47.7
"AL" 1 1974 56 1 170 60.1
"AL" 1 1974 57 2 171   62
"AL" 1 1974 57 2 172 71.8
"AL" 1 1974 57 2 173 73.4
"AL" 1 1974 58 3 174 78.9
"AL" 1 1974 58 3 175 77.8
"AL" 1 1974 58 3 176 70.9
"AL" 1 1974 59 4 177 60.3
"AL" 1 1974 59 4 178 52.6
"AL" 1 1974 59 4 179 46.7
"AL" 1 1975 60 1 180   49
"AL" 1 1975 60 1 181 51.1
"AL" 1 1975 60 1 182 53.7
"AL" 1 1975 61 2 183 60.9
"AL" 1 1975 61 2 184 72.3
"AL" 1 1975 61 2 185 76.1
"AL" 1 1975 62 3 186   78
"AL" 1 1975 62 3 187 78.5
"AL" 1 1975 62 3 188 70.4
"AL" 1 1975 63 4 189 64.3
"AL" 1 1975 63 4 190 54.4
"AL" 1 1975 63 4 191 45.2
"AL" 1 1976 64 1 192 40.6
"AL" 1 1976 64 1 193   53
"AL" 1 1976 64 1 194 58.2
"AL" 1 1976 65 2 195 62.7
"AL" 1 1976 65 2 196 65.4
"AL" 1 1976 65 2 197 74.6
"AL" 1 1976 66 3 198 78.6
"AL" 1 1976 66 3 199 77.1
"AL" 1 1976 66 3 200   72
"AL" 1 1976 67 4 201 57.9
"AL" 1 1976 67 4 202 45.9
"AL" 1 1976 67 4 203 42.1
"AL" 1 1977 68 1 204 33.3
"AL" 1 1977 68 1 205 45.2
"AL" 1 1977 68 1 206 57.5
"AL" 1 1977 69 2 207 64.9
"AL" 1 1977 69 2 208 71.9
"AL" 1 1977 69 2 209 79.1
"AL" 1 1977 70 3 210 81.3
"AL" 1 1977 70 3 211 79.6
"AL" 1 1977 70 3 212 75.7
"AL" 1 1977 71 4 213 60.3
"AL" 1 1977 71 4 214 55.9
"AL" 1 1977 71 4 215 44.8
"AL" 1 1978 72 1 216 35.6
"AL" 1 1978 72 1 217 38.8
"AL" 1 1978 72 1 218 51.2
"AL" 1 1978 73 2 219 63.9
"AL" 1 1978 73 2 220   70
"AL" 1 1978 73 2 221   78
"AL" 1 1978 74 3 222 80.7
"AL" 1 1978 74 3 223 79.3
"AL" 1 1978 74 3 224 76.9
"AL" 1 1978 75 4 225 62.2
"AL" 1 1978 75 4 226 59.2
"AL" 1 1978 75 4 227 46.6
"AL" 1 1979 76 1 228 37.8
"AL" 1 1979 76 1 229 43.6
"AL" 1 1979 76 1 230 55.8
"AL" 1 1979 77 2 231 63.6
end
format %ty yearly
format %tq quarterly
format %tm monthly

The output of your code suggestion:

. tabdisp rank if quarter==1, c(State mediantavg)

----------------------------------------------------
unique    |
rank of   |
(-mediant |
avg) by   |
statecode |
quarter   | State abbreviations           mediantavg
----------+-----------------------------------------
        1 |                  AL                 49.9
        2 |                  AL                 49.9
        3 |                  AL                 49.9
        4 |                  AL                 49.9
        5 |                  AL                 49.9
        6 |                  AL                 49.9
        7 |                  AL                 49.9
        8 |                  AL                 49.9
        9 |                  AL                 49.9
       10 |                  AL                 49.9
       11 |                  AL                 49.9
       12 |                  AL                 49.9
       13 |                  AL                 49.9
       14 |                  AL                 49.9
       15 |                  AL                 49.9
       16 |                  AL                 49.9
       17 |                  AL                 49.9
       18 |                  AL                 49.9
       19 |                  AL                 49.9
       20 |                  AL                 49.9
       21 |                  AL                 49.9
       22 |                  AL                 49.9
       23 |                  AL                 49.9
       24 |                  AL                 49.9
       25 |                  AL                 49.9
       26 |                  AL                 49.9
       27 |                  AL                 49.9
       28 |                  AL                 49.9
       29 |                  AL                 49.9
       30 |                  AL                 49.9
       31 |                  AL                 49.9
       32 |                  AL                 49.9
       33 |                  AL                 49.9
       34 |                  AL                 49.9
       35 |                  AL                 49.9
       36 |                  AL                 49.9
       37 |                  AL                 49.9
       38 |                  AL                 49.9
       39 |                  AL                 49.9
       40 |                  AL                 49.9
       41 |                  AL                 49.9
       42 |                  AL                 49.9
       43 |                  AL                 49.9
       44 |                  AL                 49.9
       45 |                  AL                 49.9
       46 |                  AL                 49.9
       47 |                  AL                 49.9
       48 |                  AL                 49.9
       49 |                  AL                 49.9
       50 |                  AL                 49.9
       51 |                  AL                 49.9
       52 |                  AL                 49.9
       53 |                  AL                 49.9
       54 |                  AL                 49.9
       55 |                  AL                 49.9
       56 |                  AL                 49.9
       57 |                  AL                 49.9
       58 |                  AL                 49.9
       59 |                  AL                 49.9
       60 |                  AL                 49.9
        . |                  AL                    
----------------------------------------------------

.
end of do-file

Unfortunately the table does not show only one rank for each of the 48 states.

[Nick Cox]

Thanks for the data example, which as said does help mightily, not least in exposing my own silly errors.

Your full dataset is too large to show here and the example shows just one state and no years in 2000-19 but we won't let either trouble us.

The trick here is that once you have calculated the median it applies to all the observations from which it was calculated -- yet seeing just one copy is enough. My code should have been much more careful about that.

This I hope is closer to what you want. Naturally the rankings for each quarter will differ and I don't yet know how you want to handle that.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str2 State byte statecode float(yearly quarterly quarter monthly tavg)
"AL" 1 1971 44 1 132   45
"AL" 1 1971 44 1 133 45.8
"AL" 1 1971 44 1 134   50
"AL" 1 1971 45 2 135 61.2
"AL" 1 1971 45 2 136 66.5
"AL" 1 1971 45 2 137   78
"AL" 1 1971 46 3 138   78
"AL" 1 1971 46 3 139 77.7
"AL" 1 1971 46 3 140 75.2
"AL" 1 1971 47 4 141 67.7
"AL" 1 1971 47 4 142 52.2
"AL" 1 1971 47 4 143 54.9
"AL" 1 1972 48 1 144 49.1
"AL" 1 1972 48 1 145   47
"AL" 1 1972 48 1 146 55.1
"AL" 1 1972 49 2 147 63.5
"AL" 1 1972 49 2 148 68.7
"AL" 1 1972 49 2 149   75
"AL" 1 1972 50 3 150 77.6
"AL" 1 1972 50 3 151 79.1
"AL" 1 1972 50 3 152 77.2
"AL" 1 1972 51 4 153 63.4
"AL" 1 1972 51 4 154 50.7
"AL" 1 1972 51 4 155 49.1
"AL" 1 1973 52 1 156 43.2
"AL" 1 1973 52 1 157 44.6
"AL" 1 1973 52 1 158 59.9
"AL" 1 1973 53 2 159 60.1
"AL" 1 1973 53 2 160 67.7
"AL" 1 1973 53 2 161 76.9
"AL" 1 1973 54 3 162 79.9
"AL" 1 1973 54 3 163 77.6
"AL" 1 1973 54 3 164 76.5
"AL" 1 1973 55 4 165 66.7
"AL" 1 1973 55 4 166 57.6
"AL" 1 1973 55 4 167   46
"AL" 1 1974 56 1 168   55
"AL" 1 1974 56 1 169 47.7
"AL" 1 1974 56 1 170 60.1
"AL" 1 1974 57 2 171   62
"AL" 1 1974 57 2 172 71.8
"AL" 1 1974 57 2 173 73.4
"AL" 1 1974 58 3 174 78.9
"AL" 1 1974 58 3 175 77.8
"AL" 1 1974 58 3 176 70.9
"AL" 1 1974 59 4 177 60.3
"AL" 1 1974 59 4 178 52.6
"AL" 1 1974 59 4 179 46.7
"AL" 1 1975 60 1 180   49
"AL" 1 1975 60 1 181 51.1
"AL" 1 1975 60 1 182 53.7
"AL" 1 1975 61 2 183 60.9
"AL" 1 1975 61 2 184 72.3
"AL" 1 1975 61 2 185 76.1
"AL" 1 1975 62 3 186   78
"AL" 1 1975 62 3 187 78.5
"AL" 1 1975 62 3 188 70.4
"AL" 1 1975 63 4 189 64.3
"AL" 1 1975 63 4 190 54.4
"AL" 1 1975 63 4 191 45.2
"AL" 1 1976 64 1 192 40.6
"AL" 1 1976 64 1 193   53
"AL" 1 1976 64 1 194 58.2
"AL" 1 1976 65 2 195 62.7
"AL" 1 1976 65 2 196 65.4
"AL" 1 1976 65 2 197 74.6
"AL" 1 1976 66 3 198 78.6
"AL" 1 1976 66 3 199 77.1
"AL" 1 1976 66 3 200   72
"AL" 1 1976 67 4 201 57.9
"AL" 1 1976 67 4 202 45.9
"AL" 1 1976 67 4 203 42.1
"AL" 1 1977 68 1 204 33.3
"AL" 1 1977 68 1 205 45.2
"AL" 1 1977 68 1 206 57.5
"AL" 1 1977 69 2 207 64.9
"AL" 1 1977 69 2 208 71.9
"AL" 1 1977 69 2 209 79.1
"AL" 1 1977 70 3 210 81.3
"AL" 1 1977 70 3 211 79.6
"AL" 1 1977 70 3 212 75.7
"AL" 1 1977 71 4 213 60.3
"AL" 1 1977 71 4 214 55.9
"AL" 1 1977 71 4 215 44.8
"AL" 1 1978 72 1 216 35.6
"AL" 1 1978 72 1 217 38.8
"AL" 1 1978 72 1 218 51.2
"AL" 1 1978 73 2 219 63.9
"AL" 1 1978 73 2 220   70
"AL" 1 1978 73 2 221   78
"AL" 1 1978 74 3 222 80.7
"AL" 1 1978 74 3 223 79.3
"AL" 1 1978 74 3 224 76.9
"AL" 1 1978 75 4 225 62.2
"AL" 1 1978 75 4 226 59.2
"AL" 1 1978 75 4 227 46.6
"AL" 1 1979 76 1 228 37.8
"AL" 1 1979 76 1 229 43.6
"AL" 1 1979 76 1 230 55.8
"AL" 1 1979 77 2 231 63.6
end
format %ty yearly
format %tq quarterly
format %tm monthly

bysort statecode quarter: egen mediantavg = median(tavg)
egen tag = tag(statecode quarter)
format mediantavg %2.1f
bysort quarter: egen rank = rank(-mediantavg) if tag , unique
* blank out label
label var rank
tabdisp rank if quarter == 1 & tag, c(State mediantavg)



----------------------------------
     rank |      State  mediantavg
----------+-----------------------
        1 |         AL        49.0
----------------------------------

The code should work with all the states too! But so far, just for each specific quarter.

(For interfacing with social or economic data your quarters should be JFM AMJ JAS OND but climatologists go DJF MAM JJA SON.)

[Lea Reifenroether]

Thank you very much, it works fine. I matched the temperature data set with bank performance data that is only reported at the fiscal quarter level. Thus i can't use climate season. My goal ist to measures how lagged temperature anomalies will impact bank performance. But thanks a lot for the hint. You're that will might lead to biased results.