-
-
Save theneweinstein/f077003e8344f4111a7a6b69c192fd9c to your computer and use it in GitHub Desktop.
merge_statistics
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
{ | |
"cells": [ | |
{ | |
"cell_type": "code", | |
"execution_count": 1, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"data": { | |
"text/plain": [ | |
"'\\nThis scripts merges the statistics of 2 sensors into 1\\nIt does this for multiple sensors because i had added almost the same sensors via 2 different integrations\\n'" | |
] | |
}, | |
"execution_count": 1, | |
"metadata": {}, | |
"output_type": "execute_result" | |
} | |
], | |
"source": [ | |
"\"\"\"\n", | |
"This scripts merges the statistics of 2 sensors into 1\n", | |
"\"\"\"" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 15, | |
"metadata": {}, | |
"outputs": [], | |
"source": [ | |
"# mapping of the sensors, statistics of the left ones will be merged into the right ones\n", | |
"mapping={\n", | |
" 'sensor.old1': 'sensor.new1',\n", | |
" 'sensor.old2': 'sensor.new2',\n", | |
" }" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"# Connect to DB" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 2, | |
"metadata": {}, | |
"outputs": [], | |
"source": [ | |
"import pandas\n", | |
"import sqlalchemy\n", | |
"import pandas\n", | |
"pandas.options.plotting.backend = \"plotly\"\n", | |
"\n", | |
"# Read sqlite query results into a pandas DataFrame\n", | |
"engine = sqlalchemy.create_engine(\"mysql://USERNAME:PASSWORD@core-mariadb/homeassistant\")\n", | |
"con = engine.connect()" | |
] | |
}, | |
{ | |
"cell_type": "markdown", | |
"metadata": {}, | |
"source": [ | |
"# Merge" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 3, | |
"metadata": {}, | |
"outputs": [], | |
"source": [ | |
"def merge(source_sensor, target_sensor, table='statistics'):\n", | |
" print(f\"source: {source_sensor}, target: {target_sensor}\")\n", | |
" # read data from target sensor\n", | |
" target_sensor_id=pandas.read_sql_query(f\"\"\"select id FROM statistics_meta \n", | |
" where statistic_id like '{target_sensor}';\"\"\", con).loc[0,'id']\n", | |
" target_df=pandas.read_sql_query(\n", | |
" f\"select * FROM {table} where metadata_id = '{target_sensor_id}';\", con\n", | |
" )\n", | |
" print(f\"length of existing statistics for target sensor: {len(target_df)}\")\n", | |
"\n", | |
" # read data from source sensor\n", | |
" source_sensor_id=pandas.read_sql_query(f\"\"\"select id FROM statistics_meta \n", | |
" where statistic_id like '{source_sensor}';\"\"\", con).loc[0,'id']\n", | |
" source_df=pandas.read_sql_query(\n", | |
" f\"select * FROM {table} where metadata_id = '{source_sensor_id}';\", con\n", | |
" )\n", | |
" print(f\"length of existing statistics for source sensor: {len(source_df)}\")\n", | |
" \n", | |
" print(source_sensor_id)\n", | |
" print(target_sensor_id)\n", | |
" \n", | |
" stmnt = sqlalchemy.text(\n", | |
" f\"\"\"UPDATE {table}\n", | |
" SET metadata_id = {target_sensor_id} \n", | |
" WHERE metadata_id = {source_sensor_id};\"\"\"\n", | |
" )\n", | |
" con.execute(stmnt)" | |
] | |
}, | |
{ | |
"cell_type": "code", | |
"execution_count": 16, | |
"metadata": {}, | |
"outputs": [ | |
{ | |
"name": "stdout", | |
"output_type": "stream", | |
"text": [ | |
"statistics\n", | |
"source: sensor.gas_verbruik, target: sensor.meterkast_gasverbruik\n", | |
"length of existing statistics for target sensor: 4\n", | |
"length of existing statistics for source sensor: 13705\n", | |
"652\n", | |
"1113\n", | |
"statistics_short_term\n", | |
"source: sensor.gas_verbruik, target: sensor.meterkast_gasverbruik\n", | |
"length of existing statistics for target sensor: 40\n", | |
"length of existing statistics for source sensor: 3042\n", | |
"652\n", | |
"1113\n" | |
] | |
} | |
], | |
"source": [ | |
"for source_sensor, target_sensor in mapping.items():\n", | |
" for table in (\"statistics\", \"statistics_short_term\"): \n", | |
" print(table)\n", | |
" merge(source_sensor, target_sensor, table)" | |
] | |
} | |
], | |
"metadata": { | |
"kernelspec": { | |
"display_name": "Python 3 (ipykernel)", | |
"language": "python", | |
"name": "python3" | |
}, | |
"language_info": { | |
"codemirror_mode": { | |
"name": "ipython", | |
"version": 3 | |
}, | |
"file_extension": ".py", | |
"mimetype": "text/x-python", | |
"name": "python", | |
"nbconvert_exporter": "python", | |
"pygments_lexer": "ipython3", | |
"version": "3.9.2" | |
}, | |
"vscode": { | |
"interpreter": { | |
"hash": "29f62ddf6052fc3cbbf17655180ba76afb223cb4671e29d3f02f8ca2f3b5d212" | |
} | |
} | |
}, | |
"nbformat": 4, | |
"nbformat_minor": 4 | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment