data_extractor.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys \n",
    "import os\n",
    "#import pandahelper as pdh\n",
    "\n",
    "\n",
    "import cymetric as cym\n",
    "import pandas as pd\n",
    "\n",
    "import numpy as np\n",
    "import sklearn as skl\n",
    "from sklearn import datasets\n",
    "import random\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "plt.rcParams['figure.figsize'] = (40,24)\n",
    "from cymetric import graphs as cgr\n",
    "from cymetric import timeseries as tm\n",
    "from cymetric import filters as fl\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "file = \"u_model.sqlite\"\n",
    "db = cym.dbopen(file)\n",
    "ev = cym.Evaluator(db=db, write=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/svg+xml": [
       "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?>\n",
       "<!DOCTYPE svg PUBLIC \"-//W3C//DTD SVG 1.1//EN\"\n",
       " \"http://www.w3.org/Graphics/SVG/1.1/DTD/svg11.dtd\">\n",
       "<!-- Generated by graphviz version 2.40.1 (20161225.0304)\n",
       " -->\n",
       "<!-- Title: G Pages: 1 -->\n",
       "<svg width=\"426pt\" height=\"324pt\"\n",
       " viewBox=\"0.00 0.00 425.96 324.00\" xmlns=\"http://www.w3.org/2000/svg\" xmlns:xlink=\"http://www.w3.org/1999/xlink\">\n",
       "<g id=\"graph0\" class=\"graph\" transform=\"scale(1 1) rotate(0) translate(4 320)\">\n",
       "<title>G</title>\n",
       "<polygon fill=\"#ffffff\" stroke=\"transparent\" points=\"-4,4 -4,-320 421.9553,-320 421.9553,4 -4,4\"/>\n",
       "<!-- SimpleHiderRegion -->\n",
       "<g id=\"node1\" class=\"node\">\n",
       "<title>SimpleHiderRegion</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"80.8459\" cy=\"-298\" rx=\"80.6921\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"80.8459\" y=\"-293.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">SimpleHiderRegion</text>\n",
       "</g>\n",
       "<!-- inst -->\n",
       "<g id=\"node2\" class=\"node\">\n",
       "<title>inst</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"206.8459\" cy=\"-298\" rx=\"27\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"206.8459\" y=\"-293.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">inst</text>\n",
       "</g>\n",
       "<!-- UraniumMine -->\n",
       "<g id=\"node3\" class=\"node\">\n",
       "<title>UraniumMine</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"311.8459\" cy=\"-298\" rx=\"60.4202\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"311.8459\" y=\"-293.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">UraniumMine</text>\n",
       "</g>\n",
       "<!-- LEUEnrichmentFacility -->\n",
       "<g id=\"node4\" class=\"node\">\n",
       "<title>LEUEnrichmentFacility</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"238.8459\" cy=\"-212\" rx=\"95.6413\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"238.8459\" y=\"-207.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">LEUEnrichmentFacility</text>\n",
       "</g>\n",
       "<!-- UraniumMine&#45;&gt;LEUEnrichmentFacility -->\n",
       "<g id=\"edge4\" class=\"edge\">\n",
       "<title>UraniumMine&#45;&gt;LEUEnrichmentFacility</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M296.7203,-280.1807C286.2471,-267.8425 272.1489,-251.2337 260.4895,-237.4979\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"263.0919,-235.1552 253.9522,-229.7964 257.7552,-239.6852 263.0919,-235.1552\"/>\n",
       "<text text-anchor=\"middle\" x=\"307.9006\" y=\"-250.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">7.20e+07 </text>\n",
       "</g>\n",
       "<!-- HEUenrich -->\n",
       "<g id=\"node7\" class=\"node\">\n",
       "<title>HEUenrich</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"362.8459\" cy=\"-158\" rx=\"50.2551\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"362.8459\" y=\"-153.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">HEUenrich</text>\n",
       "</g>\n",
       "<!-- UraniumMine&#45;&gt;HEUenrich -->\n",
       "<g id=\"edge3\" class=\"edge\">\n",
       "<title>UraniumMine&#45;&gt;HEUenrich</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M328.0737,-280.3551C332.4543,-274.8134 336.8148,-268.4493 339.8459,-262 351.2174,-237.8046 357.0804,-207.7683 360.03,-186.2052\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"363.5294,-186.4255 361.288,-176.0705 356.5827,-185.5631 363.5294,-186.4255\"/>\n",
       "<text text-anchor=\"middle\" x=\"373.9006\" y=\"-250.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">1.04e+06 </text>\n",
       "</g>\n",
       "<!-- reactor -->\n",
       "<g id=\"node5\" class=\"node\">\n",
       "<title>reactor</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"125.8459\" cy=\"-104\" rx=\"34.8024\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"125.8459\" y=\"-99.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">reactor</text>\n",
       "</g>\n",
       "<!-- LEUEnrichmentFacility&#45;&gt;reactor -->\n",
       "<g id=\"edge7\" class=\"edge\">\n",
       "<title>LEUEnrichmentFacility&#45;&gt;reactor</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M208.7103,-194.7464C200.2314,-189.2609 191.274,-182.829 183.7365,-176 168.5777,-162.2659 154.1553,-144.3472 143.4977,-129.8216\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"146.047,-127.3707 137.3753,-121.2836 140.3584,-131.4498 146.047,-127.3707\"/>\n",
       "<text text-anchor=\"middle\" x=\"211.9006\" y=\"-153.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">3.30e+05 </text>\n",
       "</g>\n",
       "<!-- LEUtoHEUenrich -->\n",
       "<g id=\"node8\" class=\"node\">\n",
       "<title>LEUtoHEUenrich</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"252.8459\" cy=\"-104\" rx=\"74.393\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"252.8459\" y=\"-99.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">LEUtoHEUenrich</text>\n",
       "</g>\n",
       "<!-- LEUEnrichmentFacility&#45;&gt;LEUtoHEUenrich -->\n",
       "<g id=\"edge5\" class=\"edge\">\n",
       "<title>LEUEnrichmentFacility&#45;&gt;LEUtoHEUenrich</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M240.9842,-193.9388C242.7312,-179.3697 245.3011,-158.3537 247.7365,-140 248.0708,-137.4812 248.4262,-134.8646 248.7879,-132.2431\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"252.2587,-132.6947 250.1824,-122.3053 245.3267,-131.7219 252.2587,-132.6947\"/>\n",
       "<text text-anchor=\"middle\" x=\"275.9006\" y=\"-153.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">7.67e+06 </text>\n",
       "</g>\n",
       "<!-- SpentFuelSink -->\n",
       "<g id=\"node6\" class=\"node\">\n",
       "<title>SpentFuelSink</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"125.8459\" cy=\"-18\" rx=\"62.384\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"125.8459\" y=\"-13.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">SpentFuelSink</text>\n",
       "</g>\n",
       "<!-- reactor&#45;&gt;SpentFuelSink -->\n",
       "<g id=\"edge6\" class=\"edge\">\n",
       "<title>reactor&#45;&gt;SpentFuelSink</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M125.8459,-85.7616C125.8459,-74.3597 125.8459,-59.4342 125.8459,-46.494\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"129.346,-46.2121 125.8459,-36.2121 122.346,-46.2121 129.346,-46.2121\"/>\n",
       "<text text-anchor=\"middle\" x=\"153.9006\" y=\"-56.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">2.31e+05 </text>\n",
       "</g>\n",
       "<!-- HEUSink -->\n",
       "<g id=\"node9\" class=\"node\">\n",
       "<title>HEUSink</title>\n",
       "<ellipse fill=\"none\" stroke=\"#000000\" cx=\"326.8459\" cy=\"-18\" rx=\"44.501\" ry=\"18\"/>\n",
       "<text text-anchor=\"middle\" x=\"326.8459\" y=\"-13.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">HEUSink</text>\n",
       "</g>\n",
       "<!-- HEUenrich&#45;&gt;HEUSink -->\n",
       "<g id=\"edge1\" class=\"edge\">\n",
       "<title>HEUenrich&#45;&gt;HEUSink</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M362.6507,-139.8061C361.9159,-118.6185 359.087,-82.8428 348.8459,-54 347.7185,-50.8249 346.286,-47.6284 344.6943,-44.5209\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"347.5851,-42.5248 339.5881,-35.5751 341.5057,-45.9949 347.5851,-42.5248\"/>\n",
       "<text text-anchor=\"middle\" x=\"389.9006\" y=\"-99.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">1.63e+02 </text>\n",
       "</g>\n",
       "<!-- LEUtoHEUenrich&#45;&gt;HEUSink -->\n",
       "<g id=\"edge2\" class=\"edge\">\n",
       "<title>LEUtoHEUenrich&#45;&gt;HEUSink</title>\n",
       "<path fill=\"none\" stroke=\"#000000\" d=\"M266.5176,-86.115C274.0577,-76.4448 283.6992,-64.3892 292.7365,-54 296.0246,-50.2201 299.5801,-46.2987 303.1116,-42.4953\"/>\n",
       "<polygon fill=\"#000000\" stroke=\"#000000\" points=\"305.9228,-44.6155 310.2336,-34.9373 300.8283,-39.8149 305.9228,-44.6155\"/>\n",
       "<text text-anchor=\"middle\" x=\"320.9006\" y=\"-56.8\" font-family=\"Times,serif\" font-size=\"14.00\" fill=\"#000000\">3.12e+05 </text>\n",
       "</g>\n",
       "</g>\n",
       "</svg>\n"
      ],
      "text/plain": [
       "<graphviz.dot.Digraph at 0x1203e4c88>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cym.graphs.flow_graph(ev, label = \"mass\")"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "trim throughput starting by constraining one facility and moving on \n",
    "\n",
    "relative quantites more important than absolute quantities \n",
    "\n",
    "ask Baptiste about cymetric issues because there is usually a better way to do somthing "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   AgentId              Prototype\n",
      "0       15      SimpleHiderRegion\n",
      "1       16                   inst\n",
      "2       17            UraniumMine\n",
      "3       18  LEUEnrichmentFacility\n",
      "4       19                reactor\n",
      "5       20          SpentFuelSink\n",
      "6       21              HEUenrich\n",
      "7       22         LEUtoHEUenrich\n",
      "8       23                HEUSink\n",
      "     SenderId  ReceiverId  ResourceId Commodity  Time\n",
      "0          17          18          14    c_uore     0\n",
      "1          17          21          16    c_uore     0\n",
      "2          18          19          35     c_leu     1\n",
      "3          18          19          41     c_leu     1\n",
      "4          18          19          47     c_leu     1\n",
      "..        ...         ...         ...       ...   ...\n",
      "513        21          23        3363     c_heu   142\n",
      "514        22          23        3366     c_heu   142\n",
      "515        17          21        3378    c_uore   143\n",
      "516        18          22        3381     c_leu   143\n",
      "517        21          23        3387     c_heu   143\n",
      "\n",
      "[518 rows x 5 columns]\n",
      "[18 21 19 22 23 20]\n",
      "      ResourceId        Quantity\n",
      "0             14  1000000.000000\n",
      "1             16  1000000.000000\n",
      "2             31   702919.708029\n",
      "3             32   297080.291971\n",
      "4             34   264080.291971\n",
      "...          ...             ...\n",
      "1256        3381   111081.081081\n",
      "1257        3383   999502.278517\n",
      "1258        3384      248.860741\n",
      "1259        3386      247.720476\n",
      "1260        3387        1.140265\n",
      "\n",
      "[1261 rows x 2 columns]\n"
     ]
    }
   ],
   "source": [
    "agentTable = ev.eval(\"AgentEntry\")\n",
    "#print(type(agentTable))\n",
    "#print(agentTable)\n",
    "agents = agentTable.loc[:, [\"AgentId\", \"Prototype\"]]\n",
    "print(agents)\n",
    "\n",
    "transTable = ev.eval(\"Transactions\")\n",
    "transactions = transTable.loc[:, [\"SenderId\", \"ReceiverId\", \"ResourceId\", \"Commodity\", \"Time\"]]\n",
    "print(transactions)\n",
    "print (transactions['ReceiverId'].unique())\n",
    "    \n",
    "resourceTable = ev.eval(\"Resources\")\n",
    "resources = resourceTable.loc[:, [\"ResourceId\", \"Quantity\"]]\n",
    "print(resources)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def tidy_df(file):\n",
    "    db = cym.dbopen(file)\n",
    "    ev = cym.Evaluator(db=db, write=True)\n",
    "    \n",
    "    agentTable = ev.eval(\"AgentEntry\")\n",
    "    #print(type(agentTable))\n",
    "    #print(agentTable)\n",
    "    agents = agentTable.loc[:, [\"AgentId\", \"Prototype\"]]\n",
    "    #print(agents)\n",
    "    \n",
    "    transTable = ev.eval(\"Transactions\")\n",
    "    transactions = transTable.loc[:, [\"SenderId\", \"ReceiverId\", \"ResourceId\", \"Commodity\", \"Time\"]]\n",
    "    #print(transactions)\n",
    "    \n",
    "    resourceTable = ev.eval(\"Resources\")\n",
    "    resources = resourceTable.loc[:, [\"ResourceId\", \"Quantity\"]]\n",
    "    #resources\n",
    "    \n",
    "    #merge agents, transactions, and resources\n",
    "    int1 = pd.merge(transactions, resources, on='ResourceId', how='inner')\n",
    "    #int1\n",
    "    #rename AgentId column to facilitate merge \n",
    "    send = agents.rename(columns = {\"AgentId\": \"SenderId\"})\n",
    "    receive = agents.rename(columns = {\"AgentId\": \"ReceiverId\"})\n",
    "    #int2 = pd.merge(int1, send, on = \"SenderId\", how = \"inner\")\n",
    "    #int3 = pd.merge(int2, receive, on = \"ReceiverId\", how = \"inner\")\n",
    "\n",
    "    for i in range(len(int1)):\n",
    "        for j in range(len(send)):\n",
    "            if int1.loc[i,\"SenderId\"] == send.loc[j, \"SenderId\"]:\n",
    "                int1.loc[i,\"SenderId\"] = send.loc[j, \"Prototype\"]\n",
    "    int1\n",
    "    for i in range(len(int1)):\n",
    "        for j in range(len(receive)):\n",
    "            if int1.loc[i,\"ReceiverId\"] == receive.loc[j, \"ReceiverId\"]:\n",
    "                int1.loc[i,\"ReceiverId\"] = receive.loc[j, \"Prototype\"]\n",
    "\n",
    "    \n",
    "    return int1\n",
    "    #tidy = spread(int1)\n",
    "    #return tidy\n",
    "\n",
    "def spread(df):\n",
    "    #conceal commodity transactions\n",
    "    material = [] \n",
    "    print(df[\"Commodity\"].size)\n",
    "    for row in df[\"Commodity\"]:\n",
    "        #print (row)\n",
    "        if row == \"c_uore\":\n",
    "            material.append(\"ore\")\n",
    "        if row == \"c_leu\" or row == \"c_heu\":\n",
    "            material.append(\"fuel\")\n",
    "        if row == \"c_spent_leu\":\n",
    "            material.append(\"spent\")\n",
    "    print(len(material))\n",
    "    df[\"material\"] = material\n",
    "    \n",
    "    int2 = df[[\"Time\", \"Quantity\", \"material\"]]\n",
    "    \n",
    "    return int2\n",
    "    #merge back indexed by time\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           SenderId       ReceiverId  ResourceId Commodity  Time  \\\n",
      "0       UraniumMine  EnrichmentPlant           8      uore     0   \n",
      "1   EnrichmentPlant              LWR          22  freshuox     1   \n",
      "2   EnrichmentPlant              LWR          28  freshuox     1   \n",
      "3   EnrichmentPlant              LWR          34  freshuox     1   \n",
      "4       UraniumMine  EnrichmentPlant          39      uore     2   \n",
      "5               LWR    SpentFuelSink          72  spentuox    19   \n",
      "6   EnrichmentPlant              LWR          83  freshuox    19   \n",
      "7       UraniumMine  EnrichmentPlant          88      uore    20   \n",
      "8               LWR    SpentFuelSink         123  spentuox    38   \n",
      "9   EnrichmentPlant              LWR         134  freshuox    38   \n",
      "10      UraniumMine  EnrichmentPlant         139      uore    39   \n",
      "11              LWR    SpentFuelSink         174  spentuox    57   \n",
      "12  EnrichmentPlant              LWR         185  freshuox    57   \n",
      "13      UraniumMine  EnrichmentPlant         190      uore    58   \n",
      "14              LWR    SpentFuelSink         225  spentuox    76   \n",
      "15  EnrichmentPlant              LWR         236  freshuox    76   \n",
      "16      UraniumMine  EnrichmentPlant         241      uore    77   \n",
      "17              LWR    SpentFuelSink         276  spentuox    95   \n",
      "18  EnrichmentPlant              LWR         287  freshuox    95   \n",
      "19      UraniumMine  EnrichmentPlant         292      uore    96   \n",
      "20              LWR    SpentFuelSink         327  spentuox   114   \n",
      "21  EnrichmentPlant              LWR         338  freshuox   114   \n",
      "22      UraniumMine  EnrichmentPlant         343      uore   115   \n",
      "23              LWR    SpentFuelSink         378  spentuox   133   \n",
      "24  EnrichmentPlant              LWR         389  freshuox   133   \n",
      "25      UraniumMine  EnrichmentPlant         394      uore   134   \n",
      "\n",
      "          Quantity  \n",
      "0   1000000.000000  \n",
      "1     33000.000000  \n",
      "2     33000.000000  \n",
      "3     33000.000000  \n",
      "4    891240.875912  \n",
      "5     33000.000000  \n",
      "6     33000.000000  \n",
      "7    297080.291971  \n",
      "8     33000.000000  \n",
      "9     33000.000000  \n",
      "10   297080.291971  \n",
      "11    33000.000000  \n",
      "12    33000.000000  \n",
      "13   297080.291971  \n",
      "14    33000.000000  \n",
      "15    33000.000000  \n",
      "16   297080.291971  \n",
      "17    33000.000000  \n",
      "18    33000.000000  \n",
      "19   297080.291971  \n",
      "20    33000.000000  \n",
      "21    33000.000000  \n",
      "22   297080.291971  \n",
      "23    33000.000000  \n",
      "24    33000.000000  \n",
      "25   297080.291971  \n"
     ]
    }
   ],
   "source": [
    "leu_data = tidy_df(\"leuonly.sqlite\")\n",
    "print(leu_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                  SenderId             ReceiverId  ResourceId Commodity  Time  \\\n",
      "0              UraniumMine  LEUEnrichmentFacility          14    c_uore     0   \n",
      "1              UraniumMine              HEUenrich          16    c_uore     0   \n",
      "2    LEUEnrichmentFacility                reactor          35     c_leu     1   \n",
      "3    LEUEnrichmentFacility                reactor          41     c_leu     1   \n",
      "4    LEUEnrichmentFacility                reactor          47     c_leu     1   \n",
      "..                     ...                    ...         ...       ...   ...   \n",
      "513              HEUenrich                HEUSink        3363     c_heu   142   \n",
      "514         LEUtoHEUenrich                HEUSink        3366     c_heu   142   \n",
      "515            UraniumMine              HEUenrich        3378    c_uore   143   \n",
      "516  LEUEnrichmentFacility         LEUtoHEUenrich        3381     c_leu   143   \n",
      "517              HEUenrich                HEUSink        3387     c_heu   143   \n",
      "\n",
      "           Quantity  \n",
      "0    1000000.000000  \n",
      "1    1000000.000000  \n",
      "2      33000.000000  \n",
      "3      33000.000000  \n",
      "4      33000.000000  \n",
      "..              ...  \n",
      "513        1.140265  \n",
      "514     4581.939799  \n",
      "515      248.860741  \n",
      "516   111081.081081  \n",
      "517        1.140265  \n",
      "\n",
      "[518 rows x 6 columns]\n"
     ]
    }
   ],
   "source": [
    "tidy_data = tidy_df(\"u_model.sqlite\")\n",
    "print(tidy_data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sender</th>\n",
       "      <th>receiver</th>\n",
       "      <th>timestep</th>\n",
       "      <th>kg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>0</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>0</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>reactor</td>\n",
       "      <td>1</td>\n",
       "      <td>33000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>reactor</td>\n",
       "      <td>1</td>\n",
       "      <td>33000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>reactor</td>\n",
       "      <td>1</td>\n",
       "      <td>33000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>1</td>\n",
       "      <td>12081.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>1</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>2</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>2</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>2</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>2</td>\n",
       "      <td>498.327759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>3</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>3</td>\n",
       "      <td>111081.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>3</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>4</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>4</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>4</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>4</td>\n",
       "      <td>4581.939799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>5</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>5</td>\n",
       "      <td>111081.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>5</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>6</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>6</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>6</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>6</td>\n",
       "      <td>4581.939799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>7</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>7</td>\n",
       "      <td>111081.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>7</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>8</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>8</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>135</td>\n",
       "      <td>111081.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>489</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>135</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>490</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>136</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>136</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>492</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>136</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>493</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>136</td>\n",
       "      <td>4581.939799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>494</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>137</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>495</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>137</td>\n",
       "      <td>111081.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>496</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>137</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>138</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>138</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>138</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>500</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>138</td>\n",
       "      <td>4581.939799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>501</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>139</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>502</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>139</td>\n",
       "      <td>111081.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>503</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>139</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>504</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>140</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>505</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>140</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>506</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>140</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>507</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>140</td>\n",
       "      <td>4581.939799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>508</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>141</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>509</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>141</td>\n",
       "      <td>111081.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>510</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>141</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>511</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>142</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>512</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>142</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>513</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>142</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>514</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>142</td>\n",
       "      <td>4581.939799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>515</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>143</td>\n",
       "      <td>248.860741</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>516</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>143</td>\n",
       "      <td>111081.081081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>517</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>143</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>518 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         sender    receiver  timestep              kg\n",
       "0          mine  enrichment         0  1000000.000000\n",
       "1          mine  enrichment         0  1000000.000000\n",
       "2    enrichment     reactor         1    33000.000000\n",
       "3    enrichment     reactor         1    33000.000000\n",
       "4    enrichment     reactor         1    33000.000000\n",
       "..          ...         ...       ...             ...\n",
       "513  enrichment     HEUSink       142        1.140265\n",
       "514  enrichment     HEUSink       142     4581.939799\n",
       "515        mine  enrichment       143      248.860741\n",
       "516  enrichment  enrichment       143   111081.081081\n",
       "517  enrichment     HEUSink       143        1.140265\n",
       "\n",
       "[518 rows x 4 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#CHECK FOR MISSING FACILITIES HERE!!!!!!!!!!!\n",
    "\n",
    "#rename facilities and mask material \n",
    "#1) assume all of the enrichment happens in one building \n",
    "senders = []\n",
    "for row in tidy_data[\"SenderId\"]: \n",
    "    if \"nrich\" in row: \n",
    "        senders.append(\"enrichment\")       \n",
    "    elif \"Mine\" in row: \n",
    "        senders.append(\"mine\")\n",
    "    else:\n",
    "        senders.append(\"reactor\")\n",
    "receivers = []\n",
    "for row in tidy_data[\"ReceiverId\"]:\n",
    "    if \"nrich\" in row: \n",
    "        receivers.append(\"enrichment\")\n",
    "    else: \n",
    "        receivers.append(row)\n",
    "senders = pd.Series([s for s in senders])\n",
    "receivers = pd.Series([r for r in receivers])\n",
    "#2) remove commodity labels\n",
    "set1 = pd.concat([senders, receivers, tidy_data[\"Time\"], tidy_data[\"Quantity\"]], axis = 1)\n",
    "set1.columns = [\"sender\", \"receiver\", \"timestep\", \"kg\"]\n",
    "set1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sender</th>\n",
       "      <th>receiver</th>\n",
       "      <th>timestep</th>\n",
       "      <th>kg</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>0</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>0</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>reactor</td>\n",
       "      <td>1</td>\n",
       "      <td>33000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>reactor</td>\n",
       "      <td>1</td>\n",
       "      <td>33000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>reactor</td>\n",
       "      <td>1</td>\n",
       "      <td>33000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>enrichment</td>\n",
       "      <td>HEUSink</td>\n",
       "      <td>1</td>\n",
       "      <td>1.140265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>mine</td>\n",
       "      <td>enrichment</td>\n",
       "      <td>2</td>\n",
       "      <td>1000000.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       sender    receiver  timestep              kg\n",
       "0        mine  enrichment         0  1000000.000000\n",
       "1        mine  enrichment         0  1000000.000000\n",
       "2  enrichment     reactor         1    33000.000000\n",
       "3  enrichment     reactor         1    33000.000000\n",
       "4  enrichment     reactor         1    33000.000000\n",
       "6  enrichment     HEUSink         1        1.140265\n",
       "7        mine  enrichment         2  1000000.000000"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#remove rows going from enrichment to enrichment \n",
    "enrichrows = []\n",
    "for i in range(len(set1)):\n",
    "    if set1.iloc[i, 0] == set1.iloc[i, 1]:\n",
    "        enrichrows.append(i)\n",
    "\n",
    "set2 = set1.drop(enrichrows, axis = 0)\n",
    "set2.head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n"
     ]
    }
   ],
   "source": [
    "#condense information by timestep\n",
    "transactions = {}\n",
    "for i in range(144):\n",
    "    transactions[i] = []\n",
    "\n",
    "for i in range(len(set2)):\n",
    "    transactions[set2.iloc[i,2]].append({\"sender\": set2.iloc[i,0], \"receiver\": set2.iloc[i,1], \"kg\": set2.iloc[i,3]})\n",
    "transactions\n",
    "print(\"SpentFuelSink\" in transactions)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "#consolidate transactions between same facilities in same timestep (from merging enrichments)\n",
    "\n",
    "#remove transactions from enrichment to enrichment \n",
    "def check_duplicate(timesteplist):\n",
    "    trans_pairs = set()\n",
    "    for transaction in timesteplist: \n",
    "        pair = transaction[\"sender\"], transaction[\"receiver\"], \n",
    "        \n",
    "    #make set of transaction pairs\n",
    "    #if pair is already in set, sum the quantity sent between the duplicate pair \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#weight limit for shipping containers is 20000kg "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}