{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "1.3.1\n" ] } ], "source": [ "#----------------------------------------------------\n", "# template to access a SQL database on teh SciServer\n", "# and read the result into a Pandas DataFrame\n", "#\n", "# Alex Szalay, Baltimore, 2020-04-21\n", "#-------------------------------------------------------\n", "import os\n", "import numpy as np\n", "import scipy as sp\n", "import matplotlib.pyplot as plt\n", "import SciServer.CasJobs as cj\n", "\n", "print (sp.__version__)\n", "cpath = '/home/idies/workspace/Storage/AS_171_205_2021/AS_171_205_2021-Course/data/'" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "def getTop():\n", " # get the time series for the top 200 counties\n", " sql=\"\"\"\n", " select dd, sum(cases) cases, sum(deaths) deaths\n", " from StatsC\n", " where fips in (select top 200 fips from censusData where county is not null order by popdensity desc)\n", " group by dd \n", " order by dd\n", " \"\"\"\n", " return cj.executeQuery(sql,\"COVIDNYT\")" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "def getStateData():\n", " # get all the attributes from the census database for all states\n", " sql=\"\"\"\n", " select *\n", " from censusData\n", " where county is null\n", " \"\"\"\n", " return cj.executeQuery(sql,\"COVIDNYT\")" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fips State County Population HousingUnits TotalArea \\\n", "0 1 Alabama 4779736 2171853 52420.07 \n", "1 2 Alaska 710231 306967 665384.10 \n", "2 4 Arizona 6392017 2844526 113990.30 \n", "3 5 Arkansas 2915918 1316299 53178.55 \n", "4 6 California 37253956 13680081 163694.70 \n", "5 8 Colorado 5029196 2212898 104093.70 \n", "6 9 Connecticut 3574097 1487891 5543.41 \n", "7 10 Delaware 897934 405885 2488.72 \n", "8 11 District of Columbia 601723 296719 68.34 \n", "9 12 Florida 18801310 8989580 65757.70 \n", "10 13 Georgia 9687653 4088801 59425.15 \n", "11 15 Hawaii 1360301 519508 10931.72 \n", "12 16 Idaho 1567582 667796 83568.95 \n", "13 17 Illinois 12830632 5296715 57913.55 \n", "14 18 Indiana 6483802 2795541 36419.55 \n", "15 19 Iowa 3046355 1336417 56272.81 \n", "16 20 Kansas 2853118 1233215 82278.36 \n", "17 21 Kentucky 4339367 1927164 40407.80 \n", "18 22 Louisiana 4533372 1964981 52378.13 \n", "19 23 Maine 1328361 721830 35379.74 \n", "20 24 Maryland 5773552 2378814 12405.93 \n", "21 25 Massachusetts 6547629 2808254 10554.39 \n", "22 26 Michigan 9883640 4532233 96713.51 \n", "23 27 Minnesota 5303925 2347201 86935.83 \n", "24 28 Mississippi 2967297 1274719 48431.78 \n", "25 29 Missouri 5988927 2712729 69706.99 \n", "26 30 Montana 989415 482825 147039.70 \n", "27 31 Nebraska 1826341 796793 77347.81 \n", "28 32 Nevada 2700551 1173814 110571.80 \n", "29 33 New Hampshire 1316470 614754 9349.16 \n", "30 34 New Jersey 8791894 3553562 8722.58 \n", "31 35 New Mexico 2059179 901388 121590.30 \n", "32 36 New York 19378102 8108103 54554.98 \n", "33 37 North Carolina 9535483 4327528 53819.16 \n", "34 38 North Dakota 672591 317498 70698.32 \n", "35 39 Ohio 11536504 5127508 44825.58 \n", "36 40 Oklahoma 3751351 1664378 69898.87 \n", "37 41 Oregon 3831074 1675562 98378.54 \n", "38 42 Pennsylvania 12702379 5567315 46054.34 \n", "39 44 Rhode Island 1052567 463388 1544.89 \n", "40 45 South Carolina 4625364 2137683 32020.49 \n", "41 46 South Dakota 814180 363438 77115.68 \n", "42 47 Tennessee 6346105 2812133 42144.25 \n", "43 48 Texas 25145561 9977436 268596.50 \n", "44 49 Utah 2763885 979709 84896.88 \n", "45 50 Vermont 625741 322539 9616.36 \n", "46 51 Virginia 8001024 3364939 42774.93 \n", "47 53 Washington 6724540 2885677 71297.95 \n", "48 54 West Virginia 1852994 881917 24230.04 \n", "49 55 Wisconsin 5686986 2624358 65496.38 \n", "50 56 Wyoming 563626 261868 97813.01 \n", "51 72 Puerto Rico 3725789 1636946 5324.84 \n", "\n", " WaterArea LandArea Popdensity HouseDensity \n", "0 1774.74 50645.33 94.376640 42.883580 \n", "1 94743.10 570640.90 1.244620 0.537934 \n", "2 396.22 113594.10 56.270690 25.041150 \n", "3 1143.07 52035.48 56.037110 25.296180 \n", "4 7915.52 155779.20 239.145900 87.817110 \n", "5 451.78 103641.90 48.524740 21.351390 \n", "6 701.06 4842.36 738.089900 307.265700 \n", "7 540.18 1948.54 460.824000 208.302100 \n", "8 7.29 61.05 9856.232000 4860.262000 \n", "9 12132.94 53624.76 350.608700 167.638600 \n", "10 1911.67 57513.49 168.441400 71.092900 \n", "11 4509.09 6422.63 211.798100 80.887120 \n", "12 925.83 82643.12 18.968090 8.080480 \n", "13 2394.62 55518.93 231.103700 95.403760 \n", "14 593.44 35826.11 180.979800 78.030830 \n", "15 415.68 55857.13 54.538340 23.925630 \n", "16 519.64 81758.72 34.896800 15.083590 \n", "17 921.46 39486.34 109.895400 48.805840 \n", "18 9174.23 43203.90 104.929700 45.481570 \n", "19 4536.82 30842.92 43.068590 23.403430 \n", "20 2698.69 9707.24 594.767600 245.055600 \n", "21 2754.33 7800.06 839.433200 360.029800 \n", "22 40174.61 56538.90 174.811300 80.161320 \n", "23 7309.09 79626.74 66.609850 29.477550 \n", "24 1508.50 46923.27 63.237220 27.166030 \n", "25 965.47 68741.52 87.122410 39.462740 \n", "26 1493.91 145545.80 6.797964 3.317341 \n", "27 523.64 76824.17 23.773000 10.371640 \n", "28 790.65 109781.20 24.599400 10.692310 \n", "29 396.51 8952.65 147.048100 68.667270 \n", "30 1368.36 7354.22 1195.490000 483.200400 \n", "31 292.15 121298.10 16.976180 7.431177 \n", "32 7428.58 47126.40 411.194200 172.050100 \n", "33 5201.25 48617.91 196.131100 89.010990 \n", "34 1697.52 69000.80 9.747583 4.601367 \n", "35 3964.89 40860.69 282.337500 125.487500 \n", "36 1303.95 68594.92 54.688470 24.263870 \n", "37 2390.53 95988.01 39.912010 17.455950 \n", "38 1311.64 44742.70 283.898400 124.429600 \n", "39 511.07 1033.81 1018.143000 448.233200 \n", "40 1959.79 30060.70 153.867500 71.112220 \n", "41 1304.68 75811.00 10.739600 4.794001 \n", "42 909.36 41234.90 153.901300 68.197890 \n", "43 7364.75 261231.70 96.257690 38.193820 \n", "44 2727.26 82169.62 33.636340 11.923010 \n", "45 399.71 9216.66 67.892380 34.995220 \n", "46 3284.84 39490.09 202.608400 85.209710 \n", "47 4842.43 66455.52 101.188600 43.422680 \n", "48 191.83 24038.21 77.085360 36.688130 \n", "49 11338.57 54157.80 105.007700 48.457620 \n", "50 719.87 97093.14 5.805003 2.697080 \n", "51 1901.07 3423.78 1088.209000 478.110700 \n" ] } ], "source": [ "stateData = getStateData()\n", "print(stateData)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3.7 (py37)", "language": "python", "name": "py37" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.4" } }, "nbformat": 4, "nbformat_minor": 4 }