Cider fermentation web app

[Courtney Meier]

During the last few dark winter months as cider batches sat quietly in the cold basement and began to clarify, I found myself making the same graphs over and over again to see how things stand. It occurred to me it would be grand to have a system where one entered only a minimum of data and code did the rest. I also wanted to make prise de mousse and sulphite calculations as easy as possible, using data from Claude and Andrew's experiments and published work (I consulted with each of them first to make sure it was acceptable to do so - thank you gentlemen!)

Anyway, I wanted to share the app with this community in the event that some of you might also find it useful:

https://meier.shinyapps.io/meier-cider/

Cheers and happy to answer any questions if they come up!

Courtney 

---------------------
Courtney Meier | Plant Ecologist | Cider Hobbyist | Boulder, CO

[Patrick Mann]

That looks very handy.
What are your plans for the future of the app if a lot of folks start putting their data in it?

Patrick

[Martin Thoburn]

This is amazing.  Very helpful.  How do I get an account to start logging my own ciders?

[Courtney Meier]

Looking at my original post, I realize it lacks a few important details... 

As is probably obvious after poking around a bit, the 'Sulfite' and 'Bottling' tabs are generally useful to anyone. In contrast, the 'Summary', 'Fermentation', and 'Trees Map' tabs of the app rely on data I have entered and stored in the free Airtable service and are useful only to me since they are my personal cider fermentation data. The Trees Map tab is my attempt to keep track of the location, public/private access, sugar, and acid data that I have collected for all the trees around town that I have mapped and from which I have collected fruit for cider. I password protected it because probably more than half are on private property and I haven't yet spoken to the owners, much less asked their permission to have the location of their trees published on the internet! I attached a screen shot of that tab to give you a sense of what it looks like. The general way I've put the map together would be useful for anyone who has collected location data they want to organize for trees - with or without the sugar and acid data. If you look closely, there is an example in the screen shot of a pop-up that shows whatever customizable data you want to see for each tree. The drop-downs are also customizable to show only trees publicly accessible, etc.

For the parts of the app that rely on Airtable, my initial thought was to share the table structure and source code with anyone who might want it. I coded the app in R/Shiny and can share via Github if anyone is interested. The links below show the structure of the related tables the app relies on:

• 'trees' table: https://airtable.com/shrPsWFXSqKdqnqG8 --> this one and the 'juice' table supply the data for the Trees Map tab.
• 'juice' table: https://airtable.com/shrNshYofUCOmf7Fz
• 'blends' table: https://airtable.com/shrWFSCJLmgLQzh0b
• 'batches' table: https://airtable.com/shrgdA21QgqSGfiBG
• 'fermentation' table: https://airtable.com/shrpNcIA8fbWiKoMn --> this one drives most of the action on the Fermentation tab of the app

I also have tables for 'tasters' and 'scores' for some fancy future when I invite people over and people pair my cider with cheese and fresh crusty bread and then pay me with their opinions. This hasn't happened yet, since I haven't yet proved even to myself that I can make something delicious, but perhaps the future will be brighter than the present in that regard...

Airtable and the Shiny app server space are both free at the personal-use level, and to keep it free I can't open up this particular version of the app to others. Beyond sharing the code and structure, I haven't really thought about how to create a service where people could create their own accounts. On the other hand, an alternative would be that I could help anyone interested to setup their own tables and then clone a copy of the app that could then be passed off. It would probably take me 3-4 h to setup the tables and do the cloning so someone else could have their own personal copy. I haven't thought about what compensation would look like for that effort, but message me offline to figure something out.

Cheers all,

Courtney

[Claude Jolicoeur]

One question I have Courtney...

What is the advantage in making this a web app vs making an Excel spreadsheet local to your computer?

All what is in there is easily doable in Excel - and in fact the spreadsheets that are companion material to my book pretty much contain the same functionalities.

Maybe it is just a question of generation... I learned about computing and programming way before Internet was accessible to everyone, and so I have absolutely no natural tendency to do such things on the web.

Claude

[Courtney Meier]

Hi Claude - I think it's a valid question. For me, the web app approach is one I like because after writing the code once, it automatically makes the graphs I want and all I have to do is enter the date, SG, and batchID into the fermentation table in Airtable. The rest is done automatically. I made graphs in Excel for several years and I also make these types of Shiny/R applications for data quality control as part of my job, and eventually I realized I could put in a few hours upfront with the code and then the rest is done with no effort.

The second reason is that Airtable is basically an online spreadsheet similar to Excel, but has the added functionality of being able to link records across tables - i.e., a simplified relational database. The area where I live has a long history of orchards and there are old, unknown trees all over the place. One thing I'd love to do over the next several decades is make cider from these trees and share with friends and neighbors, and I think it would be interesting to be able to easily track which batches and styles people (and myself!) like most back to the source trees. The relational tables make that easier to do than working in Excel, and as long as I have the data there, it is easy to pull into the app to monitor fermentation automatically at the same time. An added benefit is the Trees Map, which is interactive and can't be done in Excel.

So for me, it was convenient to put all of these different functionalities into one place, from trees to juice to batches and fermentation progress to bottling. Eventually I will probably add an analysis of tasting scores once I have enough data. If there are old trees that consistently make a high-quality cider and exist nowhere else, I have some colleagues at the University nearby who started the Boulder Apple Tree Project and are currently doing DNA sequencing and grafting of many of these old trees. So perhaps long-term I could use these data to preserve valuable heirloom varieties. 

Of course, the bottom line is that people should use the tools that work for them...

Courtney

[Vince Wakefield]

I like it,

 

Tried to do something similar myself with Airtable but also incorporating tractability for apples, so2, malic etc. with the addition of blending and batch numbers for the finished bottles, got way too complicated for my ability so gave up.

 

The thing with Airtable is it is accessible from any internet enabled device, phone, tablet or computer so a quick reading can be entered without having to fire up the computer and spread sheet.

 

Cheers

Vince

[Jean-David Camus]

Hi Courtney and others..

I think this is a wonderful idea and use of Airtable. Congrats for this work. I would be ready to find a compensation for a personal setup. How can I contact you?

[Jeff Westerman]

Courtney, 

What a great project you have come up with!  As with Jean-David, I would also be very interested in doing a personal setup with you. I don't know how to reach out to you directly, my apologies.  Just let me know how I can contact you and we will chat further!  Again, really impressive little app you threw together there.

Cheers,

Jeff

[Peter C. Ross - Incy Wincy Cyder (Aust.)]

Hi Claude, I also had this thought, however I am also often very critical of people "record keeping" in Excel vs a database. The main reason is that it's too easy to make mistakes in S/Sheets, whereas coded applications like this provide longer term consistency of record keeping and structure making analysis and reporting much easier and MUCH more reliable. The main advantages of a web database over a local one are that someone else backs up the data and you can access it from any device anywhere you need to (of course Google Sheets or Numbers in ICloud also provide these facilities)

I'd be very happy to pay to use an App like this on the web. It's well written.

[Claude Jolicoeur]

I agree some applications work better in database, while others work better in spreadsheet, and some can work well in either...

I do use relational databases for all about my orchard, trees, varieties, production, etc... So I know what this is about.

In the present case of following a fermentation, I feel I get the same functionalities with Excel - table for additions to the cider, graph for history of SG and FSU, etc. So I guess this is a case of either database of spreadsheet can be adaquate for the application.

As of doing it on the cloud - maybe I am too old school for this - I like to have my things local with backups on UBS disks. And honestly, I don't really trust the cloud. Will this still work in 10 years? I have been using my things for over 20 (and some for 30) years and everything still works (albeit my database needs to be run inside a Windows 2000 virtual machine as it is 16 bit, and not supported by any modern OS!)

[Courtney Meier]

I think Claude has some valid points regarding the use and longevity of Excel for the graphing work. I work as an ecological data scientist and if you really want to make sure data are likely going to be readable in the long-term, you write to .csv format. These .csv files don't support formulas and macros though, so Excel files are the next best choice since Excel is probably going to be a thing for the long haul, due to the fact that Microsoft software runs a god-awful number of critical business processes. However, I recently had the lovely experience of having the Office 365 version of Microsoft Word refuse to open a Word file that I created in the late 90s, suggesting there is at least one way MS has already screwed things up just with their own legacy document formats. Regardless, the disappearance of Excel would mean the disappearance of Microsoft, which seems unimaginable to me, at this point in time. Could one make a similar argument for Airtable? Certainly not.

Airtable has been around for 8 years though, so it's out of the start-up phase and has managed to persist. In this light, I liked the online nature of it and the fact it has an iOS app, and a free account offering. I monitor fruit production on a number of trees around Boulder from which I forage fruit or would like to forage fruit (300+ trees). I mapped each of these trees using an EpiCollect form I made, and I have some R code for taking the mapping data from EpiCollect to create a GoogleMaps layer that I then use when I am biking/walking around town to check on specific trees throughout the growing season. As the growing season progresses, I use the Airtable iOS app to record fruit set data for trees I have mapped (e.g., fruit abundance, likely ripening month), and then pair these data with SG and Total Acidity data from past years so it is efficient during harvest time for me to target high-quality trees that are likely going to have a lot of fruit and be ripe when I show up. 

After I made this system for keeping track of trees and juice data from those trees (much as it seems Claude does with a database), I thought it was natural to begin tracking batches I made from those trees in a relational way (e.g., the proportion of each juice used, the blend pH, blend TA, etc.). It was then easy enough to make another relational 'fermentation' table to keep track of fermentation from the batches, including SG, FSU, ABV, etc. through time. I then realized I could code something with R/Shiny to automatically make and display the graphs of SG & FSU through time for each fermenting batch. Up to this point, I was using Excel to manually make graphs. Maybe there's a macro for this? Not having a macro (or knowing of one), I found that manually making these graphs over and over again was tedious. The R/Shiny approach made graph production a simple matter of a click once I developed the code. All this said, if I went to Vegas to bet on it, I would bet Excel will outlive Airtable (and hence my whole approach). Because of this, I have some code I run periodically that downloads the Airtable data and saves it all as a .csv, just in case.

The final reason I like the R/Shiny approach is that I was able to create separate tabs for the Sulfite and Bottling calculations that I found myself routinely doing. It's completely true that these calculations can be readily performed by a good spreadsheet routine, which I believe Claude developed as companion software to his excellent book (which I consulted when making this app, along with Andrew's excellent book). But since I had already gravitated to addressing these other tasks with Airtable and R/Shiny, I figured I would incorporate the functionality in a code-driven way, and put a different UI on the calculations that fit my workstyle a little better. Which is only to say, I think people should use the tools they like best to produce the cider we all enjoy, so long as the different tools produce calculations that are equal and accurate!

Cheers,

[Courtney Meier]

Hi all,

I have continued to work on and update the Shiny application, and I have removed the password feature so I could share some entertaining map updates I made to the 'Trees Map' tab for finding which trees/varieties went into a batch of cider from foraged trees. I also updated the 'Summary' tab to automatically create a graph of the composition of each cider when a row in the main summary table is clicked, and to show a graph of the total juice volume by apple variety used for the year. 

https://meier.shinyapps.io/meier-cider-public/

As I mentioned when I originally posted, I am happy to share the code freely via GitHub and give people a copy of the underlying Airtable structure for free if anyone is interested in building something similar for themselves using what I've put together as a starting point. Unfortunately, there is no way to sign up for an account since there's no business model to support it, but I can clone an exact copy for free.

Cheers,

Courtney

---------------------
Courtney Meier | Plant Ecologist | Cider Hobbyist | Boulder, CO