class: center, middle, inverse, title-slide # Obtain & wrangle OpenAQ data with R ## ropenaq and beyond ### Maëlle Salmon - 2020-03-31 ### <svg viewBox='0 0 512 512' xmlns='http://www.w3.org/2000/svg' style='height:1em;fill:currentColor;position:relative;display:inline-block;top:.1em;'> <path d='M326.612 185.391c59.747 59.809 58.927 155.698.36 214.59-.11.12-.24.25-.36.37l-67.2 67.2c-59.27 59.27-155.699 59.262-214.96 0-59.27-59.26-59.27-155.7 0-214.96l37.106-37.106c9.84-9.84 26.786-3.3 27.294 10.606.648 17.722 3.826 35.527 9.69 52.721 1.986 5.822.567 12.262-3.783 16.612l-13.087 13.087c-28.026 28.026-28.905 73.66-1.155 101.96 28.024 28.579 74.086 28.749 102.325.51l67.2-67.19c28.191-28.191 28.073-73.757 0-101.83-3.701-3.694-7.429-6.564-10.341-8.569a16.037 16.037 0 0 1-6.947-12.606c-.396-10.567 3.348-21.456 11.698-29.806l21.054-21.055c5.521-5.521 14.182-6.199 20.584-1.731a152.482 152.482 0 0 1 20.522 17.197zM467.547 44.449c-59.261-59.262-155.69-59.27-214.96 0l-67.2 67.2c-.12.12-.25.25-.36.37-58.566 58.892-59.387 154.781.36 214.59a152.454 152.454 0 0 0 20.521 17.196c6.402 4.468 15.064 3.789 20.584-1.731l21.054-21.055c8.35-8.35 12.094-19.239 11.698-29.806a16.037 16.037 0 0 0-6.947-12.606c-2.912-2.005-6.64-4.875-10.341-8.569-28.073-28.073-28.191-73.639 0-101.83l67.2-67.19c28.239-28.239 74.3-28.069 102.325.51 27.75 28.3 26.872 73.934-1.155 101.96l-13.087 13.087c-4.35 4.35-5.769 10.79-3.783 16.612 5.864 17.194 9.042 34.999 9.69 52.721.509 13.906 17.454 20.446 27.294 10.606l37.106-37.106c59.271-59.259 59.271-155.699.001-214.959z'></path> </svg> <a href="https://maelle.github.io/communityseries/#1">tiny.cc/ropenaq</a> <svg viewBox='0 0 496 512' xmlns='http://www.w3.org/2000/svg' style='height:1em;fill:currentColor;position:relative;display:inline-block;top:.1em;'> <path d='M336.5 160C322 70.7 287.8 8 248 8s-74 62.7-88.5 152h177zM152 256c0 22.2 1.2 43.5 3.3 64h185.3c2.1-20.5 3.3-41.8 3.3-64s-1.2-43.5-3.3-64H155.3c-2.1 20.5-3.3 41.8-3.3 64zm324.7-96c-28.6-67.9-86.5-120.4-158-141.6 24.4 33.8 41.2 84.7 50 141.6h108zM177.2 18.4C105.8 39.6 47.8 92.1 19.3 160h108c8.7-56.9 25.5-107.8 49.9-141.6zM487.4 192H372.7c2.1 21 3.3 42.5 3.3 64s-1.2 43-3.3 64h114.6c5.5-20.5 8.6-41.8 8.6-64s-3.1-43.5-8.5-64zM120 256c0-21.5 1.2-43 3.3-64H8.6C3.2 212.5 0 233.8 0 256s3.2 43.5 8.6 64h114.6c-2-21-3.2-42.5-3.2-64zm39.5 96c14.5 89.3 48.7 152 88.5 152s74-62.7 88.5-152h-177zm159.3 141.6c71.4-21.2 129.4-73.7 158-141.6h-108c-8.8 56.9-25.6 107.8-50 141.6zM19.3 352c28.6 67.9 86.5 120.4 158 141.6-24.4-33.8-41.2-84.7-50-141.6h-108z'></path> </svg> <a href="https://masalmon.eu">masalmon.eu</a> <svg viewBox='0 0 512 512' xmlns='http://www.w3.org/2000/svg' style='height:1em;fill:currentColor;position:relative;display:inline-block;top:.1em;'> <path d='M459.37 151.716c.325 4.548.325 9.097.325 13.645 0 138.72-105.583 298.558-298.558 298.558-59.452 0-114.68-17.219-161.137-47.106 8.447.974 16.568 1.299 25.34 1.299 49.055 0 94.213-16.568 130.274-44.832-46.132-.975-84.792-31.188-98.112-72.772 6.498.974 12.995 1.624 19.818 1.624 9.421 0 18.843-1.3 27.614-3.573-48.081-9.747-84.143-51.98-84.143-102.985v-1.299c13.969 7.797 30.214 12.67 47.431 13.319-28.264-18.843-46.781-51.005-46.781-87.391 0-19.492 5.197-37.36 14.294-52.954 51.655 63.675 129.3 105.258 216.365 109.807-1.624-7.797-2.599-15.918-2.599-24.04 0-57.828 46.782-104.934 104.934-104.934 30.213 0 57.502 12.67 76.67 33.137 23.715-4.548 46.456-13.32 66.599-25.34-7.798 24.366-24.366 44.833-46.132 57.827 21.117-2.273 41.584-8.122 60.426-16.243-14.292 20.791-32.161 39.308-52.628 54.253z'></path> </svg> ma_salmon <svg viewBox='0 0 496 512' xmlns='http://www.w3.org/2000/svg' style='height:1em;fill:currentColor;position:relative;display:inline-block;top:.1em;'> <path d='M165.9 397.4c0 2-2.3 3.6-5.2 3.6-3.3.3-5.6-1.3-5.6-3.6 0-2 2.3-3.6 5.2-3.6 3-.3 5.6 1.3 5.6 3.6zm-31.1-4.5c-.7 2 1.3 4.3 4.3 4.9 2.6 1 5.6 0 6.2-2s-1.3-4.3-4.3-5.2c-2.6-.7-5.5.3-6.2 2.3zm44.2-1.7c-2.9.7-4.9 2.6-4.6 4.9.3 2 2.9 3.3 5.9 2.6 2.9-.7 4.9-2.6 4.6-4.6-.3-1.9-3-3.2-5.9-2.9zM244.8 8C106.1 8 0 113.3 0 252c0 110.9 69.8 205.8 169.5 239.2 12.8 2.3 17.3-5.6 17.3-12.1 0-6.2-.3-40.4-.3-61.4 0 0-70 15-84.7-29.8 0 0-11.4-29.1-27.8-36.6 0 0-22.9-15.7 1.6-15.4 0 0 24.9 2 38.6 25.8 21.9 38.6 58.6 27.5 72.9 20.9 2.3-16 8.8-27.1 16-33.7-55.9-6.2-112.3-14.3-112.3-110.5 0-27.5 7.6-41.3 23.6-58.9-2.6-6.5-11.1-33.3 2.6-67.9 20.9-6.5 69 27 69 27 20-5.6 41.5-8.5 62.8-8.5s42.8 2.9 62.8 8.5c0 0 48.1-33.6 69-27 13.7 34.7 5.2 61.4 2.6 67.9 16 17.7 25.8 31.5 25.8 58.9 0 96.5-58.9 104.2-114.8 110.5 9.2 7.9 17 22.9 17 46.4 0 33.7-.3 75.4-.3 83.6 0 6.5 4.6 14.4 17.3 12.1C428.2 457.8 496 362.9 496 252 496 113.3 383.5 8 244.8 8zM97.2 352.9c-1.3 1-1 3.3.7 5.2 1.6 1.6 3.9 2.3 5.2 1 1.3-1 1-3.3-.7-5.2-1.6-1.6-3.9-2.3-5.2-1zm-10.8-8.1c-.7 1.3.3 2.9 2.3 3.9 1.6 1 3.6.7 4.3-.7.7-1.3-.3-2.9-2.3-3.9-2-.6-3.6-.3-4.3.7zm32.4 35.6c-1.6 1.3-1 4.3 1.3 6.2 2.3 2.3 5.2 2.6 6.5 1 1.3-1.3.7-4.3-1.3-6.2-2.2-2.3-5.2-2.6-6.5-1zm-11.4-14.7c-1.6 1-1.6 3.6 0 5.9 1.6 2.3 4.3 3.3 5.6 2.3 1.6-1.3 1.6-3.9 0-6.2-1.4-2.3-4-3.3-5.6-2z'></path> </svg> maelle <svg viewBox='0 0 512 512' xmlns='http://www.w3.org/2000/svg' style='height:1em;fill:currentColor;position:relative;display:inline-block;top:.1em;'> <path d='M512 144v288c0 26.5-21.5 48-48 48H48c-26.5 0-48-21.5-48-48V144c0-26.5 21.5-48 48-48h88l12.3-32.9c7-18.7 24.9-31.1 44.9-31.1h125.5c20 0 37.9 12.4 44.9 31.1L376 96h88c26.5 0 48 21.5 48 48zM376 288c0-66.2-53.8-120-120-120s-120 53.8-120 120 53.8 120 120 120 120-53.8 120-120zm-32 0c0 48.5-39.5 88-88 88s-88-39.5-88-88 39.5-88 88-88 88 39.5 88 88z'></path> </svg> <a href="https://www.pexels.com/photo/person-wearing-black-jacket-using-black-umbrella-3229623/">Ryan Millier on Pexels</a> --- # Context (1/2) 😷 ❤️ In December 2015 I was a statistician & data manager with the [CHAI project](https://chaiproject.org/) (_Cardiovascular Health Effects of Air Pollution in Telangana, India._) ➡️ interest in open air quality data -- 📊 📉 Work with R, _An open-source language and environment for statistical computing._ ➡️ interest in wrangling such data with R -- Hear of OpenAQ... 💥 -- 👨🏭 **Decision to create an R client for OpenAQ API!** --- # Context (2/2) That client now exists: `ropenaq`, [docs.ropensci.org/ropenaq](https://docs.ropensci.org/ropenaq/) -- * Actively maintained -- * Distributed on CRAN `install.packages("ropenaq")` -- * [Peer-reviewed by rOpenSci](https://ropensci.org/software-review/) -- 🙋 No longer with the CHAI project but still into R and air quality data. 😉 --- # Why R by the way? Open-source, free language with many capabilities: * Data wrangling, modeling, visualization; * Reproducible reports and analyses; * Interactive plots and apps. --- # And what is an R package actually? The OpenAQ R client is an R "package": * An add-in to R; * A collection of code and documentation. --- # What ropenaq does Gets data into R * From OpenAQ's API endpoints (measurements, latest and countries, cities, locations); * as tables called "data.frames", common format in R. --- # What ropenaq does ```r library("ropenaq") aq_latest(country = "IN") ``` --- # What ropenaq does ```r library("ropenaq") aq_latest(country = "IN") ``` ``` ## # A tibble: 5,527 x 15 ## location city country distance parameter value lastUpdated unit ## <chr> <chr> <chr> <dbl> <chr> <dbl> <dttm> <chr> ## 1 AAQMS K… Pune IN 1.41e7 co 1220 2018-02-22 03:00:00 µg/m³ ## 2 AAQMS K… Pune IN 1.41e7 pm10 165 2018-02-22 03:00:00 µg/m³ ## 3 AAQMS K… Pune IN 1.41e7 pm25 66 2018-02-22 03:00:00 µg/m³ ## 4 AAQMS K… Pune IN 1.41e7 o3 15.8 2018-02-22 03:00:00 µg/m³ ## 5 AAQMS K… Pune IN 1.41e7 so2 22.8 2018-02-22 03:00:00 µg/m³ ## 6 AP Tiru… Chit… IN NA no2 49.9 2016-06-30 13:00:00 µg/m³ ## 7 AP Tiru… Chit… IN NA o3 32.1 2016-06-30 13:00:00 µg/m³ ## 8 AP Tiru… Chit… IN NA so2 5.7 2016-06-30 13:00:00 µg/m³ ## 9 AP Tiru… Chit… IN NA so2 6.9 2016-07-04 06:15:00 µg/m³ ## 10 AP Tiru… Chit… IN NA pm10 12 2016-07-04 06:15:00 µg/m³ ## # … with 5,517 more rows, and 7 more variables: sourceName <chr>, ## # averagingPeriod_value <dbl>, averagingPeriod_unit <chr>, latitude <dbl>, ## # longitude <dbl>, cityURL <chr>, locationURL <chr> ``` --- # Select locations ```r delhi_locations <- aq_locations( city = "Delhi", country = "IN", parameter = "pm25" ) delhi_locations ``` --- # Select locations ```r delhi_locations <- aq_locations( city = "Delhi", country = "IN", parameter = "pm25" ) delhi_locations ``` ``` ## # A tibble: 71 x 25 ## id country city cities location locations sourceName sourceNames ## <chr> <chr> <chr> <list> <chr> <list> <chr> <list> ## 1 IN-2… IN Delhi <chr … Alipur,… <chr [1]> caaqm <chr [1]> ## 2 IN-1… IN Delhi <chr … Anand V… <chr [1]> caaqm <chr [1]> ## 3 IN-60 IN Delhi <chr … Anand V… <chr [2]> caaqm <chr [2]> ## 4 IN-6 IN Delhi <chr … Anand V… <chr [2]> CPCB <chr [2]> ## 5 IN-2… IN Delhi <chr … Ashok V… <chr [1]> caaqm <chr [1]> ## 6 IN-1… IN Delhi <chr … Ashok V… <chr [1]> caaqm <chr [1]> ## 7 IN-71 IN Delhi <chr … Aya Nag… <chr [2]> caaqm <chr [2]> ## 8 IN-1… IN Delhi <chr … Bawana,… <chr [1]> caaqm <chr [1]> ## 9 IN-1… IN Delhi <chr … Burari … <chr [2]> caaqm <chr [2]> ## 10 IN-8 IN Delhi <chr … Civil L… <chr [1]> CPCB <chr [1]> ## # … with 61 more rows, and 17 more variables: sourceType <chr>, ## # sourceTypes <list>, firstUpdated <dttm>, lastUpdated <dttm>, ## # countsByMeasurement <list>, count <int>, longitude <dbl>, latitude <dbl>, ## # pm25 <lgl>, pm10 <lgl>, no2 <lgl>, so2 <lgl>, o3 <lgl>, co <lgl>, bc <lgl>, ## # cityURL <chr>, locationURL <chr> ``` --- # Select locations For each location, useful metadata to select the optimal location(s) for your needs! ```r head(delhi_locations$sourceName) ``` ``` ## [1] "caaqm" "caaqm" "caaqm" "CPCB" "caaqm" "caaqm" ``` ```r head(delhi_locations$pm25) ``` ``` ## [1] TRUE TRUE TRUE TRUE TRUE TRUE ``` ```r min(delhi_locations$firstUpdated) ``` ``` ## [1] "2015-04-09 06:00:00 UTC" ``` --- # Use locations ```r head(delhi_locations$location) ``` ``` ## [1] "Alipur, Delhi - DPCC" "Anand Vihar, Delhi - DPCC" ## [3] "Anand Vihar, Delhi - DPCC" "Anand Vihar, Delhi - DPCC" ## [5] "Ashok Vihar, Delhi - DPCC" "Ashok Vihar, Delhi - DPCC" ``` ```r head(delhi_locations$locationURL) ``` ``` ## [1] "Alipur%2C+Delhi+-+DPCC" "Anand+Vihar%2C+Delhi+-+DPCC" ## [3] "Anand+Vihar%2C+Delhi+-+DPCC" "Anand+Vihar%2C+Delhi+-+DPCC" ## [5] "Ashok+Vihar%2C+Delhi+-+DPCC" "Ashok+Vihar%2C+Delhi+-+DPCC" ``` --- # Use locations 💡 In `aq_latest()` and `aq_measurements()` use the URLencoded city / location. ```r res <- aq_latest(location = "Alipur, Delhi - DPCC") ``` ``` ## Error: This location/city/country combination is not available within the platform. See ?aq_locations ``` ```r res <- aq_latest(location = "Alipur%2C+Delhi+-+DPCC") ``` --- # Delhi PM2.5 vs WHO threshold <code class ='r hljs remark-code'>data <- aq_measurements(<span style='background-color:#ffff7f'>location = "US+Diplomatic+Post%3A+New+Delhi"</span>,<br> parameter = "pm25")</code> ```r # dplyr by Hadley Wickham et al data <- dplyr::filter(data, value > 0) ``` --- # Delhi PM2.5 vs WHO threshold ```r data ``` ``` ## # A tibble: 2,158 x 12 ## location parameter value unit country city latitude longitude ## <chr> <chr> <int> <chr> <chr> <chr> <dbl> <dbl> ## 1 US Dipl… pm25 12 µg/m³ IN Delhi 28.6 77.2 ## 2 US Dipl… pm25 18 µg/m³ IN Delhi 28.6 77.2 ## 3 US Dipl… pm25 16 µg/m³ IN Delhi 28.6 77.2 ## 4 US Dipl… pm25 19 µg/m³ IN Delhi 28.6 77.2 ## 5 US Dipl… pm25 44 µg/m³ IN Delhi 28.6 77.2 ## 6 US Dipl… pm25 38 µg/m³ IN Delhi 28.6 77.2 ## 7 US Dipl… pm25 17 µg/m³ IN Delhi 28.6 77.2 ## 8 US Dipl… pm25 15 µg/m³ IN Delhi 28.6 77.2 ## 9 US Dipl… pm25 13 µg/m³ IN Delhi 28.6 77.2 ## 10 US Dipl… pm25 15 µg/m³ IN Delhi 28.6 77.2 ## # … with 2,148 more rows, and 4 more variables: dateUTC <dttm>, ## # dateLocal <dttm>, cityURL <chr>, locationURL <chr> ``` --- # Delhi PM2.5 vs WHO threshold <code class ='r hljs remark-code'>library("ggplot2") # Hadley Wickham et al<br><br><span style='background-color:#ffff7f'>ggplot(data) +<br> geom_point(aes(x = dateLocal, y = value)</span>, <br> col = "CornflowerBlue") +<br> <span style='background-color:#ffff7f'>geom_hline(yintercept = 25</span>, <br> size = 1.2, <br> col = "darkred") +<br> ylab(expression(paste("PM2.5 concentration (", mu, "g/",m^3,")")))+<br> xlab("Time (days)") +<br> ggtitle(data$location[1]) +<br> hrbrthemes::theme_ipsum(<br> base_size = 16,<br> axis_title_size = 16)</code> <!-- --> --- # Delhi PM2.5 vs WHO threshold  --- # London PM2.5 vs WHO threshold <code class ='r hljs remark-code'>data <- aq_measurements(<span style='background-color:#ffff7f'>location = "London+Westminster"</span>,<br> parameter = "pm25")</code> ```r data <- dplyr::filter(data, value > 0) ``` --- # London PM2.5 vs WHO threshold ```r ggplot(data) + geom_point(aes(x = dateLocal, y = value), col = "CornflowerBlue") + geom_hline(yintercept = 25, size = 1.2, col = "darkred") + ylab(expression(paste("PM2.5 concentration (", mu, "g/",m^3,")")))+ xlab("Time (days)") + ggtitle(data$location[1]) + hrbrthemes::theme_ipsum( base_size = 16, axis_title_size = 16) ``` --- # London PM2.5 vs WHO threshold  --- # How much data can you get? Maximum number of results for any query is 100 (pages) times 10000 (results per page). -- You don't need to use the `page` and `limit` arguments, `ropenaq` handles all of this for you. Just wait a bit. ⌚ -- ```r res <- aq_measurements(country = "IN") nrow(res) ``` ``` ## [1] 1000000 ``` ```r attr(res, "meta")$found ``` ``` ## [1] 29175743 ``` --- # What if you need more data * **More data than 1,000,000 results?** Split your query, e.g. using `date_from` and `date_to`. Or see following item. -- * **More data than the last 90 days?** OpenAQ data older than 90 days is on Amazon Web Services (AWS) Athena. **More setup for you but worth it and transferrable skills** 💪 --- # OpenAQ on AWS: setup in the browser (1/2) * Set up an AWS account (root and IAM) * Create an S3 bucket. To avoid S3 fees, [set up a lifecycle policy](https://gist.github.com/jflasher/573525aff9a5d8a966e5718272ceb25a#gistcomment-3196277). -- 💰 **Will I lose all my money?** 💰 For OpenAQ on AWS you can't avoid Athena fees. They're super small. If you're nervous, do like me, check the billing interface often until you're confident. --- # OpenAQ on AWS: setup in the browser (2/2) Go to the Athena console. [Joe Flasher's gist](https://gist.github.com/jflasher/573525aff9a5d8a966e5718272ceb25a) -- * Check your location -- * Create the OpenAQ table -- * Run a query like in the gist to see if it works -- * Get your credentials (go into your "My security credentials" settings from the drop-down menu at the top right of the console where your username is.) --- # OpenAQ on AWS: setup in R Now, install `AWR.Athena`. It depends on `rJava`. 🍀. _Another newer R package without rJava: [`noctua`](https://dyfanjones.github.io/noctua/)_ 👀 -- Set up credentials. I used AWS CLI and ran `aws configure`, YMMV. 🤷 -- In a script, try setting up a connection. --- # OpenAQ on AWS: test query in R ```r library("AWR.Athena") # Neal Fultz and Gergely Daróczi library("DBI") # Kirill Müller et al con <- dbConnect(AWR.Athena::Athena(), region = 'us-east-1', S3OutputLocation = 's3://ropenaq', Schema = 'default') query <- "SELECT * FROM openaq WHERE location='US Diplomatic Post: Hanoi' LIMIT 10" meas <- dbGetQuery(con, query) ``` -- Now on to cooler stuff! --- # OpenAQ on AWS: use in R Selecting locations in Wuhan with `ropenaq`. ```r locations <- ropenaq::aq_locations( country = "CN", longitude = 114.3055, latitude = 30.5928, radius = 5000 ) unique(locations$city) ``` ``` ## [1] "武汉市" ``` --- # OpenAQ on AWS: use in R Prepare your query with `glue`. ```r query <- glue::glue( "SELECT * FROM openaq WHERE city='{locations$city[1]}' AND parameter='pm25'" ) query ``` ``` ## SELECT * ## FROM openaq ## WHERE city='武汉市' ## AND parameter='pm25' ``` --- # OpenAQ on AWS: use in R Make the query with `AWR.Athena` ```r library("AWR.Athena") library("DBI") con <- dbConnect(AWR.Athena::Athena(), region = 'us-east-1', S3OutputLocation = 's3://ropenaq', Schema = 'default') meas <- dbGetQuery(con, query) ``` -- Now save `meas` and munge it, plot it, etc. --- # OpenAQ in R In short, * `ropenaq` alone: only OpenAQ API data i.e. last 90 days, no setup, no credit card info. * `AWR.Athena` (or `noctua`?): All OpenAQ data via AWS, more setup, pennies. Take your time, document steps. In both cases, you can do a lot from R once you got the data! 🎉 --- # Conclusion: why use ropenaq * Need OpenAQ API data in R? * Or want an engaging dataset to learn how to use R for further projects? `install.packages("ropenaq")` `browseURL("https://docs.ropensci.org/ropenaq")` -- * Want more data than the last 90 days? Setup your AWS account, install the `AWR.Athena` package (or `noctua`?). Use `ropenaq` to select locations. --- # Conclusion: how to learn R Some resources * [R for Data Science book](https://r4ds.had.co.nz/) by Hadley Wickham and Garrett Grolemund, free online version; * [RStudio education website](https://education.rstudio.com/); * [Data Science specialization by John Hopkins University on Coursera](https://www.coursera.org/specializations/jhu-data-science); -- * Where/how to get help: [my post](https://masalmon.eu/2018/07/22/wheretogethelp/), [Sam Tyner's post](https://sctyner.github.io/rhelp.html); * Keep informed: [my post](https://masalmon.eu/2019/01/25/uptodate/), [R Weekly](https://rweekly.org/). --- # Conclusion: how to contribute **ropenaq R package, R client for the OpenAQ API** -- * Use cases: report them on [rOpenSci forum](https://discuss.ropensci.org/c/usecases/10) -- * Docs? Code? Let's talk at [github.com/ropensci/ropenaq](https://github.com/ropensci/ropenaq) Code cool stuff: HTTP requests with `crul`, asynchronous queries, tests with mocking. -- * Bugs? GitHub too! 🐛 -- --- class: part-slide, center, bottom background-image:url(img/RopenAQ.jpg) **[tiny.cc/ropenaq](https://maelle.github.io/communityseries/#1)** Diagram drawn by [Damien Cornu](https://twitter.com/damienaberlin). 😍