class: center, middle, inverse, title-slide # Databases and Data Formats ### K Arnold --- ## Q&As > How can we control our personal use of tech when it's autonomously engineering itself for maximum attention? * As individuals: Set boundaries (a "rule of life"), and times of reflection ("examen"). Authors: [Justin W. Earley](https://www.thecommonrule.org/), James K.A. Smith, Andy Crouch. * As developers * Suggest topics/questions instead of specific videos? * Develop UI for helping people *reflect on* and *control* their engagement ("you often click on X, Y, and Z. How many minutes per day do you want to spend watching videos about X?") > Do companies have ethics requirements? Many do, but they can be vague. Everyone needs to be responsible. ACM Code of Ethics. --- ## Q&As > How has DS changed recently? * *Much* more data, *much* more compute. * Covid: data-inspired decisions are shaping our lives. --- ## Q&As > My thoughts on social media censoring? * Some censorship seems essential (child porn etc.) * Some choices we have to make: * Subreddits (moderators decide) vs Twitter (committee decides)? * Control at the source (Twitter) vs recommendations/ads (Facebook) * Where does responsibility lie: publisher? platform? algorithm? reader? * Should we even have algorithms recommending / ranking content? * Recommendation algorithms are opaquely amplifying certain content (and implicitly censoring others). * Recommendation subtly implies endorsement. So some censorship is required. --- ## Q&As > My thoughts on social media censoring? An issue of the heart: * Entirely uncensored anonymous communities tend to become hostile * We'll find ways to "satisfy our itching ears" (if not YouTube then talk radio, moving to avoid uncomfortable people/ideas, ...) * We must seek out, amplify, and create what is "true, honorable, just, pure", ... (Philippians 4) --- ## Rest of the semester * Today: Data formats, APIs, SQL * Wednesday: Text classification, bias * Friday: advanced modeling and forecasting * Monday: communication, publishing, more reflections on ethics and impact All at-home focus is on projects. I'm finally almost done with midterm project feedback! --- ## Data Formats * Tabular, delimited (CSV, TSV) or fixed-width * Tabular, structured: Excel, SPSS/Stata/SAS, etc. * Hierarchical * JSON * XML * HTML * Database * SQLite (most apps) * PostgreSQL / MySQL / Oracle / Microsoft SQL Server * Google BigQuery (For a big table, see the [`rio` package vignette](https://cloud.r-project.org/web/packages/rio/vignettes/rio.html)) --- ## Spreadsheets vs Databases .pull-left[ * Often exchanged by email * Hope the format doesn't change * [Capacity limited](https://www.bbc.com/news/technology-54423988) ([other article](https://theconversation.com/why-you-should-never-use-microsoft-excel-to-count-coronavirus-cases-147681)) * Slow to query * Decentralized (resilient?) ] .pull-right[ * Centralized, highly available servers * Documented schema * Large capacity * Fast queries ] --- ## APIs .question[ Name as many APIs as you can think of. ] --- class: larger-table ## API vs Local Data | Local Data | API | |-----|--------| | Any query you want | Only queries the API exposes | | As much as you want | Often rate-limited | | Full dataset must fit on your computer | Practically unlimited data | | | ... but you can only see a small part of it | | Must be complete dataset | Can stream in new data | --- ## Example API: Bike Share Feeds > The [General Bikeshare Feed Specification](https://github.com/NABSA/gbfs), known as GBFS, is the open data standard for bikeshare. GBFS makes real-time data feeds in a uniform format publicly available online, with an emphasis on findability. * [Capital Bikeshare data](https://gbfs.capitalbikeshare.com/gbfs/gbfs.json) * Station Information: <https://gbfs.capitalbikeshare.com/gbfs/en/station_information.json> --- ### Step 1: Retrieve data via HTTP(S) ```r station_info_response <- httr::GET("https://gbfs.capitalbikeshare.com/gbfs/en/station_information.json") station_info_response ``` ``` ## Response [https://gbfs.capitalbikeshare.com/gbfs/en/station_information.json] ## Date: 2020-11-30 04:11 ## Status: 200 ## Content-Type: application/json ## Size: 305 kB ``` ```r station_info_text <- httr::content(station_info_response, as = "text") str_sub(station_info_text, end = 500) %>% cat() ``` ``` ## {"data":{"stations":[{"rental_methods":["CREDITCARD","KEY"],"station_type":"classic","lat":38.858971,"electric_bike_surcharge_waiver":false,"rental_uris":{"ios":"https://dc.lft.to/lastmile_qr_scan","android":"https://dc.lft.to/lastmile_qr_scan"},"short_name":"31000","station_id":"1","name":"Eads St & 15th St S","lon":-77.05323,"has_kiosk":true,"region_id":"41","legacy_id":"1","external_id":"082469cc-1f3f-11e7-bf6b-3863bb334450","eightd_station_services":[],"eightd_has_key_dispenser":false,"capac ``` --- ### Step 2: Parse the resulting JSON ```r station_info <- jsonlite::fromJSON(station_info_text, simplifyDataFrame = TRUE) stations <- station_info$data$stations stations %>% glimpse() ``` ``` ## Rows: 610 ## Columns: 16 ## $ rental_methods <list> [<"CREDITCARD", "KEY">, <"CREDITCARD", "KEY">, <"CREDITCA… ## $ station_type <chr> "classic", "classic", "classic", "classic", "classic", "cl… ## $ lat <dbl> 38.85897, 38.85725, 38.85643, 38.86017, 38.85787, 38.86230… ## $ electric_bike_surcharge_waiver <lgl> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FA… ## $ rental_uris <df[,2]> <data.frame[33 x 2]> ## $ short_name <chr> "31000", "31001", "31002", "31003", "31004", "31005", "310… ## $ station_id <chr> "1", "2", "3", "4", "5", "6", "7", "8", "10", "11", "12", … ## $ name <chr> "Eads St & 15th St S", "18th St & S Eads St", "Crystal Dr … ## $ lon <dbl> -77.05323, -77.05332, -77.04923, -77.04959, -77.05949, -77… ## $ has_kiosk <lgl> TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE… ## $ region_id <chr> "41", "41", "41", "41", "41", "41", "41", "41", "41", "41"… ## $ legacy_id <chr> "1", "2", "3", "4", "5", "6", "7", "8", "10", "11", "12", … ## $ external_id <chr> "082469cc-1f3f-11e7-bf6b-3863bb334450", "08246b69-1f3f-11e… ## $ eightd_station_services <list> [[], [], [], [], [], [], [], [], [], [], [], [], [], [], … ## $ eightd_has_key_dispenser <lgl> FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FA… ## $ capacity <int> 15, 11, 17, 11, 11, 19, 15, 19, 11, 11, 17, 11, 15, 31, 27… ``` --- ### Step 3: Profit .small-code[ ```r capacity_palette <- leaflet::colorNumeric("GnBu", domain = stations$capacity) lp <- stations %>% leaflet() %>% addProviderTiles(providers$CartoDB.Positron) %>% addCircleMarkers(~ lon, ~ lat, color = ~capacity_palette(capacity), radius = 2, stroke = FALSE, fillOpacity = 1) %>% addLegend("bottomright", pal = capacity_palette, values = ~capacity) lp$height <- 300 lp ``` <div id="htmlwidget-1b4ff99564eb6e8884a5" style="width:100%;height:300px;" class="leaflet html-widget"></div> <script type="application/json" data-for="htmlwidget-1b4ff99564eb6e8884a5">{"x":{"options":{"crs":{"crsClass":"L.CRS.EPSG3857","code":null,"proj4def":null,"projectedBounds":null,"options":{}}},"calls":[{"method":"addProviderTiles","args":["CartoDB.Positron",null,null,{"errorTileUrl":"","noWrap":false,"detectRetina":false}]},{"method":"addCircleMarkers","args":[[38.858971,38.85725,38.856425,38.86017,38.857866,38.862303,38.8637,38.857404962615,38.848466,38.8426,38.8533,38.850688,38.9003,38.9177645,38.929464,38.926088,38.922925,38.9268,38.923203,38.9319,38.876697,38.909868,38.913046,38.9086,38.8963,38.9007106254746,38.9366839345886,38.9375,38.9346,38.90304,38.8896,38.9308,38.8600564700757,38.934267,38.878,38.897063,38.901385,38.862669,38.867373,38.8952,38.919077,38.9172,38.9249,38.8743,38.9154,38.9155,38.8543805151775,38.8763,38.9101,38.905697,38.9057107919073,38.90276,38.899408,38.8851,38.8803,38.8829154847676,38.9121,38.944551,38.9126,38.8792,38.938736,38.934881,38.947774,38.865784,38.873057,38.886952,38.899632,38.894,38.8862769450651,38.902314,38.933668,38.894919,38.886266,38.893028,38.897293,38.8842600879717,38.9050668745531,38.947607,38.900426739881,38.897222,38.8990323441632,38.905737,38.927872,38.9037414509194,38.90375,38.878693599397,38.894758,38.916442,38.900283,38.8997,38.932514,38.910972,38.899972,38.900412,38.900413,38.889955,38.890461,38.8692,38.894832,38.915544,38.898364,38.894514,38.8972830485385,38.902061,38.908905,38.895344,38.8923,38.90774,38.899983,38.903827,38.89696,38.897446,38.9055785,38.922581,38.898409,38.844015,38.88412,38.897857,38.896104,38.897315,38.8946,38.893438,38.891696,38.892164,38.86559,38.892459,38.889,38.920669,38.9059956999911,38.9059,38.8904,38.8881,38.88786,38.89968,38.8863719405453,38.88412,38.881185,38.912719,38.9418,38.915417,38.928121111321,38.917761,38.889935,38.930282,38.896544,38.905126,38.88732,38.844711,38.876393,38.8503365778844,38.896923,38.9024,38.885801,38.896355,38.8788543780633,38.922649,38.928743,38.882788,38.88397,38.884734,38.8885525,38.888774,38.87887,38.894573,38.893237,38.89593,38.89054,38.88081,38.881044,38.882629,38.879819,38.866611,38.883921,38.880012,38.884616,38.90509,38.903584,38.903819,38.901539,38.902204,38.803124,38.804718,38.810741479387,38.805317,38.902,38.805648,38.811456,38.814577,38.8057628487349,38.9066,38.895914,38.90088,38.883669,38.884961,38.889365,38.888255,38.894722,38.90093,38.943837,38.954812,38.9054697149433,38.863833,38.8471290457426,38.846222,38.918809,38.956555547722,38.949662,38.9424753556028,38.956432,38.847977,38.8444,38.860789,38.84736,38.84232,38.848454,38.987,38.90849,38.896553,38.955016,38.908142,38.92333,38.889988,38.912659,38.889908,38.897274,38.927095,38.897195,38.928644,38.8432298873083,38.863897,38.903732,38.916787,38.852248,38.865029,38.880705,38.867262,38.887378,38.894851,38.917622,38.918155,38.892275,38.886978,38.887312,38.927497,38.862478,38.856319,38.871822,38.985404,38.981103,38.983838,39.096312,39.093783,38.988562,39.084125,38.98954,39.094772,38.98128,38.983627,38.9948958031902,38.983525,38.961763,38.921074,38.87501,38.920387,38.876737,39.099376,39.083673,39.123513,38.990249,38.982456,39.0253871962363,39.095661,38.989724,38.975,38.977933,38.992375,38.990639,38.977093,39.076331,39.102212,38.9940348506084,38.999388,38.997033,39.114688,39.110314,38.923583,38.9073325611145,38.898536,38.908473,38.878433,38.873755,38.862753,38.999634,38.96115,38.899703,38.837666,38.873219,38.869418,38.9411391565301,39.103091,39.097636,38.997445,39.085394,38.900358,38.952369,38.975219,38.920682,38.866471,38.839912,39.119765,38.964992,39.084379,38.984691,38.88992,38.9037315,38.804378,38.894941,38.869442,38.8982432877485,38.897612,38.901755,38.801111,38.82175,38.802677,38.820064,38.82595,38.820932,38.833077,38.890612,38.986743,38.865553,38.9645435092459,38.903649,38.8982829495236,38.997653,38.912648,38.859254,38.907908,38.896456,38.898984,38.895377,38.898412,38.9147511088312,38.876528,38.8621502452149,38.902814,38.913761,38.86612,38.89967,38.90864,38.961339,38.894474,38.896134,38.915604,38.946182,38.994113,39.105295,38.898301,38.812718,38.80704,38.813485,38.999378,38.884829,38.958267,38.928893,38.912652,38.999679,38.979875,38.903658,38.908643,38.831516,38.893511,38.909394,38.995681,38.799267,38.925284,38.822738,38.90843,38.8843227241466,38.947156,38.894972,38.828437,38.8853207197507,38.880992,38.812711,38.8200582335422,38.8904955,38.890539,38.888097,38.798133,38.843422,38.9024055658419,38.841291,38.797557,38.818748,38.829545,38.928552,38.901136451737,38.949813,38.91263,38.897407,38.870695,38.929261,38.92403,38.919475,38.920939,38.924437,38.931911,38.928919,38.932636,38.970046,38.962524,38.962095,38.960574,38.955079,38.957037,38.884916,38.946607921215,38.959633,38.95534,38.892441,38.891805,38.960084,38.955314,38.951443,38.89386,38.967505,38.923083,38.812351,38.990874,38.9190185,38.882489,38.957463,38.876695,38.915246,38.9029315735912,39.014716,38.98265,38.988101,39.0384238285183,39.044887,39.035965,39.043272,39.032029,38.970412,38.9551705980449,38.968834,38.93293,38.88345,39.121327,38.905368,38.924088,38.888282,38.886048,38.858524,39.12333,38.9642077410062,39.105642,38.8995,38.905424,38.925636,38.912644,38.892556,38.936298,38.892244,38.905303,39.106971,39.106152,38.84627,38.84377,38.84623,38.836,38.824481,38.82373,38.81909,38.883011,38.8964647474865,38.88044,38.915,39.04313,38.95674,38.89222,38.87335,39.062041,39.04753,39.049,39.049765,39.04317,39.04873,39.0629,39.06219,39.00048,38.92245,38.908391,38.899811,38.969581,38.95548,38.936213,39.050539,38.8696831486163,38.848304,38.88994,38.855004,38.931991,38.9170363119413,38.969489,38.876893,38.782633,38.798749,38.9313218587456,38.876751,38.948266303586,38.944707,38.92755,38.902674,38.876211,38.907444,38.897108,38.901935,38.892934,38.923389,38.9240020752018,38.906299,38.897808,38.885908,38.951596,38.835737,38.911268,38.8828,38.959361,38.840107,38.9546,38.975505,38.974205,38.939271,38.9725,38.7968,38.961737,38.9698,38.895172,38.882746,38.88837,38.887403,38.880612,38.878085,38.883601,38.87972,38.885434,38.885621,38.9001,38.928502,38.932128,38.879355,38.870093,38.9243,38.9142043187298,38.912524,38.942146,38.942024743273,38.934405,38.938889,38.955349,38.888524,38.965742,38.962119,38.968842,39.0473228,38.924144,38.9027321642508,38.8959545570075,38.9004684878336,38.7859,38.8366457817575,38.8762274457515,38.8573690375875,38.9126135946919,38.8989254671684,38.8999416268848,38.9097008620447,39.0313141,39.081638,39.0461435,39.0778751,39.0449444,39.0456521,39.0391022,39.085314,39.0379809,39.04104,38.8794766089543,38.8974996,38.8693365,38.949008,38.902386,38.808161,38.840797,38.7930647,38.802738,38.910993,38.8840581,38.866277,38.910674,38.9169364532365,38.8854825273407,38.9657749934222,38.814185,38.801947,38.898308,38.881123,38.958098],[-77.05323,-77.05332,-77.049232,-77.049593,-77.05949,-77.059936,-77.0633,-77.0511317253113,-77.051514,-77.0502,-77.0498,-77.05152,-77.0429,-77.032096,-77.027822,-77.036536,-77.042581,-77.0322,-77.047637,-77.0388,-77.0178975,-77.034421,-77.032008,-77.0323,-77.045,-77.0464485883713,-77.0241808891296,-77.0328,-76.9955,-77.019027,-76.9769,-77.0315,-76.9678397476673,-77.057979,-76.9607,-76.947446,-76.941877,-76.994637,-76.988039,-77.0436,-77.000648,-77.0259,-77.0222,-77.0057,-77.0446,-77.0222,-77.0534116029739,-77.0037,-77.0444,-77.005483,-77.0473176240921,-77.03863,-77.015289,-77.0023,-76.9862,-76.9879066944122,-77.0387,-77.063896,-77.0135,-76.9953,-77.087171,-77.072755,-77.032818,-76.9784,-76.971015,-76.996806,-77.031686,-76.947974,-77.0282417535782,-77.0591415,-76.991016,-77.046587,-77.022241,-77.026013,-77.05557,-76.9947355985641,-77.0417788624763,-77.079382,-76.9882500171661,-77.019347,-77.0333540439606,-77.02227,-77.043358,-77.0424520969391,-77.06269,-77.0230543613434,-76.997114,-77.0682,-77.029822,-77.023086,-76.992889,-77.00495,-76.998347,-77.001949,-76.982872,-77.000349,-76.988355,-76.9599,-76.987633,-77.038252,-77.027869,-77.031617,-77.0221906900406,-77.038322,-77.04478,-77.016106,-77.0436,-77.071652,-76.991383,-77.053485,-77.00493,-77.009888,-77.027313,-77.070334,-77.032278,-77.050537,-77.04657,-77.026975,-77.049882,-77.070993,-77.072305,-77.076389,-77.0846,-77.079375,-76.952103,-77.046567,-77.0925,-77.04368,-77.0398020744324,-77.0325,-77.0889,-77.09308,-77.094875,-77.041539,-77.0177006721497,-77.017445,-77.001828,-77.022155,-77.0251,-77.012289,-77.0237946510315,-77.04062,-76.93723,-77.055599,-76.96012,-77.056887,-76.983569,-76.987823,-77.107735,-77.1009886264801,-77.086502,-77.02622,-77.097745,-77.078408,-77.0057272911072,-77.077271,-77.012457,-77.103148,-77.10783,-77.093485,-77.032427,-77.028694,-77.1207,-77.01994,-77.086063,-77.089006,-77.08095,-77.090792,-77.111768,-77.109366,-77.037413,-76.985238,-77.116817,-77.107854,-77.10108,-76.9941,-77.044789,-77.0284,-77.046564,-77.04337,-77.040363,-77.043363,-77.0446327328682,-77.049883,-77.03353,-77.05293,-77.050276,-77.052808,-77.060626745224,-77.05152,-77.026064,-77.048911,-77.113905,-77.08777,-77.077294,-77.0494365,-77.045128,-77.018677,-77.077078,-77.082426,-77.0021304488182,-77.080319,-77.1125502884388,-77.069275,-77.041571,-77.0198142528534,-77.027333,-77.0326781272888,-77.032947,-77.075104,-77.085931,-77.09586,-77.095431,-77.089555,-77.084918,-77.029417,-77.063586,-77.06714,-77.069956,-77.038359,-77.0352,-76.995193,-77.017669,-76.983326,-76.994749,-76.978924,-76.983575,-76.990955,-76.9993758201599,-76.990037,-76.987211,-77.028139,-77.105022,-77.039511,-77.08596,-77.072315,-77.001955,-77.02324,-77.01597,-77.004746,-77.013917,-77.013769,-77.025762,-76.997194,-77.086599,-77.11153,-77.107906,-77.023082,-77.097426,-77.09221,-77.192672,-77.202501,-77.096539,-77.151291,-77.098029,-77.145213,-77.011336,-77.006311,-77.0295318961143,-77.095367,-77.085998,-77.031887,-77.0024,-77.025672,-76.994468,-77.188014,-77.149162,-77.15741,-77.02935,-77.091991,-77.0445629954338,-77.159048,-77.023854,-77.01121,-77.006472,-77.100104,-77.100239,-77.094589,-77.141378,-77.177091,-77.030371427536,-77.031555,-77.025608,-77.171487,-77.182669,-77.050046,-77.0153604447842,-76.931862,-76.933099,-77.03023,-77.089233,-77.05428,-77.109647,-77.088659,-77.008911,-77.09482,-77.082104,-77.095596,-77.061977237463,-77.196442,-77.196636,-77.023894,-77.145803,-77.012108,-77.002721,-77.016855,-76.995876,-77.076131,-77.087083,-77.166093,-77.103381,-77.146866,-77.094537,-77.071301,-77.0675285,-77.060866,-77.09169,-77.104503,-77.026235461235,-77.080851,-77.051084,-77.068952,-77.047494,-77.063562,-77.057619,-77.058541,-77.053096,-77.059821,-77.084801,-77.000035,-77.05003,-77.0751348137856,-77.034918,-77.039732336998,-77.034499,-77.041834,-77.063275,-76.99707,-77.104562,-77.078317,-77.09713,-77.043182,-77.0035600662231,-77.12712,-77.0681208372116,-77.061043,-77.027025,-77.08787,-77.003666,-77.02277,-77.027855,-76.974828,-76.9929,-76.983683,-77.08059,-77.076986,-77.194774,-77.118009,-77.044097,-77.059817,-77.049468,-77.097882,-77.127671,-77.084636,-77.03625,-77.036278,-77.051168,-77.093522,-77.031737,-77.012365,-77.008133,-77.041544,-77.048728,-77.038721,-77.0447,-77.032375,-77.049265,-77.02714,-77.0260101556778,-77.065115,-77.003135,-77.086031,-77.1564272046089,-77.135271,-77.061715,-77.0628207921982,-77.0172465,-77.049383,-77.038325,-77.0487,-77.064016,-77.0160055160522,-77.063093,-77.053766,-77.047783,-77.047844,-77.032224,-77.0344510674477,-77.080217,-76.971923,-76.925907,-76.982359,-77.240654,-77.235955,-77.221179,-77.222115,-77.217664,-77.219261,-77.225394,-77.231825,-77.338686,-77.361902,-77.358815,-77.356324,-77.351649,-77.359718,-77.005965,-77.3393914103508,-77.358741,-77.357611,-77.048947,-76.913563,-77.353414,-77.368416,-77.340377,-77.070862,-77.027976,-77.227417,-77.040691,-77.016311,-77.034449,-76.990121,-77.002239,-77.112982,-77.220157,-76.9984048604965,-77.107143,-76.988311,-76.988422,-77.0510204136372,-77.047788,-77.048395,-77.053085,-77.04735,-77.340787,-77.3630940914154,-77.016258,-76.979517,-77.021741,-77.164969,-77.065149,-77.040787,-77.111066,-77.142317,-77.103728,-77.16486,-77.3542803525925,-77.20386,-77.054155,-77.034674,-77.027112,-77.04564,-77.082089,-77.087128,-77.033234,-77.050264,-77.192645,-77.202354,-76.98185,-76.97557,-76.97703,-77.0009,-77.005497,-77.00223,-77.01033,-76.949709,-77.0333808660507,-77.025236,-77.0078,-77.05006,-77.02791,-76.99602,-76.9834,-77.120776,-77.11287,-77.10933,-77.11368,-77.1135,-77.1257,-77.113454,-77.129235,-77.00077,-77.23034,-76.843263,-76.844604,-76.937349,-76.940133,-76.960054,-77.115212,-77.0108154416084,-77.05122,-76.825535,-76.984512,-77.032956,-77.0957824587822,-77.033509,-77.006072,-77.016059,-77.00103,-77.0282471179962,-77.000736,-77.0219814777374,-77.335425,-77.226644,-77.021392,-77.012443,-76.986813,-77.011616,-76.978627,-76.998273,-77.051833,-77.208126783371,-76.983221,-76.979634,-76.991476,-77.012808,-76.983144,-77.041829,-77.2283,-76.946361,-76.9753,-76.9595,-76.952199,-76.995227,-76.955047,-76.9807,-77.0026,-76.995922,-76.9493,-77.190275,-77.170032,-77.179832,-77.176992,-77.171891,-77.153953,-77.173438,-77.178408,-77.173605,-77.166917,-77.1887,-77.215624,-77.0235,-77.230681,-77.22997,-76.9859,-77.0221745967865,-77.008798,-77.038684,-77.0182210206985,-77.032687,-76.997226,-76.968084,-76.913188,-76.954803,-76.935578,-76.954171,-77.0510969,-77.002098,-76.9951499107992,-76.9678448436025,-76.9472873210907,-77.0167,-76.9935888651526,-76.9409812695812,-76.9776880143763,-77.0743226407649,-77.0488523839595,-76.994896531105,-77.0856463909149,-77.0041217,-76.945495,-76.9757228,-76.9418666,-76.9811552,-76.9854381,-76.9918237,-76.940547,-76.9945391,-76.987125,-77.1145632863045,-76.9695094,-76.978519,-77.355299,-77.005649,-77.100796,-77.108938,-77.049436,-77.0824626,-76.985854,-76.9863852,-77.010551,-77.03188,-77.0296311378479,-76.9771872460842,-77.0686264336109,-77.043809,-77.040293,-77.046402,-77.017658,-77.013821],2,null,null,{"interactive":true,"className":"","stroke":false,"color":["#DFF3DA","#EFF9E9","#D8F0D2","#EFF9E9","#EFF9E9","#D1EDCB","#DFF3DA","#D1EDCB","#EFF9E9","#EFF9E9","#D8F0D2","#EFF9E9","#DFF3DA","#84CFC1","#A3DBB7","#75C8C7","#DFF3DA","#D1EDCB","#BCE5BE","#D1EDCB","#BCE5BE","#D1EDCB","#D1EDCB","#BCE5BE","#DFF3DA","#66BFCC","#D1EDCB","#D1EDCB","#EFF9E9","#D1EDCB","#DFF3DA","#D1EDCB","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#EFF9E9","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#DFF3DA","#EFF9E9","#47AACE","#DFF3DA","#DFF3DA","#DFF3DA","#D8F0D2","#2582B9","#DFF3DA","#D1EDCB","#BCE5BE","#DFF3DA","#EFF9E9","#DFF3DA","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#D1EDCB","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#BCE5BE","#EFF9E9","#47AACE","#D1EDCB","#EFF9E9","#DFF3DA","#DFF3DA","#84CFC1","#D1EDCB","#D1EDCB","#A3DBB7","#DFF3DA","#A3DBB7","#84CFC1","#D1EDCB","#BCE5BE","#DFF3DA","#D1EDCB","#BCE5BE","#D1EDCB","#BCE5BE","#EFF9E9","#BCE5BE","#75C8C7","#DFF3DA","#BCE5BE","#BCE5BE","#D1EDCB","#DFF3DA","#EFF9E9","#B0E0B8","#EFF9E9","#84CFC1","#BCE5BE","#47AACE","#B0E0B8","#A3DBB7","#66BFCC","#D1EDCB","#DFF3DA","#66BFCC","#BCE5BE","#D1EDCB","#DFF3DA","#084081","#BCE5BE","#D1EDCB","#DFF3DA","#BCE5BE","#BCE5BE","#84CFC1","#DFF3DA","#D1EDCB","#A3DBB7","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#BCE5BE","#75C8C7","#EFF9E9","#DFF3DA","#D8F0D2","#A3DBB7","#47AACE","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#EFF9E9","#DFF3DA","#BCE5BE","#EFF9E9","#EFF9E9","#DFF3DA","#D1EDCB","#DFF3DA","#D1EDCB","#EFF9E9","#DFF3DA","#BCE5BE","#66BFCC","#DFF3DA","#D1EDCB","#EFF9E9","#D1EDCB","#EFF9E9","#EFF9E9","#BCE5BE","#EFF9E9","#BCE5BE","#DFF3DA","#EFF9E9","#DFF3DA","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#BCE5BE","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#A3DBB7","#DFF3DA","#D8F0D2","#94D5BC","#BCE5BE","#DFF3DA","#DFF3DA","#DFF3DA","#EFF9E9","#EFF9E9","#EFF9E9","#B0E0B8","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#BCE5BE","#DFF3DA","#EFF9E9","#DFF3DA","#BCE5BE","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#EFF9E9","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#EFF9E9","#DFF3DA","#D1EDCB","#E7F6E1","#BCE5BE","#D1EDCB","#DFF3DA","#EFF9E9","#DFF3DA","#EFF9E9","#BCE5BE","#DFF3DA","#D1EDCB","#DFF3DA","#EFF9E9","#EFF9E9","#DFF3DA","#84CFC1","#EFF9E9","#DFF3DA","#EFF9E9","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#A3DBB7","#84CFC1","#DFF3DA","#DFF3DA","#EBF7E5","#DFF3DA","#EFF9E9","#DFF3DA","#D1EDCB","#EFF9E9","#D1EDCB","#D1EDCB","#EFF9E9","#DFF3DA","#EFF9E9","#F7FCF0","#DFF3DA","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#C9EAC4","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#EFF9E9","#EFF9E9","#DFF3DA","#D1EDCB","#DFF3DA","#EFF9E9","#DFF3DA","#D5EECF","#DFF3DA","#D1EDCB","#D1EDCB","#EFF9E9","#DFF3DA","#D8F0D2","#EFF9E9","#D8F0D2","#D1EDCB","#D1EDCB","#D1EDCB","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#EFF9E9","#E7F6E1","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#EFF9E9","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#EBF7E5","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#BCE5BE","#DFF3DA","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#C9EAC4","#DFF3DA","#DFF3DA","#D1EDCB","#BCE5BE","#84CFC1","#DFF3DA","#D1EDCB","#DFF3DA","#BCE5BE","#DFF3DA","#DFF3DA","#DFF3DA","#BCE5BE","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#BCE5BE","#DFF3DA","#BCE5BE","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#BCE5BE","#D1EDCB","#DFF3DA","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#BCE5BE","#D1EDCB","#D1EDCB","#BCE5BE","#D1EDCB","#BCE5BE","#BCE5BE","#D1EDCB","#DFF3DA","#D1EDCB","#DFF3DA","#84CFC1","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#BCE5BE","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#84CFC1","#B0E0B8","#B0E0B8","#D1EDCB","#DFF3DA","#BCE5BE","#DFF3DA","#EFF9E9","#EFF9E9","#EFF9E9","#EFF9E9","#E7F6E1","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#D8F0D2","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#BCE5BE","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#EFF9E9","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#BCE5BE","#DFF3DA","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#D1EDCB","#EFF9E9","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#DFF3DA","#D1EDCB","#DFF3DA","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#E3F4DE","#B0E0B8","#DFF3DA","#DFF3DA","#DCF1D6","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#E7F6E1","#DFF3DA","#D8F0D2","#D8F0D2","#DFF3DA","#DFF3DA","#DFF3DA","#BCE5BE","#EFF9E9","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#EFF9E9","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#75C8C7","#D1EDCB","#D1EDCB","#BCE5BE","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#D8F0D2","#DFF3DA","#DFF3DA","#DFF3DA","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#D1EDCB","#EFF9E9","#D1EDCB","#EFF9E9","#EFF9E9","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D8F0D2","#D1EDCB","#D8F0D2","#D8F0D2","#D8F0D2","#EFF9E9","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D8F0D2","#D1EDCB","#D8F0D2","#D1EDCB","#D1EDCB","#DFF3DA","#BCE5BE","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#EBF7E5","#D1EDCB","#D1EDCB","#EFF9E9","#D8F0D2","#EBF7E5","#D1EDCB","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB"],"weight":5,"opacity":0.5,"fill":true,"fillColor":["#DFF3DA","#EFF9E9","#D8F0D2","#EFF9E9","#EFF9E9","#D1EDCB","#DFF3DA","#D1EDCB","#EFF9E9","#EFF9E9","#D8F0D2","#EFF9E9","#DFF3DA","#84CFC1","#A3DBB7","#75C8C7","#DFF3DA","#D1EDCB","#BCE5BE","#D1EDCB","#BCE5BE","#D1EDCB","#D1EDCB","#BCE5BE","#DFF3DA","#66BFCC","#D1EDCB","#D1EDCB","#EFF9E9","#D1EDCB","#DFF3DA","#D1EDCB","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#EFF9E9","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#DFF3DA","#EFF9E9","#47AACE","#DFF3DA","#DFF3DA","#DFF3DA","#D8F0D2","#2582B9","#DFF3DA","#D1EDCB","#BCE5BE","#DFF3DA","#EFF9E9","#DFF3DA","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#D1EDCB","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#BCE5BE","#EFF9E9","#47AACE","#D1EDCB","#EFF9E9","#DFF3DA","#DFF3DA","#84CFC1","#D1EDCB","#D1EDCB","#A3DBB7","#DFF3DA","#A3DBB7","#84CFC1","#D1EDCB","#BCE5BE","#DFF3DA","#D1EDCB","#BCE5BE","#D1EDCB","#BCE5BE","#EFF9E9","#BCE5BE","#75C8C7","#DFF3DA","#BCE5BE","#BCE5BE","#D1EDCB","#DFF3DA","#EFF9E9","#B0E0B8","#EFF9E9","#84CFC1","#BCE5BE","#47AACE","#B0E0B8","#A3DBB7","#66BFCC","#D1EDCB","#DFF3DA","#66BFCC","#BCE5BE","#D1EDCB","#DFF3DA","#084081","#BCE5BE","#D1EDCB","#DFF3DA","#BCE5BE","#BCE5BE","#84CFC1","#DFF3DA","#D1EDCB","#A3DBB7","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#BCE5BE","#75C8C7","#EFF9E9","#DFF3DA","#D8F0D2","#A3DBB7","#47AACE","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#EFF9E9","#DFF3DA","#BCE5BE","#EFF9E9","#EFF9E9","#DFF3DA","#D1EDCB","#DFF3DA","#D1EDCB","#EFF9E9","#DFF3DA","#BCE5BE","#66BFCC","#DFF3DA","#D1EDCB","#EFF9E9","#D1EDCB","#EFF9E9","#EFF9E9","#BCE5BE","#EFF9E9","#BCE5BE","#DFF3DA","#EFF9E9","#DFF3DA","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#BCE5BE","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#A3DBB7","#DFF3DA","#D8F0D2","#94D5BC","#BCE5BE","#DFF3DA","#DFF3DA","#DFF3DA","#EFF9E9","#EFF9E9","#EFF9E9","#B0E0B8","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#BCE5BE","#DFF3DA","#EFF9E9","#DFF3DA","#BCE5BE","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#EFF9E9","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#EFF9E9","#DFF3DA","#D1EDCB","#E7F6E1","#BCE5BE","#D1EDCB","#DFF3DA","#EFF9E9","#DFF3DA","#EFF9E9","#BCE5BE","#DFF3DA","#D1EDCB","#DFF3DA","#EFF9E9","#EFF9E9","#DFF3DA","#84CFC1","#EFF9E9","#DFF3DA","#EFF9E9","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#A3DBB7","#84CFC1","#DFF3DA","#DFF3DA","#EBF7E5","#DFF3DA","#EFF9E9","#DFF3DA","#D1EDCB","#EFF9E9","#D1EDCB","#D1EDCB","#EFF9E9","#DFF3DA","#EFF9E9","#F7FCF0","#DFF3DA","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#C9EAC4","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#EFF9E9","#EFF9E9","#DFF3DA","#D1EDCB","#DFF3DA","#EFF9E9","#DFF3DA","#D5EECF","#DFF3DA","#D1EDCB","#D1EDCB","#EFF9E9","#DFF3DA","#D8F0D2","#EFF9E9","#D8F0D2","#D1EDCB","#D1EDCB","#D1EDCB","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#EFF9E9","#E7F6E1","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#EFF9E9","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#EBF7E5","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#BCE5BE","#DFF3DA","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#C9EAC4","#DFF3DA","#DFF3DA","#D1EDCB","#BCE5BE","#84CFC1","#DFF3DA","#D1EDCB","#DFF3DA","#BCE5BE","#DFF3DA","#DFF3DA","#DFF3DA","#BCE5BE","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#BCE5BE","#DFF3DA","#BCE5BE","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#BCE5BE","#D1EDCB","#DFF3DA","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#BCE5BE","#D1EDCB","#D1EDCB","#BCE5BE","#D1EDCB","#BCE5BE","#BCE5BE","#D1EDCB","#DFF3DA","#D1EDCB","#DFF3DA","#84CFC1","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#BCE5BE","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#84CFC1","#B0E0B8","#B0E0B8","#D1EDCB","#DFF3DA","#BCE5BE","#DFF3DA","#EFF9E9","#EFF9E9","#EFF9E9","#EFF9E9","#E7F6E1","#EFF9E9","#EFF9E9","#EFF9E9","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#D8F0D2","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#BCE5BE","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#EFF9E9","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#EFF9E9","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#BCE5BE","#DFF3DA","#DFF3DA","#DFF3DA","#EFF9E9","#DFF3DA","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#D1EDCB","#EFF9E9","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#DFF3DA","#D1EDCB","#DFF3DA","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#E3F4DE","#B0E0B8","#DFF3DA","#DFF3DA","#DCF1D6","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#E7F6E1","#DFF3DA","#D8F0D2","#D8F0D2","#DFF3DA","#DFF3DA","#DFF3DA","#BCE5BE","#EFF9E9","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#EFF9E9","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB","#75C8C7","#D1EDCB","#D1EDCB","#BCE5BE","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#DFF3DA","#D8F0D2","#DFF3DA","#DFF3DA","#DFF3DA","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#EBF7E5","#D1EDCB","#EFF9E9","#D1EDCB","#EFF9E9","#EFF9E9","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D8F0D2","#D1EDCB","#D8F0D2","#D8F0D2","#D8F0D2","#EFF9E9","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D8F0D2","#D1EDCB","#D8F0D2","#D1EDCB","#D1EDCB","#DFF3DA","#BCE5BE","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#EBF7E5","#D1EDCB","#D1EDCB","#EFF9E9","#D8F0D2","#EBF7E5","#D1EDCB","#DFF3DA","#DFF3DA","#D1EDCB","#D1EDCB","#DFF3DA","#D1EDCB","#D1EDCB","#D1EDCB","#D1EDCB","#DFF3DA","#EFF9E9","#D1EDCB","#D1EDCB","#D1EDCB"],"fillOpacity":1},null,null,null,null,null,{"interactive":false,"permanent":false,"direction":"auto","opacity":1,"offset":[0,0],"textsize":"10px","textOnly":false,"className":"","sticky":true},null]},{"method":"addLegend","args":[{"colors":["#F7FCF0 , #F3FAEC 2.17391304347826%, #DFF3DA 13.0434782608696%, #CEECC7 23.9130434782609%, #B0E0B8 34.7826086956522%, #8CD2BF 45.6521739130435%, #66BFCC 56.5217391304348%, #42A4CB 67.3913043478261%, #2582B9 78.2608695652174%, #0963A6 89.1304347826087%, #084081 100%, #084081 "],"labels":["10","15","20","25","30","35","40","45","50","55"],"na_color":null,"na_label":"NA","opacity":0.5,"position":"bottomright","type":"numeric","title":"capacity","extra":{"p_1":0.0217391304347826,"p_n":1},"layerId":null,"className":"info legend","group":null}]}],"limits":{"lat":[38.782633,39.123513],"lng":[-77.368416,-76.825535]}},"evals":[],"jsHooks":[]}</script> ] See `leaflet` docs on [markers](http://rstudio.github.io/leaflet/markers.html) and [colors](http://rstudio.github.io/leaflet/colors.html) --- ## Levels of API security 1. No key needed 2. Shared secret 3. Access delegation (typically [OAuth](https://en.wikipedia.org/wiki/OAuth)) --- ### A typical access delegation dance .center[ <img src="https://developer.spotify.com/assets/AuthG_AuthoriztionCode.png" width="50%" style="display: block; margin: auto;" /> ] .floating-source[ Source: [Spotify Developer Docs](https://developer.spotify.com/documentation/general/guides/authorization-guide/) ] --- class: center, middle ## SQL --- ## Grammar of Data | dplyr (`data %>% `) | Pandas | SQL | |-------|--------|-----| | `select(col1, col2)` | `data[['col1', 'col2']]` | `SELECT col1, col2 FROM data` | | `filter(col1 > 5)` | `data.query('col1 > 5')` | `SELECT * FROM data WHERE col1 > 5` | | `left_join(data2, by = "col2")` | `pd.merge(data, data2, by="col2", type="left")` | `SELECT * FROM data LEFT JOIN data2 ON data.col2 == data2.col2` | | `group_by(col2) %>% summarize(m = max(col1))` | `data.group_by('col2')['col1'].max()` | `SELECT max(col1) AS m FROM data GROUP BY col2` | | `pivot_longer()` | `data.melt()` or `data.pivot()` | **No standard approach** | * [SQLite syntax](https://sqlite.org/lang_select.html) * [More examples](https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_r.html) --- ## BigQuery To use BigQuery, you need to create a project in Google Cloud Platform to use for billing. ```r billing_project <- "calvindsdev" ``` Then you can use `bigrquery` to get set up to make BigQuery API calls. ```r library(bigrquery) bigrquery::bq_auth(email = TRUE) bq_connection <- DBI::dbConnect( bigrquery::bigquery(), project = "bigquery-public-data", billing = billing_project ) ``` --- ## A COVID Example ```r case_counts %>% kable() ``` |country_code |country_name | cumulative_confirmed| population_2018| cases_per_capita| |:------------|:------------------------|--------------------:|---------------:|----------------:| |US |United States of America | 13091758| 327167434| 0.0400155| |BR |Brazil | 6238350| 209469333| 0.0297817| |RU |Russia | 2215533| 144478050| 0.0153347| |MX |Mexico | 1078594| 126190788| 0.0085473| |IN |India | 9351109| 1352617328| 0.0069133| |PH |Philippines | 425918| 106651922| 0.0039935| |BD |Bangladesh | 458711| 161356039| 0.0028428| |ID |Indonesia | 522581| 267663435| 0.0019524| |PK |Pakistan | 392356| 212215030| 0.0018489| |JP |Japan | 142068| 126529100| 0.0011228| --- ```r covid_open_data <- bq_connection %>% tbl("bigquery-public-data.covid19_open_data.covid19_open_data") covid_open_data %>% glimpse() ``` ``` ## Rows: ?? ## Columns: 45 ## Database: BigQueryConnection ## $ date <date> 2020-11-25, 2020-11-26, 2020-11-27, 2020-11-28, 2020-… ## $ location_key <chr> "US_WA_53053", "US_WA_53053", "US_WA_53053", "US_WA_53… ## $ country_code <chr> "US", "US", "US", "US", "US", "US", "US", "US", "US", … ## $ country_name <chr> "United States of America", "United States of America"… ## $ subregion1_code <chr> "WA", "WA", "WA", "WA", "WA", "WA", "WA", "WA", "WA", … ## $ subregion1_name <chr> "Washington", "Washington", "Washington", "Washington"… ## $ subregion2_code <chr> "53053", "53053", "53053", "53053", "53053", "53053", … ## $ subregion2_name <chr> "Pierce County", "Pierce County", "Pierce County", "Pi… ## $ iso_3166_1_alpha_2 <chr> "US", "US", "US", "US", "US", "US", "US", "US", "US", … ## $ iso_3166_1_alpha_3 <chr> "USA", "USA", "USA", "USA", "USA", "USA", "USA", "USA"… ## $ aggregation_level <int> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, … ## $ new_confirmed <int> 288, 0, 135, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N… ## $ new_deceased <int> 6, 0, 0, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, N… ## $ new_recovered <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ new_tested <int> 676, 611, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, … ## $ cumulative_confirmed <int> 15658, 15658, 17290, NA, NA, NA, NA, NA, NA, NA, NA, N… ## $ cumulative_deceased <int> 274, 274, 308, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA,… ## $ cumulative_recovered <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ cumulative_tested <int> 117125, 117736, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ new_hospitalized_patients <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ new_intensive_care_patients <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ new_ventilator_patients <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ cumulative_hospitalized_patients <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ cumulative_intensive_care_patients <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ cumulative_ventilator_patients <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ current_hospitalized_patients <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ current_intensive_care_patients <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ current_ventilator_patients <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ mobility_transit_stations <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ mobility_retail_and_recreation <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ mobility_grocery_and_pharmacy <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ mobility_parks <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ mobility_residential <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ mobility_workplaces <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA… ## $ wikidata_id <chr> "Q156459", "Q156459", "Q156459", "Q156459", "Q156459",… ## $ datacommons_id <chr> "geoId/53053", "geoId/53053", "geoId/53053", "geoId/53… ## $ openstreetmap_id <int> 1153347, 1153347, 1153347, 1153347, 1153347, 1153347, … ## $ latitude <dbl> 47.05, 47.05, 47.05, 47.05, 47.05, 47.05, 48.57, 48.57… ## $ longitude <dbl> -122.11, -122.11, -122.11, -122.11, -122.11, -122.11, … ## $ location_geometry <wk_wkt> POINT (-122.11 47.05), POINT (-122.11 47.05), POINT… ## $ average_temperature_celsius <dbl> NA, NA, NA, NA, NA, NA, 10.000000, 6.155556, 8.277778,… ## $ minimum_temperature_celsius <dbl> NA, NA, NA, NA, NA, NA, 8.077778, 4.033333, 4.388889, … ## $ maximum_temperature_celsius <dbl> NA, NA, NA, NA, NA, NA, 11.555556, 9.311111, 11.633333… ## $ rainfall_mm <dbl> NA, NA, NA, NA, NA, NA, 5.207000, 0.095250, 4.159250, … ## $ snowfall_mm <dbl> NA, NA, NA, NA, NA, NA, NA, 10.16, NA, NA, 10.16, NA, … ``` --- ```r world_bank_global_pop <- bq_connection %>% tbl("bigquery-public-data.world_bank_global_population.population_by_country") world_bank_global_pop %>% glimpse() ``` ``` ## Rows: ?? ## Columns: 62 ## Database: BigQueryConnection ## $ country <chr> "Sint Maarten (Dutch part)", "Not classified", "West Bank and Gaza", "Serbia… ## $ country_code <chr> "SXM", "INX", "PSE", "SRB", "KHM", "ARE", "CYP", "LMC", "MDV", "LIC", "PER",… ## $ year_1960 <int> NA, NA, NA, NA, 5722370, 92418, 572930, 928490499, 89863, 158779911, 1015501… ## $ year_1961 <int> NA, NA, NA, NA, 5872966, 100796, 576395, 949753699, 92325, 162259865, 104466… ## $ year_1962 <int> NA, NA, NA, NA, 6028431, 112118, 577691, 971742838, 94905, 165830670, 107494… ## $ year_1963 <int> NA, NA, NA, NA, 6183584, 125130, 577913, 994420244, 97551, 169529568, 110623… ## $ year_1964 <int> NA, NA, NA, NA, 6331449, 138039, 578625, 1017725630, 100171, 173410029, 1138… ## $ year_1965 <int> NA, NA, NA, NA, 6467197, 149857, 580966, 1041611332, 102718, 177509185, 1171… ## $ year_1966 <int> NA, NA, NA, NA, 6585035, 159976, 585309, 1066074552, 105136, 181839891, 1204… ## $ year_1967 <int> NA, NA, NA, NA, 6685960, 169771, 591308, 1091126634, 107478, 186385902, 1238… ## $ year_1968 <int> NA, NA, NA, NA, 6779787, 182627, 598495, 1116788548, 109891, 191120090, 1273… ## $ year_1969 <int> NA, NA, NA, NA, 6880623, 203106, 606116, 1143088505, 112582, 196001924, 1309… ## $ year_1970 <int> NA, NA, NA, NA, 6996576, 234514, 613618, 1170050244, 115691, 201001375, 1345… ## $ year_1971 <int> NA, NA, NA, NA, 7139647, 277471, 620859, 1197695284, 119302, 206110337, 1383… ## $ year_1972 <int> NA, NA, NA, NA, 7302111, 330974, 627998, 1226045232, 123354, 211335895, 1422… ## $ year_1973 <int> NA, NA, NA, NA, 7449238, 394624, 635107, 1255126850, 127701, 216681265, 1461… ## $ year_1974 <int> NA, NA, NA, NA, 7533336, 467451, 642332, 1284982577, 132104, 222154521, 1501… ## $ year_1975 <int> NA, NA, NA, NA, 7524447, 548301, 649751, 1315638970, 136417, 227761362, 1542… ## $ year_1976 <int> NA, NA, NA, NA, 7404684, 637922, 657527, 1347077568, 140563, 233513206, 1583… ## $ year_1977 <int> NA, NA, NA, NA, 7196042, 735344, 665525, 1379310126, 144631, 239409309, 1625… ## $ year_1978 <int> NA, NA, NA, NA, 6957265, 835508, 673247, 1412439164, 148780, 245430650, 1668… ## $ year_1979 <int> NA, NA, NA, NA, 6770396, 931749, 680005, 1446597778, 153276, 251550765, 1711… ## $ year_1980 <int> NA, NA, NA, NA, 6693764, 1019509, 685405, 1481859807, 158272, 257758125, 175… ## $ year_1981 <int> NA, NA, NA, NA, 6749847, 1096610, 689173, 1518293900, 163824, 264051673, 179… ## $ year_1982 <int> NA, NA, NA, NA, 6919801, 1164806, 691702, 1555686714, 169851, 270462062, 184… ## $ year_1983 <int> NA, NA, NA, NA, 7169997, 1228459, 694077, 1593969308, 176244, 277049449, 188… ## $ year_1984 <int> NA, NA, NA, NA, 7447853, 1293971, 697714, 1632885169, 182850, 283893524, 193… ## $ year_1985 <int> NA, NA, NA, NA, 7714880, 1366164, 703685, 1672196713, 189537, 291057942, 197… ## $ year_1986 <int> NA, NA, NA, NA, 7960949, 1446380, 712340, 1711883766, 196264, 298550548, 202… ## $ year_1987 <int> NA, NA, NA, NA, 8198082, 1533536, 723376, 1751925747, 203028, 306374108, 206… ## $ year_1988 <int> NA, NA, NA, NA, 8435912, 1627066, 736474, 1792245451, 209785, 314578310, 211… ## $ year_1989 <int> NA, NA, NA, NA, 8691334, 1725681, 751047, 1832773126, 216510, 323219957, 216… ## $ year_1990 <int> NA, NA, 1978248, 7586000, 8975597, 1828432, 766615, 1875358869, 223158, 3323… ## $ year_1991 <int> NA, NA, 2068845, 7595636, 9289299, 1937153, 783124, 1915913909, 229751, 3419… ## $ year_1992 <int> NA, NA, 2163591, 7646424, 9623889, 2052891, 800611, 1956554979, 236265, 3520… ## $ year_1993 <int> NA, NA, 2262676, 7699307, 9970733, 2173139, 818746, 1997079030, 242605, 3625… ## $ year_1994 <int> NA, NA, 2366298, 7734639, 10317899, 2294385, 837110, 2037399623, 248588, 373… ## $ year_1995 <int> NA, NA, 2474666, 7625357, 10656138, 2415090, 855383, 2077790619, 254139, 383… ## $ year_1996 <int> NA, NA, 2587997, 7617794, 10982917, 2539126, 873423, 2118416707, 259183, 394… ## $ year_1997 <int> NA, NA, 2706518, 7596501, 11298600, 2671362, 891190, NA, 263842, 405123344, … ## $ year_1998 <int> 31240, NA, 2776568, 7567745, 11600508, 2813214, 908702, NA, 268455, 41595941… ## $ year_1999 <int> 31084, NA, 2848431, 7540401, 11886458, 2966034, 926046, NA, 273527, 42714044… ## $ year_2000 <int> 30519, NA, 2922153, 7516346, 12155239, 3134062, 943290, NA, 279398, 43879943… ## $ year_2001 <int> 30600, NA, 2997784, 7503433, 12405408, 3302719, 960276, NA, 286306, 45096207… ## $ year_2002 <int> 30777, NA, 3075373, 7496522, 12637727, 3478777, 976964, NA, 294186, 46358476… ## $ year_2003 <int> 31472, NA, 3154969, 7480591, 12856163, 3711932, 993563, NA, 302683, 47664537… ## $ year_2004 <int> 32488, NA, 3236626, 7463157, 13066469, 4068570, 1010408, NA, 311265, 4900999… ## $ year_2005 <int> 33011, NA, 3320396, 7440769, 13273354, 4588225, 1027662, NA, 319608, 5039071… ## $ year_2006 <int> 33441, NA, 3406334, 7411569, 13477709, 5300174, 1045507, NA, 327487, 5180974… ## $ year_2007 <int> 33811, NA, 3494496, 7381579, 13679962, 6168838, 1063713, NA, 335169, 5326635… ## $ year_2008 <int> 33964, NA, 3591977, 7350222, 13883834, 7089487, 1081563, NA, 343452, 5474812… ## $ year_2009 <int> 34238, NA, 3689099, 7320807, 14093604, 7917372, 1098083, NA, 353398, 5623860… ## $ year_2010 <int> 34056, NA, 3786161, 7291436, 14312212, 8549988, 1112612, NA, 365734, 5772740… ## $ year_2011 <int> 33435, NA, 3882986, 7234099, 14541423, 8946777, 1124833, NA, 380495, 5920921… ## $ year_2012 <int> 34640, NA, 3979998, 7199077, 14780454, 9141596, 1135046, NA, 397237, 6069133… ## $ year_2013 <int> 36607, NA, 4076708, 7164132, 15026332, 9197910, 1143866, NA, 415593, 6219158… ## $ year_2014 <int> 37685, NA, 4173398, 7130576, 15274503, 9214175, 1152285, NA, 435015, 6373506… ## $ year_2015 <int> 38825, NA, 4270092, 7095383, 15521436, 9262900, 1160985, NA, 454915, 6534052… ## $ year_2016 <int> 39969, NA, 4367088, 7058322, 15766293, 9360980, 1170187, NA, 475513, 6701173… ## $ year_2017 <int> 40574, NA, 4454805, 7020858, 16009414, 9487203, 1179680, NA, 496402, 6874495… ## $ year_2018 <int> 40654, NA, 4569087, 6982084, 16249798, 9630959, 1189265, NA, 515696, 7054173… ## $ year_2019 <int> NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, … ``` --- .pull-left[ R (`dbplyr`) ```r country_pop <- world_bank_global_pop %>% select( iso_3166_1_alpha_3 = country_code, population_2018 = year_2018) ``` ] .pull-right[ BigQuery SQL ```sql SELECT country_code AS iso_3166_1_alpha_3, year_2018 AS population_2018 FROM `bigquery-public-data.world_bank_global_population.population_by_country`) ``` ] --- ## Common Table Expression (`WITH`); `JOIN` .pull-left[ ```r country_pop <- world_bank_global_pop %>% select( iso_3166_1_alpha_3 = country_code, population_2018 = year_2018) case_counts_query <- covid_open_data %>% * left_join( * country_pop, by = "iso_3166_1_alpha_3") ``` ] .pull-right[ ```sql *WITH * country_pop AS ( SELECT country_code AS iso_3166_1_alpha_3, year_2018 AS population_2018 FROM `bigquery-public-data.world_bank_global_population.population_by_country`) SELECT * FROM `bigquery-public-data.covid19_open_data.covid19_open_data` *JOIN country_pop USING (iso_3166_1_alpha_3) ``` ] --- .pull-left[ ```r country_pop <- world_bank_global_pop %>% select( iso_3166_1_alpha_3 = country_code, population_2018 = year_2018) case_counts_query <- covid_open_data %>% left_join( country_pop, by = "iso_3166_1_alpha_3") %>% * filter( * date == '2020-11-27', * aggregation_level == 0, * population_2018 > 100000000) ``` ] .pull-right[ ```sql WITH country_pop AS ( SELECT country_code AS iso_3166_1_alpha_3, year_2018 AS population_2018 FROM `bigquery-public-data.world_bank_global_population.population_by_country`) SELECT * FROM `bigquery-public-data.covid19_open_data.covid19_open_data` JOIN country_pop USING (iso_3166_1_alpha_3) *WHERE * date = '2020-11-27' * AND aggregation_level = 0 * AND population_2018 > 100000000 ``` ] --- .pull-left[ ```r country_pop <- world_bank_global_pop %>% select( iso_3166_1_alpha_3 = country_code, population_2018 = year_2018) case_counts_query <- covid_open_data %>% left_join( country_pop, by = "iso_3166_1_alpha_3") %>% filter( date == '2020-11-27', aggregation_level == 0, population_2018 > 100000000) %>% * mutate( * cases_per_capita = * cumulative_confirmed / population_2018 * ) ``` ] .pull-right[ ```sql WITH country_pop AS ( SELECT country_code AS iso_3166_1_alpha_3, year_2018 AS population_2018 FROM `bigquery-public-data.world_bank_global_population.population_by_country`) SELECT *, * cumulative_confirmed/population_2018 AS cases_per_capita FROM `bigquery-public-data.covid19_open_data.covid19_open_data` JOIN country_pop USING (iso_3166_1_alpha_3) WHERE date = '2020-11-27' AND aggregation_level = 0 AND population_2018 > 100000000 ``` ] --- .pull-left[ ```r country_pop <- world_bank_global_pop %>% select( iso_3166_1_alpha_3 = country_code, population_2018 = year_2018) case_counts_query <- covid_open_data %>% left_join( country_pop, by = "iso_3166_1_alpha_3") %>% filter( date == '2020-11-27', aggregation_level == 0, population_2018 > 100000000) %>% mutate( cases_per_capita = cumulative_confirmed / population_2018 ) %>% * select( * country_code, country_name, * cumulative_confirmed, * population_2018, * cases_per_capita) ``` ] .pull-right[ ```sql WITH country_pop AS ( SELECT country_code AS iso_3166_1_alpha_3, year_2018 AS population_2018 FROM `bigquery-public-data.world_bank_global_population.population_by_country`) SELECT * country_code, * country_name, * cumulative_confirmed, * population_2018, * cumulative_confirmed/population_2018 AS cases_per_capita FROM `bigquery-public-data.covid19_open_data.covid19_open_data` JOIN country_pop USING (iso_3166_1_alpha_3) WHERE date = '2020-11-27' AND aggregation_level = 0 AND population_2018 > 100000000 ``` ] --- .pull-left[ ```r country_pop <- world_bank_global_pop %>% select( iso_3166_1_alpha_3 = country_code, population_2018 = year_2018) case_counts_query <- covid_open_data %>% left_join( country_pop, by = "iso_3166_1_alpha_3") %>% filter( date == '2020-11-27', aggregation_level == 0, population_2018 > 100000000) %>% mutate( cases_per_capita = cumulative_confirmed / population_2018 ) %>% select( country_code, country_name, cumulative_confirmed, population_2018, cases_per_capita) %>% * arrange(desc(cases_per_capita)) ``` ] .pull-right[ ```sql WITH country_pop AS ( SELECT country_code AS iso_3166_1_alpha_3, year_2018 AS population_2018 FROM `bigquery-public-data.world_bank_global_population.population_by_country`) SELECT country_code, country_name, cumulative_confirmed, population_2018, cumulative_confirmed/population_2018 AS cases_per_capita FROM `bigquery-public-data.covid19_open_data.covid19_open_data` JOIN country_pop USING (iso_3166_1_alpha_3) WHERE date = '2020-11-27' AND aggregation_level = 0 AND population_2018 > 100000000 *ORDER BY * cases_per_capita DESC ``` ] --- ```r country_pop <- world_bank_global_pop %>% select(iso_3166_1_alpha_3 = country_code, population_2018 = year_2018) case_counts_query <- covid_open_data %>% filter(date == '2020-11-27', aggregation_level == 0) %>% left_join(country_pop, by = c("iso_3166_1_alpha_3")) %>% filter(population_2018 > 100000000) %>% mutate(cases_per_capita = cumulative_confirmed / population_2018) %>% select(country_code, country_name, cumulative_confirmed, population_2018, cases_per_capita) %>% arrange(desc(cases_per_capita)) %>% head(10) case_counts_query %>% show_query() ``` ``` ## <SQL> ## SELECT `country_code`, `country_name`, `cumulative_confirmed`, `population_2018`, `cumulative_confirmed` / `population_2018` AS `cases_per_capita` ## FROM (SELECT `date`, `location_key`, `country_code`, `country_name`, `subregion1_code`, `subregion1_name`, `subregion2_code`, `subregion2_name`, `iso_3166_1_alpha_2`, `LHS`.`iso_3166_1_alpha_3` AS `iso_3166_1_alpha_3`, `aggregation_level`, `new_confirmed`, `new_deceased`, `new_recovered`, `new_tested`, `cumulative_confirmed`, `cumulative_deceased`, `cumulative_recovered`, `cumulative_tested`, `new_hospitalized_patients`, `new_intensive_care_patients`, `new_ventilator_patients`, `cumulative_hospitalized_patients`, `cumulative_intensive_care_patients`, `cumulative_ventilator_patients`, `current_hospitalized_patients`, `current_intensive_care_patients`, `current_ventilator_patients`, `mobility_transit_stations`, `mobility_retail_and_recreation`, `mobility_grocery_and_pharmacy`, `mobility_parks`, `mobility_residential`, `mobility_workplaces`, `wikidata_id`, `datacommons_id`, `openstreetmap_id`, `latitude`, `longitude`, `location_geometry`, `average_temperature_celsius`, `minimum_temperature_celsius`, `maximum_temperature_celsius`, `rainfall_mm`, `snowfall_mm`, `population_2018` ## FROM (SELECT * ## FROM `bigquery-public-data.covid19_open_data.covid19_open_data` ## WHERE ((`date` = '2020-11-27') AND (`aggregation_level` = 0.0))) `LHS` ## LEFT JOIN (SELECT `country_code` AS `iso_3166_1_alpha_3`, `year_2018` AS `population_2018` ## FROM `bigquery-public-data.world_bank_global_population.population_by_country`) `RHS` ## ON (`LHS`.`iso_3166_1_alpha_3` = `RHS`.`iso_3166_1_alpha_3`) ## ) `q01` ## WHERE (`population_2018` > 100000000.0) ## ORDER BY `cases_per_capita` DESC ## LIMIT 10 ``` --- ### Getting the results ```r case_counts <- case_counts_query %>% collect() ``` ```r case_counts %>% kable() ``` |country_code |country_name | cumulative_confirmed| population_2018| cases_per_capita| |:------------|:------------------------|--------------------:|---------------:|----------------:| |US |United States of America | 13091758| 327167434| 0.0400155| |BR |Brazil | 6238350| 209469333| 0.0297817| |RU |Russia | 2215533| 144478050| 0.0153347| |MX |Mexico | 1078594| 126190788| 0.0085473| |IN |India | 9351109| 1352617328| 0.0069133| |PH |Philippines | 425918| 106651922| 0.0039935| |BD |Bangladesh | 458711| 161356039| 0.0028428| |ID |Indonesia | 522581| 267663435| 0.0019524| |PK |Pakistan | 392356| 212215030| 0.0018489| |JP |Japan | 142068| 126529100| 0.0011228| --- <https://console.cloud.google.com/marketplace/product/bigquery-public-datasets/covid19-open-data?filter=solution-type:dataset&q=covid19&project=calvindsdev> ``` WITH country_pop AS ( SELECT country_code AS iso_3166_1_alpha_3, year_2018 AS population_2018 FROM `bigquery-public-data.world_bank_global_population.population_by_country`) SELECT country_code, country_name, cumulative_confirmed, population_2018, cumulative_confirmed/population_2018 AS cases_per_capita FROM `bigquery-public-data.covid19_open_data.covid19_open_data` JOIN country_pop USING (iso_3166_1_alpha_3) WHERE date = '2020-11-13' AND aggregation_level = 0 AND population_2018 > 100000000 ORDER BY case_percent DESC ``` See also: <https://www.reddit.com/r/bigquery/comments/3cej2b/17_billion_reddit_comments_loaded_on_bigquery/> from <https://fivethirtyeight.com/features/dissecting-trumps-most-rabid-online-following/>