AS_2_Convert-to-graph.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# read data\n",
    "import pandas as pd\n",
    "import data_utils as du\n",
    "\n",
    "path_to_media = './data/extra_infos.json'\n",
    "path_to_train = './data/train.csv'\n",
    "\n",
    "media_info = du.json_to_pandas(path_to_media)\n",
    "train = pd.read_csv(path_to_train, encoding='utf-8', usecols=['genre_id', 'media_id', 'album_id', 'release_date', 'media_duration', 'artist_id'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>genre_id</th>\n",
       "      <th>media_id</th>\n",
       "      <th>album_id</th>\n",
       "      <th>release_date</th>\n",
       "      <th>media_duration</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>alb_title</th>\n",
       "      <th>art_name</th>\n",
       "      <th>sng_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>25471</td>\n",
       "      <td>222606</td>\n",
       "      <td>41774</td>\n",
       "      <td>20040704</td>\n",
       "      <td>223</td>\n",
       "      <td>55164</td>\n",
       "      <td>Malin plaisir</td>\n",
       "      <td>Malin Plaisir</td>\n",
       "      <td>Seul au monde</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>25571</td>\n",
       "      <td>250467</td>\n",
       "      <td>43941</td>\n",
       "      <td>20060301</td>\n",
       "      <td>171</td>\n",
       "      <td>55830</td>\n",
       "      <td>Stop talking / dead tapes</td>\n",
       "      <td>Soldout</td>\n",
       "      <td>You're not ok</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>16</td>\n",
       "      <td>305197</td>\n",
       "      <td>48078</td>\n",
       "      <td>20140714</td>\n",
       "      <td>149</td>\n",
       "      <td>2704</td>\n",
       "      <td>Dérives</td>\n",
       "      <td>René Aubry</td>\n",
       "      <td>Samuel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "      <td>900502</td>\n",
       "      <td>71521</td>\n",
       "      <td>20001030</td>\n",
       "      <td>240</td>\n",
       "      <td>938</td>\n",
       "      <td>Estilolibre</td>\n",
       "      <td>Eros Ramazzotti</td>\n",
       "      <td>Mujer Amiga Mia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7</td>\n",
       "      <td>542335</td>\n",
       "      <td>71718</td>\n",
       "      <td>20080215</td>\n",
       "      <td>150</td>\n",
       "      <td>2939</td>\n",
       "      <td>Collections - The Byrds Play Dylan</td>\n",
       "      <td>The Byrds</td>\n",
       "      <td>Mr. Tambourine Man</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   genre_id  media_id  album_id  release_date  media_duration  artist_id  \\\n",
       "0     25471    222606     41774      20040704             223      55164   \n",
       "1     25571    250467     43941      20060301             171      55830   \n",
       "2        16    305197     48078      20140714             149       2704   \n",
       "3         7    900502     71521      20001030             240        938   \n",
       "4         7    542335     71718      20080215             150       2939   \n",
       "\n",
       "                            alb_title         art_name           sng_title  \n",
       "0                       Malin plaisir    Malin Plaisir       Seul au monde  \n",
       "1           Stop talking / dead tapes          Soldout       You're not ok  \n",
       "2                             Dérives       René Aubry              Samuel  \n",
       "3                         Estilolibre  Eros Ramazzotti     Mujer Amiga Mia  \n",
       "4  Collections - The Byrds Play Dylan        The Byrds  Mr. Tambourine Man  "
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# merge data\n",
    "media = pd.merge(train, media_info, on='media_id')\n",
    "media.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>artist_id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>The Beatles</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>The Clash</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Snoop Dogg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Amadou &amp; Mariam</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   artist_id             name\n",
       "0          1      The Beatles\n",
       "1          2        The Clash\n",
       "2          3       Snoop Dogg\n",
       "3          4          America\n",
       "4          5  Amadou & Mariam"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select all artists\n",
    "by_artist = media.groupby(['artist_id'], as_index=False).agg({'art_name' : 'first'})\n",
    "by_artist[\"name\"] = by_artist[\"art_name\"]\n",
    "del by_artist[\"art_name\"]\n",
    "by_artist.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Python driver для Neo4j - гівно, працює дуже повільно. Буду імпортувати csv. Але перед цим мені потрібно зберегти ці значення."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "by_artist.to_csv('./data/graph/import/artists.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USING PERIODIC COMMIT 5000\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"file:///artists.csv\" AS row \n",
    "\n",
    "MERGE (artist:Artist {art_id: toInt(row.artist_id)})\n",
    "\n",
    "ON CREATE SET artist.art_id = toInt(row.artist_id), artist.name = row.name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CREATE CONSTRAINT ON (a:Artist) ASSERT a.art_id IS UNIQUE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Артисти є. Що ще треба завантажити:\n",
    "\n",
    "1. Альбоми. Зв'язки між виконавцями та альбомами. (done)\n",
    "\n",
    "2. Пісні. Зв'язки між альбомами та піснями (done)\n",
    "\n",
    "3. Користувачів. (done)\n",
    "\n",
    "4. Прослуховування - зв'язок між користувачем і піснею. (done)\n",
    "\n",
    "5. Жанри. Зв'язок між жанром і піснею. (done)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>album_id</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>alb_title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1976</td>\n",
       "      <td>443</td>\n",
       "      <td>Terrain vague</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4096</td>\n",
       "      <td>443</td>\n",
       "      <td>Croc' noces</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>6108</td>\n",
       "      <td>443</td>\n",
       "      <td>Fausses notes et repris de justesse</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6558</td>\n",
       "      <td>443</td>\n",
       "      <td>Irfan, le héros</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7782</td>\n",
       "      <td>443</td>\n",
       "      <td>Rue du temps</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   album_id  artist_id                            alb_title\n",
       "0      1976        443                        Terrain vague\n",
       "1      4096        443                          Croc' noces\n",
       "2      6108        443  Fausses notes et repris de justesse\n",
       "3      6558        443                      Irfan, le héros\n",
       "4      7782        443                         Rue du temps"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by_album = media.groupby(['album_id'], as_index=False).agg({'alb_title' : 'first', 'artist_id':'first'})\n",
    "by_album.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "by_album.to_csv('./data/graph/import/albums.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Думаю, який обрати relation_type. Тобто це ж має бути не з реального світу, а те що мені підходить. Типу ART_RELATED"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "https://neo4j.com/docs/developer-manual/current/get-started/cypher/importing-csv-files-with-cypher/\n",
    "    \n",
    "http://neo4j.com/docs/developer-manual/current/cypher/clauses/load-csv/\n",
    "\n",
    "https://neo4j.com/blog/bulk-data-import-neo4j-3-0/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USING PERIODIC COMMIT 10000\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"file:///albums.csv\" AS row \n",
    "\n",
    "MERGE (album:Album {alb_id: toInt(row.album_id)}) \n",
    "\n",
    "ON CREATE SET album.alb_id = toInt(row.album_id), album.name = row.alb_title\n",
    "\n",
    "WITH row, album\n",
    "\n",
    "MATCH (art:Artist)\n",
    "\n",
    "WHERE art.art_id = toInt(row.artist_id)\n",
    "\n",
    "MERGE (album)-[:ART_RELATED]-(art)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CREATE CONSTRAINT ON (a:Album) ASSERT a.alb_id IS UNIQUE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>media_id</th>\n",
       "      <th>sng_title</th>\n",
       "      <th>album_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200058</td>\n",
       "      <td>À saint nazaire y'a t'une brune</td>\n",
       "      <td>40004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200091</td>\n",
       "      <td>Robert et josiane</td>\n",
       "      <td>40006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200098</td>\n",
       "      <td>2 bi 3</td>\n",
       "      <td>40006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200099</td>\n",
       "      <td>Blanche neige et les 7 scouts</td>\n",
       "      <td>40006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200102</td>\n",
       "      <td>Mais qu'est-ce qui se passe ?</td>\n",
       "      <td>40006</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   media_id                        sng_title  album_id\n",
       "0    200058  À saint nazaire y'a t'une brune     40004\n",
       "1    200091                Robert et josiane     40006\n",
       "2    200098                           2 bi 3     40006\n",
       "3    200099    Blanche neige et les 7 scouts     40006\n",
       "4    200102    Mais qu'est-ce qui se passe ?     40006"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by_media = media.groupby(['media_id'], as_index=False).agg({'sng_title' : 'first', 'album_id':'first'})\n",
    "by_media.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "by_media.to_csv('./data/graph/import/media.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USING PERIODIC COMMIT 30000\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"file:///media.csv\" AS row\n",
    "\n",
    "MERGE (media:Media {media_id: row.media_id})\n",
    "\n",
    "ON CREATE SET media.media_id = row.media_id, media.name = row.sng_title\n",
    "\n",
    "WITH row, media\n",
    "\n",
    "MATCH (alb:Album)\n",
    "\n",
    "WHERE alb.alb_id = toInt(row.album_id)\n",
    "\n",
    "MERGE (media)-[:ART_RELATED]-(album)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Чогось зависає останній запис. Спробую запустити його по частинах, спочатку створення вершини, а потім створення зв'язку."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USING PERIODIC COMMIT 30000\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"file:///media.csv\" AS row\n",
    "\n",
    "MERGE (media:Media {media_id: row.media_id})\n",
    "\n",
    "ON CREATE SET media.media_id = row.media_id, media.name = row.sng_title"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USING PERIODIC COMMIT 10000\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"file:///media.csv\" AS row\n",
    "\n",
    "MATCH (album:Album),(media:Media)\n",
    "\n",
    "WHERE album.alb_id = toInt(row.album_id) AND media.media_id = row.media_id\n",
    "\n",
    "MERGE (media)-[:ART_RELATED]-(album)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CREATE CONSTRAINT ON (a:Media) ASSERT a.media_id IS UNIQUE"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Зроблю ще жанри, щоб не перекачувати датасет і перейду до користувачів"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>media_id</th>\n",
       "      <th>genre_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>200058</td>\n",
       "      <td>25471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>200091</td>\n",
       "      <td>25471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>200098</td>\n",
       "      <td>25471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>200099</td>\n",
       "      <td>25471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200102</td>\n",
       "      <td>25471</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   media_id  genre_id\n",
       "0    200058     25471\n",
       "1    200091     25471\n",
       "2    200098     25471\n",
       "3    200099     25471\n",
       "4    200102     25471"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by_genre = media.groupby(['media_id'], as_index=False).agg({'genre_id' : 'first'})\n",
    "by_genre.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "by_genre.to_csv('./data/graph/import/genres.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Тепер буду завжди розділяти створення вершин і ребер при імпорті щоб простіше було дебажити."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USING PERIODIC COMMIT 30000\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"file:///genres.csv\" AS row\n",
    "\n",
    "MERGE (genre:Genre {genre_id: row.genre_id})\n",
    "\n",
    "ON CREATE SET genre.genre_id = row.genre_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Чого я так тупанув і включив колонку `media_id` взагалі? Треба було спочатку створити жанри, а потім вже додати до датасету медіа жанр і створювати рілейшени."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USING PERIODIC COMMIT 30000\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"file:///genres.csv\" AS row\n",
    "\n",
    "MATCH (genre:Genre),(media:Media)\n",
    "\n",
    "WHERE genre.genre_id = row.genre_id AND media.media_id = row.media_id\n",
    "\n",
    "MERGE (media)-[:GENRE_IS]->(genre)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CREATE CONSTRAINT ON (a:Genre) ASSERT a.genre_id IS UNIQUE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "path_to_train = './data/train.csv'\n",
    "\n",
    "train = pd.read_csv(path_to_train, encoding='utf-8', usecols=['user_id', 'media_id', 'is_listened', 'ts_listen' ])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Я думаю, потім можна додати зв'язок `is_not_listened`. Але поки що буде досить взяти всі `IS_LISTENED`. Це і так дофіга данних."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>media_id</th>\n",
       "      <th>is_listened</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>17390736</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1109488</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2438245</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>983205</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>876497</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  media_id  is_listened\n",
       "0        0  17390736            1\n",
       "1        1   1109488            1\n",
       "2        2   2438245            1\n",
       "3        3    983205            1\n",
       "4        4    876497            1"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "by_user = train.groupby('user_id', as_index=False).first()\n",
    "by_user.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "by_user.to_csv('./data/graph/import/users.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USING PERIODIC COMMIT 10000\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"file:///users.csv\" AS row\n",
    "\n",
    "MERGE (user:User {user_id: row.user_id})\n",
    "\n",
    "ON CREATE SET user.user_id = row.user_id"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CREATE CONSTRAINT ON (a:User) ASSERT a.user_id IS UNIQUE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>user_id</th>\n",
       "      <th>media_id</th>\n",
       "      <th>ts_listen</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>211678</td>\n",
       "      <td>1479414226</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>211796</td>\n",
       "      <td>1479971016</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>238286</td>\n",
       "      <td>1480576692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>286191</td>\n",
       "      <td>1480153220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>304849</td>\n",
       "      <td>1478078842</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id  media_id   ts_listen\n",
       "0        0    211678  1479414226\n",
       "1        0    211796  1479971016\n",
       "2        0    238286  1480576692\n",
       "3        0    286191  1480153220\n",
       "4        0    304849  1478078842"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "listened = train[train['is_listened'] == 1].sort_values(by='ts_listen').groupby(['user_id', 'media_id'],as_index=False).agg({'ts_listen':'first'})\n",
    "listened.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "listened.to_csv('./data/graph/import/listened.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "USING PERIODIC COMMIT 50000\n",
    "\n",
    "LOAD CSV WITH HEADERS FROM \"file:///listened.csv\" AS row\n",
    "\n",
    "MATCH (user:User), (media:Media)\n",
    "\n",
    "WHERE user.user_id = row.user_id AND media.media_id = row.media_id\n",
    "\n",
    "MERGE (user)-[:LISTENED {ts_listened: row.ts_listen}]->(media)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [py35]",
   "language": "python",
   "name": "Python [py35]"
  },
  "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.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}