data_exporters/openhab_influxdb/Export_openhab_data_from_influxdb.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1fa1fca4-49ad-4562-a5e5-f55660a8a536",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Export openhab data from influxdb"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2fd38277-f532-493e-9c06-624fa887a80b",
   "metadata": {},
   "source": [
    "Persisted power data can be exported from the influxdb of an openhab installation.\n",
    "\n",
    "__Export data to file__  \n",
    "\n",
    "Run the following command on your openhab server to stores all measurements of the item `Strom_Leistung_Gesamt` (containing your power data) into a text file. The time frame of the export has to be adjusted according the the time period of the recoding. However, too long time periods (> 3 month) might lead to errors during the export.\n",
    "\n",
    "```bash\n",
    "curl -G http://localhost:8086/query?db=openhab_db -u admin:'PASSWORD' --data-urlencode \"q=SELECT * FROM Strom_Leistung_Gesamt WHERE time > '2020-01-01T00:00:00Z' AND time < '2020-01-02T00:00:00Z'\" > export.json\n",
    "```\n",
    "  \n",
    "  \n",
    "__Copy data from openhab server__\n",
    "\n",
    "Run the following command on the PC where you will do the analysis of the data. After providing the password for the `openhab` user, it exports the above created json file and stores it in the current directory. (Notice the \".\" at the end of the command).\n",
    "\n",
    "```bash\n",
    "scp openhabian@OPENHAB_IP:/home/openhabian/resultAprilMai.json .\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27a5678d-db70-48eb-b147-62f0c9fdf075",
   "metadata": {},
   "source": [
    "## Convert to data\n",
    "\n",
    "The exported data is converted to the required data format of the analysis script."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "e651713d-4403-44d8-8a82-3a1780adb9d1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import pandas as pd\n",
    "\n",
    "with open('export.json') as json_file:\n",
    "    raw_data = json.load(json_file)\n",
    "\n",
    "data = raw_data['results'][0]['series'][0]['values']\n",
    "\n",
    "# Add more data files if needed:\n",
    "#with open('export2.json') as json_file:\n",
    "#    raw_data2= json.load(json_file)  \n",
    "# data2 = raw_data2['results'][0]['series'][0]['values']\n",
    "# data.extend(data2)\n",
    "\n",
    "\n",
    "# Save data to csv for analysis\n",
    "df = pd.DataFrame.from_records(data, columns = ['timestamp', '', 'power'])[['timestamp', 'power']]\n",
    "df.to_csv(\"../../sample_data.csv\")\n",
    "\n"
   ]
  }
 ],
 "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}