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

Question

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       

Answer 1

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.