{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create these two files and place them on the machine where you are running Jupyter Notebook.\n",
    "The specified file path is relative to the working directory for Jupyter Notebook.\n",
    "Reading from the client filesystem, as is done in this example, is more convenient and is appropriate for small data sizes.\n",
    "However, it is much slower than reading from the server filesystem.\n",
    "For more information, see [2.5 Data Movement](data.html).\n",
    "\n",
    "employees.csv\n",
    "```\n",
    "person_id,name\n",
    "123456789,Manny\n",
    "123454321,Bob\n",
    "987654321,Frank\n",
    "987656789,Alice\n",
    "```\n",
    "\n",
    "reports.csv\n",
    "```\n",
    "employee_id,boss_id,start_date,end_date\n",
    "123456789,987654321,2015-01-03,2017-04-14\n",
    "123454321,987654321,2016-04-02,2017-04-14\n",
    "987656789,987654321,2016-07-07,2017-04-14\n",
    "123456789,987656789,2017-04-15,\n",
    "123454321,987656789,2017-04-15,\n",
    "987654321,987656789,2017-04-15,\n",
    "```\n",
    "\n",
    "Notice the empty column for end_date in the last three lines.\n",
    "This results in assigning a `NULL` value to those properties.\n",
    "\n",
    "**Code to create CSV files**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('employees.csv', 'w') as emp_file:\n",
    "    emp_file.write('person_id,name\\n')\n",
    "    emp_file.write('123456789,Manny\\n')\n",
    "    emp_file.write('123454321,Bob\\n')\n",
    "    emp_file.write('987654321,Frank\\n')\n",
    "    emp_file.write('987656789,Alice\\n')\n",
    "\n",
    "with open('reports.csv', 'w') as reports_file:\n",
    "    reports_file.write('employee_id,boss_id,start_date,end_date\\n')\n",
    "    reports_file.write('123456789,987654321,2015-01-03,2017-04-14\\n')\n",
    "    reports_file.write('123454321,987654321,2016-04-02,2017-04-14\\n')\n",
    "    reports_file.write('987656789,987654321,2016-07-07,2017-04-14\\n')\n",
    "    reports_file.write('123456789,987656789,2017-04-15,\\n')\n",
    "    reports_file.write('123454321,987656789,2017-04-15,\\n')\n",
    "    reports_file.write('987654321,987656789,2017-04-15,\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Begin xGT client script**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import xgt\n",
    "# Connect to server\n",
    "server = xgt.Connection()\n",
    "\n",
    "# Drop all objects\n",
    "[server.drop_frame(f) for f in ['ReportsTo', 'Employees']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Populate Employee vertices\n",
    "try:\n",
    "  employees = server.get_frame('Employees')\n",
    "except xgt.XgtNameError:\n",
    "  employees = server.create_vertex_frame(\n",
    "      name='Employees',\n",
    "      schema=[['person_id', xgt.INT],\n",
    "              ['name', xgt.TEXT]],\n",
    "      key='person_id')\n",
    "\n",
    "employees.load(\"employees.csv\", header_mode=xgt.HeaderMode.IGNORE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Populate Reports edges\n",
    "try:\n",
    "  reports = server.get_frame('ReportsTo')\n",
    "except xgt.XgtNameError:\n",
    "  reports = server.create_edge_frame(\n",
    "      name       = 'ReportsTo',\n",
    "      schema     = [['employee_id', xgt.INT],\n",
    "                    ['boss_id', xgt.INT],\n",
    "                    ['start_date', xgt.DATE],\n",
    "                    ['end_date', xgt.DATE]],\n",
    "      source     = employees,\n",
    "      target     = employees,\n",
    "      source_key = 'employee_id',\n",
    "      target_key = 'boss_id')\n",
    "\n",
    "reports.load(\"reports.csv\", header_mode=xgt.HeaderMode.IGNORE)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Utility to print the data sizes currently in xGT\n",
    "def print_data_summary(server, frames=None, namespace=None):\n",
    "    if frames is None:\n",
    "        frames = server.get_frames()\n",
    "    for frame in frames:\n",
    "        frame_name = frame.name\n",
    "        if namespace is None or frame_name.startswith(namespace):\n",
    "            if isinstance(frame, xgt.EdgeFrame):\n",
    "                frame_type = 'edge'\n",
    "            elif isinstance(frame, xgt.VertexFrame):\n",
    "                frame_type = 'vertex'\n",
    "            elif isinstance(frame, xgt.TableFrame):\n",
    "                frame_type = 'table'\n",
    "            else:\n",
    "                frame_type = 'unknown'\n",
    "            print(\"{} ({}): {:,}\".format(frame_name, frame_type, frame.num_rows))\n",
    "\n",
    "print_data_summary(server, namespace='example')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query in section 4.1.5.1\n",
    "df = server.run_job(\"\"\"\n",
    "    MATCH (x:Employees)-[edge1:ReportsTo]->(y:Employees)\n",
    "    RETURN x.person_id, edge1.start_date, edge1.end_date, y.person_id\n",
    "\"\"\").get_data(format='pandas')\n",
    "print(\"Number of answers: {:,}\".format(len(df)))\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query in section 4.1.5.2\n",
    "df = server.run_job(\"\"\"\n",
    "    MATCH (x:Employees)-[edge1:ReportsTo]->(y)-[edge2]->(x)\n",
    "    WHERE edge1.end_date <= edge2.start_date\n",
    "    RETURN x.person_id, y.person_id,\n",
    "           edge1.start_date, edge1.end_date,\n",
    "           edge2.start_date, edge2.end_date\n",
    "\"\"\").get_data(format='pandas')\n",
    "print(\"Number of answers: {:,}\".format(len(df)))\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}