{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5a04c4e5",
   "metadata": {},
   "source": [
    "#### Use below code to convert the csv files to an sqlite database dump."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1b89c713",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import sqlite3\n",
    "import glob\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b102f2e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "eventTypeTableFilenames = [fn for fn in glob.glob('event_*.csv') \n",
    "                           if not fn == \"event_map_type.csv\" and not fn == \"event_object.csv\"]\n",
    "objectTypeTableFilenames =  [fn for fn in glob.glob('object_*.csv') \n",
    "                             if not fn == \"object_map_type.csv\" and not fn == \"object_object.csv\"]\n",
    "\n",
    "TABLES = dict()\n",
    "\n",
    "TABLES[\"event\"] = pd.read_csv(\"event.csv\", sep=\";\")\n",
    "TABLES[\"event_map_type\"] = pd.read_csv(\"event_map_type.csv\", sep=\";\")\n",
    "TABLES[\"event_object\"] = pd.read_csv(\"event_object.csv\", sep=\";\")\n",
    "TABLES[\"object\"] = pd.read_csv(\"object.csv\", sep=\";\")\n",
    "TABLES[\"object_object\"] = pd.read_csv(\"object_object.csv\", sep=\";\")\n",
    "TABLES[\"object_map_type\"] = pd.read_csv(\"object_map_type.csv\", sep=\";\")\n",
    "\n",
    "for fn in eventTypeTableFilenames:\n",
    "    table_name = fn.split(\".\")[0]\n",
    "    table = pd.read_csv(fn, sep=\";\")\n",
    "    TABLES[table_name] = table\n",
    "    \n",
    "for fn in objectTypeTableFilenames:\n",
    "    table_name = fn.split(\".\")[0]\n",
    "    table = pd.read_csv(fn, sep=\";\")\n",
    "    TABLES[table_name] = table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4f35b71a",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_path = \"ContainerLogistics.sqlite\"\n",
    "if os.path.exists(sql_path):\n",
    "    os.remove(sql_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b364103d",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn = sqlite3.connect(sql_path)\n",
    "for tn, df in TABLES.items():\n",
    "    df.to_sql(tn, conn, index=False)\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "Remove Duplicates"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "fd78535e21d56d1a"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# Connect to your SQLite database\n",
    "conn = sqlite3.connect(\"ContainerLogistics.sqlite\")\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Create a new table without duplicates\n",
    "cursor.execute('''CREATE TABLE IF NOT EXISTS object_object_no_duplicates AS\n",
    "                  SELECT DISTINCT * FROM object_object''')\n",
    "\n",
    "# Drop the original table\n",
    "cursor.execute('DROP TABLE IF EXISTS object_object')\n",
    "\n",
    "# Rename the new table to the original table name\n",
    "cursor.execute('ALTER TABLE object_object_no_duplicates RENAME TO object_object')\n",
    "\n",
    "# Commit changes and close the connection\n",
    "conn.commit()\n",
    "conn.close()"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "b314f82139822be0"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# Connect to your SQLite database\n",
    "conn = sqlite3.connect(\"ContainerLogistics.sqlite\")\n",
    "cursor = conn.cursor()\n",
    "\n",
    "# Create a new table without duplicates\n",
    "cursor.execute('''CREATE TABLE IF NOT EXISTS event_object_no_duplicates AS\n",
    "                  SELECT DISTINCT * FROM event_object''')\n",
    "\n",
    "# Drop the original table\n",
    "cursor.execute('DROP TABLE IF EXISTS event_object')\n",
    "\n",
    "# Rename the new table to the original table name\n",
    "cursor.execute('ALTER TABLE event_object_no_duplicates RENAME TO event_object')\n",
    "\n",
    "# Commit changes and close the connection\n",
    "conn.commit()\n",
    "conn.close()"
   ],
   "metadata": {
    "collapsed": false
   },
   "id": "9e9e09613e11cee7"
  },
  {
   "cell_type": "markdown",
   "id": "5ba7bcfd",
   "metadata": {},
   "source": [
    "#### Use below code to start working with the log in PM4PY."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2521ce7",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pm4py\n",
    "\n",
    "ocel = pm4py.read_ocel2_sqlite(sql_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c6f57c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "ocel"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "330f9d05",
   "metadata": {},
   "outputs": [],
   "source": [
    "ocpn = pm4py.discover_oc_petri_net(ocel)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8944e20",
   "metadata": {},
   "outputs": [],
   "source": [
    "pm4py.view_ocpn(ocpn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false
   },
   "id": "65838087908293c5"
  }
 ],
 "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.10.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
