Wine Drink Date

[RedOctoberSUI]

What a great integration, thank you very much! Not a bug, more a very kind request: Any chance we can get the "Drink by" data? After all, this is what most people use CT for :)

Thank you very much!

[rwez]

True. This I would like to see too, if at all possible.

[pmaere]

I'd like to add that with the list of bottles that you have. Will need some help on quality assurance. Anyone interested?

[rwez]

what can I do to help ?

[RedOctoberSUI]

what can i do to help...On 2 Apr 2023 22:30, rwez ***@***.***> wrote: what can I do to help ? —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

[pmaere]

what can I do to help ?

I figured out yesterday that basically all the data of your cellar should be available. The question is how to get it in home assistant. You can add groups, but I actually want just a list of bottles with their characteristics.
I'm very limited in time, so if I can get something working on my HA instance, I might forward the changes for someone to perfect and do the PR on github.
If I find the time...

[pmaere]

Ok, i got this to work.

First, you need to change the following class in your init file:

class WineCellarData:
    """Get the latest data and update the states."""

    def __init__(self, username, password):
        """Init the Canary data object."""

        # self._api = Api(username, password, timeout)

        # self._locations_by_id = {}
        # self._readings_by_device_id = {}
        self._username = username
        self._password = password
        

    def get_reading(self, key):
      return self._data[key]

    def get_readings(self):
      return self._data

    @Throttle(MIN_TIME_BETWEEN_UPDATES)
    def update(self, **kwargs):
      data = {}
      username = self._username
      password = self._password


      client = cellartracker.CellarTracker(username, password)
      list = client.get_inventory()
      df = pd.DataFrame(list)
      df[["Price","Valuation"]] = df[["Price","Valuation"]].apply(pd.to_numeric)
      
      group_data = df.groupby(["iWine"]).agg({'Wine': ['count', 'first'], 'Valuation': ['sum', 'mean'], 'Appellation': 'first','Vintage': 'first', 'Color': 'first', 'BeginConsume':'first','EndConsume':'first'})
      group_data.columns = ['_'.join(col).strip() if col[1] else col[0] for col in group_data.columns.values]
      for row_main, item_main in group_data.iterrows():
          # the name represents the group
          name = item_main['Wine_first'] + '_' + item_main['Vintage_first']
          data[name] = {}
          data[name][row_main] = {"count": item_main['Wine_count'], "value_total": item_main['Valuation_sum'],
                             "value_avg": item_main['Valuation_mean'], "%": 1}
          data[name][row_main]["Wine Name"] = item_main['Wine_first']
          data[name][row_main]["Vintage"] = item_main['Vintage_first']
          data[name][row_main]["Colour"] = item_main['Color_first']
          data[name][row_main]["Appellation"] = item_main['Appellation_first']
          data[name][row_main]["Best after"] = item_main['BeginConsume_first']
          data[name][row_main]["Best before"] = item_main['EndConsume_first']
#          data[name][row_main]["Wine"] = item_main['Wine_first']
 
      
#      groups = ['Varietal', 'Country', 'Vintage', 'Producer', 'Type', 'Wine']


#      for group in groups:
#        group_data = df.groupby(group).agg({'iWine':'count','Valuation':['sum','mean']})
#        group_data.columns = group_data.columns.droplevel(0)
#        group_data["%"] = 1
#        group_data["%"] = (group_data['count']/group_data['count'].sum() ) * 100
#        group_data.columns = ["count", "value_total", "value_avg", "%"]
#        data[group] = {}
#        for row, item in group_data.iterrows():
#          data[group][row] = item.to_dict()
#          data[group][row]["sub_type"] = row



      data["total_bottles"] = len(df)
      data["total_value"] = df['Valuation'].sum()
      data["average_value"] = df['Valuation'].mean()
      self._data = data

Then, I've changed this property in sensor.py

@property
    def name(self):
        """Return the name of the sensor."""
        if(self._sub_type):
            return "cellar_tracker." + self._sensor_type.lower()# + "." + self._slug.lower()
        else:
            return "cellar_tracker." + self._sensor_type.lower()

Then, add this to your dashboard:

type: vertical-stack
cards:
  - type: glance
    entities:
      - entity: sensor.cellar_tracker_total_bottles
  - type: custom:auto-entities
    filter:
      include:
        - attributes:
            Colour: Red
    card:
      type: custom:flex-table-card
      title: Rood
      clickable: true
      columns:
        - name: Name
          data: Wine Name
        - name: Vintage
          data: Vintage
        - name: Colour
          data: Colour
        - name: Count
          data: count
        - name: Value
          data: value_avg
          modify: >-
            if(parseFloat(x).toFixed(0) == 0) {""}
            else{parseFloat(x).toFixed(0)+"€"}
        - name: Best after
          data: Best after
          modify: if(x.length == 0){""} else{parseFloat(x).toFixed(0)}
        - name: Best before
          data: Best before
          modify: if(x.length == 0){""} else{parseFloat(x).toFixed(0)}
      sort_by:
        - Colour
        - Vintage-
  - type: custom:auto-entities
    filter:
      include:
        - attributes:
            Colour: White
    card:
      type: custom:flex-table-card
      title: Wit
      clickable: true
      columns:
        - name: Name
          data: Wine Name
        - name: Vintage
          data: Vintage
        - name: Colour
          data: Colour
        - name: Count
          data: count
        - name: Value
          data: value_avg
          modify: >-
            if(parseFloat(x).toFixed(0) == 0) {""}
            else{parseFloat(x).toFixed(0)+"€"}
        - name: Best after
          data: Best after
          modify: if(x.length == 0){""} else{parseFloat(x).toFixed(0)}
        - name: Best before
          data: Best before
          modify: if(x.length == 0){""} else{parseFloat(x).toFixed(0)}
      sort_by:
        - Colour
        - Vintage-
  - type: custom:auto-entities
    filter:
      include:
        - attributes:
            Colour: Rosé
    card:
      type: custom:flex-table-card
      title: Rosé
      clickable: true
      columns:
        - name: Name
          data: Wine Name
        - name: Vintage
          data: Vintage
        - name: Colour
          data: Colour
        - name: Count
          data: count
        - name: Value
          data: value_avg
          modify: >-
            if(parseFloat(x).toFixed(0) == 0) {""}
            else{parseFloat(x).toFixed(0)+"€"}
        - name: Best after
          data: Best after
          modify: if(x.length == 0){""} else{parseFloat(x).toFixed(0)}
        - name: Best before
          data: Best before
          modify: if(x.length == 0){""} else{parseFloat(x).toFixed(0)}
      sort_by:
        - Colour
        - Vintage-

To get a list of all your wines, sorted by Red, White, Rosé, with attributes like drink date, vintage, value in the list.

[soggypete]

@pmaere Thank you for the code. Getting in each bottle looks great and I've managed to alter my code to do the same.

However, I would also like to get groups of countries/colours in, as it's interesting to see at a glance the percentage of wines from france vs portugal for example.

Furthermore, when I remove a wine from cellar tracker it doesn't seem to remove the sensor. Any ideas?