DataAnalysis.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": "03d0090f",
   "metadata": {},
   "source": [
    "### Using Naming Conventions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "b0cf02e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_using_naming():\n",
    "    nodesNames = ('supplier', 'customer',  'retailer','service providers', 'distributors', 'sales channels', 'consumers', 'producers', 'vendors', 'transportation', 'wholesaler', 'distribution center', 'warehouses')\n",
    "\n",
    "    for name in list(All_dfs.keys()):\n",
    "        lowerCasedName = name.lower()\n",
    "        if(lowerCasedName in nodesNames):\n",
    "            nodes.add(lowerCasedName)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2e5fc642",
   "metadata": {
    "id": "2e5fc642"
   },
   "source": [
    "### Find Each Table's primary key"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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": 4,
   "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": 5,
   "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": 6,
   "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": 7,
   "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",
    "\n",
    "def add_nodesV2(nodeNames, nodesTB):\n",
    "    for node in nodeNames:\n",
    "        print(node)\n",
    "        dfNumpy =  All_dfs[node].to_numpy()\n",
    "        # All_dfs.pop(node)\n",
    "        allDfsKeys.remove(node)\n",
    "        for row in dfNumpy:\n",
    "            newRow = [{'Label': node, 'ID': row[0] , 'Attributes': row[1:]}]\n",
    "            tmp = pd.DataFrame(newRow)\n",
    "            nodesTB = pd.concat([nodesTB, tmp], ignore_index=True)\n",
    "    return nodesTB\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fab162b7",
   "metadata": {
    "id": "fab162b7"
   },
   "source": [
    "### Create Edges Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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\n",
    "\n",
    "def add_edgesV2(edges,edgesTable,pk,fk,ref_in):\n",
    "    for e in edges:\n",
    "        df = All_dfs[e] #df of the cur edge\n",
    "        dfNumpy =  df.to_numpy()\n",
    "        for row in dfNumpy:\n",
    "            label = list(edges[e].keys())\n",
    "            indexOfFromColumn =  list(df.columns).index(label[0])\n",
    "            from_id = nodesTableV1[(nodesTableV1[\"Label\"] == edges[e][label[0]]) & (row[indexOfFromColumn] == nodesTableV1[\"ID\"])]\n",
    "            from_id = from_id.index[0]\n",
    "            indexToFromColumn =  list(df.columns).index(label[1])\n",
    "            to_id = nodesTableV1[(nodesTableV1[\"Label\"] == edges[e][label[1]]) & (row[indexToFromColumn] == nodesTableV1[\"ID\"])]\n",
    "            to_id = to_id.index[0]\n",
    "            pk_col = pk[e]\n",
    "            primary_key = row[list(df.columns).index(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",
    "            edgesTableV1 = pd.concat([edgesTableV1, 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": 9,
   "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": 10,
   "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": 11,
   "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": 12,
   "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": 13,
   "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": 14,
   "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": 15,
   "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": 16,
   "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": 17,
   "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": 18,
   "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": 19,
   "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": 20,
   "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": 21,
   "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": 22,
   "id": "964662f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype('O')"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs['manufacturing']['Different_suppliers'].dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "ab6ec34b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 23,
     "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": 24,
   "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": 25,
   "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": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "initial_internalshipments_df = All_dfs[\"internalshipments\"]\n",
    "initial_internalshipments_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "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": 27,
   "id": "4fe5f572",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "522"
      ]
     },
     "execution_count": 27,
     "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": 28,
   "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": 29,
   "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": 30,
   "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": 31,
   "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": 31,
     "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": 32,
   "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": 33,
   "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": 34,
   "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": 35,
   "id": "c190b061",
   "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": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs['ssintorders']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "4fbc9990",
   "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": 37,
   "id": "5f0abdf9",
   "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>prod_id</th>\n",
       "      <th>product_type</th>\n",
       "      <th>product_name</th>\n",
       "      <th>warehouses</th>\n",
       "      <th>price</th>\n",
       "      <th>profit_margin (%)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>911</td>\n",
       "      <td>M</td>\n",
       "      <td>WWW</td>\n",
       "      <td>[5498, 6164, 5623, 7418, 8962, 7721, 4208, 871...</td>\n",
       "      <td>3088.68</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>316</td>\n",
       "      <td>F</td>\n",
       "      <td>MMM</td>\n",
       "      <td>[3865, 5351]</td>\n",
       "      <td>107.39</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>549</td>\n",
       "      <td>P</td>\n",
       "      <td>LLL</td>\n",
       "      <td>[1969]</td>\n",
       "      <td>47.14</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>801</td>\n",
       "      <td>V</td>\n",
       "      <td>SSS</td>\n",
       "      <td>[3613, 4412, 2730, 1482, 2430, 7465, 8954, 118...</td>\n",
       "      <td>13.62</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>791</td>\n",
       "      <td>R</td>\n",
       "      <td>AAA</td>\n",
       "      <td>[3514, 9292, 9388, 6379, 1149, 1526, 6928, 1888]</td>\n",
       "      <td>6.35</td>\n",
       "      <td>7</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>495</th>\n",
       "      <td>835</td>\n",
       "      <td>D</td>\n",
       "      <td>KKK</td>\n",
       "      <td>[1835, 4047, 2155, 2969, 1249, 9807]</td>\n",
       "      <td>8530.71</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>496</th>\n",
       "      <td>323</td>\n",
       "      <td>C</td>\n",
       "      <td>III</td>\n",
       "      <td>[5381, 7237, 3468, 5177, 2881]</td>\n",
       "      <td>2310.31</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>497</th>\n",
       "      <td>742</td>\n",
       "      <td>M</td>\n",
       "      <td>OOO</td>\n",
       "      <td>[6283, 3413]</td>\n",
       "      <td>6080.30</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>498</th>\n",
       "      <td>412</td>\n",
       "      <td>X</td>\n",
       "      <td>III</td>\n",
       "      <td>[7377, 2002, 8299, 1735, 5966, 6548, 4501, 752...</td>\n",
       "      <td>21.64</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>499</th>\n",
       "      <td>711</td>\n",
       "      <td>X</td>\n",
       "      <td>MMM</td>\n",
       "      <td>[8154, 5970, 7209, 7614]</td>\n",
       "      <td>90.82</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>500 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     prod_id product_type product_name  \\\n",
       "0        911            M          WWW   \n",
       "1        316            F          MMM   \n",
       "2        549            P          LLL   \n",
       "3        801            V          SSS   \n",
       "4        791            R          AAA   \n",
       "..       ...          ...          ...   \n",
       "495      835            D          KKK   \n",
       "496      323            C          III   \n",
       "497      742            M          OOO   \n",
       "498      412            X          III   \n",
       "499      711            X          MMM   \n",
       "\n",
       "                                            warehouses    price  \\\n",
       "0    [5498, 6164, 5623, 7418, 8962, 7721, 4208, 871...  3088.68   \n",
       "1                                         [3865, 5351]   107.39   \n",
       "2                                               [1969]    47.14   \n",
       "3    [3613, 4412, 2730, 1482, 2430, 7465, 8954, 118...    13.62   \n",
       "4     [3514, 9292, 9388, 6379, 1149, 1526, 6928, 1888]     6.35   \n",
       "..                                                 ...      ...   \n",
       "495               [1835, 4047, 2155, 2969, 1249, 9807]  8530.71   \n",
       "496                     [5381, 7237, 3468, 5177, 2881]  2310.31   \n",
       "497                                       [6283, 3413]  6080.30   \n",
       "498  [7377, 2002, 8299, 1735, 5966, 6548, 4501, 752...    21.64   \n",
       "499                           [8154, 5970, 7209, 7614]    90.82   \n",
       "\n",
       "     profit_margin (%)  \n",
       "0                   24  \n",
       "1                   47  \n",
       "2                    7  \n",
       "3                    6  \n",
       "4                    7  \n",
       "..                 ...  \n",
       "495                 24  \n",
       "496                 30  \n",
       "497                 13  \n",
       "498                 44  \n",
       "499                 11  \n",
       "\n",
       "[500 rows x 6 columns]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs['products']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "b0aabebe",
   "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>291</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>140</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>519</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>418</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>831</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>752</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>911</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>533</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      291  \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      140  \n",
       "4      1975-10-29    83.16  Out For Delivery      519  \n",
       "..            ...      ...               ...      ...  \n",
       "543    1993-06-01    38.90            Closed      418  \n",
       "544    1993-06-01    38.90            Closed      831  \n",
       "545    1993-06-01    38.90            Closed      752  \n",
       "546    1993-06-01    38.90            Closed      911  \n",
       "547    1993-06-01    38.90            Closed      533  \n",
       "\n",
       "[548 rows x 9 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs['ssintorders']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d738a7f",
   "metadata": {},
   "source": [
    "### Using Naming Convention to create Nodes and Edges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "3177ec49",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['customer', 'externalservices', 'externaltransactions', 'facilities', 'internalservices', 'internaltransactions', 'manufacturing', 'products', 'retailer', 'supplier', 'warehouses', 'rcextship', 'scextship', 'srintship', 'ssintship', 'rcextorders', 'scextorders', 'srintorders', 'ssintorders']\n"
     ]
    }
   ],
   "source": [
    "allDfsKeys = list(All_dfs.keys())\n",
    "print(allDfsKeys)\n",
    "\n",
    "convert_using_naming()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "b61e3beb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['customer',\n",
       " 'externalservices',\n",
       " 'externaltransactions',\n",
       " 'facilities',\n",
       " 'internalservices',\n",
       " 'internaltransactions',\n",
       " 'manufacturing',\n",
       " 'products',\n",
       " 'retailer',\n",
       " 'supplier',\n",
       " 'warehouses',\n",
       " 'rcextship',\n",
       " 'scextship',\n",
       " 'srintship',\n",
       " 'ssintship',\n",
       " 'rcextorders',\n",
       " 'scextorders',\n",
       " 'srintorders',\n",
       " 'ssintorders']"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "allDfsKeys"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "44151839",
   "metadata": {
    "id": "44151839"
   },
   "source": [
    "### Initialize fk and ref_in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "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": 42,
   "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": 42,
     "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": 43,
   "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": 43,
     "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": 44,
   "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": 44,
     "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": 45,
   "id": "e5ca1421",
   "metadata": {
    "id": "e5ca1421"
   },
   "outputs": [],
   "source": [
    "nodes_edges(fk)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "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": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "edges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "properties"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16e305b5",
   "metadata": {
    "id": "16e305b5"
   },
   "source": [
    "### Create NodesTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "bcd83ca2",
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "bcd83ca2",
    "outputId": "53ab6f8b-8dbe-4019-95c9-e697929c2d52"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "customer\n",
      "supplier\n",
      "warehouses\n",
      "retailer\n"
     ]
    },
    {
     "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>[F, Nadia, Scheel, Cabo Verde, Cabo Verde, Pro...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>customer</td>\n",
       "      <td>42959</td>\n",
       "      <td>[M, Eric, David, Dominica, Italy, Scientist, c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>customer</td>\n",
       "      <td>50653</td>\n",
       "      <td>[F, Alessia, Villegas, Virgin Islands, British...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>customer</td>\n",
       "      <td>82485</td>\n",
       "      <td>[M, Alexei, Wulf, Samoa, Samoa, Scientist, aud...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>customer</td>\n",
       "      <td>56609</td>\n",
       "      <td>[F, Brenda, Fox, Poland, Poland, actuaire, Wid...</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>retailer</td>\n",
       "      <td>86308</td>\n",
       "      <td>[Ukraine, Chernihivska oblast, vendor, 987, 4,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1596</th>\n",
       "      <td>retailer</td>\n",
       "      <td>18464</td>\n",
       "      <td>[Haiti, Grandans, customer end, 295, 2, 5, 168...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1597</th>\n",
       "      <td>retailer</td>\n",
       "      <td>57039</td>\n",
       "      <td>[Lao People's Democratic Republic, Viangchan, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598</th>\n",
       "      <td>retailer</td>\n",
       "      <td>33250</td>\n",
       "      <td>[South Sudan, Lakes, customer end, 318, 4, 6, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1599</th>\n",
       "      <td>retailer</td>\n",
       "      <td>21789</td>\n",
       "      <td>[Cyprus, Ammochostos, distributer, 907, 3, 5, ...</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  [F, Nadia, Scheel, Cabo Verde, Cabo Verde, Pro...\n",
       "1     customer  42959  [M, Eric, David, Dominica, Italy, Scientist, c...\n",
       "2     customer  50653  [F, Alessia, Villegas, Virgin Islands, British...\n",
       "3     customer  82485  [M, Alexei, Wulf, Samoa, Samoa, Scientist, aud...\n",
       "4     customer  56609  [F, Brenda, Fox, Poland, Poland, actuaire, Wid...\n",
       "...        ...    ...                                                ...\n",
       "1595  retailer  86308  [Ukraine, Chernihivska oblast, vendor, 987, 4,...\n",
       "1596  retailer  18464  [Haiti, Grandans, customer end, 295, 2, 5, 168...\n",
       "1597  retailer  57039  [Lao People's Democratic Republic, Viangchan, ...\n",
       "1598  retailer  33250  [South Sudan, Lakes, customer end, 318, 4, 6, ...\n",
       "1599  retailer  21789  [Cyprus, Ammochostos, distributer, 907, 3, 5, ...\n",
       "\n",
       "[1600 rows x 3 columns]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# nodesTable = add_nodes(nodes,nodesTable)\n",
    "\n",
    "nodesTable = add_nodesV2(nodes, nodesTable)\n",
    "\n",
    "nodesTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "1f846299",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "warehouses    1000\n",
       "supplier       300\n",
       "customer       150\n",
       "retailer       150\n",
       "Name: Label, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodesTable[\"Label\"].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92bfeb8a",
   "metadata": {
    "id": "92bfeb8a"
   },
   "source": [
    "### Create Edges Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "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>334</td>\n",
       "      <td>140</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>276</td>\n",
       "      <td>72</td>\n",
       "      <td>[{'ExtServ_id': 8658, 'placed_when': '1998-06-...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>315</td>\n",
       "      <td>2</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>224</td>\n",
       "      <td>137</td>\n",
       "      <td>[]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>325</td>\n",
       "      <td>92</td>\n",
       "      <td>[{'ExtServ_id': 7764, 'placed_when': '1973-08-...</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>285</td>\n",
       "      <td>340</td>\n",
       "      <td>[{'IntOrders_id': 10448, 'quantity': 766, 'pla...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>884</th>\n",
       "      <td>449</td>\n",
       "      <td>340</td>\n",
       "      <td>[{'IntOrders_id': 10449, 'quantity': 766, 'pla...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>885</th>\n",
       "      <td>343</td>\n",
       "      <td>198</td>\n",
       "      <td>[{'IntOrders_id': 10450, 'quantity': 766, 'pla...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>886</th>\n",
       "      <td>280</td>\n",
       "      <td>198</td>\n",
       "      <td>[{'IntOrders_id': 10451, 'quantity': 766, 'pla...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>887</th>\n",
       "      <td>443</td>\n",
       "      <td>198</td>\n",
       "      <td>[{'IntOrders_id': 10452, 'quantity': 766, 'pla...</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            334        140                                                 []\n",
       "1            276         72  [{'ExtServ_id': 8658, 'placed_when': '1998-06-...\n",
       "2            315          2                                                 []\n",
       "3            224        137                                                 []\n",
       "4            325         92  [{'ExtServ_id': 7764, 'placed_when': '1973-08-...\n",
       "..           ...        ...                                                ...\n",
       "883          285        340  [{'IntOrders_id': 10448, 'quantity': 766, 'pla...\n",
       "884          449        340  [{'IntOrders_id': 10449, 'quantity': 766, 'pla...\n",
       "885          343        198  [{'IntOrders_id': 10450, 'quantity': 766, 'pla...\n",
       "886          280        198  [{'IntOrders_id': 10451, 'quantity': 766, 'pla...\n",
       "887          443        198  [{'IntOrders_id': 10452, 'quantity': 766, 'pla...\n",
       "\n",
       "[888 rows x 3 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "edgesTable = add_edges(edges, edgesTable,pk,fk,ref_in)\n",
    "edgesTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "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": 53,
   "id": "9c727854",
   "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>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>retailer</td>\n",
       "      <td>86308</td>\n",
       "      <td>{'country': 'Ukraine', 'city_name': 'Chernihiv...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1596</th>\n",
       "      <td>retailer</td>\n",
       "      <td>18464</td>\n",
       "      <td>{'country': 'Haiti', 'city_name': 'Grandans', ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1597</th>\n",
       "      <td>retailer</td>\n",
       "      <td>57039</td>\n",
       "      <td>{'country': 'Lao People's Democratic Republic'...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1598</th>\n",
       "      <td>retailer</td>\n",
       "      <td>33250</td>\n",
       "      <td>{'country': 'South Sudan', 'city_name': 'Lakes...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1599</th>\n",
       "      <td>retailer</td>\n",
       "      <td>21789</td>\n",
       "      <td>{'country': 'Cyprus', 'city_name': 'Ammochosto...</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  retailer  86308  {'country': 'Ukraine', 'city_name': 'Chernihiv...\n",
       "1596  retailer  18464  {'country': 'Haiti', 'city_name': 'Grandans', ...\n",
       "1597  retailer  57039  {'country': 'Lao People's Democratic Republic'...\n",
       "1598  retailer  33250  {'country': 'South Sudan', 'city_name': 'Lakes...\n",
       "1599  retailer  21789  {'country': 'Cyprus', 'city_name': 'Ammochosto...\n",
       "\n",
       "[1600 rows x 3 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "9c2ee0f5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "warehouses    1000\n",
       "supplier       300\n",
       "customer       150\n",
       "retailer       150\n",
       "Name: Label, dtype: int64"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes_df['Label'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "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": 56,
   "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>334</td>\n",
       "      <td>1600</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1600</td>\n",
       "      <td>140</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>Customer</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>276</td>\n",
       "      <td>1601</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1601</td>\n",
       "      <td>72</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>Customer</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>315</td>\n",
       "      <td>1602</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</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>198</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1772</th>\n",
       "      <td>280</td>\n",
       "      <td>2486</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1773</th>\n",
       "      <td>2486</td>\n",
       "      <td>198</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1774</th>\n",
       "      <td>443</td>\n",
       "      <td>2487</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1775</th>\n",
       "      <td>2487</td>\n",
       "      <td>198</td>\n",
       "      <td>Ssintship</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>42</td>\n",
       "      <td>ssintship</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      334  1600              Supplier  Externaltransactions     42   \n",
       "1     1600   140  Externaltransactions              Customer     42   \n",
       "2      276  1601              Supplier  Externaltransactions     42   \n",
       "3     1601    72  Externaltransactions              Customer     42   \n",
       "4      315  1602              Supplier  Externaltransactions     42   \n",
       "...    ...   ...                   ...                   ...    ...   \n",
       "1771  2485   198             Ssintship              Supplier     42   \n",
       "1772   280  2486              Supplier             Ssintship     42   \n",
       "1773  2486   198             Ssintship              Supplier     42   \n",
       "1774   443  2487              Supplier             Ssintship     42   \n",
       "1775  2487   198             Ssintship              Supplier     42   \n",
       "\n",
       "                 Edge_Name  \n",
       "0     externaltransactions  \n",
       "1     externaltransactions  \n",
       "2     externaltransactions  \n",
       "3     externaltransactions  \n",
       "4     externaltransactions  \n",
       "...                    ...  \n",
       "1771             ssintship  \n",
       "1772             ssintship  \n",
       "1773             ssintship  \n",
       "1774             ssintship  \n",
       "1775             ssintship  \n",
       "\n",
       "[1776 rows x 6 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "edges_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "JjImjta5GIKZ",
   "metadata": {
    "id": "JjImjta5GIKZ"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "facilities\n",
      "srintorders\n",
      "scextorders\n",
      "externalservices\n",
      "internalservices\n",
      "products\n",
      "rcextorders\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": 58,
   "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"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "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": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "All_dfs.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "cb8c0040",
   "metadata": {},
   "outputs": [],
   "source": [
    "ss_internal_orders_df = All_dfs[\"ssintorders\"]\n",
    "for index, 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": 61,
   "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": 66,
   "id": "12d88df2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5910, 3)"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "c72d4ddc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(8120, 6)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "edges_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "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>334</td>\n",
       "      <td>1600</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1600</td>\n",
       "      <td>140</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>Customer</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>276</td>\n",
       "      <td>1601</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1601</td>\n",
       "      <td>72</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>Customer</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>315</td>\n",
       "      <td>1602</td>\n",
       "      <td>Supplier</td>\n",
       "      <td>Externaltransactions</td>\n",
       "      <td>42</td>\n",
       "      <td>externaltransactions</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>4933</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>2486</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>4810</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>2487</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>5185</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  \\\n",
       "0      334  1600              Supplier  Externaltransactions     42   \n",
       "1     1600   140  Externaltransactions              Customer     42   \n",
       "2      276  1601              Supplier  Externaltransactions     42   \n",
       "3     1601    72  Externaltransactions              Customer     42   \n",
       "4      315  1602              Supplier  Externaltransactions     42   \n",
       "...    ...   ...                   ...                   ...    ...   \n",
       "8115  5907  4933           Ssintorders              Products     42   \n",
       "8116  2486  5908             Ssintship           Ssintorders     42   \n",
       "8117  5908  4810           Ssintorders              Products     42   \n",
       "8118  2487  5909             Ssintship           Ssintorders     42   \n",
       "8119  5909  5185           Ssintorders              Products     42   \n",
       "\n",
       "                 Edge_Name  \n",
       "0     externaltransactions  \n",
       "1     externaltransactions  \n",
       "2     externaltransactions  \n",
       "3     externaltransactions  \n",
       "4     externaltransactions  \n",
       "...                    ...  \n",
       "8115        Orders_Prodcut  \n",
       "8116                 Order  \n",
       "8117        Orders_Prodcut  \n",
       "8118                 Order  \n",
       "8119        Orders_Prodcut  \n",
       "\n",
       "[8120 rows x 6 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "edges_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "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": "code",
   "execution_count": 65,
   "id": "173378a9",
   "metadata": {},
   "outputs": [
    {
     "ename": "ServiceUnavailable",
     "evalue": "Couldn't connect to localhost:7687 (resolved to ('[::1]:7687', '127.0.0.1:7687')):\nFailed to establish connection to ResolvedIPv6Address(('::1', 7687, 0, 0)) (reason [WinError 10061] No connection could be made because the target machine actively refused it)\nFailed to establish connection to ResolvedIPv4Address(('127.0.0.1', 7687)) (reason [WinError 10061] No connection could be made because the target machine actively refused it)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mConnectionRefusedError\u001b[0m                    Traceback (most recent call last)",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\io\\_socket.py:147\u001b[0m, in \u001b[0;36mBoltSocket._connect\u001b[1;34m(cls, resolved_address, timeout, keep_alive)\u001b[0m\n\u001b[0;32m    146\u001b[0m log\u001b[39m.\u001b[39mdebug(\u001b[39m\"\u001b[39m\u001b[39m[#0000]  C: <OPEN> \u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m\"\u001b[39m, resolved_address)\n\u001b[1;32m--> 147\u001b[0m s\u001b[39m.\u001b[39;49mconnect(resolved_address)\n\u001b[0;32m    148\u001b[0m s\u001b[39m.\u001b[39msettimeout(t)\n",
      "\u001b[1;31mConnectionRefusedError\u001b[0m: [WinError 10061] No connection could be made because the target machine actively refused it",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mServiceUnavailable\u001b[0m                        Traceback (most recent call last)",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\io\\_socket.py:284\u001b[0m, in \u001b[0;36mBoltSocket.connect\u001b[1;34m(cls, address, timeout, custom_resolver, ssl_context, keep_alive)\u001b[0m\n\u001b[0;32m    283\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m--> 284\u001b[0m     s \u001b[39m=\u001b[39m BoltSocket\u001b[39m.\u001b[39;49m_connect(resolved_address, timeout, keep_alive)\n\u001b[0;32m    285\u001b[0m     s \u001b[39m=\u001b[39m BoltSocket\u001b[39m.\u001b[39m_secure(s, resolved_address\u001b[39m.\u001b[39mhost_name,\n\u001b[0;32m    286\u001b[0m                            ssl_context)\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\io\\_socket.py:164\u001b[0m, in \u001b[0;36mBoltSocket._connect\u001b[1;34m(cls, resolved_address, timeout, keep_alive)\u001b[0m\n\u001b[0;32m    163\u001b[0m s\u001b[39m.\u001b[39mclose()\n\u001b[1;32m--> 164\u001b[0m \u001b[39mraise\u001b[39;00m ServiceUnavailable(\n\u001b[0;32m    165\u001b[0m     \u001b[39m\"\u001b[39m\u001b[39mFailed to establish connection to \u001b[39m\u001b[39m{!r}\u001b[39;00m\u001b[39m (reason \u001b[39m\u001b[39m{}\u001b[39;00m\u001b[39m)\u001b[39m\u001b[39m\"\u001b[39m\u001b[39m.\u001b[39mformat(\n\u001b[0;32m    166\u001b[0m         resolved_address, error))\n",
      "\u001b[1;31mServiceUnavailable\u001b[0m: Failed to establish connection to ResolvedIPv6Address(('::1', 7687, 0, 0)) (reason [WinError 10061] No connection could be made because the target machine actively refused it)",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mServiceUnavailable\u001b[0m                        Traceback (most recent call last)",
      "\u001b[1;32mc:\\Users\\Omar Galal\\Desktop\\data_analysis_internship\\DataAnalysis.ipynb Cell 101\u001b[0m in \u001b[0;36m<cell line: 1>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> <a href='vscode-notebook-cell:/c%3A/Users/Omar%20Galal/Desktop/data_analysis_internship/DataAnalysis.ipynb#Y161sZmlsZQ%3D%3D?line=0'>1</a>\u001b[0m draw_graph_Neo4j(nodes_df,edges_df)\n",
      "\u001b[1;32mc:\\Users\\Omar Galal\\Desktop\\data_analysis_internship\\DataAnalysis.ipynb Cell 101\u001b[0m in \u001b[0;36mdraw_graph_Neo4j\u001b[1;34m(nodes_table, edges_table)\u001b[0m\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/Omar%20Galal/Desktop/data_analysis_internship/DataAnalysis.ipynb#Y161sZmlsZQ%3D%3D?line=15'>16</a>\u001b[0m     neo4j_create_statement \u001b[39m=\u001b[39m create_relation_statement(edge)\n\u001b[0;32m     <a href='vscode-notebook-cell:/c%3A/Users/Omar%20Galal/Desktop/data_analysis_internship/DataAnalysis.ipynb#Y161sZmlsZQ%3D%3D?line=16'>17</a>\u001b[0m     transaction_execution_commands\u001b[39m.\u001b[39mappend(neo4j_create_statement)\n\u001b[1;32m---> <a href='vscode-notebook-cell:/c%3A/Users/Omar%20Galal/Desktop/data_analysis_internship/DataAnalysis.ipynb#Y161sZmlsZQ%3D%3D?line=19'>20</a>\u001b[0m execute_transactions(transaction_execution_commands)\n",
      "\u001b[1;32mc:\\Users\\Omar Galal\\Desktop\\data_analysis_internship\\DataAnalysis.ipynb Cell 101\u001b[0m in \u001b[0;36mexecute_transactions\u001b[1;34m(transaction_execution_commands)\u001b[0m\n\u001b[0;32m      <a href='vscode-notebook-cell:/c%3A/Users/Omar%20Galal/Desktop/data_analysis_internship/DataAnalysis.ipynb#Y161sZmlsZQ%3D%3D?line=2'>3</a>\u001b[0m session \u001b[39m=\u001b[39m data_base_connection\u001b[39m.\u001b[39msession()    \n\u001b[0;32m      <a href='vscode-notebook-cell:/c%3A/Users/Omar%20Galal/Desktop/data_analysis_internship/DataAnalysis.ipynb#Y161sZmlsZQ%3D%3D?line=3'>4</a>\u001b[0m \u001b[39mfor\u001b[39;00m i \u001b[39min\u001b[39;00m transaction_execution_commands:\n\u001b[1;32m----> <a href='vscode-notebook-cell:/c%3A/Users/Omar%20Galal/Desktop/data_analysis_internship/DataAnalysis.ipynb#Y161sZmlsZQ%3D%3D?line=4'>5</a>\u001b[0m     session\u001b[39m.\u001b[39;49mrun(i)\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\work\\simple.py:204\u001b[0m, in \u001b[0;36mSession.run\u001b[1;34m(self, query, parameters, **kwparameters)\u001b[0m\n\u001b[0;32m    201\u001b[0m     \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_autoResult\u001b[39m.\u001b[39m_buffer_all()  \u001b[39m# This will buffer upp all records for the previous auto-transaction\u001b[39;00m\n\u001b[0;32m    203\u001b[0m \u001b[39mif\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_connection:\n\u001b[1;32m--> 204\u001b[0m     \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_connect(\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_config\u001b[39m.\u001b[39;49mdefault_access_mode)\n\u001b[0;32m    205\u001b[0m cx \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_connection\n\u001b[0;32m    206\u001b[0m protocol_version \u001b[39m=\u001b[39m cx\u001b[39m.\u001b[39mPROTOCOL_VERSION\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\work\\simple.py:108\u001b[0m, in \u001b[0;36mSession._connect\u001b[1;34m(self, access_mode)\u001b[0m\n\u001b[0;32m    106\u001b[0m \u001b[39mif\u001b[39;00m access_mode \u001b[39mis\u001b[39;00m \u001b[39mNone\u001b[39;00m:\n\u001b[0;32m    107\u001b[0m     access_mode \u001b[39m=\u001b[39m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_config\u001b[39m.\u001b[39mdefault_access_mode\n\u001b[1;32m--> 108\u001b[0m \u001b[39msuper\u001b[39;49m()\u001b[39m.\u001b[39;49m_connect(access_mode)\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\work\\__init__.py:82\u001b[0m, in \u001b[0;36mWorkspace._connect\u001b[1;34m(self, access_mode)\u001b[0m\n\u001b[0;32m     68\u001b[0m     \u001b[39melse\u001b[39;00m:\n\u001b[0;32m     69\u001b[0m         \u001b[39m# This is the first time we open a connection to a server in a\u001b[39;00m\n\u001b[0;32m     70\u001b[0m         \u001b[39m# cluster environment for this session without explicitly\u001b[39;00m\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m     73\u001b[0m         \u001b[39m# we shall use this database explicitly for all subsequent\u001b[39;00m\n\u001b[0;32m     74\u001b[0m         \u001b[39m# actions within this session.\u001b[39;00m\n\u001b[0;32m     75\u001b[0m         \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_pool\u001b[39m.\u001b[39mupdate_routing_table(\n\u001b[0;32m     76\u001b[0m             database\u001b[39m=\u001b[39m\u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_config\u001b[39m.\u001b[39mdatabase,\n\u001b[0;32m     77\u001b[0m             imp_user\u001b[39m=\u001b[39m\u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_config\u001b[39m.\u001b[39mimpersonated_user,\n\u001b[1;32m   (...)\u001b[0m\n\u001b[0;32m     80\u001b[0m             database_callback\u001b[39m=\u001b[39m\u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_set_cached_database\n\u001b[0;32m     81\u001b[0m         )\n\u001b[1;32m---> 82\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_connection \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_pool\u001b[39m.\u001b[39;49macquire(\n\u001b[0;32m     83\u001b[0m     access_mode\u001b[39m=\u001b[39;49maccess_mode,\n\u001b[0;32m     84\u001b[0m     timeout\u001b[39m=\u001b[39;49mtimeout,\n\u001b[0;32m     85\u001b[0m     acquisition_timeout\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_config\u001b[39m.\u001b[39;49mconnection_acquisition_timeout,\n\u001b[0;32m     86\u001b[0m     database\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_config\u001b[39m.\u001b[39;49mdatabase,\n\u001b[0;32m     87\u001b[0m     bookmarks\u001b[39m=\u001b[39;49m\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_bookmarks\n\u001b[0;32m     88\u001b[0m )\n\u001b[0;32m     89\u001b[0m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39m_connection_access_mode \u001b[39m=\u001b[39m access_mode\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\io\\__init__.py:926\u001b[0m, in \u001b[0;36mBoltPool.acquire\u001b[1;34m(self, access_mode, timeout, acquisition_timeout, database, bookmarks)\u001b[0m\n\u001b[0;32m    922\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39macquire\u001b[39m(\u001b[39mself\u001b[39m, access_mode, timeout,  acquisition_timeout, database,\n\u001b[0;32m    923\u001b[0m             bookmarks):\n\u001b[0;32m    924\u001b[0m     \u001b[39m# The access_mode and database is not needed for a direct connection, its just there for consistency.\u001b[39;00m\n\u001b[0;32m    925\u001b[0m     deadline \u001b[39m=\u001b[39m merge_deadlines_and_timeouts(timeout, acquisition_timeout)\n\u001b[1;32m--> 926\u001b[0m     \u001b[39mreturn\u001b[39;00m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49m_acquire(\u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49maddress, deadline)\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\io\\__init__.py:801\u001b[0m, in \u001b[0;36mIOPool._acquire\u001b[1;34m(self, address, deadline)\u001b[0m\n\u001b[0;32m    793\u001b[0m         \u001b[39mif\u001b[39;00m (\n\u001b[0;32m    794\u001b[0m             timeout \u001b[39m==\u001b[39m \u001b[39m0\u001b[39m  \u001b[39m# deadline expired\u001b[39;00m\n\u001b[0;32m    795\u001b[0m             \u001b[39mor\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mcond\u001b[39m.\u001b[39mwait(timeout)\n\u001b[0;32m    796\u001b[0m         ):\n\u001b[0;32m    797\u001b[0m             \u001b[39mraise\u001b[39;00m ClientError(\n\u001b[0;32m    798\u001b[0m                 \u001b[39m\"\u001b[39m\u001b[39mFailed to obtain a connection from pool within \u001b[39m\u001b[39m{!r}\u001b[39;00m\u001b[39ms\u001b[39m\u001b[39m\"\u001b[39m\n\u001b[0;32m    799\u001b[0m                 \u001b[39m.\u001b[39mformat(deadline\u001b[39m.\u001b[39moriginal_timeout)\n\u001b[0;32m    800\u001b[0m             )\n\u001b[1;32m--> 801\u001b[0m \u001b[39mreturn\u001b[39;00m connection_creator()\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\io\\__init__.py:729\u001b[0m, in \u001b[0;36mIOPool._acquire_new_later.<locals>.connection_creator\u001b[1;34m()\u001b[0m\n\u001b[0;32m    727\u001b[0m \u001b[39mtry\u001b[39;00m:\n\u001b[0;32m    728\u001b[0m     \u001b[39mtry\u001b[39;00m:\n\u001b[1;32m--> 729\u001b[0m         connection \u001b[39m=\u001b[39m \u001b[39mself\u001b[39;49m\u001b[39m.\u001b[39;49mopener(\n\u001b[0;32m    730\u001b[0m             address, deadline\u001b[39m.\u001b[39;49mto_timeout()\n\u001b[0;32m    731\u001b[0m         )\n\u001b[0;32m    732\u001b[0m     \u001b[39mexcept\u001b[39;00m ServiceUnavailable:\n\u001b[0;32m    733\u001b[0m         \u001b[39mself\u001b[39m\u001b[39m.\u001b[39mdeactivate(address)\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\io\\__init__.py:907\u001b[0m, in \u001b[0;36mBoltPool.open.<locals>.opener\u001b[1;34m(addr, timeout)\u001b[0m\n\u001b[0;32m    906\u001b[0m \u001b[39mdef\u001b[39;00m \u001b[39mopener\u001b[39m(addr, timeout):\n\u001b[1;32m--> 907\u001b[0m     \u001b[39mreturn\u001b[39;00m Bolt\u001b[39m.\u001b[39mopen(\n\u001b[0;32m    908\u001b[0m         addr, auth\u001b[39m=\u001b[39mauth, timeout\u001b[39m=\u001b[39mtimeout, routing_context\u001b[39m=\u001b[39m\u001b[39mNone\u001b[39;00m,\n\u001b[0;32m    909\u001b[0m         \u001b[39m*\u001b[39m\u001b[39m*\u001b[39mpool_config\n\u001b[0;32m    910\u001b[0m     )\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\io\\__init__.py:327\u001b[0m, in \u001b[0;36mBolt.open\u001b[1;34m(cls, address, auth, timeout, routing_context, **pool_config)\u001b[0m\n\u001b[0;32m    324\u001b[0m \u001b[39melif\u001b[39;00m timeout \u001b[39mis\u001b[39;00m \u001b[39mnot\u001b[39;00m \u001b[39mNone\u001b[39;00m:\n\u001b[0;32m    325\u001b[0m     socket_connection_timeout \u001b[39m=\u001b[39m \u001b[39mmin\u001b[39m(pool_config\u001b[39m.\u001b[39mconnection_timeout,\n\u001b[0;32m    326\u001b[0m                                     time_remaining())\n\u001b[1;32m--> 327\u001b[0m s, pool_config\u001b[39m.\u001b[39mprotocol_version, handshake, data \u001b[39m=\u001b[39m BoltSocket\u001b[39m.\u001b[39;49mconnect(\n\u001b[0;32m    328\u001b[0m     address,\n\u001b[0;32m    329\u001b[0m     timeout\u001b[39m=\u001b[39;49msocket_connection_timeout,\n\u001b[0;32m    330\u001b[0m     custom_resolver\u001b[39m=\u001b[39;49mpool_config\u001b[39m.\u001b[39;49mresolver,\n\u001b[0;32m    331\u001b[0m     ssl_context\u001b[39m=\u001b[39;49mpool_config\u001b[39m.\u001b[39;49mget_ssl_context(),\n\u001b[0;32m    332\u001b[0m     keep_alive\u001b[39m=\u001b[39;49mpool_config\u001b[39m.\u001b[39;49mkeep_alive,\n\u001b[0;32m    333\u001b[0m )\n\u001b[0;32m    335\u001b[0m \u001b[39m# Carry out Bolt subclass imports locally to avoid circular dependency\u001b[39;00m\n\u001b[0;32m    336\u001b[0m \u001b[39m# issues.\u001b[39;00m\n\u001b[0;32m    337\u001b[0m \u001b[39mif\u001b[39;00m pool_config\u001b[39m.\u001b[39mprotocol_version \u001b[39m==\u001b[39m (\u001b[39m3\u001b[39m, \u001b[39m0\u001b[39m):\n",
      "File \u001b[1;32mc:\\Users\\Omar Galal\\anaconda3\\lib\\site-packages\\neo4j\\io\\_socket.py:311\u001b[0m, in \u001b[0;36mBoltSocket.connect\u001b[1;34m(cls, address, timeout, custom_resolver, ssl_context, keep_alive)\u001b[0m\n\u001b[0;32m    306\u001b[0m     \u001b[39mraise\u001b[39;00m ServiceUnavailable(\n\u001b[0;32m    307\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39mCouldn\u001b[39m\u001b[39m'\u001b[39m\u001b[39mt connect to \u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m (resolved to \u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m)\u001b[39m\u001b[39m\"\u001b[39m \u001b[39m%\u001b[39m (\n\u001b[0;32m    308\u001b[0m             \u001b[39mstr\u001b[39m(address), \u001b[39mtuple\u001b[39m(\u001b[39mmap\u001b[39m(\u001b[39mstr\u001b[39m, resolved_addresses)))\n\u001b[0;32m    309\u001b[0m     )\n\u001b[0;32m    310\u001b[0m \u001b[39melse\u001b[39;00m:\n\u001b[1;32m--> 311\u001b[0m     \u001b[39mraise\u001b[39;00m ServiceUnavailable(\n\u001b[0;32m    312\u001b[0m         \u001b[39m\"\u001b[39m\u001b[39mCouldn\u001b[39m\u001b[39m'\u001b[39m\u001b[39mt connect to \u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m (resolved to \u001b[39m\u001b[39m%s\u001b[39;00m\u001b[39m):\u001b[39m\u001b[39m\\n\u001b[39;00m\u001b[39m%s\u001b[39;00m\u001b[39m\"\u001b[39m \u001b[39m%\u001b[39m (\n\u001b[0;32m    313\u001b[0m             \u001b[39mstr\u001b[39m(address), \u001b[39mtuple\u001b[39m(\u001b[39mmap\u001b[39m(\u001b[39mstr\u001b[39m, resolved_addresses)),\n\u001b[0;32m    314\u001b[0m             \u001b[39m\"\u001b[39m\u001b[39m\\n\u001b[39;00m\u001b[39m\"\u001b[39m\u001b[39m.\u001b[39mjoin(\u001b[39mmap\u001b[39m(\u001b[39mstr\u001b[39m, errors))\n\u001b[0;32m    315\u001b[0m         )\n\u001b[0;32m    316\u001b[0m     ) \u001b[39mfrom\u001b[39;00m \u001b[39merrors\u001b[39;00m[\u001b[39m0\u001b[39m]\n",
      "\u001b[1;31mServiceUnavailable\u001b[0m: Couldn't connect to localhost:7687 (resolved to ('[::1]:7687', '127.0.0.1:7687')):\nFailed to establish connection to ResolvedIPv6Address(('::1', 7687, 0, 0)) (reason [WinError 10061] No connection could be made because the target machine actively refused it)\nFailed to establish connection to ResolvedIPv4Address(('127.0.0.1', 7687)) (reason [WinError 10061] No connection could be made because the target machine actively refused it)"
     ]
    }
   ],
   "source": [
    "draw_graph_Neo4j(nodes_df,edges_df)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Copy of Data Transformation.ipynb",
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3.9.7",
   "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.9.7"
  },
  "vscode": {
   "interpreter": {
    "hash": "bd8840e4b805e6c306030f2f45ef2cb25a3f70d3a477bda7a0575d8107e3354d"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}