Heat map/ histogram / drinkability window ideas?

CellarTracker Main Site

Heat map/ histogram / drinkability window ideas?

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> [Cellar Talk] >> General Discussion >> Heat map/ histogram / drinkability window ideas?

Page: [1]

Message

<< Older Topic Newer Topic >>

Heat map/ histogram / drinkability window ideas? - 11/27/2021 1:51:49 PM

curtavino

Posts: 19
Joined: 8/3/2018
Status: offline

Hi all,
I'm trying to figure out how to best go about this problem:

I would like to get a report that shows a drinkability heat-map for my cellar by time. At its simplest, I'm thinking it would show the middle of the drinking window for each wine on the same chart (as a dot, or some sort of histogram bar), so I can see at a glance how well my cellar is stocked for near term (next 3 years), mid term (4 to 10), and long term (10+ years).

First cut would probably just use the Excel dump of my CT data and try to parse the data that way, but eventually if it works, maybe it can be a report available on CT?

Anyway, interested if any of you have ideas or better ways to parse the data to get this. I'm a wires and pliers guy, data analysis is not my wheelhouse.

Thanks.

(also, if CT has something similar to this already, apologies that I didn't see it or know about it yet)

Post #: 1

RE: Heat map/ histogram / drinkability window ideas? - 11/27/2021 4:00:52 PM

wine247365

Posts: 1012
Joined: 5/1/2012
From: OC, CA
Status: offline

One view of your idea is under Quick Links, Show Reports, Time to Drink Up.

_____________________________

The number of bottles I buy is nothing in comparison to the bottles I don’t buy. Let’s have a little perspective please.

(in reply to curtavino)

Post #: 2

RE: Heat map/ histogram / drinkability window ideas? - 11/27/2021 7:54:19 PM

BenG

Posts: 846
Joined: 5/5/2009
From: Australian in Idaho
Status: offline

Graphing a wine by the middle of its drinking window might be problematic if you have multiples of that wine, because you might want to spread the dozen bottles or so evenly across the drinking window.

One idea could be to have that wine counted for each year that it's in the drinking window. So if wine XYZ has a window from 2025 to 2034, it would appear in each of those years. You could then plot a density graph showing the number of wines that are within the window for each year. A true density graph may count the wine as 1/X units for the X number of years it is in the drinking window (so if the window is 10 years, it counts as 1/10 of a bottle for each of the years that it is in).

You could display it as a bar chart with a bar for each year and even as a stacked bar chart with different colors - a color representing short, mid and long term cellared wines or colors for varietal/region/style of wine.

(in reply to wine247365)

Post #: 3

RE: Heat map/ histogram / drinkability window ideas? - 11/28/2021 5:26:39 AM

racerchris

Posts: 1622
Joined: 6/22/2015
From: Bolton, CT
Status: offline

There's a thread in here about a drinking tsunami, started by user "soteriologist" I think.

_____________________________

Chris Foley
Searching for very good to excellent, cheap Bordeaux
stainlesswineracks.com

(in reply to BenG)

Post #: 4

RE: Heat map/ histogram / drinkability window ideas? - 11/28/2021 6:17:30 AM

DoubleD1969

Posts: 3601
Joined: 8/19/2008
From: New Jersey
Status: offline

Sounds like a lot of work for a fun hobby. The challenge with mapping your cellar on when you should be drinking them is that your inputs (drink window) is very flawed. I find CT drinking windows useless because they are based on people’s tastes that don’t necessarily align to mine. If you are using your experience, which I assume is limited at this time, you will need to periodically adjust your windows as your experience increases. Like I said it is a lot of work.

< Message edited by DoubleD1969 -- 11/28/2021 6:18:51 AM >

(in reply to racerchris)

Post #: 5

RE: Heat map/ histogram / drinkability window ideas? - 11/28/2021 7:12:31 AM

BenG

Posts: 846
Joined: 5/5/2009
From: Australian in Idaho
Status: offline

Eric the CT data scientist has been doing some work on CT drinking windows. Since this is crowd-sourced, (as DoubleD1969 has said) individual CTers may find it quite different from their own experience. It may be useful as a benchmark however and if, for instance, your individual drinking window starts later than most, you could just add a few years to each CT drinking window.

< Message edited by BenG -- 11/28/2021 7:13:29 AM >

(in reply to DoubleD1969)

Post #: 6

RE: Heat map/ histogram / drinkability window ideas? - 11/28/2021 7:19:50 AM

BenG

Posts: 846
Joined: 5/5/2009
From: Australian in Idaho
Status: offline

I might also add, re drinking windows, that quite a few CTers aren't aware of the "cocoon phase" that some wines go through (especially riesling). So, as soon as the primary fruit starts to fade, some CTers will declare that the wine is past it, even though that same wine might develop some glorious tertiary flavors within a few years. That may be an interesting data modeling challenge.

(in reply to BenG)

Post #: 7

RE: Heat map/ histogram / drinkability window ideas? - 11/30/2021 6:02:26 AM

curtavino

Posts: 19
Joined: 8/3/2018
Status: offline

Hi all,
Thanks for the replies. A lot of things to consider here.

I definitely don't want this to be burdensome. Just a quick report a couple times a year. I periodically update drinking windows and make sure all the wines have some year there, even if it's just a quick guess.

Time To Drink Up report is nice, but that seems to be the end of the windows.

Really this is more for buying and cellar management (it's almost full). So I don't stock up on 30 year agers when what I really need is stuff to drink in 10, and of course that target will always move.

Thanks again all, if/when I come up with something I will definitely share.

(in reply to BenG)

Post #: 8

RE: Heat map/ histogram / drinkability window ideas? - 11/30/2021 5:21:34 PM

racerchris

Posts: 1622
Joined: 6/22/2015
From: Bolton, CT
Status: offline

I also refer to a custom saved search that I call Drinking Window. I'm pretty sure it was recommended by another user on this forum a few years ago.
Starting from My Cellar I group by Drink Starting, then by Drink By, then by Individual Wine.
Take a look. I use it several times a year.

_____________________________

Chris Foley
Searching for very good to excellent, cheap Bordeaux
stainlesswineracks.com

(in reply to curtavino)

Post #: 9

RE: Heat map/ histogram / drinkability window ideas? - 12/1/2021 6:31:02 AM

bmuir40

Posts: 86
Joined: 5/8/2015
Status: offline

I approached this problem in 2 different hybrid ways.

1) I am kind of an excel nerd, and I started a tab for each year into the future. For each wine I acquire, I put that wine in the tab of the year I think I will want to drink them. So, when I want to open a bottle, I can look at the excel chart and it's almost like a wine list for me. I have even printed them before to look like a wine list. Pre-Covid, I would invite people over, and let them select a bottle that way. There are some times when I do jump ahead, and take one from an upcoming year, but I use this table as a guide.

2) For times I don't look at the list, I place wines I need to drink soon, at eye level right as I open my cellar door. Then it's easier for me to make a selection.

(in reply to racerchris)

Post #: 10

RE: Heat map/ histogram / drinkability window ideas? - 12/1/2021 5:53:33 PM

KPB

Posts: 4659
Joined: 11/25/2012
From: Ithaca, New York
Status: offline

Jancis Robinson wrote a book about this, called vintage wine charts. One big issue is that wines don’t necessarily improve steadily as they age. Take my favorites, from Chateauneuf du Pape, and compare with Napa or Bordeaux. The former will be great on release but lacking more evolved, spicy characteristics. Then they often shut down hard for six years, but then the best wines reopen and blossom, but this is vintage dependent. The blend varies year to year, so the aging profile varies. It gets very hard to predict.

With Napa and a Bordeaux, where Cabernet Sauvignon dominates, the evolution is more linear. Undrinkable on release, although the same wine often was very approachable in barrel. Somehow, bottling and the dose of SO2 shut those down. Then they re-emerge but more steadily and peak at age 15-25. Yet you can coax them from their shells with extended decanting for much of that whole period.

So how would a heat map really look? Not obvious to me….

< Message edited by KPB -- 12/1/2021 5:54:52 PM >

_____________________________

Ken Birman
The Professor of Brettology

(in reply to bmuir40)

Post #: 11

RE: Heat map/ histogram / drinkability window ideas? - 12/3/2021 12:11:18 PM

curtavino

Posts: 19
Joined: 8/3/2018
Status: offline

Well I came up with a decent 1st cut at my estimates. It's garbage in / garbage out, so I will have to keep up on my drink dates. And Ken, the double-peak ones will be difficult, but this is more for informing bulk-buying than drinkability. I believe CT has some modeling in the drinkability calculations for double-peaks and what not, but one of their folks will have to elaborate on that.

For my model, I took a full csv dump of the CT individual bottles and made sure there was some data in each start/end date. I then used the normal-distribution function (Bell curve, basically) centered on the middle of the drinking window and a 30% standard deviation. This spreads each wine out over its window, weighing the middle of the drinking window heavier. I also had to add smarts to tack on over-the-hill wines to the current year. I then sum up all the "partial" values for each year and it gives me a total number of bottles expected to "peak" in that year.

I then made a target curve using our current annual drinking habits based on short/mid/long aging wines. It turned out better than I had hoped. I'd share the chart here, but I have no idea how to attach files. If I figure that out, I'll post it.

End result is that I am actually not too far off. I'm a little heavy in the 5 year window and start to get light on the long haulers, but just by a few bottles. If I keep up my "replace what you drink" and add a few Barolos, Beaucastels, and Bourdeaux every year, I should be in good shape.

Thanks for all the inputs folks. Gave me a lot to think about and definitely changed my original approach.

(in reply to KPB)

Post #: 12

RE: Heat map/ histogram / drinkability window ideas? - 1/15/2022 9:06:02 AM

al00p

Posts: 28
Joined: 8/10/2020
From: Portland, OR
Status: offline

My onesies and twosies I can find in the drink by reports. What I'm more interested in are the top five varietals in my cellar. When are these ready to go? I look at them based on the begin consume and end consume dates. I also look at the midpoint of the drinking window. Here's how I SEE it on my iPad/iPhone/Mac. Tap a square to view the bottles driving the the chart.

I'm using WineGPS to view my Cellar Tracker wines.

Open to suggestions! Just not about my Tempranillo. ;-)

(in reply to curtavino)

Post #: 13

RE: Heat map/ histogram / drinkability window ideas? - 1/15/2022 9:34:23 AM

Sean McGrath

Posts: 371
Joined: 11/3/2020
From: US in NL
Status: offline

I do two things, one for consumption and one for purchasing.

I have a modified CT drinkability report where I fix NVs and put in a guess for blank drinking windows. I also summarize by my wine categories and by grape variety (two reports), so that ten 0.2 per year wines are summarized as 2 per year. I use that to make sure I don't drink them all too early.

I also have in excel a summary by grape, average vintage age, and with some rough estimates for windows and a consumption target per year (below). I use that to estimate what varietals are under-represented and/or under-aged in their distribution. Red means buy! :-)

_____________________________

(in reply to al00p)

Post #: 14

RE: Heat map/ histogram / drinkability window ideas? - 1/15/2022 10:06:22 AM

Sean McGrath

Posts: 371
Joined: 11/3/2020
From: US in NL
Status: offline

quote:

ORIGINAL: al00p

What I'm more interested in are the top five varietals in my cellar. When are these ready to go?

Hi al00p,
Do you also use it for purchase, or mostly consumption?

_____________________________

(in reply to al00p)

Post #: 15

RE: Heat map/ histogram / drinkability window ideas? - 1/16/2022 4:56:55 AM

Echinosum

Posts: 604
Joined: 1/28/2021
From: Buckinghamshire, UK
Status: offline

quote:

ORIGINAL: curtavino
Hi all,
I'm trying to figure out how to best go about this problem:

I would like to get a report that shows a drinkability heat-map for my cellar by time. At its simplest, I'm thinking it would show the middle of the drinking window for each wine on the same chart (as a dot, or some sort of histogram bar), so I can see at a glance how well my cellar is stocked for near term (next 3 years), mid term (4 to 10), and long term (10+ years).

First cut would probably just use the Excel dump of my CT data and try to parse the data that way, but eventually if it works, maybe it can be a report available on CT?

Anyway, interested if any of you have ideas or better ways to parse the data to get this. I'm a wires and pliers guy, data analysis is not my wheelhouse.

I think the big issue is when is the drinking window of a given wine. I find a bit of trial and error, in terms of opening bottles, is required. I find merchants often try and persuade you it will be drinkable before in reality it really is: or it may be drinkable but you'd do better to wait.

Before I even joined CT, I made a little spreadsheet of my wine collection to test if I had too much wine for any future year. Because in the past I have got overstocked for certain drinking windows, and ended up keeping stuff

It's not so tricky if you are used to excel, and doing a few sums.

So for my pre-CT effort, I had a line for each wine, showing number of bottles, start year and end year of drinking window. Then I had a cell for each year. As it was a one-off effort, I just manually spread the drinking over the drinking window, with scatters of whole numbers that added up to the total number of bottles, and included provision for a hump of drinking. That involved putting in lots of data, so it isn't really practical except as a one-off effort.

But we can easily download the data out of CT, and build a little formula to spread the drinking. That's the big issue, how do we do that? When we realise it doesn't really matter if the numbers are whole numbers, because we are going to add it up vertically down the years, then such a function becomes more easily designable.

I'd probably just build a little pyramid function, that was zero outside the drinking window, scaled to the width of the drinking window, and scaled to the total number of bottles. Outside the drinking window, the function would be negative, so we can cut that off very easily. Now we have a free parameter, which is how high is the pyramid, in fact probably 2 parameters, as we rescale the slope for different pyramid widths.

I'm probably going to do this sometime, but not today!

If you have your entire wine collection present and easily accessible, now you also have a drinking schedule. My situation is a bit more complicated. I only have space for about 30% of my collection at home, the rest is remote. It has to be delivered in whole cases. Also my pit cellar is very simple, and has to be arranged as things that are easily accessible, and things that are less easily accessible. So I do have a lot of bottles at the back of the cellar that I probably should not have had delivered so early! But the sodding merchants persuaded me it would be drinking. I know better now. Especially with Italian reds....

(in reply to curtavino)

Post #: 16

RE: Heat map/ histogram / drinkability window ideas? - 1/16/2022 7:49:55 AM

BenG

Posts: 846
Joined: 5/5/2009
From: Australian in Idaho
Status: offline

Lots of great ideas. I like the color coding of Al00p (or shades of grey, if colorblind) and Sean's use of percentage of target. Breaking it into different categories is useful. For myself, I have a pretty good idea of how often I'll be cooking different types of cuisine, so I would tend to group wines by which cuisine they would complement. If a wine is suited to two different types of cuisine, allocate half a bottle to each.

I also like Echinosum's idea of the pyramid. We used to use an allocation method at work for assigning a percentage to prior year values in order to estimate future values (a higher percentage for more recent years, except for the last year for which data was not settled). You could do something similar with a bottle of wine - if it's 6 year drinking envelope, you can allocate say 25% of the bottle to each of the middle years, 15% to the adjacent years and 10% to the outer years (to add up to 100%). These would not need to be entered manually if you put in a formula to find the length of the envelope and then calculate the percentage according to a standardized allocation. (Why not use Pascal's triangle - 1; 1,1; 1,2,1; 1,3,3,1; 1,4, 6, 4, 1; etc. - with the selection of the line dependent on the length of the envelope. There is a formula in excel which can calculate these numbers using factorials - COMBIN. Each set of numbers adds up to a power of 2, so you can divide the factorial result by the power of 2 to allocate to each of the years in the envelope)

Each column would represent a year, and then add up all the 0.25s, 0.15s, 0.1s etc. to find out how many bottles you have allocated to that year. Then you would have a summary tab with the color coding to show how close you are to the targets.

< Message edited by BenG -- 1/16/2022 8:10:29 AM >

(in reply to Echinosum)

Post #: 17

RE: Heat map/ histogram / drinkability window ideas? - 1/16/2022 8:49:14 AM

jmc167

Posts: 81
Joined: 12/30/2015
From: Portland, Or
Status: offline

quote:

ORIGINAL: racerchris

I also refer to a custom saved search that I call Drinking Window. I'm pretty sure it was recommended by another user on this forum a few years ago.
Starting from My Cellar I group by Drink Starting, then by Drink By, then by Individual Wine.
Take a look. I use it several times a year.

This is brilliant - thank you sir!

_____________________________

Just a guy trying to drink as much as I purchase, and losing this battle brilliantly!

(in reply to racerchris)

Post #: 18

RE: Heat map/ histogram / drinkability window ideas? - 1/16/2022 9:38:44 AM

Echinosum

Posts: 604
Joined: 1/28/2021
From: Buckinghamshire, UK
Status: offline

quote:

ORIGINAL: BenG
Why not use Pascal's triangle - 1; 1,1; 1,2,1; 1,3,3,1; 1,4, 6, 4, 1; etc. - with the selection of the line dependent on the length of the envelope. There is a formula in excel which can calculate these numbers using factorials - COMBIN. Each set of numbers adds up to a power of 2, so you can divide the factorial result by the power of 2 to allocate to each of the years in the envelope

Well done for spotting a predesigned integer function of known sum that already does what I was looking for. Not sure it's the shape I would have in mind, it's a bit abrupt. But it's a good one to do an initial build and test. Stripping the 1's off each end, would make it a bit less abrupt.

(in reply to BenG)

Post #: 19

RE: Heat map/ histogram / drinkability window ideas? - 1/16/2022 11:10:50 AM

BenG

Posts: 846
Joined: 5/5/2009
From: Australian in Idaho
Status: offline

quote:

ORIGINAL: Echinosum
Well done for spotting a predesigned integer function of known sum that already does what I was looking for. Not sure it's the shape I would have in mind, it's a bit abrupt. But it's a good one to do an initial build and test. Stripping the 1's off each end, would make it a bit less abrupt.

Stripping the 1's is a good idea. The denominator would then need to be changed to (2^n - 2). The formula might read -

=IF(AND(K$1>=A2,K$1<=B2),COMBIN(B2-A2+2,K$1-B2+1)/(2^(B2-A2+2)-2),0)

where A1 and A2 where the start and end of the drinking envelope and K was the column for the year (the formula can be copied to other columns).

(in reply to Echinosum)

Post #: 20