Copy_of_Data_Transformation - Copy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e0e2ddb5",
   "metadata": {},
   "source": [
    "[Section 1](#section_1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c98e8ef",
   "metadata": {
    "id": "1c98e8ef"
   },
   "source": [
    "## Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "db4381b1",
   "metadata": {
    "id": "db4381b1"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import numpy as np\n",
    "from pyvis.network import Network\n",
    "from neo4j import GraphDatabase\n",
    "import re\n",
    "from functools import reduce\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcd66061",
   "metadata": {
    "id": "bcd66061"
   },
   "source": [
    "## Functions "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e5fc642",
   "metadata": {
    "id": "2e5fc642"
   },
   "source": [
    "### Find Each Table's primary key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "a33b18f5",
   "metadata": {
    "id": "a33b18f5"
   },
   "outputs": [],
   "source": [
    "def pk_finder(name, df):\n",
    "    for i in range(len(df.columns)):\n",
    "        col = df.iloc[:,i]\n",
    "        #if(col.size == col.drop_duplicates().size):\n",
    "        if(col.nunique() == col.size):\n",
    "            pk[name] = df.columns[i] \n",
    "            break\n",
    "        "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5649ff29",
   "metadata": {
    "id": "5649ff29"
   },
   "source": [
    "### Find Each Table's foreign key\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c36338f1",
   "metadata": {
    "id": "c36338f1"
   },
   "outputs": [],
   "source": [
    "def fk_finder(name, df):\n",
    "    for i in range(len(df.columns)):\n",
    "        col_name = df.columns[i]\n",
    "        col = df[col_name]\n",
    "        for j in pk: #loops on the key j = table names\n",
    "            if j != name:\n",
    "                primary_key_column = All_dfs[j][(pk[j])]\n",
    "                \n",
    "                if primary_key_column.dtype == col.dtype:\n",
    "                    status = col.isin(primary_key_column)\n",
    "                    if status[status== False].size == 0:\n",
    "                        fk[name][col_name] = j \n",
    "                        ref_in[j].add(name)\n",
    "                        break\n",
    "\n",
    "                elif (isinstance(col[0],list) and type(primary_key_column[0]) == type(col[0][0])):\n",
    "                    status = col.explode().reset_index(drop=True).isin(primary_key_column)\n",
    "                    if status[status== False].size == 0:\n",
    "                        fk[name][col_name] = j \n",
    "                        ref_in[j].add(name)\n",
    "                        break"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c8ba2d83",
   "metadata": {},
   "outputs": [],
   "source": [
    "# type(All_dfs[\"warehouses\"].warehouse_id[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "094b5a4a",
   "metadata": {
    "id": "094b5a4a"
   },
   "source": [
    "### Assign Tables to nodes or edges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "36bc7af4",
   "metadata": {
    "id": "36bc7af4"
   },
   "outputs": [],
   "source": [
    "def nodes_edges(fk):\n",
    "    for f in fk:\n",
    "        if(f not in ['ssintorders','manufacturing']):\n",
    "            if len(fk[f]) == 2:\n",
    "                inner_dict = list(fk[f])\n",
    "                edges.add(f)\n",
    "            elif len(fk[f]) == 1:\n",
    "                properties.add(f)\n",
    "            elif len(fk[f]) == 0:\n",
    "                nodes.add(f)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83acb674",
   "metadata": {
    "id": "83acb674"
   },
   "source": [
    "### Create Nodes Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "62f17479",
   "metadata": {
    "id": "62f17479"
   },
   "outputs": [],
   "source": [
    "def add_nodes(nodes, nodesTable):\n",
    "    for node in nodes:\n",
    "#     for n in range(len(nodes)):\n",
    "        column_names = list(All_dfs[node].columns) #get column names\n",
    "        for index,row in All_dfs[node].iterrows():\n",
    "#         for index, row in nodes[list(nodes.keys())[n]].iterrows():\n",
    "            \n",
    "            att = {}\n",
    "            for i in range(1, len(column_names)):\n",
    "                att[column_names[i]] = All_dfs[node].iloc[index, i]\n",
    "#                 att[column_names[i]] = nodes[list(nodes.keys())[n]].iloc[index, i]\n",
    "                \n",
    "            newRow = [{'Label': node , 'ID': All_dfs[node].iloc[index,0] , 'Attributes': att}]\n",
    "            tmp = pd.DataFrame(newRow)\n",
    "            nodesTable = pd.concat([nodesTable, tmp], ignore_index=True)\n",
    "    return nodesTable\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fab162b7",
   "metadata": {
    "id": "fab162b7"
   },
   "source": [
    "### Create Edges Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7193efa5",
   "metadata": {
    "id": "7193efa5"
   },
   "outputs": [],
   "source": [
    "def add_edges(edges,edgesTable,pk,fk,ref_in):\n",
    "    \n",
    "    for e in edges:\n",
    "        df = All_dfs[e] #df of the cur edge\n",
    "        for r in range(len(df)):\n",
    "            label = list(fk[e].keys())\n",
    "            from_id = nodesTable[(nodesTable[\"Label\"] == fk[e][label[0]]) & (df.loc[r,label[0]] == nodesTable[\"ID\"])]\n",
    "            from_id = from_id.index[0]\n",
    "            to_id = nodesTable[(nodesTable[\"Label\"] == fk[e][label[1]]) & (df.loc[r,label[1]] == nodesTable[\"ID\"])]\n",
    "            to_id = to_id.index[0]\n",
    "            pk_col = pk[e]\n",
    "#             primary_key = df[pk_col].iloc[r]\n",
    "            primary_key = df.loc[r,pk_col]\n",
    "            att = convert_prop(ref_in,e,primary_key,fk)\n",
    "            newRow = [{'From_Node_ID': from_id, 'To_Node_ID': to_id, 'order/service' : att}]\n",
    "            tmp = pd.DataFrame(newRow)\n",
    "            edgesTable = pd.concat([edgesTable, tmp], ignore_index=True)\n",
    "    return edgesTable"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b212538d",
   "metadata": {
    "id": "b212538d"
   },
   "source": [
    "### Properties of the Edge"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4829dbe7",
   "metadata": {
    "id": "4829dbe7"
   },
   "outputs": [],
   "source": [
    "def convert_prop(ref_in,edge_key, pk_value,fk):\n",
    "    for referenced_table_name in ref_in[edge_key]:\n",
    "        referenced_table = All_dfs[referenced_table_name]\n",
    "        fk_of_ref_table = fk[referenced_table_name]\n",
    "        for foreign_key in fk_of_ref_table:\n",
    "            if fk_of_ref_table[foreign_key] == edge_key:\n",
    "#                 referenced_table[referenced_table[foreign_key]== id]\n",
    "                all_occurances_df = referenced_table[referenced_table[foreign_key]== pk_value].drop([foreign_key],axis=1)\n",
    "                return all_occurances_df.to_dict('records')\n",
    "                "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67c2907a",
   "metadata": {},
   "source": [
    "### generate list of n colors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "47cb3129",
   "metadata": {},
   "outputs": [],
   "source": [
    "import random\n",
    "get_colors = lambda n: list(map(lambda i: \"#\" + \"%06x\" % random.randint(0, 0xFFFFFF),range(n)))\n",
    "# get_colors(5) # sample return:  ['#8af5da', '#fbc08c', '#b741d0', '#e599f1', '#bbcb59', '#a2a6c0']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56580eb8",
   "metadata": {},
   "source": [
    "### Draw Pyvis graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8d8a13c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def draw_graph_pyvis(nodes_table,edges_table):\n",
    "\n",
    "    nodes_name = [(x[1]+'_'+str(x[0])).capitalize() for x in zip(nodes_table.ID,nodes_table.Label)]\n",
    "    indices = list(map(lambda x: int(x),list(nodes_table.index)))\n",
    "    nodes_attributes = list(map(lambda x: str(x).replace(',',\"\\n\"),list(nodes_table.Attributes)))\n",
    "    nodes_tables = list(nodes_df.Label.unique())\n",
    "    colors = get_colors(len(nodes_name))\n",
    "    nodes_color = list(map(lambda x: colors[nodes_tables.index(x)],list(nodes_table.Label)))\n",
    "    \n",
    "    g = Network(width='100%')\n",
    "#     g.barnes_hut()\n",
    "    g.add_nodes(indices,\n",
    "                title= nodes_attributes,\n",
    "                label=nodes_name,\n",
    "                color=nodes_color,\n",
    "                )\n",
    "    for e in range(len(edges_table)):\n",
    "        src = int(edges_table.iloc[e][\"From\"])\n",
    "        dst = int(edges_table.iloc[e][\"To\"])\n",
    "        g.add_edge(src,dst,weight=5)\n",
    "\n",
    "    print(\"Number of Nodes: \",len(g.get_nodes()))\n",
    "    print(\"Number of Edges: \",len(g.get_edges()))\n",
    "    \n",
    "\n",
    "    g.show('test.html')\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2ea01894",
   "metadata": {},
   "source": [
    "### Draw Graph Neo4j\n",
    "\n",
    "<a id='Neo4jFunction'></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c578abb",
   "metadata": {},
   "source": [
    "[Noe4jCallFunction](#Neo4jCall)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "439e2fff",
   "metadata": {},
   "source": [
    "#### prepare satements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "3bb78c33",
   "metadata": {},
   "outputs": [],
   "source": [
    "def draw_graph_Neo4j(nodes_table,edges_table):\n",
    "\n",
    "    transaction_execution_commands = []\n",
    "    transaction_execution_commands.append(\"match (n) detach delete n\")\n",
    "    # creating a statement for each node (row in node_table)\n",
    "    for i, node in nodes_table.iterrows():\n",
    "        label = node[\"Label\"].capitalize()\n",
    "        index = i\n",
    "        IDs = node[\"ID\"]\n",
    "        attributes = node[\"Attributes\"]\n",
    "        neo4j_create_statement = Create_Statement(label,i,IDs,attributes)\n",
    "        transaction_execution_commands.append(neo4j_create_statement)\n",
    "\n",
    "    for i, edge in edges_table.iterrows():\n",
    "\n",
    "        neo4j_create_statement = create_relation_statement(edge)\n",
    "        transaction_execution_commands.append(neo4j_create_statement)\n",
    "\n",
    "     \n",
    "    execute_transactions(transaction_execution_commands)\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c079d3da",
   "metadata": {},
   "source": [
    "#### Connect and execute statements"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a9097aa2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def execute_transactions(transaction_execution_commands):\n",
    "    data_base_connection = GraphDatabase.driver(uri = \"bolt://localhost:7687\", auth=(\"neo4j\", \"password\"))\n",
    "    session = data_base_connection.session()    \n",
    "    for i in transaction_execution_commands:\n",
    "        session.run(i)\n",
    "\n",
    " "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c4e8f87",
   "metadata": {},
   "source": [
    "#### Create a \"Create statement for nodes\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7b5dbf69",
   "metadata": {},
   "outputs": [],
   "source": [
    "def Create_Statement(label,index,IDs,attributes):\n",
    "#     att = destructure_dict(attributes)\n",
    "#     return f\"CREATE (x:{label} {'{'}name: {get_name(label,IDs)},index:{index},ID:{IDs} ,{destructure_dict(attributes)} {'}'})\"\n",
    "    att = (\", \" + destructure_dict(attributes)) if len(attributes) > 0 else \"\"\n",
    "    return f\"CREATE (x:{label} {'{'}name: {get_name(label,IDs)},index:{index},ID:{IDs} {att} {'}'})\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7e7de542",
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_name(label,ID):\n",
    "    return '\\\"' + label.capitalize()+' '+str(ID)+'\\\"'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c17d7fa2",
   "metadata": {},
   "source": [
    "#### prepare attributes for create statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c128f9c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def destructure_dict(dic):\n",
    "    s = \"\"\n",
    "    for key in dic:\n",
    "        value = dic[key]\n",
    "        if isinstance (value,str):\n",
    "            value = \"\\\"\" + value +\"\\\"\"\n",
    "        s=s + re.sub(\"[^\\w_]\", '',str(key).replace(\" \",\"_\")) + \":\" + str(value)+\",\"\n",
    "    return s[:-1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "78bdb262",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_relation_statement(edge):\n",
    "        from_id = edge['From']\n",
    "        to_id = edge['To']\n",
    "        from_name = edge['From_Table']\n",
    "        to_name = edge['To_Table']\n",
    "        rel_name = edge['Edge_Name']\n",
    "        w = edge['Weight']\n",
    "        return f\"Match (a:{from_name}),(b:{to_name}) WHERE a.index ={from_id} AND b.index = {to_id} CREATE (a) - [r:{rel_name} {'{ weight: '+ str(w) +' }'}]->(b)\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "5d1a5750",
   "metadata": {},
   "outputs": [],
   "source": [
    "def and_agg(series):\n",
    "       return reduce(lambda x, y: x and y, series)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e2c1e35",
   "metadata": {
    "id": "0e2c1e35"
   },
   "source": [
    "## Global Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9fe74450",
   "metadata": {
    "id": "9fe74450"
   },
   "outputs": [],
   "source": [
    "# {Table name: df}\n",
    "All_dfs = {}\n",
    "# {Table name: Table's Primary key}\n",
    "pk = {}\n",
    "# {Table name: {Column Name: Referenced Table}}\n",
    "fk = {}\n",
    "# {Table name: set(Tables that references the table)}\n",
    "ref_in = {}\n",
    "# {Table name: df}\n",
    "nodes = set()\n",
    "# {Table name: {Column Name: Referenced Table}}\n",
    "edges = set()\n",
    "# _\n",
    "properties = set()\n",
    "# Nodes_df\n",
    "nodesTable = pd.DataFrame(columns=['Label', 'ID', 'Attributes'])\n",
    "# Edges_df\n",
    "edgesTable = pd.DataFrame(columns = ['From_Node_ID', 'To_Node_ID', 'order/service'])\n",
    "# Properties_df\n",
    "propertiesTable = pd.DataFrame(columns = [['index','From', 'Label', 'Attributes']])\n",
    "#Label = ['facilities'/ 'order' / 'service']\n",
    "\n",
    "\n",
    "# Nodes\n",
    "# Edges ['From_Node_ID', 'To_Node_ID']\n",
    "# Properties ['Label', 'ID', 'Attributes']\n",
    "# (^Nodes in graph)\n",
    "# Relationship [\"Relation_name\",\"from\",\"to\"]\n",
    "\n",
    "\n",
    "nodes_df = pd.DataFrame(columns=['Label', 'ID', 'Attributes'])\n",
    "edges_df = pd.DataFrame(columns=['From','To','From_Table','To_Table','Weight','Edge_Name'])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3bb863a2",
   "metadata": {
    "id": "3bb863a2"
   },
   "source": [
    "<a id='section_1'></a>\n",
    "## Reading Data Set\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9aa7852e",
   "metadata": {
    "id": "9aa7852e"
   },
   "source": [
    " creating a dictionary where\n",
    " key: first word of the table name \n",
    " value: df "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "793fb3eb",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "793fb3eb",
    "outputId": "7e77c5d9-1d3f-4eac-97b6-4c0480dca9f2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "customer_data.csv\n",
      "ExternalOrders_data.csv\n",
      "ExternalServices_data.csv\n",
      "ExternalShipments_data.csv\n",
      "ExternalTransactions.csv\n",
      "facilities_data.csv\n",
      "InternalOrders_data.csv\n",
      "InternalServices_data.csv\n",
      "InternalShipments_data.csv\n",
      "InternalTransactions.csv\n",
      "Manufacturing_data.csv\n",
      "Products_data.csv\n",
      "Retailer_data.csv\n",
      "Supplier_data.csv\n",
      "warehouses_data.csv\n"
     ]
    }
   ],
   "source": [
    "import os\n",
    "path_of_the_directory = './DataSet/'\n",
    "ext = ('.csv')\n",
    "for file in os.listdir(path_of_the_directory):\n",
    "    if file.endswith(ext):\n",
    "        print(file) \n",
    "        temp = (file.replace(\"_\",\" \").replace(\".\",\" \").split(\" \")[0].lower())\n",
    "        All_dfs[temp] = pd.read_csv(path_of_the_directory+file)\n",
    "    else:\n",
    "        continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "fbff72ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "for table_nm in All_dfs:\n",
    "    table = All_dfs[table_nm]\n",
    "    for column_name in table.columns:\n",
    "        column =  table[column_name]\n",
    "        if column.dtype=='O' and isinstance(column[0],str):\n",
    "            if  (isinstance(column[0],str) and column.apply(lambda x: (str(x).startswith('[') and str(x).endswith(']'))\n",
    "                         or \n",
    "                         (str(x).startswith('(') and str(x).endswith(')'))).agg(and_agg)):\n",
    "                \n",
    "                s = column.apply(lambda x: x.strip(\"[](,)\").split(','))\n",
    "                if (s[0][0].isdigit()):\n",
    "                    s = s.apply(lambda x: list(map(np.int64,x)))\n",
    "                All_dfs[table_nm][column_name] = s\n",
    "                \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "ab6ec34b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs[\"products\"].warehouses.explode().isin(All_dfs[\"warehouses\"].warehouse_id ).agg(and_agg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "120a6b4a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ware_house_id = All_dfs[\"warehouses\"].warehouse_id \n",
    "\n",
    "# warehouse_product_isin_warehouse = All_dfs[\"products\"].warehouses.explode().isin(ware_house_id)\n",
    "# warehouse_product_not_in_warehouse_values = list(All_dfs[\"products\"].warehouses.explode()[[not elem for elem in warehouse_product_isin_warehouse]].unique())\n",
    "# print(len(warehouse_product_not_in_warehouse_values))\n",
    "# warehouse_isin_warehouse_product =  ware_house_id.isin(All_dfs[\"products\"].warehouses.explode())\n",
    "# print(len( list(ware_house_id[[not elem for elem in warehouse_isin_warehouse_product]].unique())))\n",
    "# warehouses_to_be_replaced = list(ware_house_id[[not elem for elem in warehouse_isin_warehouse_product]].unique())[:len(warehouse_product_not_in_warehouse_values)]\n",
    "\n",
    "# All_dfs[\"warehouses\"].warehouse_id  =  ware_house_id.replace(warehouses_to_be_replaced,warehouse_product_not_in_warehouse_values)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4dd67254",
   "metadata": {},
   "source": [
    "### Adding Missing Edges\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "46c3bb8d",
   "metadata": {
    "scrolled": true
   },
   "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>IntShip_id</th>\n",
       "      <th>listSuppIds</th>\n",
       "      <th>factoryIds</th>\n",
       "      <th>from_to_where</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>7281</td>\n",
       "      <td>90942</td>\n",
       "      <td>52160</td>\n",
       "      <td>SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5897</td>\n",
       "      <td>36243</td>\n",
       "      <td>67361</td>\n",
       "      <td>SR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3477</td>\n",
       "      <td>87916</td>\n",
       "      <td>30692</td>\n",
       "      <td>SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>8796</td>\n",
       "      <td>90236</td>\n",
       "      <td>26533</td>\n",
       "      <td>SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2360</td>\n",
       "      <td>89467</td>\n",
       "      <td>46954</td>\n",
       "      <td>SR</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>95</th>\n",
       "      <td>5797</td>\n",
       "      <td>11283</td>\n",
       "      <td>24646</td>\n",
       "      <td>SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>6673</td>\n",
       "      <td>19072</td>\n",
       "      <td>31857</td>\n",
       "      <td>SR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>3632</td>\n",
       "      <td>29277</td>\n",
       "      <td>97142</td>\n",
       "      <td>SS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>3089</td>\n",
       "      <td>72315</td>\n",
       "      <td>90349</td>\n",
       "      <td>SR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>3916</td>\n",
       "      <td>46204</td>\n",
       "      <td>35266</td>\n",
       "      <td>SS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    IntShip_id  listSuppIds  factoryIds from_to_where\n",
       "0         7281        90942       52160            SS\n",
       "1         5897        36243       67361            SR\n",
       "2         3477        87916       30692            SS\n",
       "3         8796        90236       26533            SS\n",
       "4         2360        89467       46954            SR\n",
       "..         ...          ...         ...           ...\n",
       "95        5797        11283       24646            SS\n",
       "96        6673        19072       31857            SR\n",
       "97        3632        29277       97142            SS\n",
       "98        3089        72315       90349            SR\n",
       "99        3916        46204       35266            SS\n",
       "\n",
       "[100 rows x 4 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "initial_internalshipments_df = All_dfs[\"internalshipments\"]\n",
    "initial_internalshipments_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d58dcd3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "c = 9995\n",
    "from_shipment = All_dfs[\"internalshipments\"].listSuppIds\n",
    "to_shipment = All_dfs[\"internalshipments\"].factoryIds\n",
    "\n",
    "for r in range(len(All_dfs[\"manufacturing\"])):\n",
    "    diff_supp = All_dfs[\"manufacturing\"].loc[r,\"Different_suppliers\"]\n",
    "    factory = All_dfs[\"manufacturing\"].loc[r,\"Factory_id\"]\n",
    "    for s in range(len(diff_supp)):\n",
    "        \n",
    "        supp = diff_supp[s]\n",
    "        if supp == factory:\n",
    "            print(factory)\n",
    "        if len(All_dfs[\"internalshipments\"].query(f\"listSuppIds == {supp} and factoryIds == {factory}\")) ==0:\n",
    "            new_row = [{\"IntShip_id\":c, \"listSuppIds\":supp, \"factoryIds\": factory, \"from_to_where\": \"SS\"}] \n",
    "            df = pd.DataFrame(new_row)\n",
    "            c = c+1\n",
    "            All_dfs[\"internalshipments\"] = pd.concat([All_dfs[\"internalshipments\"], df], ignore_index=True)\n",
    "\n",
    "#         if (supp not in from_shipmentfrom_shipment)  :\n",
    "#                 if factory in All_dfs[\"supplier\"].supp_id:\n",
    "#                     i = \"SS\"\n",
    "\n",
    "#         new_row = [{\"IntShip_id\":c, \"listSuppIds\":diff_supp[s], \"factoryIds\": factory, \"from_to_where\": i}] \n",
    "#         df = pd.DataFrame(new_row)\n",
    "#         c = c+1\n",
    "#         All_dfs[\"internalshipments\"] = pd.concat([All_dfs[\"internalshipments\"], df], ignore_index=True)\n",
    "      "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f6ff1585",
   "metadata": {},
   "source": [
    "### Adding Missing Orders\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "4fe5f572",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "522"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders_int_ship_id = All_dfs[\"internalorders\"].IntShip_id\t\n",
    "internal_ship_id = All_dfs[\"internalshipments\"].IntShip_id\n",
    "ship_id_in_orders_ship_id = internal_ship_id.isin(orders_int_ship_id)\n",
    "ship_id_not_in_orders_ship_id = list(All_dfs[\"internalshipments\"].IntShip_id[[not elem for elem in ship_id_in_orders_ship_id]].unique())\n",
    "len(ship_id_not_in_orders_ship_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "ff976549",
   "metadata": {},
   "outputs": [],
   "source": [
    "c = 9931\n",
    "for id in ship_id_not_in_orders_ship_id:\n",
    "    new_row = [{\"IntOrders_id\":c, \"IntShip_id\":id, \"quantity\": 766, \"placed_when\": \"1993-05-08\",\n",
    "    \"actual_date\":\"1993-05-30 00:00:00\", \"expected_date\":\"1993-06-01\", \"cost\":\"38.90\", \"status\":\"Closed\"}] \n",
    "    df = pd.DataFrame(new_row)\n",
    "    c = c+1\n",
    "    All_dfs[\"internalorders\"] = pd.concat([All_dfs[\"internalorders\"], df], ignore_index=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cb547a6",
   "metadata": {
    "id": "7cb547a6"
   },
   "source": [
    "### Splitting Shipments tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "a61a8f33",
   "metadata": {
    "id": "a61a8f33"
   },
   "outputs": [],
   "source": [
    "SRIntShip = All_dfs[\"internalshipments\"].query('from_to_where == \"SR\"')\n",
    "SRIntShip = SRIntShip.drop(['from_to_where'], axis=1).reset_index(drop = True)\n",
    "#SRIntShip\n",
    "RCExtShip = All_dfs[\"externalshipments\"].query('from_to_where == \"RC\"')\n",
    "RCExtShip = RCExtShip.drop(['from_to_where'], axis=1).reset_index(drop = True)\n",
    "#RCExtShip\n",
    "SSIntShip = All_dfs[\"internalshipments\"].query('from_to_where == \"SS\"')\n",
    "SSIntShip = SSIntShip.drop(['from_to_where'], axis=1).reset_index(drop = True)\n",
    "#SSIntShip\n",
    "SCExtShip = All_dfs[\"externalshipments\"].query('from_to_where == \"SC\"')\n",
    "SCExtShip = SCExtShip.drop(['from_to_where'], axis=1).reset_index(drop = True)\n",
    "#SCExtShip\n",
    "All_dfs[\"RCExtShip\".lower()] = RCExtShip\n",
    "All_dfs[\"SCExtShip\".lower()] = SCExtShip\n",
    "All_dfs[\"SRIntShip\".lower()] = SRIntShip\n",
    "All_dfs[\"SSIntShip\".lower()] = SSIntShip"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "792e06a4",
   "metadata": {
    "id": "792e06a4"
   },
   "source": [
    "### Splitting Orders Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "e05ff098",
   "metadata": {
    "id": "e05ff098"
   },
   "outputs": [],
   "source": [
    "filter_list = All_dfs[\"rcextship\"][\"ExtShip_id\"]\n",
    "All_dfs[\"rcextorders\"] = All_dfs[\"externalorders\"][All_dfs[\"externalorders\"].ExtShip_id.isin(filter_list)].reset_index(drop=True)\n",
    "filter_list = All_dfs[\"scextship\"][\"ExtShip_id\"]\n",
    "All_dfs[\"scextorders\"] = All_dfs[\"externalorders\"][All_dfs[\"externalorders\"].ExtShip_id.isin(filter_list)].reset_index(drop=True)\n",
    "#All_dfs[\"scextorders\"] = All_dfs[\"externalorders\"].query(\"ExtShip_id.isin(@filter_list)\").reset_index(drop=True)\n",
    "filter_list = All_dfs[\"srintship\"][\"IntShip_id\"]\n",
    "All_dfs[\"srintorders\"] = All_dfs[\"internalorders\"][All_dfs[\"internalorders\"].IntShip_id.isin(filter_list)].reset_index(drop=True)\n",
    "#All_dfs[\"srintorders\"] = All_dfs[\"internalorders\"].query(\"IntShip_id.isin(@filter_list)\").reset_index(drop=True)\n",
    "filter_list = All_dfs[\"ssintship\"][\"IntShip_id\"]\n",
    "All_dfs[\"ssintorders\"] = All_dfs[\"internalorders\"][All_dfs[\"internalorders\"].IntShip_id.isin(filter_list)].reset_index(drop=True)\n",
    "#All_dfs[\"ssintorders\"] = All_dfs[\"internalorders\"].query(\"IntShip_id.isin(@filter_list)\").reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27b40154",
   "metadata": {
    "id": "27b40154"
   },
   "source": [
    "### Removing Redundant Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "1dce7a31",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "1dce7a31",
    "outputId": "f7113947-2d2d-46bd-89f4-68c6341fa514"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['customer', 'externalservices', 'externaltransactions', 'facilities', 'internalservices', 'internaltransactions', 'manufacturing', 'products', 'retailer', 'supplier', 'warehouses', 'rcextship', 'scextship', 'srintship', 'ssintship', 'rcextorders', 'scextorders', 'srintorders', 'ssintorders'])"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs.pop(\"internalshipments\")\n",
    "All_dfs.pop(\"externalshipments\")\n",
    "All_dfs.pop(\"externalorders\")\n",
    "All_dfs.pop(\"internalorders\")\n",
    "All_dfs.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "fe82c0a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# for table in All_dfs:\n",
    "#     print(table)\n",
    "#     print(All_dfs[table].isna().sum())\n",
    "#     print(\"_________________________\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9e191d11",
   "metadata": {},
   "source": [
    "### Removing nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b1b0b012",
   "metadata": {},
   "outputs": [],
   "source": [
    "for table in All_dfs:\n",
    "    All_dfs[table].fillna('Unknown',inplace = True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "SFbYcLkyvgI1",
   "metadata": {
    "id": "SFbYcLkyvgI1"
   },
   "source": [
    "### Execlude Tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "NPz3VcBXvjj-",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "NPz3VcBXvjj-",
    "outputId": "bf669753-ae70-4f7f-8723-500d897e74b4"
   },
   "outputs": [],
   "source": [
    "# All_dfs = {key: value for key, value in All_dfs.items() \n",
    "#            if key not in [\"products\",\"warehouses\",\"manufacturing\"]}\n",
    "# All_dfs.keys()\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "ccd0294b",
   "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>IntOrders_id</th>\n",
       "      <th>IntShip_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>placed_when</th>\n",
       "      <th>actual_date</th>\n",
       "      <th>expected_date</th>\n",
       "      <th>cost</th>\n",
       "      <th>status</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6266</td>\n",
       "      <td>6224</td>\n",
       "      <td>438</td>\n",
       "      <td>1975-01-08</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1975-02-01</td>\n",
       "      <td>7.95</td>\n",
       "      <td>Out For Delivery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5318</td>\n",
       "      <td>6798</td>\n",
       "      <td>801</td>\n",
       "      <td>2021-01-18</td>\n",
       "      <td>2021-02-07 00:00:00</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>7560.41</td>\n",
       "      <td>Delayed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2439</td>\n",
       "      <td>8796</td>\n",
       "      <td>882</td>\n",
       "      <td>1978-07-09</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1978-07-28</td>\n",
       "      <td>35.13</td>\n",
       "      <td>Out For Delivery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1851</td>\n",
       "      <td>4895</td>\n",
       "      <td>220</td>\n",
       "      <td>2002-12-28</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2003-01-22</td>\n",
       "      <td>2.96</td>\n",
       "      <td>Out For Delivery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9800</td>\n",
       "      <td>3996</td>\n",
       "      <td>399</td>\n",
       "      <td>1975-10-06</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1975-10-29</td>\n",
       "      <td>83.16</td>\n",
       "      <td>Out For Delivery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>10448</td>\n",
       "      <td>10478</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>10449</td>\n",
       "      <td>10479</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>10450</td>\n",
       "      <td>10480</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>546</th>\n",
       "      <td>10451</td>\n",
       "      <td>10481</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>547</th>\n",
       "      <td>10452</td>\n",
       "      <td>10482</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>548 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     IntOrders_id  IntShip_id  quantity placed_when          actual_date  \\\n",
       "0            6266        6224       438  1975-01-08              Unknown   \n",
       "1            5318        6798       801  2021-01-18  2021-02-07 00:00:00   \n",
       "2            2439        8796       882  1978-07-09              Unknown   \n",
       "3            1851        4895       220  2002-12-28              Unknown   \n",
       "4            9800        3996       399  1975-10-06              Unknown   \n",
       "..            ...         ...       ...         ...                  ...   \n",
       "543         10448       10478       766  1993-05-08  1993-05-30 00:00:00   \n",
       "544         10449       10479       766  1993-05-08  1993-05-30 00:00:00   \n",
       "545         10450       10480       766  1993-05-08  1993-05-30 00:00:00   \n",
       "546         10451       10481       766  1993-05-08  1993-05-30 00:00:00   \n",
       "547         10452       10482       766  1993-05-08  1993-05-30 00:00:00   \n",
       "\n",
       "    expected_date     cost            status  \n",
       "0      1975-02-01     7.95  Out For Delivery  \n",
       "1      2021-01-29  7560.41           Delayed  \n",
       "2      1978-07-28    35.13  Out For Delivery  \n",
       "3      2003-01-22     2.96  Out For Delivery  \n",
       "4      1975-10-29    83.16  Out For Delivery  \n",
       "..            ...      ...               ...  \n",
       "543    1993-06-01    38.90            Closed  \n",
       "544    1993-06-01    38.90            Closed  \n",
       "545    1993-06-01    38.90            Closed  \n",
       "546    1993-06-01    38.90            Closed  \n",
       "547    1993-06-01    38.90            Closed  \n",
       "\n",
       "[548 rows x 8 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs['ssintorders']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "be421a41",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "537\n",
      "100\n",
      "True     327\n",
      "False    210\n",
      "Name: listSuppIds, dtype: int64\n",
      "437\n"
     ]
    }
   ],
   "source": [
    "import random\n",
    "x=All_dfs['ssintship']['listSuppIds']\n",
    "print(len(x))\n",
    "y=All_dfs[\"manufacturing\"]['Factory_id']\n",
    "print(len(y))\n",
    "z=[]\n",
    "print(x.isin(y).value_counts())\n",
    "# z=All_dfs['ssintorders']\n",
    "for i in range (len(x)):\n",
    "    temp=np.where(y==x[i])[0]\n",
    "    if(len(temp)>1):#returns the indices that makes this statement true\n",
    "        z.append(random.choice(temp))\n",
    "    elif(len(temp)==1):\n",
    "        z.append(temp[0])\n",
    "    elif(len(temp)==0):\n",
    "        z.append(random.randint(0,len(y)-1))\n",
    "\n",
    "# now z has size = 537 and the intship has size of 548 so will randomly add 11 more values\n",
    "for i in range(11):\n",
    "    z.append(random.randint(0,len(y)-1))\n",
    "print(len(x)-len(y))\n",
    "len(z)\n",
    "prod_id=[]\n",
    "for i in range(len(z)):\n",
    "    prod_id.append(All_dfs[\"manufacturing\"]['Product_id'][z[i]])\n",
    "prod_id    \n",
    "All_dfs['ssintorders'].insert(8,'prod_id',prod_id,True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "007bdf47",
   "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>IntOrders_id</th>\n",
       "      <th>IntShip_id</th>\n",
       "      <th>quantity</th>\n",
       "      <th>placed_when</th>\n",
       "      <th>actual_date</th>\n",
       "      <th>expected_date</th>\n",
       "      <th>cost</th>\n",
       "      <th>status</th>\n",
       "      <th>prod_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6266</td>\n",
       "      <td>6224</td>\n",
       "      <td>438</td>\n",
       "      <td>1975-01-08</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1975-02-01</td>\n",
       "      <td>7.95</td>\n",
       "      <td>Out For Delivery</td>\n",
       "      <td>108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5318</td>\n",
       "      <td>6798</td>\n",
       "      <td>801</td>\n",
       "      <td>2021-01-18</td>\n",
       "      <td>2021-02-07 00:00:00</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>7560.41</td>\n",
       "      <td>Delayed</td>\n",
       "      <td>582</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2439</td>\n",
       "      <td>8796</td>\n",
       "      <td>882</td>\n",
       "      <td>1978-07-09</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1978-07-28</td>\n",
       "      <td>35.13</td>\n",
       "      <td>Out For Delivery</td>\n",
       "      <td>679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1851</td>\n",
       "      <td>4895</td>\n",
       "      <td>220</td>\n",
       "      <td>2002-12-28</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>2003-01-22</td>\n",
       "      <td>2.96</td>\n",
       "      <td>Out For Delivery</td>\n",
       "      <td>938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9800</td>\n",
       "      <td>3996</td>\n",
       "      <td>399</td>\n",
       "      <td>1975-10-06</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>1975-10-29</td>\n",
       "      <td>83.16</td>\n",
       "      <td>Out For Delivery</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>543</th>\n",
       "      <td>10448</td>\n",
       "      <td>10478</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "      <td>291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>544</th>\n",
       "      <td>10449</td>\n",
       "      <td>10479</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "      <td>549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>545</th>\n",
       "      <td>10450</td>\n",
       "      <td>10480</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "      <td>933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>546</th>\n",
       "      <td>10451</td>\n",
       "      <td>10481</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "      <td>740</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>547</th>\n",
       "      <td>10452</td>\n",
       "      <td>10482</td>\n",
       "      <td>766</td>\n",
       "      <td>1993-05-08</td>\n",
       "      <td>1993-05-30 00:00:00</td>\n",
       "      <td>1993-06-01</td>\n",
       "      <td>38.90</td>\n",
       "      <td>Closed</td>\n",
       "      <td>483</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>548 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     IntOrders_id  IntShip_id  quantity placed_when          actual_date  \\\n",
       "0            6266        6224       438  1975-01-08              Unknown   \n",
       "1            5318        6798       801  2021-01-18  2021-02-07 00:00:00   \n",
       "2            2439        8796       882  1978-07-09              Unknown   \n",
       "3            1851        4895       220  2002-12-28              Unknown   \n",
       "4            9800        3996       399  1975-10-06              Unknown   \n",
       "..            ...         ...       ...         ...                  ...   \n",
       "543         10448       10478       766  1993-05-08  1993-05-30 00:00:00   \n",
       "544         10449       10479       766  1993-05-08  1993-05-30 00:00:00   \n",
       "545         10450       10480       766  1993-05-08  1993-05-30 00:00:00   \n",
       "546         10451       10481       766  1993-05-08  1993-05-30 00:00:00   \n",
       "547         10452       10482       766  1993-05-08  1993-05-30 00:00:00   \n",
       "\n",
       "    expected_date     cost            status  prod_id  \n",
       "0      1975-02-01     7.95  Out For Delivery      108  \n",
       "1      2021-01-29  7560.41           Delayed      582  \n",
       "2      1978-07-28    35.13  Out For Delivery      679  \n",
       "3      2003-01-22     2.96  Out For Delivery      938  \n",
       "4      1975-10-29    83.16  Out For Delivery      112  \n",
       "..            ...      ...               ...      ...  \n",
       "543    1993-06-01    38.90            Closed      291  \n",
       "544    1993-06-01    38.90            Closed      549  \n",
       "545    1993-06-01    38.90            Closed      933  \n",
       "546    1993-06-01    38.90            Closed      740  \n",
       "547    1993-06-01    38.90            Closed      483  \n",
       "\n",
       "[548 rows x 9 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs['ssintorders']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44151839",
   "metadata": {
    "id": "44151839"
   },
   "source": [
    "### Initialize fk and ref_in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "d0650994",
   "metadata": {
    "id": "d0650994"
   },
   "outputs": [],
   "source": [
    "table_name = list(All_dfs.keys())\n",
    "for table_name in All_dfs:\n",
    "    fk[table_name] = {}\n",
    "    ref_in[table_name] = set()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "abaac2b1",
   "metadata": {
    "id": "abaac2b1"
   },
   "source": [
    "### Get Primary key for each table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "2009ba51",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "2009ba51",
    "outputId": "ca52c92d-cdf1-4e43-f6ae-521a641f6143"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'customer': 'cust_id',\n",
       " 'externalservices': 'ExtServ_id',\n",
       " 'externaltransactions': 'ExtTran_id',\n",
       " 'facilities': 'fac_id',\n",
       " 'internalservices': 'IntServ_id',\n",
       " 'internaltransactions': 'IntTran_id',\n",
       " 'manufacturing': 'Manf_id',\n",
       " 'products': 'prod_id',\n",
       " 'retailer': 'retailer_id',\n",
       " 'supplier': 'supp_id',\n",
       " 'warehouses': 'warehouse_id',\n",
       " 'rcextship': 'ExtShip_id',\n",
       " 'scextship': 'ExtShip_id',\n",
       " 'srintship': 'IntShip_id',\n",
       " 'ssintship': 'IntShip_id',\n",
       " 'rcextorders': 'ExtOrders_id',\n",
       " 'scextorders': 'ExtOrders_id',\n",
       " 'srintorders': 'IntOrders_id',\n",
       " 'ssintorders': 'IntOrders_id'}"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for t in All_dfs:\n",
    "    pk_finder(t,All_dfs[t])\n",
    "pk"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dcedd35e",
   "metadata": {
    "id": "dcedd35e"
   },
   "source": [
    "### Get Foreing key for each table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "ea835d03",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "ea835d03",
    "outputId": "d1773b0b-f680-4a53-a7b1-45229492cb26"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'customer': {},\n",
       " 'externalservices': {'ExtTrans_id': 'externaltransactions'},\n",
       " 'externaltransactions': {'CompFrom': 'supplier', 'Custto': 'customer'},\n",
       " 'facilities': {'supplier_id': 'supplier'},\n",
       " 'internalservices': {'IntTrans_id': 'internaltransactions'},\n",
       " 'internaltransactions': {'CompFrom': 'supplier', 'Compto': 'supplier'},\n",
       " 'manufacturing': {'Different_suppliers': 'supplier',\n",
       "  'Product_id': 'products',\n",
       "  'Factory_id': 'supplier'},\n",
       " 'products': {'warehouses': 'warehouses'},\n",
       " 'retailer': {},\n",
       " 'supplier': {},\n",
       " 'warehouses': {},\n",
       " 'rcextship': {'factoryIds/retailerIds': 'retailer', 'idsTo': 'customer'},\n",
       " 'scextship': {'factoryIds/retailerIds': 'supplier', 'idsTo': 'customer'},\n",
       " 'srintship': {'listSuppIds': 'supplier', 'factoryIds': 'retailer'},\n",
       " 'ssintship': {'listSuppIds': 'supplier', 'factoryIds': 'supplier'},\n",
       " 'rcextorders': {'ExtShip_id': 'rcextship'},\n",
       " 'scextorders': {'ExtShip_id': 'scextship'},\n",
       " 'srintorders': {'IntShip_id': 'srintship'},\n",
       " 'ssintorders': {'IntShip_id': 'ssintship', 'prod_id': 'products'}}"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "for t in All_dfs:\n",
    "    fk_finder(t,All_dfs[t])\n",
    "fk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "db523572",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "db523572",
    "outputId": "56de3687-8004-45ee-d1cd-2a194d42c74b"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'customer': {'externaltransactions', 'rcextship', 'scextship'},\n",
       " 'externalservices': set(),\n",
       " 'externaltransactions': {'externalservices'},\n",
       " 'facilities': set(),\n",
       " 'internalservices': set(),\n",
       " 'internaltransactions': {'internalservices'},\n",
       " 'manufacturing': set(),\n",
       " 'products': {'manufacturing', 'ssintorders'},\n",
       " 'retailer': {'rcextship', 'srintship'},\n",
       " 'supplier': {'externaltransactions',\n",
       "  'facilities',\n",
       "  'internaltransactions',\n",
       "  'manufacturing',\n",
       "  'scextship',\n",
       "  'srintship',\n",
       "  'ssintship'},\n",
       " 'warehouses': {'products'},\n",
       " 'rcextship': {'rcextorders'},\n",
       " 'scextship': {'scextorders'},\n",
       " 'srintship': {'srintorders'},\n",
       " 'ssintship': {'ssintorders'},\n",
       " 'rcextorders': set(),\n",
       " 'scextorders': set(),\n",
       " 'srintorders': set(),\n",
       " 'ssintorders': set()}"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ref_in"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e47d40f8",
   "metadata": {
    "id": "e47d40f8"
   },
   "source": [
    "### Determine nodes and edges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "e5ca1421",
   "metadata": {
    "id": "e5ca1421"
   },
   "outputs": [],
   "source": [
    "nodes_edges(fk)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "557ca7af",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "557ca7af",
    "outputId": "087881bb-4c71-4280-fe8f-2acb191670bf"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'customer', 'retailer', 'supplier', 'warehouses'}"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "4e3ebbb5",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "4e3ebbb5",
    "outputId": "51aa740e-5aa0-4d32-a52a-aa2eb58182e1"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'externaltransactions',\n",
       " 'internaltransactions',\n",
       " 'rcextship',\n",
       " 'scextship',\n",
       " 'srintship',\n",
       " 'ssintship'}"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "edges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "5ba55d53",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "5ba55d53",
    "outputId": "20e0398e-6349-48ab-d1cb-63958dda0890"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'externalservices',\n",
       " 'facilities',\n",
       " 'internalservices',\n",
       " 'products',\n",
       " 'rcextorders',\n",
       " 'scextorders',\n",
       " 'srintorders'}"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16e305b5",
   "metadata": {
    "id": "16e305b5"
   },
   "source": [
    "### Create NodesTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "bcd83ca2",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "bcd83ca2",
    "outputId": "53ab6f8b-8dbe-4019-95c9-e697929c2d52"
   },
   "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>Label</th>\n",
       "      <th>ID</th>\n",
       "      <th>Attributes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>customer</td>\n",
       "      <td>33736</td>\n",
       "      <td>{'gender': 'F', 'first_name': 'Nadia', 'last_n...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>customer</td>\n",
       "      <td>42959</td>\n",
       "      <td>{'gender': 'M', 'first_name': 'Eric', 'last_na...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>customer</td>\n",
       "      <td>50653</td>\n",
       "      <td>{'gender': 'F', 'first_name': 'Alessia', 'last...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>customer</td>\n",
       "      <td>82485</td>\n",
       "      <td>{'gender': 'M', 'first_name': 'Alexei', 'last_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>customer</td>\n",
       "      <td>56609</td>\n",
       "      <td>{'gender': 'F', 'first_name': 'Brenda', 'last_...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1595</th>\n",
       "      <td>warehouses</td>\n",
       "      <td>9964</td>\n",
       "      <td>{'country': 'Norway', 'city_name': 'Jan Mayen ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1596</th>\n",
       "      <td>warehouses</td>\n",
       "      <td>9970</td>\n",
       "      <td>{'country': 'Bouvet Island', 'city_name': 'Unk...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1597</th>\n",
       "      <td>warehouses</td>\n",
       "      <td>9975</td>\n",
       "      <td>{'country': 'Bolivia, Plurinational State of',...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598</th>\n",
       "      <td>warehouses</td>\n",
       "      <td>9981</td>\n",
       "      <td>{'country': 'Qatar', 'city_name': 'Ash Shamāl'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1599</th>\n",
       "      <td>warehouses</td>\n",
       "      <td>9996</td>\n",
       "      <td>{'country': 'Tunisia', 'city_name': 'Ben Arous...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1600 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Label     ID                                         Attributes\n",
       "0       customer  33736  {'gender': 'F', 'first_name': 'Nadia', 'last_n...\n",
       "1       customer  42959  {'gender': 'M', 'first_name': 'Eric', 'last_na...\n",
       "2       customer  50653  {'gender': 'F', 'first_name': 'Alessia', 'last...\n",
       "3       customer  82485  {'gender': 'M', 'first_name': 'Alexei', 'last_...\n",
       "4       customer  56609  {'gender': 'F', 'first_name': 'Brenda', 'last_...\n",
       "...          ...    ...                                                ...\n",
       "1595  warehouses   9964  {'country': 'Norway', 'city_name': 'Jan Mayen ...\n",
       "1596  warehouses   9970  {'country': 'Bouvet Island', 'city_name': 'Unk...\n",
       "1597  warehouses   9975  {'country': 'Bolivia, Plurinational State of',...\n",
       "1598  warehouses   9981  {'country': 'Qatar', 'city_name': 'Ash Shamāl'...\n",
       "1599  warehouses   9996  {'country': 'Tunisia', 'city_name': 'Ben Arous...\n",
       "\n",
       "[1600 rows x 3 columns]"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodesTable = add_nodes(nodes,nodesTable)\n",
    "nodesTable"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92bfeb8a",
   "metadata": {
    "id": "92bfeb8a"
   },
   "source": [
    "### Create Edges Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "243aacff",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "243aacff",
    "outputId": "4aa0f919-0ceb-466f-b768-fe040f2f78c0"
   },
   "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>From_Node_ID</th>\n",
       "      <th>To_Node_ID</th>\n",
       "      <th>order/service</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>390</td>\n",
       "      <td>314</td>\n",
       "      <td>[{'IntOrders_id': 7965, 'quantity': 259, 'plac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>309</td>\n",
       "      <td>222</td>\n",
       "      <td>[{'IntOrders_id': 6575, 'quantity': 900, 'plac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>276</td>\n",
       "      <td>275</td>\n",
       "      <td>[{'IntOrders_id': 2439, 'quantity': 882, 'plac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>255</td>\n",
       "      <td>263</td>\n",
       "      <td>[{'IntOrders_id': 9909, 'quantity': 235, 'plac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>220</td>\n",
       "      <td>338</td>\n",
       "      <td>[{'IntOrders_id': 9933, 'quantity': 766, 'plac...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>883</th>\n",
       "      <td>409</td>\n",
       "      <td>119</td>\n",
       "      <td>[{'ExtServ_id': 9526, 'placed_when': '2007-04-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>884</th>\n",
       "      <td>377</td>\n",
       "      <td>35</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>885</th>\n",
       "      <td>308</td>\n",
       "      <td>110</td>\n",
       "      <td>[{'ExtServ_id': 2634, 'placed_when': '1987-06-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>180</td>\n",
       "      <td>39</td>\n",
       "      <td>[{'ExtServ_id': 3132, 'placed_when': '1985-12-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>409</td>\n",
       "      <td>113</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>888 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    From_Node_ID To_Node_ID                                      order/service\n",
       "0            390        314  [{'IntOrders_id': 7965, 'quantity': 259, 'plac...\n",
       "1            309        222  [{'IntOrders_id': 6575, 'quantity': 900, 'plac...\n",
       "2            276        275  [{'IntOrders_id': 2439, 'quantity': 882, 'plac...\n",
       "3            255        263  [{'IntOrders_id': 9909, 'quantity': 235, 'plac...\n",
       "4            220        338  [{'IntOrders_id': 9933, 'quantity': 766, 'plac...\n",
       "..           ...        ...                                                ...\n",
       "883          409        119  [{'ExtServ_id': 9526, 'placed_when': '2007-04-...\n",
       "884          377         35                                                 []\n",
       "885          308        110  [{'ExtServ_id': 2634, 'placed_when': '1987-06-...\n",
       "886          180         39  [{'ExtServ_id': 3132, 'placed_when': '1985-12-...\n",
       "887          409        113                                                 []\n",
       "\n",
       "[888 rows x 3 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "edgesTable = add_edges(edges, edgesTable,pk,fk,ref_in)\n",
    "edgesTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "ziqKFfbOwK60",
   "metadata": {
    "id": "ziqKFfbOwK60"
   },
   "outputs": [],
   "source": [
    "# nodes_df = pd.DataFrame(columns=['Label', 'ID', 'Attributes'])\n",
    "# edges_df = pd.DataFrame(columns=['From','To'])\n",
    "for node in nodes:\n",
    "    column_names = list(All_dfs[node].columns) #get column names\n",
    "    for index, row in All_dfs[node].iterrows():\n",
    "        att = {}\n",
    "        for i in range(1, len(column_names)):\n",
    "            att[column_names[i]] = All_dfs[node].iloc[index, i]\n",
    "        newRow = [{'Label': node , 'ID': All_dfs[node].iloc[index,0] , 'Attributes': att}]\n",
    "        tmp = pd.DataFrame(newRow)\n",
    "        nodes_df = pd.concat([nodes_df, tmp], ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "9c2ee0f5",
   "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>Label</th>\n",
       "      <th>ID</th>\n",
       "      <th>Attributes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>supplier</td>\n",
       "      <td>34258</td>\n",
       "      <td>{'supplier_name': 'Taylor PLC', 'country': 'La...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>supplier</td>\n",
       "      <td>40868</td>\n",
       "      <td>{'supplier_name': 'Sorrentino SPA', 'country':...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>supplier</td>\n",
       "      <td>27813</td>\n",
       "      <td>{'supplier_name': 'Persico e figli', 'country'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>supplier</td>\n",
       "      <td>57551</td>\n",
       "      <td>{'supplier_name': 'Karge OHG mbH', 'country': ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>supplier</td>\n",
       "      <td>50099</td>\n",
       "      <td>{'supplier_name': 'Smith, Porter and Lynch', '...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>445</th>\n",
       "      <td>supplier</td>\n",
       "      <td>90256</td>\n",
       "      <td>{'supplier_name': 'Förster', 'country': 'Ethio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>446</th>\n",
       "      <td>supplier</td>\n",
       "      <td>35266</td>\n",
       "      <td>{'supplier_name': 'Gotthard Zahn GmbH', 'count...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>447</th>\n",
       "      <td>supplier</td>\n",
       "      <td>52647</td>\n",
       "      <td>{'supplier_name': 'Cook-Webb', 'country': 'Mor...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>448</th>\n",
       "      <td>supplier</td>\n",
       "      <td>13355</td>\n",
       "      <td>{'supplier_name': 'Shah-Lowe', 'country': 'Cen...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>449</th>\n",
       "      <td>supplier</td>\n",
       "      <td>48467</td>\n",
       "      <td>{'supplier_name': 'Brandt Ltd', 'country': 'An...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>300 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        Label     ID                                         Attributes\n",
       "150  supplier  34258  {'supplier_name': 'Taylor PLC', 'country': 'La...\n",
       "151  supplier  40868  {'supplier_name': 'Sorrentino SPA', 'country':...\n",
       "152  supplier  27813  {'supplier_name': 'Persico e figli', 'country'...\n",
       "153  supplier  57551  {'supplier_name': 'Karge OHG mbH', 'country': ...\n",
       "154  supplier  50099  {'supplier_name': 'Smith, Porter and Lynch', '...\n",
       "..        ...    ...                                                ...\n",
       "445  supplier  90256  {'supplier_name': 'Förster', 'country': 'Ethio...\n",
       "446  supplier  35266  {'supplier_name': 'Gotthard Zahn GmbH', 'count...\n",
       "447  supplier  52647  {'supplier_name': 'Cook-Webb', 'country': 'Mor...\n",
       "448  supplier  13355  {'supplier_name': 'Shah-Lowe', 'country': 'Cen...\n",
       "449  supplier  48467  {'supplier_name': 'Brandt Ltd', 'country': 'An...\n",
       "\n",
       "[300 rows x 3 columns]"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes_df.query(\"Label == 'supplier'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "cBd3GL_EwK9t",
   "metadata": {
    "id": "cBd3GL_EwK9t"
   },
   "outputs": [],
   "source": [
    "\n",
    "for edge_name in edges:\n",
    "    \n",
    "\n",
    "    foreign_keys = list(fk[edge_name].keys())\n",
    "\n",
    "    from_col = foreign_keys[0]\n",
    "    from_table_name = fk[edge_name][from_col]\n",
    "    from_df = All_dfs[from_table_name]\n",
    "    from_df_pk = pk[from_table_name]\n",
    "\n",
    "    to_col = foreign_keys[-1]\n",
    "    to_table_name = fk[edge_name][to_col]\n",
    "    to_df = All_dfs[to_table_name]\n",
    "    to_df_pk = pk[to_table_name]\n",
    "\n",
    "    column_names = list(All_dfs[edge_name].columns) #get column names\n",
    "    for index, _ in All_dfs[edge_name].iterrows():\n",
    "        att = {}\n",
    "        from_ref_id,to_ref_id = None, None\n",
    "\n",
    "\n",
    "        for i in range(1, len(column_names)):\n",
    "            column_name = column_names[i]\n",
    "\n",
    "\n",
    "            if column_name not in foreign_keys:\n",
    "                att[column_name] = All_dfs[edge_name].iloc[index, i]\n",
    "\n",
    "            else:\n",
    "                reference_id = All_dfs[edge_name].iloc[index, i]\n",
    "                if column_name == from_col:\n",
    "                # from_ref_id = from_df[from_df[from_df_pk] == reference_id].index[0]\n",
    "                    from_ref_id = reference_id\n",
    "                else:\n",
    "                # to_ref_id = to_df[to_df[to_df_pk] == reference_id].index[0]\n",
    "                    to_ref_id = reference_id\n",
    "\n",
    "        # Adding new entry to node tabel\n",
    "        newRow = [{'Label': edge_name , 'ID': All_dfs[edge_name].iloc[index,0] , 'Attributes': att}]\n",
    "        tmp = pd.DataFrame(newRow)\n",
    "        nodes_df = pd.concat([nodes_df, tmp], ignore_index=True)\n",
    "        edge_node_index = len(nodes_df)-1\n",
    "        # print(nodes_df.iloc[len(nodes_df)-1],All_dfs[Edge_name].iloc[index,0] )\n",
    "        # creating two edges, one from the from_node to the edge node and one from edge node to to_node\n",
    "        from_node_id = nodes_df[(nodes_df['Label']==from_table_name) &  (nodes_df['ID']==from_ref_id)].index[0]\n",
    "        to_node_id =  nodes_df[(nodes_df['Label']==to_table_name) &  (nodes_df['ID']==to_ref_id)].index[0] \n",
    "\n",
    "        # from ---> edge \n",
    "        new_from_edge_row = [{'From': from_node_id , 'To':edge_node_index,'From_Table': from_table_name.capitalize(),'To_Table': edge_name.capitalize()\n",
    "                              ,'Weight': 42,'Edge_Name': edge_name}]\n",
    "        tmp = pd.DataFrame(new_from_edge_row)\n",
    "        edges_df = pd.concat([edges_df, tmp], ignore_index=True)\n",
    "        # edge --->to\n",
    "        new_to_edge_row = [{'From': edge_node_index , 'To':  to_node_id, 'From_Table':edge_name.capitalize(), 'To_Table': to_table_name.capitalize()\n",
    "                           ,'Weight': 42,'Edge_Name': edge_name}]\n",
    "        tmp = pd.DataFrame(new_to_edge_row)\n",
    "        edges_df = pd.concat([edges_df, tmp], ignore_index=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "3c898317",
   "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>From</th>\n",
       "      <th>To</th>\n",
       "      <th>From_Table</th>\n",
       "      <th>To_Table</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Edge_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>390</td>\n",
       "      <td>1600</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1600</td>\n",
       "      <td>314</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>309</td>\n",
       "      <td>1601</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1601</td>\n",
       "      <td>222</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>276</td>\n",
       "      <td>1602</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1771</th>\n",
       "      <td>2485</td>\n",
       "      <td>110</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>Customer</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1772</th>\n",
       "      <td>180</td>\n",
       "      <td>2486</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1773</th>\n",
       "      <td>2486</td>\n",
       "      <td>39</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>Customer</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1774</th>\n",
       "      <td>409</td>\n",
       "      <td>2487</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775</th>\n",
       "      <td>2487</td>\n",
       "      <td>113</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>Customer</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1776 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      From    To            From_Table              To_Table Weight  \\\n",
       "0      390  1600              Supplier             Ssintship     42   \n",
       "1     1600   314             Ssintship              Supplier     42   \n",
       "2      309  1601              Supplier             Ssintship     42   \n",
       "3     1601   222             Ssintship              Supplier     42   \n",
       "4      276  1602              Supplier             Ssintship     42   \n",
       "...    ...   ...                   ...                   ...    ...   \n",
       "1771  2485   110  Externaltransactions              Customer     42   \n",
       "1772   180  2486              Supplier  Externaltransactions     42   \n",
       "1773  2486    39  Externaltransactions              Customer     42   \n",
       "1774   409  2487              Supplier  Externaltransactions     42   \n",
       "1775  2487   113  Externaltransactions              Customer     42   \n",
       "\n",
       "                 Edge_Name  \n",
       "0                ssintship  \n",
       "1                ssintship  \n",
       "2                ssintship  \n",
       "3                ssintship  \n",
       "4                ssintship  \n",
       "...                    ...  \n",
       "1771  externaltransactions  \n",
       "1772  externaltransactions  \n",
       "1773  externaltransactions  \n",
       "1774  externaltransactions  \n",
       "1775  externaltransactions  \n",
       "\n",
       "[1776 rows x 6 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "edges_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "JjImjta5GIKZ",
   "metadata": {
    "id": "JjImjta5GIKZ"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "externalservices\n",
      "internalservices\n",
      "rcextorders\n",
      "facilities\n",
      "products\n",
      "scextorders\n",
      "srintorders\n"
     ]
    }
   ],
   "source": [
    "\n",
    "for property_name in properties:\n",
    "  print(property_name)\n",
    "\n",
    "  property_df = All_dfs[property_name]\n",
    "      \n",
    "  foreign_keys = list(fk[property_name].keys())\n",
    "\n",
    "  fk_col = foreign_keys[0]\n",
    "  referenced_table_name = fk[property_name][fk_col]\n",
    "  referenced_table_df = All_dfs[referenced_table_name]\n",
    "  referenced_table_pk = pk[referenced_table_name]\n",
    "  \n",
    "  column_names = list(property_df.columns) #get column names\n",
    "  \n",
    "  for index, _ in property_df.iterrows():    \n",
    "      att = {}\n",
    "      reference_id = None;      \n",
    "      \n",
    "      for i in range(1, len(column_names)):\n",
    "        column_name = column_names[i]\n",
    "        \n",
    "        \n",
    "        if column_name not in foreign_keys:\n",
    "          att[column_name] = property_df.iloc[index, i]\n",
    "      \n",
    "        else:\n",
    "          # capturing foreign key value\n",
    "          reference_id = property_df.iloc[index, i]\n",
    "\n",
    "      # Adding new entry to node tabel\n",
    "      newRow = [{'Label': property_name , 'ID': property_df.iloc[index,0] , 'Attributes': att}]\n",
    "      tmp = pd.DataFrame(newRow)\n",
    "      nodes_df = pd.concat([nodes_df, tmp], ignore_index=True)\n",
    "      property_node_index = len(nodes_df)-1\n",
    "      # print(nodes_df.iloc[len(nodes_df)-1],property_df.iloc[index,0] )\n",
    "\n",
    "      # creating two edges, one from the from_node to the edge node and one from edge node to to_node\n",
    "      if  isinstance(reference_id,list):\n",
    "        for list_element_id in reference_id:\n",
    "          referenced_node_id = nodes_df[(nodes_df['Label']==referenced_table_name) &  (nodes_df['ID']==list_element_id)].index[0] \n",
    "        \n",
    "          new_property_edge_row = [{'From': referenced_node_id , 'To':  property_node_index,'From_Table':referenced_table_name.capitalize(), 'To_Table': property_name.capitalize()\n",
    "                                    ,'Weight': 42,'Edge_Name': \"Related_To\"}]\n",
    "          tmp = pd.DataFrame(new_property_edge_row)\n",
    "          edges_df = pd.concat([edges_df, tmp], ignore_index=True)\n",
    "\n",
    "      else:\n",
    "        referenced_node_id = nodes_df[(nodes_df['Label']==referenced_table_name) &  (nodes_df['ID']==reference_id)].index[0] \n",
    "        \n",
    "        new_property_edge_row = [{'From': referenced_node_id , 'To':  property_node_index,'From_Table':referenced_table_name.capitalize(), 'To_Table': property_name.capitalize()\n",
    "                                 ,'Weight': 42,'Edge_Name': \"Related_To\"}]\n",
    "        tmp = pd.DataFrame(new_property_edge_row)\n",
    "        edges_df = pd.concat([edges_df, tmp], ignore_index=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "b71417e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "products_df = All_dfs[\"products\"]\n",
    "manufacturing_df = All_dfs[\"manufacturing\"]\n",
    "supplier_df = All_dfs[\"supplier\"]\n",
    "\n",
    "for i, manufacturing_row in manufacturing_df.iterrows():\n",
    "    factory_id = manufacturing_row[\"Factory_id\"]\n",
    "    supplier_node_index = nodes_df.query(f\"(Label == 'supplier' ) and (ID == {factory_id}) \").index[0]\n",
    "    \n",
    "    product_id = manufacturing_row[\"Product_id\"]\n",
    "    product_node_index = nodes_df.query(f\"(Label == 'products' ) and (ID == {product_id}) \").index[0]\n",
    "\n",
    "    new_edge_row = [{'From': supplier_node_index , 'To':  product_node_index,'From_Table':\"supplier\".capitalize(), 'To_Table': \"products\".capitalize()\n",
    "                    ,'Weight': 42,'Edge_Name': \"Manufactures\"}]\n",
    "    tmp = pd.DataFrame(new_edge_row)\n",
    "    edges_df = pd.concat([edges_df, tmp], ignore_index=True)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "e830ee84",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['customer', 'externalservices', 'externaltransactions', 'facilities', 'internalservices', 'internaltransactions', 'manufacturing', 'products', 'retailer', 'supplier', 'warehouses', 'rcextship', 'scextship', 'srintship', 'ssintship', 'rcextorders', 'scextorders', 'srintorders', 'ssintorders'])"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "cb8c0040",
   "metadata": {},
   "outputs": [],
   "source": [
    "ss_internal_orders_df = All_dfs[\"ssintorders\"]\n",
    "for i, ss_internal_order_row in ss_internal_orders_df.iterrows():\n",
    "    \n",
    "    att = {}\n",
    "    for column_name in ss_internal_orders_df:\n",
    "        if(column_name not in fk[\"ssintorders\"].keys() and column_name != pk[\"ssintorders\"] ):\n",
    "            att[column_name] = ss_internal_order_row[column_name]\n",
    "    \n",
    "    newRow = [{'Label': \"ssintorders\" , 'ID': ss_internal_orders_df.iloc[index,0] , 'Attributes': att}]\n",
    "\n",
    "    tmp = pd.DataFrame(newRow)\n",
    "    nodes_df = pd.concat([nodes_df, tmp], ignore_index=True)\n",
    "    \n",
    "    internal_order_index = len(nodes_df)-1\n",
    "    \n",
    "    internal_shipment_id = ss_internal_order_row[\"IntShip_id\"]\n",
    "    internal_shipment_node_index = nodes_df.query(f\"(Label == 'ssintship' ) and (ID == {internal_shipment_id}) \").index[0]\n",
    "\n",
    "    \n",
    "    product_id = ss_internal_order_row[\"prod_id\"]\n",
    "    product_node_index = nodes_df.query(f\"(Label == 'products' ) and (ID == {product_id}) \").index[0]\n",
    "    \n",
    "    \n",
    "    # from ---> edge \n",
    "    new_from_edge_row = [{'From': internal_shipment_node_index , 'To':internal_order_index,'From_Table': \"ssintship\".capitalize(),'To_Table': \"ssintorders\".capitalize()\n",
    "                              ,'Weight': 42,'Edge_Name': \"Order\"}]\n",
    "    tmp = pd.DataFrame(new_from_edge_row)\n",
    "    edges_df = pd.concat([edges_df, tmp], ignore_index=True)\n",
    "    # edge --->to\n",
    "    new_to_edge_row = [{'From':  internal_order_index, 'To': product_node_index , 'From_Table':\"ssintorders\".capitalize(), 'To_Table': \"products\".capitalize()\n",
    "                       ,'Weight': 42,'Edge_Name': \"Orders_Prodcut\"}]\n",
    "    tmp = pd.DataFrame(new_to_edge_row)\n",
    "    edges_df = pd.concat([edges_df, tmp], ignore_index=True)\n",
    "\n",
    "    \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "78aa6a18",
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Not Generic At All:\n",
    "# # looping over each row in internal shipments and checking if it belongs to manufacturing\n",
    "# internal_shipments_df = All_dfs[\"ssintship\"]\n",
    "# products_df = All_dfs[\"products\"]\n",
    "# manufacturing_df = All_dfs[\"manufacturing\"]\n",
    "\n",
    "# for i, internal_shipments_row in internal_shipments_df.iterrows():\n",
    "#     internal_shipments_ID = internal_shipments_row[0]\n",
    "#     from_id = internal_shipments_row[1]\n",
    "#     to_id = internal_shipments_row[2]\n",
    "#     factory_records = manufacturing_df.query(f\"Factory_id == {to_id}\")\n",
    "#     for j,manufacturing_row in factory_records.iterrows(): \n",
    "#         if from_id in manufacturing_row[1]:\n",
    "#             product_id = manufacturing_row[2]\n",
    "#             product_node_index = nodes_df.query(f\"(Label == 'products' ) and (ID == {product_id}) \").index[0]\n",
    "#             internal_shipment_node_index = nodes_df.query(f\"(Label == 'ssintship' ) and (ID == {internal_shipments_ID}) \").index[0]\n",
    "#             new_edge_row = [{'From': product_node_index , 'To':  internal_shipment_node_index,'From_Table':\"products\".capitalize(), 'To_Table': \"ssintship\".capitalize()}]\n",
    "#             tmp = pd.DataFrame(new_edge_row)\n",
    "#             edges_df = pd.concat([edges_df, tmp], ignore_index=True)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "12d88df2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "facilities              2000\n",
       "warehouses              1000\n",
       "ssintorders              548\n",
       "ssintship                537\n",
       "products                 500\n",
       "supplier                 300\n",
       "customer                 150\n",
       "retailer                 150\n",
       "internaltransactions     100\n",
       "externalservices         100\n",
       "internalservices         100\n",
       "externaltransactions     100\n",
       "srintorders               74\n",
       "rcextorders               52\n",
       "rcextship                 51\n",
       "srintship                 51\n",
       "scextship                 49\n",
       "scextorders               48\n",
       "Name: Label, dtype: int64"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes_df.Label.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "f6b4997f",
   "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>From</th>\n",
       "      <th>To</th>\n",
       "      <th>From_Table</th>\n",
       "      <th>To_Table</th>\n",
       "      <th>Weight</th>\n",
       "      <th>Edge_Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>390</td>\n",
       "      <td>1600</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1600</td>\n",
       "      <td>314</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>309</td>\n",
       "      <td>1601</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1601</td>\n",
       "      <td>222</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>276</td>\n",
       "      <td>1602</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8115</th>\n",
       "      <td>5907</td>\n",
       "      <td>4754</td>\n",
       "      <td>Ssintorders</td>\n",
       "      <td>Products</td>\n",
       "      <td>42</td>\n",
       "      <td>Orders_Prodcut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8116</th>\n",
       "      <td>2135</td>\n",
       "      <td>5908</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>Ssintorders</td>\n",
       "      <td>42</td>\n",
       "      <td>Order</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8117</th>\n",
       "      <td>5908</td>\n",
       "      <td>4896</td>\n",
       "      <td>Ssintorders</td>\n",
       "      <td>Products</td>\n",
       "      <td>42</td>\n",
       "      <td>Orders_Prodcut</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8118</th>\n",
       "      <td>2136</td>\n",
       "      <td>5909</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>Ssintorders</td>\n",
       "      <td>42</td>\n",
       "      <td>Order</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8119</th>\n",
       "      <td>5909</td>\n",
       "      <td>5232</td>\n",
       "      <td>Ssintorders</td>\n",
       "      <td>Products</td>\n",
       "      <td>42</td>\n",
       "      <td>Orders_Prodcut</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8120 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      From    To   From_Table     To_Table Weight       Edge_Name\n",
       "0      390  1600     Supplier    Ssintship     42       ssintship\n",
       "1     1600   314    Ssintship     Supplier     42       ssintship\n",
       "2      309  1601     Supplier    Ssintship     42       ssintship\n",
       "3     1601   222    Ssintship     Supplier     42       ssintship\n",
       "4      276  1602     Supplier    Ssintship     42       ssintship\n",
       "...    ...   ...          ...          ...    ...             ...\n",
       "8115  5907  4754  Ssintorders     Products     42  Orders_Prodcut\n",
       "8116  2135  5908    Ssintship  Ssintorders     42           Order\n",
       "8117  5908  4896  Ssintorders     Products     42  Orders_Prodcut\n",
       "8118  2136  5909    Ssintship  Ssintorders     42           Order\n",
       "8119  5909  5232  Ssintorders     Products     42  Orders_Prodcut\n",
       "\n",
       "[8120 rows x 6 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "edges_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "2011c1c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# draw_graph_pyvis(nodes_df,edges_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c0073eb5",
   "metadata": {},
   "source": [
    "[Neo 4j Function](#Neo4jFunction)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9db321d8",
   "metadata": {},
   "source": [
    "<a id=\"Neo4jCall\"></a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "173378a9",
   "metadata": {},
   "outputs": [],
   "source": [
    "draw_graph_Neo4j(nodes_df,edges_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "8a9683bf",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "customer 150\n"
     ]
    },
    {
     "ename": "NameError",
     "evalue": "name 'all_edges' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Input \u001b[1;32mIn [59]\u001b[0m, in \u001b[0;36m<cell line: 3>\u001b[1;34m()\u001b[0m\n\u001b[0;32m      5\u001b[0m \u001b[38;5;28mprint\u001b[39m(key, table_size)\n\u001b[0;32m      6\u001b[0m sum_nodes \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m table_size\n\u001b[1;32m----> 7\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m(key \u001b[38;5;129;01min\u001b[39;00m \u001b[43mall_edges\u001b[49m):\n\u001b[0;32m      8\u001b[0m     sum_edges \u001b[38;5;241m+\u001b[39m\u001b[38;5;241m=\u001b[39m table_size\u001b[38;5;241m*\u001b[39m\u001b[38;5;241m2\u001b[39m\n\u001b[0;32m      9\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m(key \u001b[38;5;129;01min\u001b[39;00m all_properties ):\n",
      "\u001b[1;31mNameError\u001b[0m: name 'all_edges' is not defined"
     ]
    }
   ],
   "source": [
    "sum_nodes = 0\n",
    "sum_edges = 0\n",
    "for key in All_dfs:\n",
    "    table_size = len(All_dfs[key])\n",
    "    print(key, table_size)\n",
    "    sum_nodes += table_size\n",
    "    if(key in all_edges):\n",
    "        sum_edges += table_size*2\n",
    "    if(key in all_properties ):\n",
    "        sum_edges += table_size\n",
    "print(\"Number Of Nodes: \",sum_nodes)\n",
    "print(\"Number Of Edges: \",sum_edges)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Copy of Data Transformation.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3.10.6 64-bit (windows store)",
   "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.6"
  },
  "vscode": {
   "interpreter": {
    "hash": "22b5395d0f79c24449484fb97d267bdaf9acb47036c1f25b56bcb8d4d0c52548"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}