Large sample with low R² and high RMSE; or Small aggregated sample with high R² and low RMSE?

I have 45 independent variables and 50 (US) state controls.

I have a sample of about 100,000 county-level observations. With this sample, I run my regression (observations weighted by population) and I find that my R² is 0.3851, while my RMSE is 443.24. (The mean of the dependent variable is 556.)

Using this exact same sample, I aggregate these 100,000 county-level observations into roughly 2,000 state-level observations (the aggregation is weighted by population). I run an analogous regression, but now my R² is 0.8691 and my RMSE is 139.60.

The estimates of the slope coefficients are broadly similar, but naturally they do not exactly coincide.

Which set of results should I prefer? What are some considerations?

Also, why might the R² and RMSE for the larger sample fare more poorly? F( 25, 47) = .

Linear  regression  Number  of  obs =   100851
F(  25, 47) =   .
Prob    >   F   =   .
R-squared   =   0.3851
Root    MSE =   443.24

(Std.   Err.    adjusted    for 48  clusters    in  state)
----------------------------------------------------------------------------------
|   Robust
depVar  |   Coef.   Std.    Err.    t   P>|t|   [95%    Conf.   Interval]
-----------------+----------------------------------------------------------------
year    |   16.02798    2.789082    5.75    0   10.41707    21.63889
1   indepVar1   |   -458.473    183.6673    -2.5    0.016   -827.964    -88.982
2   indepVar2   |   205.6476    45.68145    4.5 0   113.7483    297.5468
3   indepVar3   |   233.3758    60.4131 3.86    0   111.8404    354.9113
4   indepVar4   |   177.3645    95.08196    1.87    0.068   -13.91579   368.6447
5   indepVar5   |   -948.498    550.6482    -1.72   0.092   -2056.259   159.2632
6   indepVar6   |   -992.7274   756.5457    -1.31   0.196   -2514.701   529.2463
7   indepVar7   |   982.5164    755.8617    1.3 0.2 -538.0813   2503.114
8   indepVar8   |   151.0235    165.765 0.91    0.367   -182.4526   484.4996
9   indepVar9   |   -379.7011   272.9085    -1.39   0.171   -928.7223   169.32
10  indepVar10  |   -93.88511   136.5604    -0.69   0.495   -368.6093   180.839
11  indepVar11  |   28.98643    26.16698    1.11    0.274   -23.65474   81.6276
12  indepVar12  |   240.7211    193.3436    1.25    0.219   -148.236    629.6781
13  indepVar13  |   -6.526805   74.67645    -0.09   0.931   -156.7564   143.7028
14  indepVar14  |   193.1296    221.9745    0.87    0.389   -253.4254   639.6847
15  indepVar15  |   -11.20702   102.6446    -0.11   0.914   -217.7014   195.2874
16  indepVar16  |   51.82138    102.8591    0.5 0.617   -155.1045   258.7472
17  indepVar17  |   -124.7794   123.8627    -1.01   0.319   -373.9591   124.4002
18  indepVar18  |   340.7428    105.5859    3.23    0.002   128.3314    553.1543
19  indepVar19  |   1777.38 1135.562    1.57    0.124   -507.0753   4061.835
20  indepVar20  |   -191.8244   115.5749    -1.66   0.104   -424.3311   40.68239
21  indepVar21  |   1390.434    779.4091    1.78    0.081   -177.5352   2958.403
22  indepVar22  |   254.1712    88.50643    2.87    0.006   76.11923    432.2232
23  indepVar23  |   -346.1083   325.8941    -1.06   0.294   -1001.723   309.506
24  indepVar24  |   171.7473    45.03025    3.81    0   81.15817    262.3365
25  indepVar25  |   -76.53413   95.20126    -0.8    0.425   -268.0544   114.9861
26  indepVar26  |   -197.4632   81.29256    -2.43   0.019   -361.0028   -33.92371
27  indepVar27  |   -1060.178   571.7728    -1.85   0.07    -2210.436   90.08107
28  indepVar28  |   -69.41351   91.52206    -0.76   0.452   -253.5321   114.7051
29  indepVar29  |   32.38332    265.625 0.12    0.903   -501.9853   566.7519
30  indepVar30  |   -125.3714   263.3725    -0.48   0.636   -655.2085   404.4657
31  indepVar31  |   159.1979    343.29  0.46    0.645   -531.4124   849.8083
32  indepVar32  |   336.8044    382.4105    0.88    0.383   -432.5063   1106.115
33  indepVar33  |   324.824 277.9695    1.17    0.248   -234.3785   884.0266
34  indepVar34  |   -577.9178   286.0078    -2.02   0.049   -1153.291   -2.544465
35  indepVar35  |   -171.3952   334.1503    -0.51   0.61    -843.619    500.8286
36  indepVar36  |   523.0878    329.1718    1.59    0.119   -139.1206   1185.296
37  indepVar37  |   -191.6111   70.98143    -2.7    0.01    -334.4073   -48.8149
38  indepVar38  |   11.59451    40.05563    0.29    0.774   -68.98701   92.17604
39  indepVar39  |   75.86873    97.55217    0.78    0.441   -120.3809   272.1184
40  indepVar40  |   -11.92264   729.6524    -0.02   0.987   -1479.794   1455.949
41  indepVar41  |   22.75141    242.714 0.09    0.926   -465.5262   511.029
42  indepVar42  |   -58.7689    171.3043    -0.34   0.733   -403.3887   285.8509
43  indepVar43  |   -402.7508   188.6756    -2.13   0.038   -782.3171   -23.18449
44  indepVar44  |   -140.292    45.70826    -3.07   0.004   -232.2451   -48.33883
_Istate_2   |   4.68108 677.5097    0.01    0.995   -1358.293   1367.655
_Istate_3   |   -415.3851   549.4083    -0.76   0.453   -1520.652   689.8818
_Istate_4   |   -212.9707   688.97  -0.31   0.759   -1598.999   1173.058
_Istate_5   |   111.7194    689.2478    0.16    0.872   -1274.868   1498.307
_Istate_6   |   -920.1566   666.3922    -1.38   0.174   -2260.765   420.4515
_Istate_7   |   -1147.931   822.0788    -1.4    0.169   -2801.74    505.8783
_Istate_8   |   0   (omitted)
_Istate_9   |   -334.1907   556.992 -0.6    0.551   -1454.714   786.3326
_Istate_10  |   -875.1989   356.2245    -2.46   0.018   -1591.83    -158.5677
_Istate_11  |   -523.8942   891.2408    -0.59   0.559   -2316.839   1269.051
_Istate_12  |   -638.8514   677.2967    -0.94   0.35    -2001.397   723.6938
_Istate_13  |   0   (omitted)
_Istate_14  |   -243.9082   634.2313    -0.38   0.702   -1519.817   1032.001
_Istate_15  |   -416.1645   623.6256    -0.67   0.508   -1670.737   838.4084
_Istate_16  |   -1141.161   677.5166    -1.68   0.099   -2504.149   221.8263
_Istate_17  |   -3470.225   1734.007    -2  0.051   -6958.598   18.14813
_Istate_18  |   -731.1899   719.8711    -1.02   0.315   -2179.384   717.0041
_Istate_19  |   -755.1031   649.8175    -1.16   0.251   -2062.367   552.161
_Istate_20  |   176.8451    1040.597    0.17    0.866   -1916.567   2270.257
_Istate_21  |   -1216.53    653.1755    -1.86   0.069   -2530.549   97.48979
_Istate_22  |   -827.732    610.1264    -1.36   0.181   -2055.148   399.684
_Istate_23  |   -955.0581   553.2604    -1.73   0.091   -2068.075   157.9584
_Istate_24  |   276.0788    183.6137    1.5 0.139   -93.30428   645.4618
_Istate_25  |   -455.0858   720.1046    -0.63   0.53    -1903.749   993.5778
_Istate_26  |   -257.604    872.7705    -0.3    0.769   -2013.392   1498.184
_Istate_27  |   -902.1086   609.1673    -1.48   0.145   -2127.595   323.378
_Istate_28  |   -186.9086   641.4881    -0.29   0.772   -1477.416   1103.599
_Istate_29  |   -2021.791   866.2154    -2.33   0.024   -3764.392   -279.1904
_Istate_30  |   288.9218    1036.67 0.28    0.782   -1796.588   2374.432
_Istate_31  |   -653.2965   699.3336    -0.93   0.355   -2060.174   753.5812
_Istate_32  |   -169.1177   972.8816    -0.17   0.863   -2126.303   1788.068
_Istate_33  |   -3.528485   1064.729    0   0.997   -2145.487   2138.43
_Istate_34  |   -595.7262   699.0201    -0.85   0.398   -2001.973   810.5209
_Istate_35  |   -915.5309   589.8294    -1.55   0.127   -2102.115   271.0527
_Istate_36  |   -97.34604   801.2839    -0.12   0.904   -1709.321   1514.629
_Istate_37  |   -136.5235   556.0349    -0.25   0.807   -1255.121   982.0744
_Istate_38  |   -1086.956   448.4446    -2.42   0.019   -1989.11    -184.8022
_Istate_39  |   899.9832    639.4759    1.41    0.166   -386.4764   2186.443
_Istate_40  |   -1029.878   533.3683    -1.93   0.06    -2102.876   43.12109
_Istate_41  |   -424.9372   709.2462    -0.6    0.552   -1851.756   1001.882
_Istate_42  |   -650.9319   565.2988    -1.15   0.255   -1788.166   486.3025
_Istate_43  |   -362.5626   462.3638    -0.78   0.437   -1292.719   567.5934
_Istate_44  |   105.9245    533.5528    0.2 0.843   -967.4452   1179.294
_Istate_45  |   -696.8787   573.4762    -1.22   0.23    -1850.564   456.8067
_Istate_46  |   444.2534    965.5209    0.46    0.648   -1498.124   2386.631
_Istate_47  |   -453.4677   946.4425    -0.48   0.634   -2357.464   1450.529
_Istate_48  |   155.548 1004.649    0.15    0.878   -1865.545   2176.641
_Istate_49  |   -1901.979   644.8006    -2.95   0.005   -3199.151   -604.8077
_Istate_50  |   -455.9132   649.3263    -0.7    0.486   -1762.189   850.3627
_cons   |   -29004.39   5391.158    -5.38   0   -39850  -18158.77


State level

Linear  regression  Number  of  obs =   1920
F(  30, 49) =   .
Prob    >   F   =   .
R-squared   =   0.8691
Root    MSE =   139.6

(Std.   Err.    adjusted    for 50  clusters    in  state)
----------------------------------------------------------------------------------
|   Robust
cdr |   Coef.   Std.    Err.    t   P>|t|   [95%    Conf.   Interval]
-----------------+----------------------------------------------------------------
year    |   15.93609    2.797954    5.7 0   10.31339    21.55879
indepVar1   |   -462.6864   187.8189    -2.46   0.017   -840.1226   -85.25021
indepVar2   |   204.1425    45.82676    4.45    0   112.0501    296.2348
indepVar3   |   231.3854    60.60526    3.82    0   109.5945    353.1762
indepVar4   |   170.1259    93.77893    1.81    0.076   -18.3299    358.5817
indepVar5   |   318.636 495.8839    0.64    0.524   -677.8801   1315.152
indepVar6   |   -41.4763    410.9076    -0.1    0.92    -867.2261   784.2735
indepVar7   |   74.56288    419.5127    0.18    0.86    -768.4795   917.6052
indepVar8   |   148.87  169.4261    0.88    0.384   -191.6044   489.3445
indepVar9   |   -322.1897   297.0613    -1.08   0.283   -919.1568   274.7774
indepVar10  |   -132.2453   144.1476    -0.92   0.363   -421.9207   157.4301
indepVar11  |   19.46477    24.23743    0.8 0.426   -29.24216   68.1717
indepVar12  |   270.2618    197.9292    1.37    0.178   -127.4919   668.0155
indepVar13  |   -7.056924   75.70485    -0.09   0.926   -159.1915   145.0777
indepVar14  |   115.9848    230.2678    0.5 0.617   -346.7556   578.7252
indepVar15  |   50.12875    99.16391    0.51    0.615   -149.1486   249.4061
indepVar16  |   67.80731    107.6611    0.63    0.532   -148.5458   284.1604
indepVar17  |   -126.4316   126.5776    -1  0.323   -380.7987   127.9355
indepVar18  |   262.0144    79.06571    3.31    0.002   103.126 420.9029
indepVar19  |   346.017 584.5286    0.59    0.557   -828.6372   1520.671
indepVar20  |   -49.34303   59.68256    -0.83   0.412   -169.2796   70.59357
indepVar21  |   424.2462    408.4819    1.04    0.304   -396.629    1245.121
indepVar22  |   269.7198    86.80823    3.11    0.003   95.2721 444.1674
indepVar23  |   -1004.421   354.4545    -2.83   0.007   -1716.724   -292.1185
indepVar24  |   170.2637    45.17349    3.77    0   79.48418    261.0432
indepVar25  |   -74.82832   99.6722 -0.75   0.456   -275.1271   125.4705
indepVar26  |   -206.8909   86.18732    -2.4    0.02    -380.0908   -33.69103
indepVar27  |   315.7434    353.4699    0.89    0.376   -394.5811   1026.068
indepVar28  |   -81.04575   88.4054 -0.92   0.364   -258.703    96.61155
indepVar29  |   -12.19315   285.1077    -0.04   0.966   -585.1385   560.7522
indepVar30  |   -76.08443   286.5407    -0.27   0.792   -651.9095   499.7406
indepVar31  |   176.6186    371.1519    0.48    0.636   -569.2391   922.4762
indepVar32  |   -34.44861   576.5569    -0.06   0.953   -1193.083   1124.186
indepVar33  |   353.2545    286.057 1.23    0.223   -221.5985   928.1075
indepVar34  |   49.13409    251.1301    0.2 0.846   -455.5307   553.7989
indepVar35  |   -768.7232   457.9258    -1.68   0.1 -1688.959   151.5131
indepVar36  |   463.5527    355.4124    1.3 0.198   -250.6753   1177.781
indepVar37  |   -180.4985   70.34141    -2.57   0.013   -321.8548   -39.14209
indepVar38  |   4.988007    42.14908    0.12    0.906   -79.71374   89.68976
indepVar39  |   72.58692    102.0223    0.71    0.48    -132.4345   277.6084
indepVar40  |   395.4613    612.4864    0.65    0.522   -835.3763   1626.299
indepVar41  |   -115.897    203.7209    -0.57   0.572   -525.2895   293.4956
indepVar42  |   -213.2643   140.9771    -1.51   0.137   -496.5683   70.03972
indepVar43  |   -161.1463   164.0912    -0.98   0.331   -490.9  168.6075
indepVar44  |   -143.5427   46.51919    -3.09   0.003   -237.0265   -50.05888
_Istate_2   |   1316.494    487.8308    2.7 0.01    336.1608    2296.826
_Istate_3   |   817.3253    543.4823    1.5 0.139   -274.8432   1909.494
_Istate_4   |   1619.078    610.0062    2.65    0.011   393.2245    2844.931
_Istate_5   |   1377.191    428.3966    3.21    0.002   516.296 2238.086
_Istate_6   |   474.6932    396.4474    1.2 0.237   -321.9978   1271.384
_Istate_7   |   692.2386    542.2445    1.28    0.208   -397.4425   1781.92
_Istate_8   |   461.9138    216.6741    2.13    0.038   26.49081    897.3368
_Istate_9   |   293.6877    473.3727    0.62    0.538   -657.5904   1244.966
_Istate_10  |   -354.8723   465.1904    -0.76   0.449   -1289.707   579.9628
_Istate_11  |   1383.923    607.3632    2.28    0.027   163.3811    2604.465
_Istate_12  |   625.1371    444.8335    1.41    0.166   -268.7894   1519.063
_Istate_13  |   1284.684    669.603 1.92    0.061   -60.93324   2630.302
_Istate_14  |   1020.459    384.4732    2.65    0.011   247.8313    1793.087
_Istate_15  |   879.3303    378.8966    2.32    0.025   117.909 1640.752
_Istate_16  |   651.6313    632.8723    1.03    0.308   -620.1732   1923.436
_Istate_17  |   287.9311    1245.841    0.23    0.818   -2215.68    2791.542
_Istate_18  |   456.5379    370.9701    1.23    0.224   -288.9545   1202.03
_Istate_19  |   604.1628    488.2987    1.24    0.222   -377.1102   1585.436
_Istate_20  |   618.5926    284.0822    2.18    0.034   47.70796    1189.477
_Istate_21  |   218.2797    466.5063    0.47    0.642   -719.1998   1155.759
_Istate_22  |   575.3536    497.0401    1.16    0.253   -423.4859   1574.193
_Istate_23  |   330.1886    352.4509    0.94    0.353   -378.0881   1038.465
_Istate_24  |   189.497 171.7207    1.1 0.275   -155.5887   534.5828
_Istate_25  |   807.9892    495.9669    1.63    0.11    -188.6935   1804.672
_Istate_26  |   1652.076    578.7392    2.85    0.006   489.056 2815.096
_Istate_27  |   494.7572    519.676 0.95    0.346   -549.5709   1539.085
_Istate_28  |   1092.352    394.7797    2.77    0.008   299.0125    1885.691
_Istate_29  |   239.8971    701.5776    0.34    0.734   -1169.976   1649.77
_Istate_30  |   49.01292    477.8495    0.1 0.919   -911.2615   1009.287
_Istate_31  |   696.6968    488.8129    1.43    0.16    -285.6096   1679.003
_Istate_32  |   201.85  206.5152    0.98    0.333   -213.1578   616.8578
_Istate_33  |   369.3282    248.6358    1.49    0.144   -130.3241   868.9805
_Istate_34  |   1251.643    635.0245    1.97    0.054   -24.48705   2527.772
_Istate_35  |   468.8939    469.3269    1   0.323   -474.2539   1412.042
_Istate_36  |   1697.119    670.0581    2.53    0.015   350.5869    3043.651
_Istate_37  |   1140.92 369.6134    3.09    0.003   398.1538    1883.686
_Istate_38  |   226.659 495.361 0.46    0.649   -768.8062   1222.124
_Istate_39  |   -366.5443   564.3341    -0.65   0.519   -1500.616   767.5275
_Istate_40  |   268.0867    435.2812    0.62    0.541   -606.6436   1142.817
_Istate_41  |   1421.221    633.4396    2.24    0.029   148.2767    2694.166
_Istate_42  |   739.0075    447.7915    1.65    0.105   -160.8632   1638.878
_Istate_43  |   959.5551    477.1847    2.01    0.05    0.6165694   1918.494
_Istate_44  |   1403.917    512.9851    2.74    0.009   373.0345    2434.799
_Istate_45  |   707.8354    572.4191    1.24    0.222   -442.4839   1858.155
_Istate_46  |   826.5462    488.0049    1.69    0.097   -154.1364   1807.229
_Istate_47  |   1570.959    780.5679    2.01    0.05    2.34885 3139.569
_Istate_48  |   585.7089    284.5396    2.06    0.045   13.90527    1157.513
_Istate_49  |   173.9831    636.3534    0.27    0.786   -1104.817   1452.783
_Istate_50  |   870.1673    475.0868    1.83    0.073   -84.55542   1824.89
_cons   |   -30153.08   5185.637    -5.81   0   -40574.01   -19732.15

• If possible, Could you please add your output in this question as you got from software that you use ? I meant the portion that you are referring here. It would be helpful to understand better. Thanks ! – Learner Feb 14 '15 at 16:37
• This is the archetypal example of the ecological fallacy in regression. – whuber Feb 14 '15 at 16:53
• @Learner: Great suggestion, I've done just that. – Kenny LJ Feb 14 '15 at 16:54

1 Answer

As @whuber pointed out, this is an example of the ecological fallacy. More specifically as to why this might be the case: it is likely because your county level data is more variable than your aggregated data. This make sense, since in general, the sample average of some set of random variables will be less variable than the random variables themselves. (Think about the central limit theorem.)

For example, say you have three counties: one is poor and rural, one is rich and rural, one is neither poor nor rich, but urban. You average them together and you'll have something that looks (numerically) middle income and suburban. You've completely ignored the variability among the three counties (not to mention the variability among the people within those counties).

Now is the part of the answer that strays from what you have directly asked. I really hope that you have given serious thought to issues of multicolinearity with those 45 independent variables. Reducing that set of independent variables may be a good modelling strategy. Think seriously also about what you want to model as fixed versus a random effect.

• So, which should I prefer? And why? – Kenny LJ Feb 17 '15 at 3:18
• I suppose which one you should prefer depends on your objectives. But that is a much more in-depth question and answer session to figure out. You need to figure out what your goals are for this model and maybe at what level inferences from this model will affect decisions. I tend to models that incorporate information at the finest grain and aggregates within the model using all the data, but I could see going a different route for a different set of objectives. – Dalton Hance Feb 17 '15 at 18:38