I maintain a summary table of odonata sightings on our property. Its sorted by taxonomy, and has columns for total observations, earliest & latest observation, and the first & last flight dates for each season. I use Excel, which is visually useful. The Table feature makes it sortable while still leaving the summaries at the base of columns intact, but is a PITA to produce manually.
I’d like to think that many other people, esp managers of sites & projects, would like to do something like this, but I haven’t found another example. Ideally, it would regenerate from scratch on demand (there are only about 800 records/year).
Does anyone know of something like this?
Does anybody have a practical idea on what software/services would be a practical starting point for generating this? I have minimal experience with Pivot Tables, but I’ve wondered if that might be a useful starting point (a separate tab for each year might be simpler). I have the base level family subscription for Office. I don’t pay for PowerBI, and no, I never had the patience to learn that beast.
Forgive me for I am about to sin: I assume that the easiest way to do this would be with AI. If that isn’t practical for me this year, I assume its only a matter of time before it is practical and affordable.
I understand the reluctance to employ AI for a task like this. Of course I don’t speak for everyone, but I see there as being legitimate and illegitimate uses, with the problems being things like producing spam at scale or mass-producing slop art and writing. Allowing non-coders to implement personal data science workflows for non-production use would be an ideal use-case.
Using Google Gemini, it’s pretty easy to get it to implement JavaScript inside an HTML document to act as the UI. The JavaScript queries the iNaturalist API using a URL search string (just like what you’d use to filter observations) to retrieve data and then format it to your specifications. NOTE: You should not ask it to actually produce the table for you, as that is going to end up as a hallucinated mess and would be extremely inefficient even if it worked. Instead, producing the code to allow you to efficiently rerun the task yourself as-needed is much better.
There is no difference between using AI to generate art, writing, or code. It’s big tech companies taking knowledge from others and creating a system they can monetize without giving credit or payment to the original creators.
People can learn how to create pivot tables in Excel, people can learn how create tables in JavaScript to without using AI.
If I had written the code for this by hand, I would’ve done it in Python and just called it from CLI. Javascript is great for things that need a UI or for things you want to run in a user’s browser with no backend, but I find it to be a less intuitive language overall. R is also viable, especially for plotting and basic statistics. (You can also use Shiny to make UIs for Python and R code.)
I do think it’s useful to know one or more scripting languages though, and in fact I think the availability of AI coding tools makes it more valuable to know at least the basics, not less. For example, I had to edit my post because when I first took a screenshot, I realised that for the “Family” column, Gemini had instead used class, and so it was just returning “Insecta” all the way down, which was a trivial fix if you can read the code.
If you just wanted to try my implementation out, you can just download the HTML file from GitHub using this button, and then open in any browser.
to be able to understand the thing that gwheelereb vibe coded, you need to learn JavaScript, HTML, and CSS. that’s not an insignificant lift.
If you’re familiar with Excel, you’d be much better served using your time to learn how to use PivotTables, or else learn the formulas MINIFS, MAXIFS, and COUNTIF.
There are many excel pivot table videos on Youtube. Some are less than ten minutes, some are a few hours long. You could watch some videos to figure out if you can do what you need in Excel. Here’s a 3.5 hour course about pivot tables. https://www.youtube.com/watch?v=02-5BsJxqzU
