xlsxTOjson.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "86f8df89-a483-4093-9098-41196f891c64",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b6873712-56e4-46a9-8bec-031093dd28b1",
   "metadata": {},
   "outputs": [
    {
     "ename": "ImportError",
     "evalue": "Missing optional dependency 'openpyxl'.  Use pip or conda to install openpyxl.",
     "output_type": "error",
     "traceback": [
      "\u001B[1;31m---------------------------------------------------------------------------\u001B[0m",
      "\u001B[1;31mImportError\u001B[0m                               Traceback (most recent call last)",
      "\u001B[1;32m~\\AppData\\Local\\Temp/ipykernel_3872/3644074374.py\u001B[0m in \u001B[0;36m<module>\u001B[1;34m\u001B[0m\n\u001B[1;32m----> 1\u001B[1;33m \u001B[0mtable\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mpd\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mread_excel\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;34m\"AuD-Inhalt2.xlsx\"\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m      2\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python38\\site-packages\\pandas\\util\\_decorators.py\u001B[0m in \u001B[0;36mwrapper\u001B[1;34m(*args, **kwargs)\u001B[0m\n\u001B[0;32m    309\u001B[0m                     \u001B[0mstacklevel\u001B[0m\u001B[1;33m=\u001B[0m\u001B[0mstacklevel\u001B[0m\u001B[1;33m,\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    310\u001B[0m                 )\n\u001B[1;32m--> 311\u001B[1;33m             \u001B[1;32mreturn\u001B[0m \u001B[0mfunc\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;33m*\u001B[0m\u001B[0margs\u001B[0m\u001B[1;33m,\u001B[0m \u001B[1;33m**\u001B[0m\u001B[0mkwargs\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m    312\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    313\u001B[0m         \u001B[1;32mreturn\u001B[0m \u001B[0mwrapper\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python38\\site-packages\\pandas\\io\\excel\\_base.py\u001B[0m in \u001B[0;36mread_excel\u001B[1;34m(io, sheet_name, header, names, index_col, usecols, squeeze, dtype, engine, converters, true_values, false_values, skiprows, nrows, na_values, keep_default_na, na_filter, verbose, parse_dates, date_parser, thousands, comment, skipfooter, convert_float, mangle_dupe_cols, storage_options)\u001B[0m\n\u001B[0;32m    362\u001B[0m     \u001B[1;32mif\u001B[0m \u001B[1;32mnot\u001B[0m \u001B[0misinstance\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mio\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mExcelFile\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    363\u001B[0m         \u001B[0mshould_close\u001B[0m \u001B[1;33m=\u001B[0m \u001B[1;32mTrue\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m--> 364\u001B[1;33m         \u001B[0mio\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mExcelFile\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mio\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mstorage_options\u001B[0m\u001B[1;33m=\u001B[0m\u001B[0mstorage_options\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mengine\u001B[0m\u001B[1;33m=\u001B[0m\u001B[0mengine\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m    365\u001B[0m     \u001B[1;32melif\u001B[0m \u001B[0mengine\u001B[0m \u001B[1;32mand\u001B[0m \u001B[0mengine\u001B[0m \u001B[1;33m!=\u001B[0m \u001B[0mio\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mengine\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    366\u001B[0m         raise ValueError(\n",
      "\u001B[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python38\\site-packages\\pandas\\io\\excel\\_base.py\u001B[0m in \u001B[0;36m__init__\u001B[1;34m(self, path_or_buffer, engine, storage_options)\u001B[0m\n\u001B[0;32m   1231\u001B[0m         \u001B[0mself\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0mstorage_options\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mstorage_options\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m   1232\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m-> 1233\u001B[1;33m         \u001B[0mself\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0m_reader\u001B[0m \u001B[1;33m=\u001B[0m \u001B[0mself\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0m_engines\u001B[0m\u001B[1;33m[\u001B[0m\u001B[0mengine\u001B[0m\u001B[1;33m]\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mself\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0m_io\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mstorage_options\u001B[0m\u001B[1;33m=\u001B[0m\u001B[0mstorage_options\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m   1234\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m   1235\u001B[0m     \u001B[1;32mdef\u001B[0m \u001B[0m__fspath__\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mself\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python38\\site-packages\\pandas\\io\\excel\\_openpyxl.py\u001B[0m in \u001B[0;36m__init__\u001B[1;34m(self, filepath_or_buffer, storage_options)\u001B[0m\n\u001B[0;32m    519\u001B[0m             \u001B[0mpassed\u001B[0m \u001B[0mto\u001B[0m \u001B[0mfsspec\u001B[0m \u001B[1;32mfor\u001B[0m \u001B[0mappropriate\u001B[0m \u001B[0mURLs\u001B[0m \u001B[1;33m(\u001B[0m\u001B[0msee\u001B[0m\u001B[0;31m \u001B[0m\u001B[0;31m`\u001B[0m\u001B[0;31m`\u001B[0m\u001B[0m_get_filepath_or_buffer\u001B[0m\u001B[0;31m`\u001B[0m\u001B[0;31m`\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    520\u001B[0m         \"\"\"\n\u001B[1;32m--> 521\u001B[1;33m         \u001B[0mimport_optional_dependency\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;34m\"openpyxl\"\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m    522\u001B[0m         \u001B[0msuper\u001B[0m\u001B[1;33m(\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m.\u001B[0m\u001B[0m__init__\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mfilepath_or_buffer\u001B[0m\u001B[1;33m,\u001B[0m \u001B[0mstorage_options\u001B[0m\u001B[1;33m=\u001B[0m\u001B[0mstorage_options\u001B[0m\u001B[1;33m)\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    523\u001B[0m \u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;32m~\\AppData\\Local\\Packages\\PythonSoftwareFoundation.Python.3.8_qbz5n2kfra8p0\\LocalCache\\local-packages\\Python38\\site-packages\\pandas\\compat\\_optional.py\u001B[0m in \u001B[0;36mimport_optional_dependency\u001B[1;34m(name, extra, errors, min_version)\u001B[0m\n\u001B[0;32m    116\u001B[0m     \u001B[1;32mexcept\u001B[0m \u001B[0mImportError\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    117\u001B[0m         \u001B[1;32mif\u001B[0m \u001B[0merrors\u001B[0m \u001B[1;33m==\u001B[0m \u001B[1;34m\"raise\"\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[1;32m--> 118\u001B[1;33m             \u001B[1;32mraise\u001B[0m \u001B[0mImportError\u001B[0m\u001B[1;33m(\u001B[0m\u001B[0mmsg\u001B[0m\u001B[1;33m)\u001B[0m \u001B[1;32mfrom\u001B[0m \u001B[1;32mNone\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0m\u001B[0;32m    119\u001B[0m         \u001B[1;32melse\u001B[0m\u001B[1;33m:\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n\u001B[0;32m    120\u001B[0m             \u001B[1;32mreturn\u001B[0m \u001B[1;32mNone\u001B[0m\u001B[1;33m\u001B[0m\u001B[1;33m\u001B[0m\u001B[0m\n",
      "\u001B[1;31mImportError\u001B[0m: Missing optional dependency 'openpyxl'.  Use pip or conda to install openpyxl."
     ]
    }
   ],
   "source": [
    "table = pd.read_excel(\"AuD-Inhalt2.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9481135-3734-45ed-9847-8f9426bd0f00",
   "metadata": {},
   "outputs": [],
   "source": [
    "nds = []\n",
    "lks = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a138d20d-4dc3-4eb8-8ebd-71d1fb3f0cbf",
   "metadata": {},
   "outputs": [],
   "source": [
    "for row in table.itertuples():\n",
    "    nds.append({'id':row[1], 'name':row[5], 'description':row[9], 'type':row[4]})\n",
    "    for link in str(row[7]).split(', '):\n",
    "        if link != \"nan\": \n",
    "            lks.append({'source':row[1], 'target':int(link)})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c06e658c-33d1-4ba8-8a47-ae857984d6ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "result = {'nodes':nds, 'links':lks}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "565d11cd-47b6-402e-a734-b4b8a5a66181",
   "metadata": {},
   "outputs": [],
   "source": [
    "result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "841e41aa-edcf-4b6a-b946-e4696b9e5ddc",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "name": "pycharm-5e4eca5f",
   "language": "python",
   "display_name": "PyCharm (Knowledge-Space-WP-Plugin)"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}