Merge statistics of old entity to new entity

Hi,

It would be nice if we would merge entities in the statistics database. When you for instance rename an entity or replace a sensor, it would be nice if the data from the old sensor could be merged with the new sensor. For instance, in my energy dashboard I now have two sensor for my solar panel production since I started using a different integration to monitor my panels, it would be great if I could merge that into one.

Looks like you get your wish next month:

1 Like

I have created a script that merges 2 existing entities: hass_utils/merge_sensor_statistics.ipynb at 51403be6a1f180c02c6e66e1bdc75e493192006e · janmolemans/hass_utils · GitHub

Thanks! Need to make some adjustments for my use case, but now I finally have all my old entities merged into the new ones.

Hello all,

I have the same problem. I have measured the energy with different meters (Shelly Plug S, Shelly 1PM, Shelly Plus 1PM, Solarman Integration).

Now I only use Shelly Plus 1PM. For the energy history, the statistics of the old sensors are still there but they are otherwise dead. In the Energy Dashboard settings comes “Entity not defined”.

Is there an option to merge the old values? The script didn’t work for me and broke the DB (backup helped :wink: ).

I had the same issue, that the script broke my database. In the end I rewrote part of the script, so it updates the database instead of completely overriding it (merge_statistics · GitHub)

It typically still gives an error, since the timestamp of the last entry of the old sensor is the same as the timestamp for the first entry of the new sensor, but this doesn’t break the database. The error message you get tells you the timestamp that is duplicate. In the end I had to manually remove one of these duplicates before I could merge (I used the phpMyAdmin addon for this).

You also will have to correct the statistics after merging (developer tools → statistics → adjust sum), otherwise I got a large negative value at the crossing between the old and new sensor.

Hi @theneweinstein

Thanks for your script,

however the script crashes in the merge function I´m getting the following error :

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:1410, in Connection.execute(self, statement, parameters, execution_options)
   1409 try:
-> 1410     meth = statement._execute_on_connection
   1411 except AttributeError as err:

AttributeError: 'str' object has no attribute '_execute_on_connection'

The above exception was the direct cause of the following exception:

ObjectNotExecutableError                  Traceback (most recent call last)
Cell In[5], line 5
      3 for table in ("statistics", "statistics_short_term"):    
      4     print(table)
----> 5     merge(source_sensor, target_sensor, table)

Cell In[4], line 5, in merge(source_sensor, target_sensor, table)
      2 print(f"source: {source_sensor}, target: {target_sensor}")
      4 # read data from target sensor
----> 5 target_sensor_id=pandas.read_sql_query(f"""select id FROM statistics_meta 
      6                                             where statistic_id like '{target_sensor}';""", con).loc[0,'id']
      7 target_df=pandas.read_sql_query(
      8         f"select * FROM {table} where metadata_id = '{target_sensor_id}';", con
      9     )
     10 print(f"length of existing statistics for target sensor: {len(target_df)}")

File /usr/local/lib/python3.9/dist-packages/pandas/io/sql.py:397, in read_sql_query(sql, con, index_col, coerce_float, params, parse_dates, chunksize, dtype)
    339 """
    340 Read SQL query into a DataFrame.
    341 
   (...)
    394 parameter will be converted to UTC.
    395 """
    396 pandas_sql = pandasSQL_builder(con)
--> 397 return pandas_sql.read_query(
    398     sql,
    399     index_col=index_col,
    400     params=params,
    401     coerce_float=coerce_float,
    402     parse_dates=parse_dates,
    403     chunksize=chunksize,
    404     dtype=dtype,
    405 )

File /usr/local/lib/python3.9/dist-packages/pandas/io/sql.py:1560, in SQLDatabase.read_query(self, sql, index_col, coerce_float, parse_dates, params, chunksize, dtype)
   1512 """
   1513 Read SQL query into a DataFrame.
   1514 
   (...)
   1556 
   1557 """
   1558 args = _convert_params(sql, params)
-> 1560 result = self.execute(*args)
   1561 columns = result.keys()
   1563 if chunksize is not None:

File /usr/local/lib/python3.9/dist-packages/pandas/io/sql.py:1405, in SQLDatabase.execute(self, *args, **kwargs)
   1403 def execute(self, *args, **kwargs):
   1404     """Simple passthrough to SQLAlchemy connectable"""
-> 1405     return self.connectable.execution_options().execute(*args, **kwargs)

File /usr/local/lib/python3.9/dist-packages/sqlalchemy/engine/base.py:1412, in Connection.execute(self, statement, parameters, execution_options)
   1410     meth = statement._execute_on_connection
   1411 except AttributeError as err:
-> 1412     raise exc.ObjectNotExecutableError(statement) from err
   1413 else:
   1414     return meth(
   1415         self,
   1416         distilled_parameters,
   1417         execution_options or NO_OPTIONS,
   1418     )

ObjectNotExecutableError: Not an executable object: "select id FROM statistics_meta \n                                                where statistic_id like 'sensor.xx_52_total_lifetime_energy_output';"

I tried to debug the code, but could not find anything strange… Can you maybe help me out in the right direction? ( print(con) gives positive result → seems connected to the database ).

Thanks in advance,

It looks like the same problem I recently had. Apparently the pandas package does not work nicely with SQLAlchemy 2. I solved that by forcing an older version of SQLAlchemy to install in the init_commands of the jupyterlab addon (pip install "SQLAlchemy<2").

Since the latest HA release 2023.4 changing entity id keeps history data. This should help when moving to a new entity! Rename your old entity id to the new name, remove the old integration, install the new integration.

Yes, I was very happy to read that. Next time it will be a lot easier to keep my history.