project_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# GitHub repositories and users recommendations by embeddings"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Problem Statement\n",
    "\n",
    "Currently, GitHub has two possibilities to explore users and repositories:\n",
    "1. Direct search by search term leveraging names and tags.\n",
    "2. Recommender system under 'Explore' tab which gives suggestions to a user based on his usage of service.\n",
    "\n",
    "However, there is no possibility to perform a search of connected entities. E.g., find repositories or users highly related to each other."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Goal of the Project\n",
    "\n",
    "The goal of this project is to build GitHub repository search/recommender system, which would allow exploring connected repositories and people, by leveraging the underlying graph structure of the repositories database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Implemented ML solution\n",
    "\n",
    "It was decided to build graph nodes embeddings (`repo2vec` and `user2vec`) for the entire GitHub database using [PyTorch-BigGraph (PBG)](https://github.com/facebookresearch/PyTorch-BigGraph). On top of the embeddings representation, we have built query tool with the ranking engine."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pipeline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:09:01.863189Z",
     "start_time": "2019-08-14T15:09:01.828567Z"
    }
   },
   "outputs": [],
   "source": [
    "from resources.utils import *\n",
    "\n",
    "DUMP_DATE = '2019-06'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Dataset: preprocessing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "The goal of this stage is to process the GitHub dataset from http://ghtorrent.org/ project into directed graph. <br>\n",
    "The source data consists from .csv files which represent tabular SQL data. Each file is one table in source database. <br>\n",
    "Total size of the dataset is 100GB. The size of the tables used for graph building is ~7GB.\n",
    "\n",
    "*Database schema:*\n",
    "![image.png](http://ghtorrent.org/files/schema.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "The following tables are used for the graph representation:\n",
    "* Followers\n",
    "* Watchers\n",
    "* Project Members\n",
    "\n",
    "##### Graph structure \n",
    "Following nodes are defined as points of interest for this project:\n",
    "* Users\n",
    "* Projects\n",
    "\n",
    "Types of graph edges:\n",
    "* Follows\n",
    "* Watches\n",
    "* Is member of\n",
    "\n",
    "Possible edges:\n",
    "* User => follow => User\n",
    "* User => follow => Project\n",
    "* User => watch => User\n",
    "* User => watch => Project\n",
    "* User => is member => Project"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Download data (http://ghtorrent.org/)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T18:48:31.432996Z",
     "start_time": "2019-07-20T18:48:30.695050Z"
    },
    "hidden": true
   },
   "source": [
    "Run `db_download.sh` script (at terminal) to download dump"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Extract relationships tables (CSV): `followers.csv`, `watchers.csv`, `project_members.csv`\n",
    "\n",
    "and metadata: `users.csv`, `projects.csv`\n",
    "\n",
    "to `data/mysql-%Y-%m-%d` folder (or change directories code dependencies at notebooks)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T20:26:21.192351Z",
     "start_time": "2019-07-20T20:26:21.170098Z"
    },
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mysql-2019-06-01/followers.csv\n",
      "mysql-2019-06-01/watchers.csv\n",
      "mysql-2019-06-01/projects.csv\n",
      "mysql-2019-06-01/project_members.csv\n",
      "mysql-2019-06-01/users.csv\n"
     ]
    }
   ],
   "source": [
    "!tar -C data/ -xvzf data/mysql-{DUMP_DATE}-01.tar.gz mysql-{DUMP_DATE}-01/project_members.csv mysql-{DUMP_DATE}-01/followers.csv mysql-{DUMP_DATE}-01/watchers.csv mysql-{DUMP_DATE}-01/projects.csv mysql-{DUMP_DATE}-01/users.csv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Process SQL structured data into edges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:09:09.479925Z",
     "start_time": "2019-08-14T15:09:09.433952Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "from pyspark import SparkContext, SparkConf, SQLContext\n",
    "\n",
    "# constants\n",
    "DATA_FOLDER = f\"./data/mysql-{DUMP_DATE}-01/\"\n",
    "FOLLOWERS_PATH = DATA_FOLDER + \"followers.csv\"\n",
    "WATCHERS_PATH = DATA_FOLDER + \"watchers.csv\"\n",
    "PROJECT_MEMBERS_PATH = DATA_FOLDER + \"project_members.csv\"\n",
    "\n",
    "# relations constants\n",
    "IS_MEMBER_OF = \"is_member_of\"\n",
    "FOLLOWS = \"follows\"\n",
    "WATCHES = \"watches\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def addIdentifiers(row, id_1, id_2):\n",
    "    '''\n",
    "    Adds identifier for each id to distinguish them\n",
    "    '''\n",
    "    row[0] = id_1 + row[0]\n",
    "    row[1] = id_2 + row[1]\n",
    "    return row\n",
    "\n",
    "def swapColumns(row):\n",
    "    temp = row[0]\n",
    "    row[0] = row[1]\n",
    "    row[1] = temp\n",
    "    return row\n",
    "\n",
    "def addRelation(row, relation):\n",
    "    temp = row[1]\n",
    "    row[1] = relation\n",
    "    row.append(temp)\n",
    "    return row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-13T11:00:29.182186Z",
     "start_time": "2019-08-13T11:00:27.078122Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "conf = SparkConf()\n",
    "sc = SparkContext(conf=conf)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Processing of members table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-06-29T09:24:39.175904Z",
     "start_time": "2019-06-29T09:24:38.039808Z"
    },
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['user_id_1', 'is_member_of', 'repo_id_1'],\n",
       " ['user_id_2', 'is_member_of', 'repo_id_1'],\n",
       " ['user_id_4', 'is_member_of', 'repo_id_1'],\n",
       " ['user_id_24', 'is_member_of', 'repo_id_3'],\n",
       " ['user_id_5465', 'is_member_of', 'repo_id_3']]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "members_rdd = sc.textFile(PROJECT_MEMBERS_PATH).map(lambda x: x.split(\",\")[:-2])\n",
    "\n",
    "members_rdd = members_rdd.map(lambda x: addIdentifiers(x, \"repo_id_\", \"user_id_\")).map(swapColumns).map(lambda x: addRelation(x,IS_MEMBER_OF))\n",
    "members_rdd.take(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Processing of followers table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-06-29T09:24:39.246842Z",
     "start_time": "2019-06-29T09:24:39.177612Z"
    },
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['user_id_2', 'follows', 'user_id_1'],\n",
       " ['user_id_4', 'follows', 'user_id_1'],\n",
       " ['user_id_17896', 'follows', 'user_id_1'],\n",
       " ['user_id_21523', 'follows', 'user_id_1'],\n",
       " ['user_id_29121', 'follows', 'user_id_1']]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "followers_rdd = sc.textFile(FOLLOWERS_PATH).map(lambda x: x.split(\",\")[:-1]) \\\n",
    "    .map(lambda x: addIdentifiers(x, \"user_id_\", \"user_id_\")) \\\n",
    "    .map(swapColumns) \\\n",
    "    .map(lambda x: addRelation(x, FOLLOWS))\n",
    "\n",
    "followers_rdd.take(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Processing of watchers table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-06-29T09:24:39.314580Z",
     "start_time": "2019-06-29T09:24:39.247979Z"
    },
    "collapsed": true,
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['user_id_1', 'watches', 'repo_id_1'],\n",
       " ['user_id_2', 'watches', 'repo_id_1'],\n",
       " ['user_id_4', 'watches', 'repo_id_1'],\n",
       " ['user_id_6', 'watches', 'repo_id_1'],\n",
       " ['user_id_7', 'watches', 'repo_id_1']]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "watchers_rdd = sc.textFile(WATCHERS_PATH).map(lambda x: x.split(\",\")[:-1]) \\\n",
    "    .map(lambda x: addIdentifiers(x, \"repo_id_\", \"user_id_\")) \\\n",
    "    .map(swapColumns) \\\n",
    "    .map(lambda x: addRelation(x, WATCHES))\n",
    "\n",
    "watchers_rdd.take(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Split into train, validation and test datasets"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-06-29T11:50:40.470900Z",
     "start_time": "2019-06-29T11:48:15.723518Z"
    },
    "hidden": true
   },
   "source": [
    "Merge into one entity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-06-29T11:50:40.470900Z",
     "start_time": "2019-06-29T11:48:15.723518Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "rdd = members_rdd.union(followers_rdd).union(watchers_rdd)\n",
    "\n",
    "sqlContext = SQLContext(sc)\n",
    "df = sqlContext.createDataFrame(rdd, ['from_entity_id', 'relation', 'to_entity_id'])\n",
    "df.coalesce(1).write.option(\"delimiter\", \"\\t\").format('com.databricks.spark.csv').options(header='true').save(DATA_FOLDER + 'graph_edges')\n",
    "shuffledDF = df.randomSplit([0.1, 0.1, 0.8])\n",
    "val_set = shuffledDF[0]\n",
    "test_set = shuffledDF[1]\n",
    "train_set = shuffledDF[2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-06-29T11:50:40.470900Z",
     "start_time": "2019-06-29T11:48:15.723518Z"
    },
    "hidden": true
   },
   "source": [
    "Save into files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-06-29T11:50:40.470900Z",
     "start_time": "2019-06-29T11:48:15.723518Z"
    },
    "collapsed": true,
    "hidden": true
   },
   "outputs": [],
   "source": [
    "val_set.coalesce(1).write.option(\"delimiter\", \"\\t\").format('com.databricks.spark.csv').options(header='true').save(DATA_FOLDER + 'graph_edges/validation')\n",
    "test_set.coalesce(1).write.option(\"delimiter\", \"\\t\").format('com.databricks.spark.csv').options(header='true').save(DATA_FOLDER + 'graph_edges/test')\n",
    "train_set.coalesce(1).write.option(\"delimiter\", \"\\t\").format('com.databricks.spark.csv').options(header='true').save(DATA_FOLDER + 'graph_edges/train')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Store metadata"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Metainfo we store in MongoDB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def convert_dt(sdt):\n",
    "    try:\n",
    "        return pd.datetime.strptime(sdt, '%Y-%m-%d %H:%M:%S')\n",
    "    except Exception as e:\n",
    "        print(e)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "#### Users"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T20:37:37.116613Z",
     "start_time": "2019-07-20T20:37:37.076371Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(30600249, 12)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df = pd.read_csv(f'{DATA_FOLDER}users.csv',\n",
    "                       header=None,\n",
    "                       names=['id', 'login', 'company', 'created_at', 'type', 'fake', 'deleted', 'long', 'lat',\n",
    "                              'country_code', 'state', 'city', 'location'],\n",
    "#                        parse_dates=[3],\n",
    "#                        quoting=1,\n",
    "#                        quotechar='\"',\n",
    "#                        encoding='utf-8',\n",
    "                       low_memory=False,\n",
    "                       error_bad_lines=False).replace({'\\\\N': None}).set_index('id')\n",
    "print(users_df.shape)\n",
    "users_df['created_at'] = users_df['created_at'].map(convert_dt)\n",
    "users_df = users_df[~users_df['created_at'].isna()]\n",
    "users_df['fake'] = users_df['fake'].replace({1: True, 0: False, '1': True, '0': False}).astype(bool)\n",
    "users_df['deleted'] = users_df['deleted'].replace({1: True, 0: False, '1': True, '0': False}).astype(bool)\n",
    "users_df['long'] = users_df['long'].astype(float)\n",
    "users_df['lat'] = users_df['lat'].astype(float)\n",
    "users_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T20:37:12.690789Z",
     "start_time": "2019-07-20T20:37:12.665949Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'login_1'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mc['github']['users'].create_index('login')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T20:38:04.051994Z",
     "start_time": "2019-07-20T20:38:04.030011Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30600249"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "to_insert = []\n",
    "\n",
    "for uid, row in tn(users_df.iterrows(), total=users_df.shape[0]):\n",
    "    to_insert.append({'_id': uid, **row.to_dict()})\n",
    "    if len(to_insert) >= 10_000:\n",
    "        mc['github']['users'].insert_many(to_insert)\n",
    "        to_insert = []\n",
    "if len(to_insert):\n",
    "    mc['github']['users'].insert_many(to_insert)\n",
    "mc['github']['users'].estimated_document_count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Users example\n",
    "![Users](./resources/images/Users.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "#### Projects (repos)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "for field in ['name', 'owner_id', 'url']:\n",
    "    mc['github']['projects'].create_index(field)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T20:38:37.817887Z",
     "start_time": "2019-07-20T20:38:37.792961Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'[projects]: stored 116010000 projects, 135185 skiped'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "try:\n",
    "    stop = False\n",
    "    skip_count = 0\n",
    "    for i in range(1000):\n",
    "        print(i)\n",
    "        repos_df = pd.read_csv(f'{DATA_FOLDER}projects.csv',\n",
    "                               skiprows=10_000_000*i,\n",
    "                               nrows=10_000_000,\n",
    "                               header=None,\n",
    "                               na_values=['\\\\N'],\n",
    "                               names=[\"id\", \"url\", \"owner_id\", \"name\", \"description\", \"language\",\n",
    "                                      \"created_at\", \"forked_from\", \"deleted\", \"updated_at\", \"?\"],\n",
    "                               error_bad_lines=False,\n",
    "                               low_memory=False,\n",
    "                              ).set_index('id')\n",
    "        c = repos_df.shape[0]\n",
    "        if c < 10_000_000:\n",
    "            stop = True\n",
    "        repos_df['created_at'] = repos_df['created_at'].map(convert_dt)\n",
    "        repos_df = repos_df[~repos_df['created_at'].isna()]\n",
    "        repos_df['updated_at'] = repos_df['updated_at'].map(convert_dt)\n",
    "        repos_df = repos_df[~repos_df['updated_at'].isna()]\n",
    "        skip_count += (c - repos_df.shape[0])\n",
    "        repos_df.index = repos_df.index.astype(int)\n",
    "        repos_df['url'] = repos_df['url'].astype(str).map(lambda x: x[29:])\n",
    "        repos_df['deleted'] = repos_df['deleted'].replace({1: True, 0: False, 1: True, 0: False}).astype(bool)\n",
    "        repos_df['owner_id'] = repos_df['owner_id'].astype(int)\n",
    "        repos_df['forked_from'] = repos_df['forked_from'].astype(float)\n",
    "        repos_df['?'] = repos_df['?'].astype(float)\n",
    "\n",
    "        to_insert = []\n",
    "\n",
    "        for rid, row in tn(repos_df.iterrows(), total=repos_df.shape[0]):\n",
    "            to_insert.append({'_id': rid, **row.to_dict()})\n",
    "            if len(to_insert) >= 10_000:\n",
    "                mc['github']['projects'].insert_many(to_insert)\n",
    "                to_insert = []\n",
    "        if len(to_insert):\n",
    "            mc['github']['project'].insert_many(to_insert)\n",
    "        if stop:\n",
    "            break\n",
    "    m = f\"[projects]: stored {mc['github']['projects'].estimated_document_count()} projects, {skip_count} skiped\"\n",
    "    tgn(m)\n",
    "except Exception as e:\n",
    "    m = f'[projects]: error - {e}'\n",
    "    tgn(m)\n",
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Repos example\n",
    "![Projects](./resources/images/Projects.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Training"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "![Partitions](./resources/images/Partitions.png)\n",
    "\n",
    "*Figure 1.* The PBG partitioning scheme for large graphs. **Left:** nodes are divided into $P$ partitions that are sized to fit in memory. Edges are divided into buckets based on the partition of their source and destination nodes. In distributed mode, multiple buckets with non-overlapping partitions can be executed in parallel (red squares). **Center:** Entity types with small cardinality do not have to be partitioned; if all entity types used for tail nodes are unpartitioned, then edges can be divided into $P$ buckets based only on source node partitions. **Right:** the ‘inside-out’ bucket order guarantees that buckets have at least one previously-trained embedding partition. Empirically, this ordering produces better embeddings than other alternatives (or random)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "![Architecture](./resources/images/Architecture.png)\n",
    "\n",
    "*Figure 2.* Memory-efficient batched negative sampling. Embeddings are fetched for the $B$ source and destination entities in a batch of edges, as well as $B$ uniformly-sampled source and destination entities. Each chunk of $B_n/2$ edges is corrupted with all source or destination entities in its chunk, as well as the corresponding chunk of the uniform embeddings, resulting in $B_n$ negative examples per positive edge. The negative scores are computed via a batch matrix multiply."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Lerer, Adam, et al. \"PyTorch-BigGraph: A Large-scale Graph Embedding System.\" arXiv preprint arXiv:1903.12287 (2019).\n",
    "https://arxiv.org/pdf/1903.12287.pdf"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Clear dir from possible previous training (or archive configs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T23:44:32.710602Z",
     "start_time": "2019-07-20T23:44:32.338529Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "for TEMPLATE in ['entity_count_all_*.txt', 'graph-*_partitioned/*', 'dictionary.json', 'dynamic_rel_count.txt']:\n",
    "    !rm -f {DATA_FOLDER}graph_edges/{TEMPLATE}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "See `graph_config.py` config file for details (`torchbiggraph_train -h` for help)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Let's exclude 'long tail' where entity (repo/user) has less than {ENTITY_MIN_COUNT} relations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "ENTITY_MIN_COUNT = 30"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Creating needed partitions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T23:59:42.656752Z",
     "start_time": "2019-07-20T23:45:05.004959Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "%time !torchbiggraph_import_from_tsv --lhs-col=0 --rel-col=1 --rhs-col=2 --entity-min-count={ENTITY_MIN_COUNT} graph_config.py {DATA_FOLDER}graph_edges/graph-*.csv > logs/formating.log"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T00:00:58.562349Z",
     "start_time": "2019-07-21T00:00:58.216228Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking up relation types in the edge files...\r\n",
      "- Found 4 relation types\r\n",
      "- Removing the ones with fewer than 1 occurrences...\r\n",
      "- Left with 4 relation types\r\n",
      "- Shuffling them...\r\n",
      "Searching for the entities in the edge files...\r\n",
      "Entity type all:\r\n",
      "- Found 27406591 entities\r\n",
      "- Removing the ones with fewer than 10 occurrences...\r\n",
      "- Left with 3366929 entities\r\n",
      "- Shuffling them...\r\n",
      "Preparing entity path ./data/mysql-2019-06-01/graph_edges:\r\n",
      "- Writing count of entity type all and partition 0\r\n",
      "- Writing count of entity type all and partition 1\r\n",
      "- Writing count of entity type all and partition 2\r\n",
      "- Writing count of entity type all and partition 3\r\n",
      "- Writing count of dynamic relations\r\n",
      "Preparing edge path ./data/mysql-2019-06-01/graph_edges/graph-test_partitioned, out of the edges found in ./data/mysql-2019-06-01/graph_edges/graph-test.csv\r\n",
      "- Edges will be partitioned in 4 x 4 buckets.\r\n",
      "- Processed 100000 edges so far...\r\n"
     ]
    }
   ],
   "source": [
    "!head -n 20 logs/formating.log"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T23:44:32.710602Z",
     "start_time": "2019-07-20T23:44:32.338529Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "for TEMPLATE in ['checkpoint_version.txt', 'embeddings_all_*.h5', 'model.v*.h5']:\n",
    "    !rm -f {DATA_FOLDER}graph_edges/{TEMPLATE}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Actually, training"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "hidden": true
   },
   "outputs": [],
   "source": [
    "%time !torchbiggraph_train graph_config.py -p edge_paths={DATA_FOLDER}graph_edges/graph-train_partitioned > logs/train.log"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-22T23:35:11.136116Z",
     "start_time": "2019-07-22T23:35:10.272865Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-07-21 03:07:40  Loading entity counts...\n",
      "2019-07-21 03:07:40  Creating workers...\n",
      "2019-07-21 03:07:40  Initializing global model...\n",
      "2019-07-21 03:07:40  Starting epoch 1 / 10 edge path 1 / 1 edge chunk 1 / 1\n",
      "2019-07-21 03:07:40  edge_path= ./data/mysql-2019-06-01/graph_edges/graph-train_partitioned\n",
      "2019-07-21 03:07:40  Swapping partitioned embeddings None ( 3 , 3 )\n",
      "2019-07-21 03:07:40  Loading entities\n",
      "2019-07-21 03:08:50  ( 3 , 3 ): bucket 1 / 16 : Processed 7652791 edges in 68.59 s ( 0.11 M/sec ); io: 0.51 s ( 1013.96 MB/sec )\n",
      "2019-07-21 03:08:50  ( 3 , 3 ): loss:  7.348 , violators_lhs:  24.8202 , violators_rhs:  19.5466 , count:  7652791\n",
      "2019-07-21 03:08:50  Swapping partitioned embeddings ( 3 , 3 ) ( 2 , 2 )\n",
      "----------------------------------------------------------------------------------------------------\n",
      "2019-07-21 06:00:18  ( 0 , 0 ): bucket 16 / 16 : Processed 7613224 edges in 66.93 s ( 0.11 M/sec ); io: 1.05 s ( 493.80 MB/sec )\n",
      "2019-07-21 06:00:18  ( 0 , 0 ): loss:  3.15555 , violators_lhs:  3.08641 , violators_rhs:  2.01626 , count:  7613224\n",
      "2019-07-21 06:00:18  Swapping partitioned embeddings ( 0 , 0 ) None\n",
      "2019-07-21 06:00:18  Writing partitioned embeddings\n",
      "2019-07-21 06:00:18  Finished epoch 10 path 1 pass 1; checkpointing global state.\n",
      "2019-07-21 06:00:18  My rank: 0\n",
      "2019-07-21 06:00:18  Writing metadata...\n",
      "2019-07-21 06:00:18  Writing the checkpoint...\n",
      "2019-07-21 06:00:18  Switching to new checkpoint version...\n",
      "2019-07-21 06:00:18  Exiting\n"
     ]
    }
   ],
   "source": [
    "!head logs/train.log\n",
    "print('-'*100)\n",
    "!tail logs/train.log"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Evaluating"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### BigGraph Evaluation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Offline evaluation in the BigGraph performed using `torchbiggraph_eval` that samples some number of negative edges with a particular positive edge. In practice, since we are using sparse graphs - this implementation assumes that randomly given edge is a negative one with high probability. Therefore some positive edges may be mixed with a negative one.\n",
    "\n",
    "Several metrics are built-in into the library and calculated for each batch separately, and afterwards globally, such as:\n",
    " - *Mean Rank (pos_rank)* - the average of the ranks of all positives (lower is better, best is 1).\n",
    " - *Mean Reciprocal Rank (MRR)* - the average of the reciprocal of the ranks of all positives (higher is better, best is 1).\n",
    " $$\\dfrac{1}{n} \\sum_{i=1}^{n} \\dfrac{1}{rank}, $$\n",
    " where $n$ is the number of all positives, and $rank$ is rank of a positive result in a sample\n",
    " - *r1* - the fraction of positives that rank better than all their negatives, i.e., have a rank of 1 (higher is better, best is 1).\n",
    " - *r10* - the fraction of positives that rank in the top 10 among their negatives (higher is better, best is 1).\n",
    " - *r50* - the fraction of positives that rank in the top 50 among their negatives (higher is better, best is 1).\n",
    " - *AUC* - an estimation of the probability that a randomly chosen positive scores higher than a randomly chosen negative (any negative, not only the negatives constructed by corrupting that positive)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**Evaluating on train**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:12:48.712158Z",
     "start_time": "2019-08-14T15:09:28.935953Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "!torchbiggraph_eval graph_config.py -p edge_paths={DATA_FOLDER}graph_edges/graph-train_partitioned > logs/eval_train.log"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:12:49.412433Z",
     "start_time": "2019-08-14T15:12:48.715351Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-08-14 18:09:29  Starting edge path 1 / 1 (./data/mysql-2019-06-01/graph_edges/graph-train_partitioned)\n",
      "2019-08-14 18:09:42  ( 0 , 0 ): Processed 6383098 edges in 12 s (0.52M/sec); load time: 0.18 s\n",
      "2019-08-14 18:09:42  Stats for edge path 1 / 1, bucket ( 0 , 0 ): pos_rank:  6.76915 , mrr:  0.480395 , r1:  0.321494 , r10:  0.813052 , r50:  0.991382 , auc:  0.967479 , count:  6383098\n",
      "2019-08-14 18:09:54  ( 0 , 1 ): Processed 6584603 edges in 12 s (0.53M/sec); load time: 0.15 s\n",
      "2019-08-14 18:09:54  Stats for edge path 1 / 1, bucket ( 0 , 1 ): pos_rank:  6.66269 , mrr:  0.48533 , r1:  0.327922 , r10:  0.81517 , r50:  0.992204 , auc:  0.968301 , count:  6584603\n",
      "2019-08-14 18:10:07  ( 0 , 2 ): Processed 6469912 edges in 12 s (0.53M/sec); load time: 0.18 s\n",
      "2019-08-14 18:10:07  Stats for edge path 1 / 1, bucket ( 0 , 2 ): pos_rank:  6.52836 , mrr:  0.4888 , r1:  0.33052 , r10:  0.820306 , r50:  0.992694 , auc:  0.968771 , count:  6469912\n",
      "2019-08-14 18:10:19  ( 0 , 3 ): Processed 6550501 edges in 12 s (0.53M/sec); load time: 0.17 s\n",
      "2019-08-14 18:10:19  Stats for edge path 1 / 1, bucket ( 0 , 3 ): pos_rank:  6.08977 , mrr:  0.504963 , r1:  0.346434 , r10:  0.83357 , r50:  0.994735 , auc:  0.971714 , count:  6550501\n",
      "2019-08-14 18:10:32  ( 1 , 0 ): Processed 6386450 edges in 12 s (0.53M/sec); load time: 0.21 s\n",
      "----------------------------------------------------------------------------------------------------\n",
      "2019-08-14 18:12:35  ( 3 , 2 ): Processed 6419442 edges in 12 s (0.53M/sec); load time: 0.15 s\n",
      "2019-08-14 18:12:35  Stats for edge path 1 / 1, bucket ( 3 , 2 ): pos_rank:  7.00424 , mrr:  0.46733 , r1:  0.307811 , r10:  0.803912 , r50:  0.991131 , auc:  0.965458 , count:  6419442\n",
      "2019-08-14 18:12:48  ( 3 , 3 ): Processed 6497463 edges in 12 s (0.53M/sec); load time: 0.15 s\n",
      "2019-08-14 18:12:48  Stats for edge path 1 / 1, bucket ( 3 , 3 ): pos_rank:  6.77669 , mrr:  0.478799 , r1:  0.319774 , r10:  0.810832 , r50:  0.992076 , auc:  0.967188 , count:  6497463\n",
      "2019-08-14 18:12:48  \n",
      "2019-08-14 18:12:48  Stats for edge path 1 / 1: pos_rank:  6.77144 , mrr:  0.478448 , r1:  0.319965 , r10:  0.811187 , r50:  0.992095 , auc:  0.967152 , count:  103571972\n",
      "2019-08-14 18:12:48  \n",
      "2019-08-14 18:12:48  \n",
      "2019-08-14 18:12:48  Stats: pos_rank:  6.77144 , mrr:  0.478448 , r1:  0.319965 , r10:  0.811187 , r50:  0.992095 , auc:  0.967152 , count:  103571972\n",
      "2019-08-14 18:12:48  \n"
     ]
    }
   ],
   "source": [
    "!head logs/eval_train.log\n",
    "print('-'*100)\n",
    "!tail logs/eval_train.log"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**Evaluating on validation**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:17.502357Z",
     "start_time": "2019-08-14T15:12:49.416868Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "!torchbiggraph_eval graph_config.py -p edge_paths={DATA_FOLDER}graph_edges/graph-val_partitioned > logs/eval_val.log"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:18.217321Z",
     "start_time": "2019-08-14T15:13:17.505547Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-08-14 18:12:50  Starting edge path 1 / 1 (./data/mysql-2019-06-01/graph_edges/graph-val_partitioned)\n",
      "2019-08-14 18:12:51  ( 0 , 0 ): Processed 799176 edges in 1.7 s (0.48M/sec); load time: 0.12 s\n",
      "2019-08-14 18:12:51  Stats for edge path 1 / 1, bucket ( 0 , 0 ): pos_rank:  9.97506 , mrr:  0.388542 , r1:  0.236046 , r10:  0.720194 , r50:  0.97284 , auc:  0.943282 , count:  799176\n",
      "2019-08-14 18:12:53  ( 0 , 1 ): Processed 822664 edges in 1.6 s (0.52M/sec); load time: 0.1 s\n",
      "2019-08-14 18:12:53  Stats for edge path 1 / 1, bucket ( 0 , 1 ): pos_rank:  9.79906 , mrr:  0.39167 , r1:  0.23917 , r10:  0.723014 , r50:  0.974304 , auc:  0.944365 , count:  822664\n",
      "2019-08-14 18:12:55  ( 0 , 2 ): Processed 810187 edges in 1.6 s (0.52M/sec); load time: 0.084 s\n",
      "2019-08-14 18:12:55  Stats for edge path 1 / 1, bucket ( 0 , 2 ): pos_rank:  9.51358 , mrr:  0.397661 , r1:  0.243526 , r10:  0.731363 , r50:  0.975832 , auc:  0.946319 , count:  810187\n",
      "2019-08-14 18:12:56  ( 0 , 3 ): Processed 819618 edges in 1.6 s (0.51M/sec); load time: 0.082 s\n",
      "2019-08-14 18:12:56  Stats for edge path 1 / 1, bucket ( 0 , 3 ): pos_rank:  10.2807 , mrr:  0.380556 , r1:  0.230311 , r10:  0.706123 , r50:  0.972053 , auc:  0.94175 , count:  819618\n",
      "2019-08-14 18:12:58  ( 1 , 0 ): Processed 797507 edges in 1.6 s (0.51M/sec); load time: 0.17 s\n",
      "----------------------------------------------------------------------------------------------------\n",
      "2019-08-14 18:13:15  ( 3 , 2 ): Processed 801938 edges in 1.6 s (0.51M/sec); load time: 0.079 s\n",
      "2019-08-14 18:13:15  Stats for edge path 1 / 1, bucket ( 3 , 2 ): pos_rank:  9.90561 , mrr:  0.385661 , r1:  0.232808 , r10:  0.718466 , r50:  0.974189 , auc:  0.943815 , count:  801938\n",
      "2019-08-14 18:13:17  ( 3 , 3 ): Processed 812378 edges in 1.6 s (0.51M/sec); load time: 0.1 s\n",
      "2019-08-14 18:13:17  Stats for edge path 1 / 1, bucket ( 3 , 3 ): pos_rank:  10.7284 , mrr:  0.369409 , r1:  0.220775 , r10:  0.692107 , r50:  0.969629 , auc:  0.938471 , count:  812378\n",
      "2019-08-14 18:13:17  \n",
      "2019-08-14 18:13:17  Stats for edge path 1 / 1: pos_rank:  10.0585 , mrr:  0.384547 , r1:  0.232959 , r10:  0.714049 , r50:  0.973193 , auc:  0.942732 , count:  12945965\n",
      "2019-08-14 18:13:17  \n",
      "2019-08-14 18:13:17  \n",
      "2019-08-14 18:13:17  Stats: pos_rank:  10.0585 , mrr:  0.384547 , r1:  0.232959 , r10:  0.714049 , r50:  0.973193 , auc:  0.942732 , count:  12945965\n",
      "2019-08-14 18:13:17  \n"
     ]
    }
   ],
   "source": [
    "!head logs/eval_val.log\n",
    "print('-'*100)\n",
    "!tail logs/eval_val.log"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "**Result table**\n",
    "\n",
    "| Metric   | Train | Validation |\n",
    "|----------|-------|------------|\n",
    "| pos_rank | 6.77  | 10.05      |\n",
    "| MRR      | 0.48  | 0.38       |\n",
    "| r1       | 0.32  | 0.23       |\n",
    "| r10      | 0.81  | 0.71       |\n",
    "| r50      | 0.99  | 0.97       |\n",
    "| AUC      | 0.97  | 0.94       |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### User evaluation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Besides that, we performed user evaluation. We engaged users that have not heard anything about our project and actively using open source projects and GitHub. As a setting for the experiment, we asked to provide five repositories that users familiar with and proposed to them two recommendations for each. First one is our best guess from Annoy nearest neighbour search and second one - random repository with 1000+ stars. Please see the table with the experiment data:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "| Provided repo | Our suggestion    | Random repo                         | Choice                            |\n",
    "|---------------|-------------------|-------------------------------------|-----------------------------------|\n",
    "| yarnpkg/yarn  | prettier/prettier | fossasia/sphinx_fossasia_theme      | both irrelevant                   |\n",
    "| npm/npm       | gulpjs/gulp       | lhartikk/ArnoldC                    | our suggestion marked as relevant |\n",
    "| rspec/rspec   | rspec/rspec-core  | FLIF-hub/FLIF                       | both irrelevant                   |\n",
    "| rails/rails   | sinatra/sinatra   | javamelody/javamelody               | our suggestion marked as relevant |\n",
    "| ruby/ruby     | rails/rails       | ustjavac/Programming-Alpha-To-Omega | both irrelevant                   |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "As we see in all cases, users correctly identified random repositories and marked 40% of our suggestions as useful, which is similar to the rank1 metric on the validation set. However, we cannot say that this result is statistically significant due to small sample size. \n",
    "\n",
    "Besides that, one user asked to search for repos that are similar to one particular repository for his current project task and replied that the top 10 suggestions contained \"interesting results that I have not found googling\"."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Prepare tensorboard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-20T11:30:39.357971Z",
     "start_time": "2019-07-20T11:14:34.931447Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# !torchbiggraph_export_to_tsv --dict {DATA_FOLDER}/graph_edges/dictionary.json \\\n",
    "# --checkpoint {DATA_FOLDER}/graph_edges/ --out {DATA_FOLDER}/graph_edges/embeddings.tsv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Slow ↑ method"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:27:17.117214Z",
     "start_time": "2019-08-14T14:27:16.367132Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import h5py\n",
    "import os\n",
    "\n",
    "!mkdir -p tb\n",
    "!mkdir -p tb/embeddings\n",
    "\n",
    "def dropCategories(df, col, threshold):\n",
    "    drop_list = df[col].value_counts()[threshold:].index.to_list()\n",
    "    df[col].cat.remove_categories(drop_list, inplace=True)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "For faster results let's see only on repos with 1K+ stars and users with 100+ followers/following"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:26:47.907959Z",
     "start_time": "2019-08-14T14:26:47.564664Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18670"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "good_rids = pd.read_pickle('./tb/embeddings_old/repos_19k_gte1k.pkl').index.tolist()\n",
    "len(good_rids)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:26:49.699046Z",
     "start_time": "2019-08-14T14:26:48.823556Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "47666"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "good_uids = pd.read_pickle('./tb/embeddings_old/users_following_29k_gte100.pkl').index.tolist()\n",
    "good_uids += pd.read_pickle('./tb/embeddings_old/users_followers_24k_gte100.pkl').index.tolist()\n",
    "good_uids = list(set(good_uids))\n",
    "len(good_uids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Read metadata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:27:20.894560Z",
     "start_time": "2019-08-14T14:27:20.653261Z"
    },
    "hidden": true,
    "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>url</th>\n",
       "      <th>language</th>\n",
       "      <th>created_at</th>\n",
       "      <th>updated_at</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2355834</th>\n",
       "      <td>behave/behave</td>\n",
       "      <td>Python</td>\n",
       "      <td>2011-10-25 11:02:35</td>\n",
       "      <td>2019-02-25 21:52:24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9907713</th>\n",
       "      <td>gilbitron/Raneto</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>2014-05-30 10:24:18</td>\n",
       "      <td>2019-02-27 04:35:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>69826495</th>\n",
       "      <td>apollographql/apollo-server</td>\n",
       "      <td>TypeScript</td>\n",
       "      <td>2016-04-21 09:26:01</td>\n",
       "      <td>2019-02-26 15:46:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89912090</th>\n",
       "      <td>Tencent/LKImageKit</td>\n",
       "      <td>Objective-C</td>\n",
       "      <td>2018-01-03 02:22:30</td>\n",
       "      <td>2019-02-27 06:54:36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36676484</th>\n",
       "      <td>electron/devtron</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>2016-02-12 22:57:24</td>\n",
       "      <td>2019-02-26 08:50:46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  url     language          created_at  \\\n",
       "_id                                                                      \n",
       "2355834                 behave/behave       Python 2011-10-25 11:02:35   \n",
       "9907713              gilbitron/Raneto   JavaScript 2014-05-30 10:24:18   \n",
       "69826495  apollographql/apollo-server   TypeScript 2016-04-21 09:26:01   \n",
       "89912090           Tencent/LKImageKit  Objective-C 2018-01-03 02:22:30   \n",
       "36676484             electron/devtron   JavaScript 2016-02-12 22:57:24   \n",
       "\n",
       "                  updated_at  \n",
       "_id                           \n",
       "2355834  2019-02-25 21:52:24  \n",
       "9907713  2019-02-27 04:35:21  \n",
       "69826495 2019-02-26 15:46:21  \n",
       "89912090 2019-02-27 06:54:36  \n",
       "36676484 2019-02-26 08:50:46  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos_cur = mc['github']['projects'].find({\"_id\": {\"$in\": good_rids}}, ['url', 'language', 'created_at', 'updated_at'])\n",
    "repos_df = pd.DataFrame(list(repos_cur)).set_index('_id')[['url', 'language', 'created_at', 'updated_at']]\n",
    "repos_df.language = repos_df.language.astype(\"category\")\n",
    "repos_df = dropCategories(repos_df, \"language\", 42)\n",
    "repos_df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:27:31.915159Z",
     "start_time": "2019-08-14T14:27:28.744940Z"
    },
    "hidden": true,
    "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>login</th>\n",
       "      <th>type</th>\n",
       "      <th>fake</th>\n",
       "      <th>location</th>\n",
       "      <th>deleted</th>\n",
       "      <th>country_code</th>\n",
       "      <th>created_at</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>333093</th>\n",
       "      <td>dolaameng</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>False</td>\n",
       "      <td>sg</td>\n",
       "      <td>2008-11-26 05:26:45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41562368</th>\n",
       "      <td>0x7214FF</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2011-08-31 01:31:09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>569407</th>\n",
       "      <td>alekseybobkov</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2010-11-15 01:07:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35798413</th>\n",
       "      <td>alexholdenmiller</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>New York</td>\n",
       "      <td>False</td>\n",
       "      <td>us</td>\n",
       "      <td>2011-10-20 17:46:47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20472</th>\n",
       "      <td>dscape</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>London, United Kingdom</td>\n",
       "      <td>False</td>\n",
       "      <td>gb</td>\n",
       "      <td>2008-04-26 16:56:53</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     login type   fake                location  deleted  \\\n",
       "_id                                                                       \n",
       "333093           dolaameng  USR  False               Singapore    False   \n",
       "41562368          0x7214FF  USR  False                    None    False   \n",
       "569407       alekseybobkov  USR  False                    None    False   \n",
       "35798413  alexholdenmiller  USR  False                New York    False   \n",
       "20472               dscape  USR  False  London, United Kingdom    False   \n",
       "\n",
       "         country_code          created_at  \n",
       "_id                                        \n",
       "333093             sg 2008-11-26 05:26:45  \n",
       "41562368         None 2011-08-31 01:31:09  \n",
       "569407           None 2010-11-15 01:07:49  \n",
       "35798413           us 2011-10-20 17:46:47  \n",
       "20472              gb 2008-04-26 16:56:53  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ufields = ['login', 'type', 'fake', 'location', 'deleted', 'country_code', 'created_at']\n",
    "users_cur = mc['github']['users'].find({\"_id\": {\"$in\": good_uids}}, ufields)\n",
    "users_df = pd.DataFrame(list(users_cur)).set_index('_id')[ufields]\n",
    "users_df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:27:47.115249Z",
     "start_time": "2019-08-14T14:27:47.074896Z"
    },
    "hidden": true,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['./data/mysql-2019-06-01//graph_edges/embeddings_all_0.v10.h5',\n",
       " './data/mysql-2019-06-01//graph_edges/embeddings_all_1.v10.h5',\n",
       " './data/mysql-2019-06-01//graph_edges/embeddings_all_2.v10.h5',\n",
       " './data/mysql-2019-06-01//graph_edges/embeddings_all_3.v10.h5']"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = f'{DATA_FOLDER}/graph_edges'\n",
    "files = sorted([os.path.join(path, f) for f in os.listdir(path) if f.find(\"embeddings_all\") != -1])\n",
    "files = files[0:2] + files[8:] + files[2:8]\n",
    "files"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Next actions need some RAM!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:27:53.552992Z",
     "start_time": "2019-08-14T14:27:53.344585Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['relation', 'watches', 'follows', 'is_member_of']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1529391"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open(os.path.join(path, \"dictionary.json\"), \"rt\") as tf:\n",
    "    dictionary = json.load(tf)\n",
    "print(dictionary['relations'])\n",
    "all_entities = dictionary['entities']['all']\n",
    "len(all_entities)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:28:02.216501Z",
     "start_time": "2019-08-14T14:28:01.577044Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "user    1091930\n",
       "repo     437461\n",
       "dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.Series(all_entities).map(lambda x: x.split('_id_')[0]).value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Read embeddings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:28:08.532307Z",
     "start_time": "2019-08-14T14:28:07.992620Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "a994d2674fef458ba667e3d5d5d1d9fb",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=0, max=4), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(1529391, 100)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "embeddings=[]\n",
    "for file in tn(files):\n",
    "    with h5py.File(file, \"r\") as hf:\n",
    "        embedding = hf[\"embeddings\"][...]\n",
    "    embeddings.append(embedding)\n",
    "embeddings = np.concatenate(embeddings, axis=0)\n",
    "embeddings.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:28:14.230546Z",
     "start_time": "2019-08-14T14:28:12.067151Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "f65c411f47a14462ac90a811c4ad8e8e",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=1, bar_style='info', max=1), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(1529391, 1)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "emb_df = pd.Series({i: e for i, e in tn(zip(all_entities, embeddings))}).to_frame(name='embeddings')\n",
    "emb_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Combine all metainfo with embeddings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:28:19.729000Z",
     "start_time": "2019-08-14T14:28:19.367261Z"
    },
    "hidden": true,
    "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>url</th>\n",
       "      <th>language</th>\n",
       "      <th>created_at</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>embeddings</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14471725</th>\n",
       "      <td>justjavac/flarum</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>2014-12-24 14:01:38</td>\n",
       "      <td>2019-02-27 05:07:21</td>\n",
       "      <td>[0.20021674, -0.035547603, 0.14687921, 0.09368...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>266651</th>\n",
       "      <td>joewalnes/reconnecting-websocket</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>2012-01-27 20:28:51</td>\n",
       "      <td>2019-02-27 07:47:27</td>\n",
       "      <td>[0.8540273, 0.00038430316, 0.2725552, 0.238947...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28281221</th>\n",
       "      <td>up-for-grabs/up-for-grabs.net</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>2013-11-20 22:44:13</td>\n",
       "      <td>2019-02-26 12:30:42</td>\n",
       "      <td>[0.13455452, -0.08811931, -0.020408232, 0.1868...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5828347</th>\n",
       "      <td>HabitRPG/habitrpg</td>\n",
       "      <td>JavaScript</td>\n",
       "      <td>2012-06-06 22:49:48</td>\n",
       "      <td>2016-02-18 09:17:21</td>\n",
       "      <td>[0.6335141, -0.049198274, -0.040939253, 0.0998...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32907987</th>\n",
       "      <td>pybind/pybind11</td>\n",
       "      <td>C++</td>\n",
       "      <td>2015-07-05 19:46:48</td>\n",
       "      <td>2019-02-26 10:47:07</td>\n",
       "      <td>[0.31675598, -0.11396529, 0.041127887, -0.0468...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       url    language          created_at  \\\n",
       "_id                                                                          \n",
       "14471725                  justjavac/flarum  JavaScript 2014-12-24 14:01:38   \n",
       "266651    joewalnes/reconnecting-websocket  JavaScript 2012-01-27 20:28:51   \n",
       "28281221     up-for-grabs/up-for-grabs.net  JavaScript 2013-11-20 22:44:13   \n",
       "5828347                  HabitRPG/habitrpg  JavaScript 2012-06-06 22:49:48   \n",
       "32907987                   pybind/pybind11         C++ 2015-07-05 19:46:48   \n",
       "\n",
       "                  updated_at  \\\n",
       "_id                            \n",
       "14471725 2019-02-27 05:07:21   \n",
       "266651   2019-02-27 07:47:27   \n",
       "28281221 2019-02-26 12:30:42   \n",
       "5828347  2016-02-18 09:17:21   \n",
       "32907987 2019-02-26 10:47:07   \n",
       "\n",
       "                                                 embeddings  \n",
       "_id                                                          \n",
       "14471725  [0.20021674, -0.035547603, 0.14687921, 0.09368...  \n",
       "266651    [0.8540273, 0.00038430316, 0.2725552, 0.238947...  \n",
       "28281221  [0.13455452, -0.08811931, -0.020408232, 0.1868...  \n",
       "5828347   [0.6335141, -0.049198274, -0.040939253, 0.0998...  \n",
       "32907987  [0.31675598, -0.11396529, 0.041127887, -0.0468...  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos_emb_df = pd.Series({int(i.split('_')[-1]): e for i, e in emb_df.loc[[f'repo_id_{i}' for i in repos_df.index]]['embeddings'].iteritems()}).to_frame(name='embeddings')\n",
    "repos_df = repos_df.join(repos_emb_df)\n",
    "repos_df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T14:28:25.949490Z",
     "start_time": "2019-08-14T14:28:25.738816Z"
    },
    "hidden": true,
    "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>login</th>\n",
       "      <th>type</th>\n",
       "      <th>fake</th>\n",
       "      <th>location</th>\n",
       "      <th>deleted</th>\n",
       "      <th>country_code</th>\n",
       "      <th>created_at</th>\n",
       "      <th>embeddings</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12539209</th>\n",
       "      <td>NathAston</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>London</td>\n",
       "      <td>False</td>\n",
       "      <td>gb</td>\n",
       "      <td>2016-03-14 11:17:47</td>\n",
       "      <td>[-0.43741047, -0.10959671, 0.17184402, 0.16799...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14376</th>\n",
       "      <td>jsjohnst</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>New York City</td>\n",
       "      <td>False</td>\n",
       "      <td>us</td>\n",
       "      <td>2008-04-07 02:20:43</td>\n",
       "      <td>[-0.77265024, 0.03190932, 0.13513942, -0.00035...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31888096</th>\n",
       "      <td>AeronStory</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>True</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-10-18 15:04:57</td>\n",
       "      <td>[-0.97922194, 0.38436, 0.2280939, 0.09458058, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39277352</th>\n",
       "      <td>higgsfield</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2016-12-06 23:12:52</td>\n",
       "      <td>[0.5044715, -0.032197986, 0.0016144309, 0.0136...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13376482</th>\n",
       "      <td>Ljzn</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>home</td>\n",
       "      <td>False</td>\n",
       "      <td>us</td>\n",
       "      <td>2016-05-20 16:57:18</td>\n",
       "      <td>[-0.77141976, 0.25560012, -0.005199214, -0.012...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               login type   fake       location  deleted country_code  \\\n",
       "_id                                                                     \n",
       "12539209   NathAston  USR  False         London    False           gb   \n",
       "14376       jsjohnst  USR  False  New York City    False           us   \n",
       "31888096  AeronStory  USR  False           None     True         None   \n",
       "39277352  higgsfield  USR  False           None    False         None   \n",
       "13376482        Ljzn  USR  False           home    False           us   \n",
       "\n",
       "                  created_at  \\\n",
       "_id                            \n",
       "12539209 2016-03-14 11:17:47   \n",
       "14376    2008-04-07 02:20:43   \n",
       "31888096 2016-10-18 15:04:57   \n",
       "39277352 2016-12-06 23:12:52   \n",
       "13376482 2016-05-20 16:57:18   \n",
       "\n",
       "                                                 embeddings  \n",
       "_id                                                          \n",
       "12539209  [-0.43741047, -0.10959671, 0.17184402, 0.16799...  \n",
       "14376     [-0.77265024, 0.03190932, 0.13513942, -0.00035...  \n",
       "31888096  [-0.97922194, 0.38436, 0.2280939, 0.09458058, ...  \n",
       "39277352  [0.5044715, -0.032197986, 0.0016144309, 0.0136...  \n",
       "13376482  [-0.77141976, 0.25560012, -0.005199214, -0.012...  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_emb_df = pd.Series({int(i.split('_')[-1]): e for i, e in emb_df.loc[[f'user_id_{i}' for i in users_df.index]]['embeddings'].iteritems()}).to_frame(name='embeddings')\n",
    "users_df = users_df.join(users_emb_df)\n",
    "users_df.sample(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Save for TensorBoard"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-21T03:01:04.932546Z",
     "start_time": "2019-07-21T03:01:04.852663Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "repos_df.to_pickle(\"./tb/embeddings/repos_19k_gte1k.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-22T22:48:45.368031Z",
     "start_time": "2019-07-22T22:48:45.060806Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "users_df.to_pickle(\"./tb/embeddings/users_48k_gte100.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "If trained with `global = True` - embeddings for different types could be comparable (same hyperspace)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-22T22:55:35.800339Z",
     "start_time": "2019-07-22T22:55:35.388970Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "# repos_df.index = repos_df.index.map(lambda x: f'r{x}')\n",
    "# users_df.index = users_df.index.map(lambda x: f'u{x}')\n",
    "# combined_df = pd.concat([repos_df, users_df])\n",
    "# combined_df.to_pickle(\"./tb/embeddings/repos_19k_gte1k_users_48k_gte100.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "↑ embeddings for repos and users are far away from each other - no sense in viewing into combined multidimentional space\n",
    "\n",
    "(reason - no `global = True` in config for better independent representations)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-07-22T23:53:55.282108Z",
     "start_time": "2019-07-22T23:53:54.933524Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "!rm -rf ./tb/embeddings/.ipynb_checkpoints"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Now, `cd tb` & `run2.sh` at terminal with pre-installed docker or read `tb/README.md`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Then, open `HOSTNAME:PORT`, where `HOSTNAME` cane from machine, where the last script ran and `PORT` = `8002` (by default, but configurable)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Building Annoy (library to do nearest neighbor search)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-15T11:51:20.368659Z",
     "start_time": "2019-08-15T11:51:20.344330Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "from annoy import AnnoyIndex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Repos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Read dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:18.310040Z",
     "start_time": "2019-08-14T15:13:18.240816Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(18670, 5)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos_df = pd.read_pickle(\"./tb/embeddings/repos_19k_gte1k.pkl\")\n",
    "repos_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Dimension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:18.328565Z",
     "start_time": "2019-08-14T15:13:18.311093Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim = len(repos_df['embeddings'].iloc[0])\n",
    "dim"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Mapping for index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:18.505217Z",
     "start_time": "2019-08-14T15:13:18.330127Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "repos_ann = AnnoyIndex(dim, 'angular')  # Length of item vector that will be indexed\n",
    "repos_mapping = {}\n",
    "for i, (repo_id, e) in enumerate(repos_df['embeddings'].iteritems()):\n",
    "    repos_ann.add_item(i, list(e))\n",
    "    repos_mapping[repo_id] = i"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Build index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:57.282239Z",
     "start_time": "2019-08-14T15:13:18.506638Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 38.7 s, sys: 25.3 ms, total: 38.8 s\n",
      "Wall time: 38.8 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_trees = 1_000\n",
    "\n",
    "%time repos_ann.build(n_trees)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Save index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:57.436790Z",
     "start_time": "2019-08-14T15:13:57.283379Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "repos_ann.save('./data/repos.ann')\n",
    "pd.to_pickle(repos_mapping, './data/repos_mapping.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Read dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:57.537161Z",
     "start_time": "2019-08-14T15:13:57.437904Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(47666, 8)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df = pd.read_pickle(\"./tb/embeddings/users_48k_gte100.pkl\")\n",
    "users_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Dimension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:57.554931Z",
     "start_time": "2019-08-14T15:13:57.538181Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim = len(users_df['embeddings'].iloc[0])\n",
    "dim"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Mapping for index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:13:57.961505Z",
     "start_time": "2019-08-14T15:13:57.555996Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "users_ann = AnnoyIndex(dim, 'angular')\n",
    "users_mapping = {}\n",
    "for i, (user_id, e) in enumerate(users_df['embeddings'].iteritems()):\n",
    "    users_ann.add_item(i, list(e))\n",
    "    users_mapping[user_id] = i"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Build index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:15:52.870605Z",
     "start_time": "2019-08-14T15:13:57.962745Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 1min 54s, sys: 147 ms, total: 1min 54s\n",
      "Wall time: 1min 54s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_trees = 1_000\n",
    "\n",
    "%time users_ann.build(n_trees)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Save index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:15:53.235615Z",
     "start_time": "2019-08-14T15:15:52.871651Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "users_ann.save('./data/users.ann')\n",
    "pd.to_pickle(users_mapping, './data/users_mapping.pkl')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true
   },
   "source": [
    "## Recommender search example"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Repos"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Read dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-15T11:51:32.744471Z",
     "start_time": "2019-08-15T11:51:32.691713Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(18670, 5)"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos_df = pd.read_pickle(\"./tb/embeddings/repos_19k_gte1k.pkl\")\n",
    "repos_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Dimension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-15T11:51:34.515216Z",
     "start_time": "2019-08-15T11:51:34.493795Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim = len(repos_df['embeddings'].iloc[0])\n",
    "dim"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Read index & mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-15T11:51:36.318632Z",
     "start_time": "2019-08-15T11:51:36.288827Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18670"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repos_ann = AnnoyIndex(dim, 'angular')\n",
    "repos_ann.load('./data/repos.ann') # super fast, will just mmap the file\n",
    "repos_mapping = pd.read_pickle('./data/repos_mapping.pkl')\n",
    "repos_mapping_rev = {v: k for k, v in repos_mapping.items()}\n",
    "len(repos_mapping)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-15T11:51:38.289310Z",
     "start_time": "2019-08-15T11:51:38.263616Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def get_nns_by_repo_id(repo_id, n_nn=10):\n",
    "    res, dist = repos_ann.get_nns_by_item(repos_mapping[repo_id], n_nn, include_distances=True) # will find the n nearest neighbors\n",
    "    res = [repos_mapping_rev[r] for r in res]\n",
    "    res = repos_df.loc[res]\n",
    "    res['distance'] = [round(d, 3) for d in dist]\n",
    "    return res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Type interesting query for repos search (don't forget about unpopularity limitation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-15T13:42:59.595176Z",
     "start_time": "2019-08-15T13:42:59.556303Z"
    },
    "hidden": 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>url</th>\n",
       "      <th>language</th>\n",
       "      <th>created_at</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>embeddings</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8196280</th>\n",
       "      <td>apache/spark</td>\n",
       "      <td>Scala</td>\n",
       "      <td>2014-02-25 08:00:08</td>\n",
       "      <td>2019-02-26 23:57:18</td>\n",
       "      <td>[0.5067623, 0.013571289, -0.07234435, -0.00304...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  url language          created_at          updated_at  \\\n",
       "_id                                                                      \n",
       "8196280  apache/spark    Scala 2014-02-25 08:00:08 2019-02-26 23:57:18   \n",
       "\n",
       "                                                embeddings  \n",
       "_id                                                         \n",
       "8196280  [0.5067623, 0.013571289, -0.07234435, -0.00304...  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repo_query = 'apache/spark'\n",
    "\n",
    "repos_df[repos_df['url'].str.lower().str.contains(repo_query)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Choose needed one to search nearest neighbors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-15T13:43:03.318354Z",
     "start_time": "2019-08-15T13:43:03.276340Z"
    },
    "hidden": 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>url</th>\n",
       "      <th>language</th>\n",
       "      <th>created_at</th>\n",
       "      <th>updated_at</th>\n",
       "      <th>embeddings</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8196280</th>\n",
       "      <td>apache/spark</td>\n",
       "      <td>Scala</td>\n",
       "      <td>2014-02-25 08:00:08</td>\n",
       "      <td>2019-02-26 23:57:18</td>\n",
       "      <td>[0.5067623, 0.013571289, -0.07234435, -0.00304...</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45297</th>\n",
       "      <td>apache/kafka</td>\n",
       "      <td>Java</td>\n",
       "      <td>2011-08-15 18:06:16</td>\n",
       "      <td>2019-02-27 04:05:00</td>\n",
       "      <td>[0.69850904, 0.042590182, 0.0013614872, -0.006...</td>\n",
       "      <td>0.422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11610797</th>\n",
       "      <td>apache/storm</td>\n",
       "      <td>Java</td>\n",
       "      <td>2013-11-05 08:00:14</td>\n",
       "      <td>2019-02-27 04:45:34</td>\n",
       "      <td>[0.59941286, 0.078122355, 0.035178997, -0.0098...</td>\n",
       "      <td>0.445</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14807988</th>\n",
       "      <td>apache/flink</td>\n",
       "      <td>Java</td>\n",
       "      <td>2014-06-07 07:00:10</td>\n",
       "      <td>2019-02-27 04:11:52</td>\n",
       "      <td>[0.4457919, 0.11843297, -0.097657256, 0.076890...</td>\n",
       "      <td>0.460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39068</th>\n",
       "      <td>apache/hadoop</td>\n",
       "      <td>Java</td>\n",
       "      <td>2009-03-27 14:41:53</td>\n",
       "      <td>2019-02-26 11:50:35</td>\n",
       "      <td>[0.45037118, 0.10151473, 0.007142633, 0.081586...</td>\n",
       "      <td>0.491</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13773</th>\n",
       "      <td>apache/hbase</td>\n",
       "      <td>Java</td>\n",
       "      <td>2014-05-23 07:00:07</td>\n",
       "      <td>2019-02-27 00:06:19</td>\n",
       "      <td>[0.58324295, 0.006161812, -0.060786575, -0.005...</td>\n",
       "      <td>0.522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10305482</th>\n",
       "      <td>databricks/learning-spark</td>\n",
       "      <td>Java</td>\n",
       "      <td>2014-06-16 04:47:54</td>\n",
       "      <td>2019-02-26 10:02:28</td>\n",
       "      <td>[0.27490905, -0.076953545, -0.19611663, -0.061...</td>\n",
       "      <td>0.548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1366147</th>\n",
       "      <td>neo4j/neo4j</td>\n",
       "      <td>Java</td>\n",
       "      <td>2012-11-12 08:46:15</td>\n",
       "      <td>2019-02-27 02:33:20</td>\n",
       "      <td>[0.46941158, -0.06264764, 0.00042413553, -0.08...</td>\n",
       "      <td>0.564</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10045</th>\n",
       "      <td>akka/akka</td>\n",
       "      <td>Scala</td>\n",
       "      <td>2009-02-16 12:51:54</td>\n",
       "      <td>2019-02-26 10:48:39</td>\n",
       "      <td>[0.7051094, -0.10073524, 0.014133396, -0.09673...</td>\n",
       "      <td>0.566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4873</th>\n",
       "      <td>apache/lucene-solr</td>\n",
       "      <td>Java</td>\n",
       "      <td>2016-01-23 08:00:06</td>\n",
       "      <td>2019-02-27 03:04:07</td>\n",
       "      <td>[0.53500164, 0.0141313, -0.02256584, -0.024738...</td>\n",
       "      <td>0.571</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                url language          created_at  \\\n",
       "_id                                                                \n",
       "8196280                apache/spark    Scala 2014-02-25 08:00:08   \n",
       "45297                  apache/kafka     Java 2011-08-15 18:06:16   \n",
       "11610797               apache/storm     Java 2013-11-05 08:00:14   \n",
       "14807988               apache/flink     Java 2014-06-07 07:00:10   \n",
       "39068                 apache/hadoop     Java 2009-03-27 14:41:53   \n",
       "13773                  apache/hbase     Java 2014-05-23 07:00:07   \n",
       "10305482  databricks/learning-spark     Java 2014-06-16 04:47:54   \n",
       "1366147                 neo4j/neo4j     Java 2012-11-12 08:46:15   \n",
       "10045                     akka/akka    Scala 2009-02-16 12:51:54   \n",
       "4873             apache/lucene-solr     Java 2016-01-23 08:00:06   \n",
       "\n",
       "                  updated_at  \\\n",
       "_id                            \n",
       "8196280  2019-02-26 23:57:18   \n",
       "45297    2019-02-27 04:05:00   \n",
       "11610797 2019-02-27 04:45:34   \n",
       "14807988 2019-02-27 04:11:52   \n",
       "39068    2019-02-26 11:50:35   \n",
       "13773    2019-02-27 00:06:19   \n",
       "10305482 2019-02-26 10:02:28   \n",
       "1366147  2019-02-27 02:33:20   \n",
       "10045    2019-02-26 10:48:39   \n",
       "4873     2019-02-27 03:04:07   \n",
       "\n",
       "                                                 embeddings  distance  \n",
       "_id                                                                    \n",
       "8196280   [0.5067623, 0.013571289, -0.07234435, -0.00304...     0.000  \n",
       "45297     [0.69850904, 0.042590182, 0.0013614872, -0.006...     0.422  \n",
       "11610797  [0.59941286, 0.078122355, 0.035178997, -0.0098...     0.445  \n",
       "14807988  [0.4457919, 0.11843297, -0.097657256, 0.076890...     0.460  \n",
       "39068     [0.45037118, 0.10151473, 0.007142633, 0.081586...     0.491  \n",
       "13773     [0.58324295, 0.006161812, -0.060786575, -0.005...     0.522  \n",
       "10305482  [0.27490905, -0.076953545, -0.19611663, -0.061...     0.548  \n",
       "1366147   [0.46941158, -0.06264764, 0.00042413553, -0.08...     0.564  \n",
       "10045     [0.7051094, -0.10073524, 0.014133396, -0.09673...     0.566  \n",
       "4873      [0.53500164, 0.0141313, -0.02256584, -0.024738...     0.571  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "repo_id = 8196280\n",
    "\n",
    "get_nns_by_repo_id(repo_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "All Big Data, distributed tools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "heading_collapsed": true,
    "hidden": true
   },
   "source": [
    "### Users"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Read dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:25:37.035276Z",
     "start_time": "2019-08-14T15:25:36.933145Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(47666, 8)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_df = pd.read_pickle(\"./tb/embeddings/users_48k_gte100.pkl\")\n",
    "users_df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Dimension"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:25:37.265179Z",
     "start_time": "2019-08-14T15:25:37.231349Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dim = len(users_df['embeddings'].iloc[0])\n",
    "dim"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Read index & mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:25:37.560919Z",
     "start_time": "2019-08-14T15:25:37.535249Z"
    },
    "hidden": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "47666"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_ann = AnnoyIndex(dim, 'angular')\n",
    "users_ann.load('./data/users.ann') # super fast, will just mmap the file\n",
    "users_mapping = pd.read_pickle('./data/users_mapping.pkl')\n",
    "users_mapping_rev = {v: k for k, v in users_mapping.items()}\n",
    "len(users_mapping)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:25:37.728211Z",
     "start_time": "2019-08-14T15:25:37.687410Z"
    },
    "hidden": true
   },
   "outputs": [],
   "source": [
    "def get_nns_by_user_id(user_id, n_nn=10):\n",
    "    res, dist = users_ann.get_nns_by_item(users_mapping[user_id], n_nn, include_distances=True) # will find the n nearest neighbors\n",
    "    res = [users_mapping_rev[r] for r in res]\n",
    "    res = users_df.loc[res]\n",
    "    res['distance'] = [round(d, 3) for d in dist]\n",
    "    return res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Type interesting query for users search (don't forget about unpopularity limitation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:25:38.410030Z",
     "start_time": "2019-08-14T15:25:38.359784Z"
    },
    "hidden": 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>login</th>\n",
       "      <th>type</th>\n",
       "      <th>fake</th>\n",
       "      <th>location</th>\n",
       "      <th>deleted</th>\n",
       "      <th>country_code</th>\n",
       "      <th>created_at</th>\n",
       "      <th>embeddings</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1652246</th>\n",
       "      <td>wikibook</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-02-22 06:53:02</td>\n",
       "      <td>[-0.12921746, -0.4415784, -0.40390605, 0.12089...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4016654</th>\n",
       "      <td>wikimatze</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>Berlin</td>\n",
       "      <td>False</td>\n",
       "      <td>de</td>\n",
       "      <td>2010-05-04 14:46:36</td>\n",
       "      <td>[-0.3107245, 0.10202297, 0.2773474, -0.2349214...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             login type   fake location  deleted country_code  \\\n",
       "_id                                                             \n",
       "1652246   wikibook  USR  False     None    False         None   \n",
       "4016654  wikimatze  USR  False   Berlin    False           de   \n",
       "\n",
       "                 created_at                                         embeddings  \n",
       "_id                                                                             \n",
       "1652246 2013-02-22 06:53:02  [-0.12921746, -0.4415784, -0.40390605, 0.12089...  \n",
       "4016654 2010-05-04 14:46:36  [-0.3107245, 0.10202297, 0.2773474, -0.2349214...  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_query = 'wiki'\n",
    "\n",
    "users_df[users_df['login'].str.lower().str.contains(user_query)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Choose needed one to search nearest neighbors"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-14T15:25:39.418623Z",
     "start_time": "2019-08-14T15:25:39.367888Z"
    },
    "hidden": 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>login</th>\n",
       "      <th>type</th>\n",
       "      <th>fake</th>\n",
       "      <th>location</th>\n",
       "      <th>deleted</th>\n",
       "      <th>country_code</th>\n",
       "      <th>created_at</th>\n",
       "      <th>embeddings</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1652246</th>\n",
       "      <td>wikibook</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2013-02-22 06:53:02</td>\n",
       "      <td>[-0.12921746, -0.4415784, -0.40390605, 0.12089...</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3617048</th>\n",
       "      <td>gilbutITbook</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>서울특별시 마포구 서교동 467-9</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2014-03-19 05:48:20</td>\n",
       "      <td>[0.1376717, -0.29175922, -0.17365614, -0.05276...</td>\n",
       "      <td>0.839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>286093</th>\n",
       "      <td>javajigi</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2010-12-13 00:06:41</td>\n",
       "      <td>[0.064416006, -0.28612432, -0.28292352, 0.0340...</td>\n",
       "      <td>0.853</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>182762</th>\n",
       "      <td>dalinaum</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>The Peach Blossom Spring</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2009-10-28 01:13:40</td>\n",
       "      <td>[-0.86923313, -0.008363037, -0.31862542, 0.166...</td>\n",
       "      <td>0.873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>884777</th>\n",
       "      <td>ihoneymon</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>Seoul, South Korea</td>\n",
       "      <td>False</td>\n",
       "      <td>kr</td>\n",
       "      <td>2011-06-26 12:56:10</td>\n",
       "      <td>[-0.037841484, -0.36508644, -0.052486323, 0.08...</td>\n",
       "      <td>0.894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5712011</th>\n",
       "      <td>arahansa</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>Incheon, South Korea</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2014-01-18 12:54:37</td>\n",
       "      <td>[-0.5806951, -0.10983159, 0.040602323, -0.3568...</td>\n",
       "      <td>0.909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13295853</th>\n",
       "      <td>seoul-opengov</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>seoul, korea</td>\n",
       "      <td>False</td>\n",
       "      <td>kr</td>\n",
       "      <td>2016-04-25 09:22:59</td>\n",
       "      <td>[-0.26374817, -0.39799082, -0.021314356, -0.03...</td>\n",
       "      <td>0.912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68757</th>\n",
       "      <td>jongman</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>False</td>\n",
       "      <td>None</td>\n",
       "      <td>2010-03-22 05:25:55</td>\n",
       "      <td>[-0.29828942, -0.373523, 0.040735707, 0.066990...</td>\n",
       "      <td>0.915</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>68445</th>\n",
       "      <td>Sangwook</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>seoul</td>\n",
       "      <td>False</td>\n",
       "      <td>kr</td>\n",
       "      <td>2009-09-05 05:40:53</td>\n",
       "      <td>[-0.6826483, -0.31995863, -0.1767753, 0.093110...</td>\n",
       "      <td>0.937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>347837</th>\n",
       "      <td>msbaek</td>\n",
       "      <td>USR</td>\n",
       "      <td>False</td>\n",
       "      <td>Seoul, KOREA</td>\n",
       "      <td>False</td>\n",
       "      <td>kr</td>\n",
       "      <td>2008-08-27 04:49:51</td>\n",
       "      <td>[-0.3363302, -0.46947053, -0.050381757, -0.089...</td>\n",
       "      <td>0.938</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  login type   fake                  location  deleted  \\\n",
       "_id                                                                      \n",
       "1652246        wikibook  USR  False                      None    False   \n",
       "3617048    gilbutITbook  USR  False       서울특별시 마포구 서교동 467-9    False   \n",
       "286093         javajigi  USR  False                      None    False   \n",
       "182762         dalinaum  USR  False  The Peach Blossom Spring    False   \n",
       "884777        ihoneymon  USR  False        Seoul, South Korea    False   \n",
       "5712011        arahansa  USR  False      Incheon, South Korea    False   \n",
       "13295853  seoul-opengov  USR  False              seoul, korea    False   \n",
       "68757           jongman  USR  False                      None    False   \n",
       "68445          Sangwook  USR  False                     seoul    False   \n",
       "347837           msbaek  USR  False              Seoul, KOREA    False   \n",
       "\n",
       "         country_code          created_at  \\\n",
       "_id                                         \n",
       "1652246          None 2013-02-22 06:53:02   \n",
       "3617048          None 2014-03-19 05:48:20   \n",
       "286093           None 2010-12-13 00:06:41   \n",
       "182762           None 2009-10-28 01:13:40   \n",
       "884777             kr 2011-06-26 12:56:10   \n",
       "5712011          None 2014-01-18 12:54:37   \n",
       "13295853           kr 2016-04-25 09:22:59   \n",
       "68757            None 2010-03-22 05:25:55   \n",
       "68445              kr 2009-09-05 05:40:53   \n",
       "347837             kr 2008-08-27 04:49:51   \n",
       "\n",
       "                                                 embeddings  distance  \n",
       "_id                                                                    \n",
       "1652246   [-0.12921746, -0.4415784, -0.40390605, 0.12089...     0.000  \n",
       "3617048   [0.1376717, -0.29175922, -0.17365614, -0.05276...     0.839  \n",
       "286093    [0.064416006, -0.28612432, -0.28292352, 0.0340...     0.853  \n",
       "182762    [-0.86923313, -0.008363037, -0.31862542, 0.166...     0.873  \n",
       "884777    [-0.037841484, -0.36508644, -0.052486323, 0.08...     0.894  \n",
       "5712011   [-0.5806951, -0.10983159, 0.040602323, -0.3568...     0.909  \n",
       "13295853  [-0.26374817, -0.39799082, -0.021314356, -0.03...     0.912  \n",
       "68757     [-0.29828942, -0.373523, 0.040735707, 0.066990...     0.915  \n",
       "68445     [-0.6826483, -0.31995863, -0.1767753, 0.093110...     0.937  \n",
       "347837    [-0.3363302, -0.46947053, -0.050381757, -0.089...     0.938  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_id = 1652246\n",
    "\n",
    "get_nns_by_user_id(user_id)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "Korean \"cluster\"\n",
    "\n",
    "If we go for their profiles - some similar stuff could be found there"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "hidden": true
   },
   "source": [
    "It is essential to remember about absolute distance, not just TOP N, because some entities have neighbors close to them, while others could have not"
   ]
  }
 ],
 "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.7.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "position": {
    "height": "144px",
    "left": "1307px",
    "right": "20px",
    "top": "117px",
    "width": "350px"
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}