What would you like to learn about getting data from the system?

General
20

this is a continuation from the earlier post (in reply to @muir) but i’m going to change the example a bit. here, i’m going to get incremental (new) “species” (leaf taxa) for Alaska in each month in 2020. to get there, here’s my thought process:

  1. on the Explore page in iNaturalist, the Species tab data + count come from the /observations/species_count API endpoint (https://api.inaturalist.org/v1/observations/species_counts)
  2. in order to get incremental counts for each month, i need to start by getting a series of cumulative counts for each month, plus an extra one from the end of the previous year
  3. i want to filter for Alaska (place_id=6) and verifiable=true and get cumulative counts based on submit date (create_d2={last day of month}). also, i just need a total count, not any details (per_page=0). so my API request would be: https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2={last day of month}
  4. in Excel 2013 and up, there is a =WEBSERVICE(url) formula that i can use to retrieve the results returned from a URL
  5. the raw results returned by iNaturalist for this kind of request will always follow a specific format. so i can use SUBSTITUTE to eliminate the extra stuff i don’t need

so i would set up a spreadsheet with the following structure:

  • column A: months 2019-12-01 to 2020-12-01
  • column B: last days of the months from column A
  • column C: a WEBSERVICE request to the appropriate URL, varying the formula in each row by last day of month
  • column D: get just the number from column C
  • column E: subtract the cumulative count from the previous row’s cumulative count

so assuming “Month” is in cell A1, this is what that looks like:

(A) Month (B) Last Day (C) Raw JSON (D) Cumulative Species (E) Incremental Species
2019-12-01 2019-12-31 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B2) =SUBSTITUTE(SUBSTITUTE(C2,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","")
2020-01-01 2020-01-31 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B3) =SUBSTITUTE(SUBSTITUTE(C3,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D3-D2
2020-02-01 2020-02-29 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B4) =SUBSTITUTE(SUBSTITUTE(C4,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D4-D3
2020-03-01 2020-03-31 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B5) =SUBSTITUTE(SUBSTITUTE(C5,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D5-D4
2020-04-01 2020-04-30 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B6) =SUBSTITUTE(SUBSTITUTE(C6,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D6-D5
2020-05-01 2020-05-31 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B7) =SUBSTITUTE(SUBSTITUTE(C7,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D7-D6
2020-06-01 2020-06-30 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B8) =SUBSTITUTE(SUBSTITUTE(C8,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D8-D7
2020-07-01 2020-07-31 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B9) =SUBSTITUTE(SUBSTITUTE(C9,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D9-D8
2020-08-01 2020-08-31 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B10) =SUBSTITUTE(SUBSTITUTE(C10,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D10-D9
2020-09-01 2020-09-30 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B11) =SUBSTITUTE(SUBSTITUTE(C11,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D11-D10
2020-10-01 2020-10-31 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B12) =SUBSTITUTE(SUBSTITUTE(C12,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D12-D11
2020-11-01 2020-11-30 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B13) =SUBSTITUTE(SUBSTITUTE(C13,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D13-D12
2020-12-01 2020-12-31 =WEBSERVICE("https://api.inaturalist.org/v1/observations/species_counts?per_page=0&verifiable=true&place_id=6&created_d2="&B14) =SUBSTITUTE(SUBSTITUTE(C14,"{""total_results"":",""),",""page"":1,""per_page"":0,""results"":[]}","") =D14-D13

… and here’s what the final result looks like:

(A) Month (B) Last Day (C) Raw JSON (D) Cumulative Species (E) Incremental Species
2019-12-01 2019-12-31 {"total_results":4961,"page":1,"per_page":0,"results":[]} 4961
2020-01-01 2020-01-31 {"total_results":4975,"page":1,"per_page":0,"results":[]} 4975 14
2020-02-01 2020-02-29 {"total_results":5002,"page":1,"per_page":0,"results":[]} 5002 27
2020-03-01 2020-03-31 {"total_results":5022,"page":1,"per_page":0,"results":[]} 5022 20
2020-04-01 2020-04-30 {"total_results":5095,"page":1,"per_page":0,"results":[]} 5095 73
2020-05-01 2020-05-31 {"total_results":5212,"page":1,"per_page":0,"results":[]} 5212 117
2020-06-01 2020-06-30 {"total_results":5319,"page":1,"per_page":0,"results":[]} 5319 107
2020-07-01 2020-07-31 {"total_results":5446,"page":1,"per_page":0,"results":[]} 5446 127
2020-08-01 2020-08-31 {"total_results":5571,"page":1,"per_page":0,"results":[]} 5571 125
2020-09-01 2020-09-30 {"total_results":5691,"page":1,"per_page":0,"results":[]} 5691 120
2020-10-01 2020-10-31 {"total_results":5723,"page":1,"per_page":0,"results":[]} 5723 32
2020-11-01 2020-11-30 {"total_results":5761,"page":1,"per_page":0,"results":[]} 5761 38
2020-12-01 2020-12-31 {"total_results":5810,"page":1,"per_page":0,"results":[]} 5810 49

a few final notes:

  1. when the formula in column C refers to the date in column B, i need to make sure it’s formatted as text in a format that iNaturalist will understand. (if it’s not text, then the formula in C will need to convert it to the appropriate data type and format.)
  2. iNaturalist doesn’t like you to hit it with too many API requests at the same time. so in this case, you’ll probably want to go down row by row when copying down the WEBSERVICE formula in column C. (if you see it returning an invalid value, just go into the formula input box for that cell, and hit enter to trigger a refresh of that cell.) so this is a little manual, but maybe it’s still faster and less error-prone than copy-and-pasting numbers from the Explore screen.
  3. this example hits the /observations/species_counts endpoint, but you can hit other endpoints in the same way, too. the observations count comes from /obervations, and the observers count comes from /observations/observers. if you ever need to see what endpoints are available or what their parameters are, you can go to https://api.inaturalist.org/v1/docs/ to look up that information.

ok… that’s it for now. if you have any questions, let me know. otherwise, i probably won’t provide any additional Excel + API guidance until i actually write up an actual tutorial for that…

1 Like