notebooks/tracking_link_formation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import psycopg2\n",
    "\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "from copy import deepcopy\n",
    "\n",
    "import ast\n",
    "import random\n",
    "import networkx as nx\n",
    "import time, unicodedata\n",
    "import itertools\n",
    "\n",
    "from fuzzywuzzy import fuzz\n",
    "from fuzzywuzzy import process\n",
    "\n",
    "from joblib import Parallel, delayed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean(name, min_len=5, junk_replacement=''):\n",
    "    try:\n",
    "        cleaned = unicodedata.normalize('NFKD', name).encode('ascii', 'ignore').lower().decode(\"ascii\")\n",
    "    except TypeError:\n",
    "        return junk_replacement\n",
    "    if len(cleaned) < min_len:\n",
    "        return junk_replacement\n",
    "    return cleaned\n",
    "\n",
    "def get_matches_edit_distance(item, choices, limit, scorer=fuzz.WRatio):\n",
    "    return process.extract(item, choices, limit=limit, scorer=scorer)\n",
    "counter = 0\n",
    "def get_sehir_twitter_matches(twitter_users, sehir_directory, limit=1):\n",
    "    global fullnames, counter\n",
    "    twitter_user_by_screen_name = twitter_users.set_index('screen_name')\n",
    "    start = time.time()\n",
    "    for screen_name in twitter_users['screen_name']:\n",
    "        twitter_name = twitter_user_by_screen_name.loc[screen_name]['name']\n",
    "        match_name = get_matches_edit_distance(twitter_name, fullnames, limit)\n",
    "        counter += 1\n",
    "#         if counter %100 == 0:\n",
    "#             print(counter, \"out of \", len(twitter_users))\n",
    "#             start_ = time.time()\n",
    "#             print(start_-start, \"seconds\")\n",
    "#             start = start_\n",
    "        yield (screen_name, match_name)\n",
    "        \n",
    "def filter_matches_by_threshold(matches_dict, threshold=70):\n",
    "    filtered_dict = dict()\n",
    "    for screen_name, matches in matches_dict.items():\n",
    "        filtered = [(match, score) for match, score in matches if score > threshold]\n",
    "        \n",
    "        if filtered:\n",
    "            filtered_dict[screen_name] = filtered\n",
    "        \n",
    "    return filtered_dict\n",
    "\n",
    "def get_matches_dataframe(twitter_users, sehir_directory, threshold=70, limit=1):\n",
    "    matches = {screen_name : match_name for screen_name, match_name in \n",
    "               get_sehir_twitter_matches(twitter_users, sehir_directory, limit=limit)}\n",
    "    \n",
    "    filtered_matches = filter_matches_by_threshold(matches, threshold=threshold)\n",
    "    screen_names = filtered_matches.keys()\n",
    "    return pd.DataFrame({'screen_name': list(screen_names),\n",
    "                         'match_name': [filtered_matches[screen_name] for screen_name in screen_names]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "connection = psycopg2.connect('dbname=link_formation host=localhost user=postgres password=1_sehir_1')\n",
    "\n",
    "user_connections = pd.read_sql(\"SELECT * FROM twitter_connection\", connection).drop('id', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": false
   },
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>lang</th>\n",
       "      <th>match_name</th>\n",
       "      <th>match_ratio</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>truncated_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>18811</th>\n",
       "      <td>4.647111e+08</td>\n",
       "      <td>zeki kafcı</td>\n",
       "      <td>kafcizeki</td>\n",
       "      <td>en-gb</td>\n",
       "      <td>Zeki Murat Karakoc</td>\n",
       "      <td>86.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>158.0</td>\n",
       "      <td>464711123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33615</th>\n",
       "      <td>9.496794e+17</td>\n",
       "      <td>kübra</td>\n",
       "      <td>kbra84254172</td>\n",
       "      <td>en</td>\n",
       "      <td>Library</td>\n",
       "      <td>68.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>949679425</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23152</th>\n",
       "      <td>9.978261e+17</td>\n",
       "      <td>Selo  dayi</td>\n",
       "      <td>Selahat92466917</td>\n",
       "      <td>en</td>\n",
       "      <td>Selman Gunaydin</td>\n",
       "      <td>67.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>466.0</td>\n",
       "      <td>997826081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59654</th>\n",
       "      <td>1.349113e+09</td>\n",
       "      <td>Tarih Kulübü</td>\n",
       "      <td>TarihSinema</td>\n",
       "      <td>tr</td>\n",
       "      <td>! Tarih</td>\n",
       "      <td>90.0</td>\n",
       "      <td>3696.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>134911345</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46982</th>\n",
       "      <td>8.590194e+17</td>\n",
       "      <td>EÇ2💫</td>\n",
       "      <td>_eslemcoban2</td>\n",
       "      <td>tr</td>\n",
       "      <td>Erasmus -</td>\n",
       "      <td>45.0</td>\n",
       "      <td>623.0</td>\n",
       "      <td>147.0</td>\n",
       "      <td>859019351</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id          name      screen_name   lang  \\\n",
       "18811  4.647111e+08    zeki kafcı        kafcizeki  en-gb   \n",
       "33615  9.496794e+17         kübra     kbra84254172     en   \n",
       "23152  9.978261e+17    Selo  dayi  Selahat92466917     en   \n",
       "59654  1.349113e+09  Tarih Kulübü      TarihSinema     tr   \n",
       "46982  8.590194e+17          EÇ2💫     _eslemcoban2     tr   \n",
       "\n",
       "                match_name  match_ratio  followers_count  friends_count  \\\n",
       "18811   Zeki Murat Karakoc         86.0             48.0          158.0   \n",
       "33615              Library         68.0              0.0           35.0   \n",
       "23152      Selman Gunaydin         67.0             27.0          466.0   \n",
       "59654              ! Tarih         90.0           3696.0           29.0   \n",
       "46982            Erasmus -         45.0            623.0          147.0   \n",
       "\n",
       "       truncated_id  \n",
       "18811     464711123  \n",
       "33615     949679425  \n",
       "23152     997826081  \n",
       "59654     134911345  \n",
       "46982     859019351  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "truncate = lambda x: int(str(int(x))[:9])\n",
    "\n",
    "twitter_users = pd.read_sql(\"SELECT * FROM twitter_user\", connection)\n",
    "twitter_users = twitter_users.where(twitter_users.match_name.str.len()>6)\\\n",
    "                             .dropna()\n",
    "twitter_users[\"truncated_id\"] = twitter_users.id.apply(truncate)\n",
    "\n",
    "#         .where(twitter_users.match_ratio>85)\n",
    "# .where(~twitter_users.name.str.contains(\"(?i)sehir\"))\\\n",
    "twitter_users.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>lang</th>\n",
       "      <th>match_name</th>\n",
       "      <th>match_ratio</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>truncated_id</th>\n",
       "      <th>is_org</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>48260</th>\n",
       "      <td>9.619998e+17</td>\n",
       "      <td>Yağmur</td>\n",
       "      <td>yagmurakin0</td>\n",
       "      <td>tr</td>\n",
       "      <td>Yagmur Simsek</td>\n",
       "      <td>72.0</td>\n",
       "      <td>2806.0</td>\n",
       "      <td>4523.0</td>\n",
       "      <td>961999779</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20731</th>\n",
       "      <td>2.153424e+08</td>\n",
       "      <td>Ramazan YAŞAR</td>\n",
       "      <td>RYASAR</td>\n",
       "      <td>tr</td>\n",
       "      <td>Ramazan Hasar</td>\n",
       "      <td>88.0</td>\n",
       "      <td>629.0</td>\n",
       "      <td>2349.0</td>\n",
       "      <td>215342367</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12361</th>\n",
       "      <td>5.215949e+08</td>\n",
       "      <td>Sinema Bağımlısı</td>\n",
       "      <td>sinemabagimlisi</td>\n",
       "      <td>tr</td>\n",
       "      <td>Sinema Televizyon Admin</td>\n",
       "      <td>86.0</td>\n",
       "      <td>726926.0</td>\n",
       "      <td>7535.0</td>\n",
       "      <td>521594852</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50737</th>\n",
       "      <td>7.743138e+17</td>\n",
       "      <td>TuthanKamoN</td>\n",
       "      <td>SalihUygur6</td>\n",
       "      <td>tr</td>\n",
       "      <td>Safa Batuhan Kamanli</td>\n",
       "      <td>74.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>229.0</td>\n",
       "      <td>774313785</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9828</th>\n",
       "      <td>1.421121e+09</td>\n",
       "      <td>Şehir İşletme Bölümü</td>\n",
       "      <td>sehirisletme</td>\n",
       "      <td>tr</td>\n",
       "      <td>Şehir Üniversitesi (İnsan ve Toplum Bilimleri...</td>\n",
       "      <td>86.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>142112088</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 id                  name      screen_name lang  \\\n",
       "48260  9.619998e+17                Yağmur      yagmurakin0   tr   \n",
       "20731  2.153424e+08         Ramazan YAŞAR           RYASAR   tr   \n",
       "12361  5.215949e+08      Sinema Bağımlısı  sinemabagimlisi   tr   \n",
       "50737  7.743138e+17           TuthanKamoN      SalihUygur6   tr   \n",
       "9828   1.421121e+09  Şehir İşletme Bölümü     sehirisletme   tr   \n",
       "\n",
       "                                              match_name  match_ratio  \\\n",
       "48260                                      Yagmur Simsek         72.0   \n",
       "20731                                      Ramazan Hasar         88.0   \n",
       "12361                            Sinema Televizyon Admin         86.0   \n",
       "50737                               Safa Batuhan Kamanli         74.0   \n",
       "9828    Şehir Üniversitesi (İnsan ve Toplum Bilimleri...         86.0   \n",
       "\n",
       "       followers_count  friends_count  truncated_id  is_org  \n",
       "48260           2806.0         4523.0     961999779   False  \n",
       "20731            629.0         2349.0     215342367   False  \n",
       "12361         726926.0         7535.0     521594852   False  \n",
       "50737             11.0          229.0     774313785   False  \n",
       "9828             106.0           15.0     142112088    True  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "is_org = lambda x:\"sehir\" in clean(x)\n",
    "twitter_users[\"is_org\"] = twitter_users.screen_name.apply(is_org)\n",
    "twitter_users.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "sehir_orgs = twitter_users[twitter_users.is_org==True].set_index(\"id\")\n",
    "sehir_users = twitter_users[twitter_users.is_org==False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "231"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(sehir_orgs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>lang</th>\n",
       "      <th>match_name</th>\n",
       "      <th>match_ratio</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>truncated_id</th>\n",
       "      <th>is_org</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>1.262309e+09</th>\n",
       "      <td>itiraf şehir</td>\n",
       "      <td>itirafsehir</td>\n",
       "      <td>tr</td>\n",
       "      <td>Şehir Üniversitesi (İnsan ve Toplum Bilimleri...</td>\n",
       "      <td>86.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>126230883</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4.585214e+08</th>\n",
       "      <td>Şehir_Münazara</td>\n",
       "      <td>sehir_munazara</td>\n",
       "      <td>tr</td>\n",
       "      <td>Irem Naz Saral</td>\n",
       "      <td>69.0</td>\n",
       "      <td>337.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>458521438</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7.530450e+08</th>\n",
       "      <td>Şehir Gezi Kulübü</td>\n",
       "      <td>sehirgezikulubu</td>\n",
       "      <td>tr</td>\n",
       "      <td>Şehir Üniversitesi (İnsan ve Toplum Bilimleri...</td>\n",
       "      <td>86.0</td>\n",
       "      <td>111.0</td>\n",
       "      <td>127.0</td>\n",
       "      <td>753045012</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8.301260e+17</th>\n",
       "      <td>Şehir Üniversitesi Çocuk Gelişimi Kulübü</td>\n",
       "      <td>sehirunicocuk</td>\n",
       "      <td>tr</td>\n",
       "      <td>Şehir Üniversitesi</td>\n",
       "      <td>90.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>830126048</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.064906e+09</th>\n",
       "      <td>GasteŞehir</td>\n",
       "      <td>gastesehir</td>\n",
       "      <td>tr</td>\n",
       "      <td>Aisec Sehir</td>\n",
       "      <td>70.0</td>\n",
       "      <td>1198.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>306490639</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  name      screen_name lang  \\\n",
       "id                                                                             \n",
       "1.262309e+09                              itiraf şehir      itirafsehir   tr   \n",
       "4.585214e+08                            Şehir_Münazara   sehir_munazara   tr   \n",
       "7.530450e+08                         Şehir Gezi Kulübü  sehirgezikulubu   tr   \n",
       "8.301260e+17  Şehir Üniversitesi Çocuk Gelişimi Kulübü    sehirunicocuk   tr   \n",
       "3.064906e+09                                GasteŞehir       gastesehir   tr   \n",
       "\n",
       "                                                     match_name  match_ratio  \\\n",
       "id                                                                             \n",
       "1.262309e+09   Şehir Üniversitesi (İnsan ve Toplum Bilimleri...         86.0   \n",
       "4.585214e+08                                     Irem Naz Saral         69.0   \n",
       "7.530450e+08   Şehir Üniversitesi (İnsan ve Toplum Bilimleri...         86.0   \n",
       "8.301260e+17                                 Şehir Üniversitesi         90.0   \n",
       "3.064906e+09                                        Aisec Sehir         70.0   \n",
       "\n",
       "              followers_count  friends_count  truncated_id  is_org  \n",
       "id                                                                  \n",
       "1.262309e+09              1.0           10.0     126230883    True  \n",
       "4.585214e+08            337.0          111.0     458521438    True  \n",
       "7.530450e+08            111.0          127.0     753045012    True  \n",
       "8.301260e+17             48.0           18.0     830126048    True  \n",
       "3.064906e+09           1198.0           24.0     306490639    True  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sehir_orgs.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>lang</th>\n",
       "      <th>match_name</th>\n",
       "      <th>match_ratio</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>truncated_id</th>\n",
       "      <th>is_org</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5.670900e+08</td>\n",
       "      <td>Övünç Meriç</td>\n",
       "      <td>ovuncmeric</td>\n",
       "      <td>tr</td>\n",
       "      <td>Kardelen Meric</td>\n",
       "      <td>77.0</td>\n",
       "      <td>569.0</td>\n",
       "      <td>1170.0</td>\n",
       "      <td>567090020</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7.262076e+08</td>\n",
       "      <td>klasik</td>\n",
       "      <td>klasikyayinlari</td>\n",
       "      <td>tr</td>\n",
       "      <td>Yavuz Kasikci</td>\n",
       "      <td>75.0</td>\n",
       "      <td>6596.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>726207614</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>4.979428e+08</td>\n",
       "      <td>Küre Yayınları</td>\n",
       "      <td>kureyayinlari</td>\n",
       "      <td>tr</td>\n",
       "      <td>Merve Yakinlar</td>\n",
       "      <td>72.0</td>\n",
       "      <td>9158.0</td>\n",
       "      <td>166.0</td>\n",
       "      <td>497942798</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2.674867e+09</td>\n",
       "      <td>Fatıma Tuba Yaylacı</td>\n",
       "      <td>fatimatubapetek</td>\n",
       "      <td>en</td>\n",
       "      <td>Fatima Tuba Yaylaci</td>\n",
       "      <td>94.0</td>\n",
       "      <td>844.0</td>\n",
       "      <td>246.0</td>\n",
       "      <td>267486658</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1.439589e+09</td>\n",
       "      <td>Mahmut Koca</td>\n",
       "      <td>mkoca66</td>\n",
       "      <td>en</td>\n",
       "      <td>Mahmut Koca</td>\n",
       "      <td>100.0</td>\n",
       "      <td>1379.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>143958858</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             id                 name      screen_name lang  \\\n",
       "1  5.670900e+08          Övünç Meriç       ovuncmeric   tr   \n",
       "4  7.262076e+08               klasik  klasikyayinlari   tr   \n",
       "5  4.979428e+08       Küre Yayınları    kureyayinlari   tr   \n",
       "6  2.674867e+09  Fatıma Tuba Yaylacı  fatimatubapetek   en   \n",
       "7  1.439589e+09          Mahmut Koca          mkoca66   en   \n",
       "\n",
       "             match_name  match_ratio  followers_count  friends_count  \\\n",
       "1        Kardelen Meric         77.0            569.0         1170.0   \n",
       "4         Yavuz Kasikci         75.0           6596.0          142.0   \n",
       "5        Merve Yakinlar         72.0           9158.0          166.0   \n",
       "6   Fatima Tuba Yaylaci         94.0            844.0          246.0   \n",
       "7           Mahmut Koca        100.0           1379.0           44.0   \n",
       "\n",
       "   truncated_id  is_org  \n",
       "1     567090020   False  \n",
       "4     726207614   False  \n",
       "5     497942798   False  \n",
       "6     267486658   False  \n",
       "7     143958858   False  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sehir_users.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "sehir_directory = pd.read_csv('../datasets/contacts.csv', \n",
    "                               encoding = \"ISO-8859-1\", \n",
    "                               usecols=['First Name', 'Last Name', 'Primary Email'])\n",
    "sehir_directory.replace(np.nan, '', regex=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "fullnames = [' '.join(first_last_name).lower() \n",
    "                 for first_last_name in sehir_directory[['First Name', 'Last Name']].values]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "start = time.time()\n",
    "sehir_matches = Parallel(n_jobs=-1)(delayed(get_matches_dataframe)(\n",
    "    sehir_users[int(i*(len(sehir_users)/8)):int((i+1)*(len(sehir_users)/8))],\n",
    "    sehir_directory) for i in range(8))\n",
    "print(\"took: \", time.time()-start)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sehir_matches_df = pd.concat(sehir_matches)\n",
    "sehir_matches_df.index = range(len(sehir_matches_df))\n",
    "print(\"There are {} matches\".format(len(sehir_matches_df)))\n",
    "sehir_matches_df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 480,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>match_name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>match_ratio</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>28057</th>\n",
       "      <td>fatma derya mentes</td>\n",
       "      <td>DeryaaSarii</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10093</th>\n",
       "      <td>nur betã¼l yerli</td>\n",
       "      <td>bacimbilegin</td>\n",
       "      <td>90</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20027</th>\n",
       "      <td>emine bayraktar</td>\n",
       "      <td>emine_blt_1</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19182</th>\n",
       "      <td>etem hakan ergec</td>\n",
       "      <td>hakan_tunaa</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3016</th>\n",
       "      <td>sami anis abuhamdeh</td>\n",
       "      <td>samiyigit_</td>\n",
       "      <td>86</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 match_name   screen_name  match_ratio\n",
       "28057    fatma derya mentes   DeryaaSarii           90\n",
       "10093      nur betã¼l yerli  bacimbilegin           90\n",
       "20027       emine bayraktar   emine_blt_1           86\n",
       "19182      etem hakan ergec   hakan_tunaa           86\n",
       "3016    sami anis abuhamdeh    samiyigit_           86"
      ]
     },
     "execution_count": 480,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sehir_matches_df['match_ratio'] = sehir_matches_df.match_name.apply(lambda x: x[0][1])\n",
    "sehir_matches_df.match_name = sehir_matches_df.match_name.apply(lambda x: x[0][0])\n",
    "sehir_matches_df.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 481,
   "metadata": {},
   "outputs": [],
   "source": [
    "tu=twitter_users.drop(labels=[\"match_name\",\"match_ratio\"], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 482,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>match_name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>match_ratio</th>\n",
       "      <th>name</th>\n",
       "      <th>lang</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>truncated_id</th>\n",
       "      <th>is_org</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>9.970584e+17</th>\n",
       "      <td>azize fatma cakir</td>\n",
       "      <td>fgurbuz35</td>\n",
       "      <td>86</td>\n",
       "      <td>fatma gürbüz</td>\n",
       "      <td>tr</td>\n",
       "      <td>68.0</td>\n",
       "      <td>122.0</td>\n",
       "      <td>997058428</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9.369498e+17</th>\n",
       "      <td>okan mergen</td>\n",
       "      <td>miraokan42</td>\n",
       "      <td>86</td>\n",
       "      <td>miraç okan ekmekci</td>\n",
       "      <td>tr</td>\n",
       "      <td>4.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>936949783</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.306379e+09</th>\n",
       "      <td>ä°stanbul åehir ãniversitesi ä°åletme enst...</td>\n",
       "      <td>CHPIstGenclik</td>\n",
       "      <td>86</td>\n",
       "      <td>CHP İstanbul Gençlik</td>\n",
       "      <td>tr</td>\n",
       "      <td>5735.0</td>\n",
       "      <td>3223.0</td>\n",
       "      <td>230637902</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.334179e+08</th>\n",
       "      <td>hanife kubra demirci</td>\n",
       "      <td>RabiaDeemirci</td>\n",
       "      <td>86</td>\n",
       "      <td>Rabia Demirci</td>\n",
       "      <td>tr</td>\n",
       "      <td>96.0</td>\n",
       "      <td>314.0</td>\n",
       "      <td>333417875</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4.742273e+09</th>\n",
       "      <td>gizem serpil boylu</td>\n",
       "      <td>kronik__rehber</td>\n",
       "      <td>86</td>\n",
       "      <td>serpil.sedef</td>\n",
       "      <td>tr</td>\n",
       "      <td>49.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>474227276</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                     match_name  \\\n",
       "id                                                                \n",
       "9.970584e+17                                  azize fatma cakir   \n",
       "9.369498e+17                                        okan mergen   \n",
       "2.306379e+09   ä°stanbul åehir ãniversitesi ä°åletme enst...   \n",
       "3.334179e+08                               hanife kubra demirci   \n",
       "4.742273e+09                                 gizem serpil boylu   \n",
       "\n",
       "                 screen_name  match_ratio                  name lang  \\\n",
       "id                                                                     \n",
       "9.970584e+17       fgurbuz35           86          fatma gürbüz   tr   \n",
       "9.369498e+17      miraokan42           86    miraç okan ekmekci   tr   \n",
       "2.306379e+09   CHPIstGenclik           86  CHP İstanbul Gençlik   tr   \n",
       "3.334179e+08   RabiaDeemirci           86         Rabia Demirci   tr   \n",
       "4.742273e+09  kronik__rehber           86          serpil.sedef   tr   \n",
       "\n",
       "              followers_count  friends_count  truncated_id  is_org  \n",
       "id                                                                  \n",
       "9.970584e+17             68.0          122.0     997058428   False  \n",
       "9.369498e+17              4.0           19.0     936949783   False  \n",
       "2.306379e+09           5735.0         3223.0     230637902   False  \n",
       "3.334179e+08             96.0          314.0     333417875   False  \n",
       "4.742273e+09             49.0          113.0     474227276   False  "
      ]
     },
     "execution_count": 482,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitter_users = sehir_matches_df.merge(tu, on=\"screen_name\")\n",
    "twitter_users = twitter_users.set_index(\"id\")\n",
    "twitter_users.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 483,
   "metadata": {},
   "outputs": [],
   "source": [
    "sehir_orgs.to_csv(\"../datasets/orgs.csv\",index_label=\"id\")\n",
    "sehir_orgs.to_csv(\"../REST/static/orgs.csv\",index_label=\"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>match_name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>match_ratio</th>\n",
       "      <th>name</th>\n",
       "      <th>lang</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>truncated_id</th>\n",
       "      <th>is_org</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>9.728785e+08</th>\n",
       "      <td>! sosyoloji</td>\n",
       "      <td>auzefsosyoloji</td>\n",
       "      <td>90</td>\n",
       "      <td>Auzef Sosyoloji</td>\n",
       "      <td>tr</td>\n",
       "      <td>3335.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>972878497</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7.403476e+17</th>\n",
       "      <td>areen munir mohammad altounesi</td>\n",
       "      <td>fm_uni</td>\n",
       "      <td>75</td>\n",
       "      <td>Uni FM</td>\n",
       "      <td>tr</td>\n",
       "      <td>1054.0</td>\n",
       "      <td>4307.0</td>\n",
       "      <td>740347593</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.941674e+09</th>\n",
       "      <td>omer faruk ozcetin</td>\n",
       "      <td>farukozcetin</td>\n",
       "      <td>91</td>\n",
       "      <td>Ömer Faruk Özçetin</td>\n",
       "      <td>tr</td>\n",
       "      <td>129.0</td>\n",
       "      <td>331.0</td>\n",
       "      <td>194167361</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.442222e+08</th>\n",
       "      <td>meryem ayse gurpinar</td>\n",
       "      <td>AyseOzceyhan</td>\n",
       "      <td>86</td>\n",
       "      <td>Ayse Ozceyhan</td>\n",
       "      <td>tr</td>\n",
       "      <td>91.0</td>\n",
       "      <td>142.0</td>\n",
       "      <td>244222222</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.483375e+09</th>\n",
       "      <td>duygu masal</td>\n",
       "      <td>duygubektik</td>\n",
       "      <td>86</td>\n",
       "      <td>Dr Duygu Bektik (Simsek)</td>\n",
       "      <td>en</td>\n",
       "      <td>464.0</td>\n",
       "      <td>198.0</td>\n",
       "      <td>148337542</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                  match_name     screen_name  match_ratio  \\\n",
       "id                                                                          \n",
       "9.728785e+08                     ! sosyoloji  auzefsosyoloji           90   \n",
       "7.403476e+17  areen munir mohammad altounesi          fm_uni           75   \n",
       "1.941674e+09              omer faruk ozcetin    farukozcetin           91   \n",
       "2.442222e+08            meryem ayse gurpinar    AyseOzceyhan           86   \n",
       "1.483375e+09                     duygu masal     duygubektik           86   \n",
       "\n",
       "                                  name lang  followers_count  friends_count  \\\n",
       "id                                                                            \n",
       "9.728785e+08           Auzef Sosyoloji   tr           3335.0            6.0   \n",
       "7.403476e+17                    Uni FM   tr           1054.0         4307.0   \n",
       "1.941674e+09        Ömer Faruk Özçetin   tr            129.0          331.0   \n",
       "2.442222e+08             Ayse Ozceyhan   tr             91.0          142.0   \n",
       "1.483375e+09  Dr Duygu Bektik (Simsek)   en            464.0          198.0   \n",
       "\n",
       "              truncated_id  is_org  \n",
       "id                                  \n",
       "9.728785e+08     972878497   False  \n",
       "7.403476e+17     740347593   False  \n",
       "1.941674e+09     194167361   False  \n",
       "2.442222e+08     244222222   False  \n",
       "1.483375e+09     148337542   False  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twitter_users = pd.read_csv(\"../datasets/twitter_users.csv\", index_col=\"id\")\n",
    "twitter_users.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_twu = twitter_users[twitter_users.match_ratio>90]\n",
    "filtered_twu = filtered_twu.append(twitter_users.loc[291122559])  # me: Ammar Rashed :)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(41768, 2618)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(twitter_users), len(filtered_twu)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>match_name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>match_ratio</th>\n",
       "      <th>name</th>\n",
       "      <th>lang</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>truncated_id</th>\n",
       "      <th>is_org</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>2.726240e+08</th>\n",
       "      <td>dogukan kotan</td>\n",
       "      <td>dogukankotan</td>\n",
       "      <td>92</td>\n",
       "      <td>Doğukan KOTAN</td>\n",
       "      <td>tr</td>\n",
       "      <td>276.0</td>\n",
       "      <td>139.0</td>\n",
       "      <td>272623984</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7.598048e+17</th>\n",
       "      <td>mustafa celik</td>\n",
       "      <td>_mustafa__celik</td>\n",
       "      <td>96</td>\n",
       "      <td>Mustafa Çelik</td>\n",
       "      <td>tr</td>\n",
       "      <td>1.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>759804840</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9.572194e+17</th>\n",
       "      <td>nisanur yukruk</td>\n",
       "      <td>nisanurykrk</td>\n",
       "      <td>92</td>\n",
       "      <td>Nisanur Yükrük</td>\n",
       "      <td>tr</td>\n",
       "      <td>55.0</td>\n",
       "      <td>102.0</td>\n",
       "      <td>957219363</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9.281341e+08</th>\n",
       "      <td>emre kaplan</td>\n",
       "      <td>ylmdk</td>\n",
       "      <td>100</td>\n",
       "      <td>Emre Kaplan</td>\n",
       "      <td>tr</td>\n",
       "      <td>175.0</td>\n",
       "      <td>1118.0</td>\n",
       "      <td>928134109</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.576166e+08</th>\n",
       "      <td>esma demir</td>\n",
       "      <td>esmaozdmr</td>\n",
       "      <td>95</td>\n",
       "      <td>Esma Özdemir</td>\n",
       "      <td>en</td>\n",
       "      <td>729.0</td>\n",
       "      <td>380.0</td>\n",
       "      <td>157616590</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  match_name      screen_name  match_ratio            name  \\\n",
       "id                                                                           \n",
       "2.726240e+08   dogukan kotan     dogukankotan           92   Doğukan KOTAN   \n",
       "7.598048e+17   mustafa celik  _mustafa__celik           96   Mustafa Çelik   \n",
       "9.572194e+17  nisanur yukruk      nisanurykrk           92  Nisanur Yükrük   \n",
       "9.281341e+08     emre kaplan            ylmdk          100     Emre Kaplan   \n",
       "1.576166e+08      esma demir        esmaozdmr           95    Esma Özdemir   \n",
       "\n",
       "             lang  followers_count  friends_count  truncated_id  is_org  \n",
       "id                                                                       \n",
       "2.726240e+08   tr            276.0          139.0     272623984   False  \n",
       "7.598048e+17   tr              1.0           26.0     759804840   False  \n",
       "9.572194e+17   tr             55.0          102.0     957219363   False  \n",
       "9.281341e+08   tr            175.0         1118.0     928134109   False  \n",
       "1.576166e+08   en            729.0          380.0     157616590   False  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_twu = pd.read_csv(\"../datasets/filtered_twitter_users.csv\", index_col=\"id\")\n",
    "filtered_twu.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "match_name            ammar rasid\n",
       "screen_name        AmmarRashed_MB\n",
       "match_ratio                    87\n",
       "name                 Ammar Rashed\n",
       "lang                           en\n",
       "followers_count               392\n",
       "friends_count                 337\n",
       "truncated_id            291122559\n",
       "is_org                      False\n",
       "Name: 291122559.0, dtype: object"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_twu.loc[291122559]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>match_name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>match_ratio</th>\n",
       "      <th>name</th>\n",
       "      <th>lang</th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>truncated_id</th>\n",
       "      <th>is_org</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>1.466873e+07</th>\n",
       "      <td>ahmet bulut</td>\n",
       "      <td>kral</td>\n",
       "      <td>100</td>\n",
       "      <td>Ahmet Bulut</td>\n",
       "      <td>en</td>\n",
       "      <td>242.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>14668733</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.387189e+09</th>\n",
       "      <td>mehmet yilmaz</td>\n",
       "      <td>kralyoshi</td>\n",
       "      <td>92</td>\n",
       "      <td>Mehmet Yılmaz</td>\n",
       "      <td>tr</td>\n",
       "      <td>5.0</td>\n",
       "      <td>203.0</td>\n",
       "      <td>238718926</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 match_name screen_name  match_ratio           name lang  \\\n",
       "id                                                                         \n",
       "1.466873e+07    ahmet bulut        kral          100    Ahmet Bulut   en   \n",
       "2.387189e+09  mehmet yilmaz   kralyoshi           92  Mehmet Yılmaz   tr   \n",
       "\n",
       "              followers_count  friends_count  truncated_id  is_org  \n",
       "id                                                                  \n",
       "1.466873e+07            242.0           62.0      14668733   False  \n",
       "2.387189e+09              5.0          203.0     238718926   False  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "filtered_twu[filtered_twu.screen_name.str.contains(\"kral\")]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Filter connections"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>followers_count</th>\n",
       "      <th>friends_count</th>\n",
       "      <th>is_org</th>\n",
       "      <th>lang</th>\n",
       "      <th>match_name</th>\n",
       "      <th>match_ratio</th>\n",
       "      <th>name</th>\n",
       "      <th>screen_name</th>\n",
       "      <th>truncated_id</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>3.310023e+08</th>\n",
       "      <td>80.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>False</td>\n",
       "      <td>tr</td>\n",
       "      <td>elif kartal</td>\n",
       "      <td>95.0</td>\n",
       "      <td>elif karaçöl</td>\n",
       "      <td>cakkannim</td>\n",
       "      <td>331002312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.299537e+09</th>\n",
       "      <td>24.0</td>\n",
       "      <td>208.0</td>\n",
       "      <td>True</td>\n",
       "      <td>tr</td>\n",
       "      <td>Şehir Üniversitesi (İnsan ve Toplum Bilimleri...</td>\n",
       "      <td>86.0</td>\n",
       "      <td>SCORP - Şehir</td>\n",
       "      <td>Scorpsehir</td>\n",
       "      <td>329953707</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2.537778e+09</th>\n",
       "      <td>293.0</td>\n",
       "      <td>412.0</td>\n",
       "      <td>False</td>\n",
       "      <td>tr</td>\n",
       "      <td>yusuf sahan</td>\n",
       "      <td>91.0</td>\n",
       "      <td>Yusuf Şahan</td>\n",
       "      <td>ysfsahan</td>\n",
       "      <td>253777838</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3.172710e+08</th>\n",
       "      <td>62.0</td>\n",
       "      <td>324.0</td>\n",
       "      <td>False</td>\n",
       "      <td>tr</td>\n",
       "      <td>halime demir</td>\n",
       "      <td>100.0</td>\n",
       "      <td>Halime Demir</td>\n",
       "      <td>Rosican1</td>\n",
       "      <td>317270977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8.258187e+08</th>\n",
       "      <td>35.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>False</td>\n",
       "      <td>tr</td>\n",
       "      <td>mehmet kara</td>\n",
       "      <td>91.0</td>\n",
       "      <td>Mehmet ARDA</td>\n",
       "      <td>memisarda</td>\n",
       "      <td>825818708</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              followers_count  friends_count  is_org lang  \\\n",
       "id                                                          \n",
       "3.310023e+08             80.0           85.0   False   tr   \n",
       "3.299537e+09             24.0          208.0    True   tr   \n",
       "2.537778e+09            293.0          412.0   False   tr   \n",
       "3.172710e+08             62.0          324.0   False   tr   \n",
       "8.258187e+08             35.0          156.0   False   tr   \n",
       "\n",
       "                                                     match_name  match_ratio  \\\n",
       "id                                                                             \n",
       "3.310023e+08                                        elif kartal         95.0   \n",
       "3.299537e+09   Şehir Üniversitesi (İnsan ve Toplum Bilimleri...         86.0   \n",
       "2.537778e+09                                        yusuf sahan         91.0   \n",
       "3.172710e+08                                       halime demir        100.0   \n",
       "8.258187e+08                                        mehmet kara         91.0   \n",
       "\n",
       "                       name screen_name  truncated_id  \n",
       "id                                                     \n",
       "3.310023e+08   elif karaçöl   cakkannim     331002312  \n",
       "3.299537e+09  SCORP - Şehir  Scorpsehir     329953707  \n",
       "2.537778e+09    Yusuf Şahan    ysfsahan     253777838  \n",
       "3.172710e+08   Halime Demir    Rosican1     317270977  \n",
       "8.258187e+08    Mehmet ARDA   memisarda     825818708  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "twu_with_orgs = pd.concat([filtered_twu, sehir_orgs])\n",
    "twu_with_orgs.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2849"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "assert len(twu_with_orgs) == len(filtered_twu) + len(sehir_orgs)\n",
    "len(twu_with_orgs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>from_user_id</th>\n",
       "      <th>to_user_id</th>\n",
       "      <th>formation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>32188</th>\n",
       "      <td>339133152</td>\n",
       "      <td>458521438</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80823</th>\n",
       "      <td>158783378</td>\n",
       "      <td>995806488</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92443</th>\n",
       "      <td>281127185</td>\n",
       "      <td>163020501</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71572</th>\n",
       "      <td>417842607</td>\n",
       "      <td>618131962</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73092</th>\n",
       "      <td>799523617050656769</td>\n",
       "      <td>32660008</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             from_user_id  to_user_id             formation\n",
       "32188           339133152   458521438  {'2018.05.24': True}\n",
       "80823           158783378   995806488  {'2018.05.24': True}\n",
       "92443           281127185   163020501  {'2018.05.24': True}\n",
       "71572           417842607   618131962  {'2018.05.24': True}\n",
       "73092  799523617050656769    32660008  {'2018.05.24': True}"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_connections.formation = user_connections.formation.apply(lambda x:{\"2018.05.24\":True})\n",
    "user_connections.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "ids = set(twu_with_orgs.index)\n",
    "def in_sehir(row, from_col=\"from_user_id\", to_col=\"to_user_id\"):\n",
    "    return row[from_col] in ids and row[to_col] in ids"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>from_user_id</th>\n",
       "      <th>to_user_id</th>\n",
       "      <th>formation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>53782</th>\n",
       "      <td>428560048</td>\n",
       "      <td>377901136</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34228</th>\n",
       "      <td>287084927</td>\n",
       "      <td>609301446</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22209</th>\n",
       "      <td>146166034</td>\n",
       "      <td>443551180</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39386</th>\n",
       "      <td>2360133031</td>\n",
       "      <td>839025106894602240</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6544</th>\n",
       "      <td>3408479039</td>\n",
       "      <td>106086098</td>\n",
       "      <td>{'2018.05.24': True}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       from_user_id          to_user_id             formation\n",
       "53782     428560048           377901136  {'2018.05.24': True}\n",
       "34228     287084927           609301446  {'2018.05.24': True}\n",
       "22209     146166034           443551180  {'2018.05.24': True}\n",
       "39386    2360133031  839025106894602240  {'2018.05.24': True}\n",
       "6544     3408479039           106086098  {'2018.05.24': True}"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_connections[\"in_sehir\"] = user_connections.apply(lambda row: in_sehir(row), axis=1)\n",
    "sehir_connections = user_connections[user_connections.in_sehir].drop(\"in_sehir\", axis=1)\n",
    "sehir_connections.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(93296, 9020)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(user_connections),len(sehir_connections)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adding older connections"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "def get_dates(cons):\n",
    "    all_dates = set()\n",
    "    str2date = lambda strdate: datetime.strptime(strdate, '%Y.%m.%d')  # 2018.05.08\n",
    "\n",
    "    for dates in cons.formation.apply(lambda x: list(x)):\n",
    "        for date in dates:\n",
    "            all_dates.add(str2date(date))\n",
    "    return [d.strftime('%Y.%m.%d') for d in sorted(all_dates)]\n",
    "\n",
    "def present_in_date(changes_dates, queried_date):\n",
    "    \"\"\"\n",
    "    changes_dates = {d1:True, d2:False, d3:True} connection added or removed\n",
    "    \"\"\"\n",
    "    if changes_dates:\n",
    "        str2date = lambda strdate: datetime.strptime(strdate, '%Y.%m.%d')  # 2018.05.08\n",
    "        changes = sorted(changes_dates,key=lambda d: str2date(d))\n",
    "        queried_date = datetime.strptime(queried_date, '%Y.%m.%d')\n",
    "        present = False\n",
    "        for d in changes:\n",
    "            if queried_date < str2date(d):\n",
    "                break\n",
    "            present = changes_dates[d]\n",
    "        return present\n",
    "    else:\n",
    "        return False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "con2 = psycopg2.connect('dbname=old host=localhost user=postgres password=1_sehir_1')\n",
    "\n",
    "old_cons = pd.read_sql(\"SELECT * FROM twitter_connection\", con2).drop('id', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>from_user_id</th>\n",
       "      <th>to_user_id</th>\n",
       "      <th>formation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13564</th>\n",
       "      <td>700797513188712450</td>\n",
       "      <td>3892757176</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24581</th>\n",
       "      <td>2960439239</td>\n",
       "      <td>1536995378</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29858</th>\n",
       "      <td>887437856</td>\n",
       "      <td>793166250486730753</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30683</th>\n",
       "      <td>73543596</td>\n",
       "      <td>4091594428</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13578</th>\n",
       "      <td>3007709429</td>\n",
       "      <td>3892757176</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             from_user_id          to_user_id             formation\n",
       "13564  700797513188712450          3892757176  {'2018.05.08': True}\n",
       "24581          2960439239          1536995378  {'2018.05.08': True}\n",
       "29858           887437856  793166250486730753  {'2018.05.08': True}\n",
       "30683            73543596          4091594428  {'2018.05.08': True}\n",
       "13578          3007709429          3892757176  {'2018.05.08': True}"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "old_cons.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>from_user_id</th>\n",
       "      <th>to_user_id</th>\n",
       "      <th>formation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20536</th>\n",
       "      <td>1110823566</td>\n",
       "      <td>293976465</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21174</th>\n",
       "      <td>2301123770</td>\n",
       "      <td>1110823566</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32853</th>\n",
       "      <td>4217413882</td>\n",
       "      <td>737056442</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6924</th>\n",
       "      <td>107674642</td>\n",
       "      <td>106086098</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36118</th>\n",
       "      <td>450639507</td>\n",
       "      <td>149522762</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       from_user_id  to_user_id             formation\n",
       "20536    1110823566   293976465  {'2018.05.08': True}\n",
       "21174    2301123770  1110823566  {'2018.05.08': True}\n",
       "32853    4217413882   737056442  {'2018.05.08': True}\n",
       "6924      107674642   106086098  {'2018.05.08': True}\n",
       "36118     450639507   149522762  {'2018.05.08': True}"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "old_cons[\"in_sehir\"] = old_cons.apply(lambda row: in_sehir(row), axis=1)\n",
    "old_sehir = old_cons[old_cons.in_sehir].drop(\"in_sehir\", axis=1)\n",
    "old_sehir.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(39848, 6200)"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(old_cons),len(old_sehir)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "15220"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "concat_cons = pd.concat([sehir_connections, old_sehir])\n",
    "assert len(concat_cons) == len(old_sehir)+len(sehir_connections)\n",
    "len(concat_cons)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "def optimize_dates(dates_):\n",
    "    dates = {list(d.keys())[0]:d[list(d.keys())[0]] for d in dates_}\n",
    "    str2date = lambda strdate: datetime.strptime(strdate, '%Y.%m.%d')  # 2018.05.08\n",
    "    sorted_dates = sorted(dates, key=lambda d:str2date(d))\n",
    "    optimized_dates = {sorted_dates[0]: True}\n",
    "    for d in range(1, len(sorted_dates)):\n",
    "        if dates[sorted_dates[d-1]] != dates[sorted_dates[d]]:\n",
    "            optimized_dates[sorted_dates[d]] = dates[sorted_dates[d]]\n",
    "    return str(optimized_dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9031"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_cons = concat_cons.groupby([\"from_user_id\",\"to_user_id\"])[\"formation\"]\\\n",
    "                    .apply(lambda x:optimize_dates(x)).reset_index()\n",
    "len(grouped_cons)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6200, 9020)"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(old_sehir), len(sehir_connections)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>from_user_id</th>\n",
       "      <th>to_user_id</th>\n",
       "      <th>formation</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>7881</th>\n",
       "      <td>700707449079201792</td>\n",
       "      <td>1666891914</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7492</th>\n",
       "      <td>3892757176</td>\n",
       "      <td>194475532</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>793</th>\n",
       "      <td>162427198</td>\n",
       "      <td>4117422969</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3037</th>\n",
       "      <td>584303296</td>\n",
       "      <td>106086098</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2514</th>\n",
       "      <td>450639507</td>\n",
       "      <td>609301446</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            from_user_id  to_user_id             formation\n",
       "7881  700707449079201792  1666891914  {'2018.05.08': True}\n",
       "7492          3892757176   194475532  {'2018.05.08': True}\n",
       "793            162427198  4117422969  {'2018.05.08': True}\n",
       "3037           584303296   106086098  {'2018.05.08': True}\n",
       "2514           450639507   609301446  {'2018.05.08': True}"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str2dict = lambda d : ast.literal_eval(d)\n",
    "grouped_cons.formation = grouped_cons.formation.apply(lambda d: str2dict(d))\n",
    "grouped_cons.sample(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "twu_with_orgs.to_csv(\"../datasets/filtered_twitter_users.csv\", index_label=\"id\")\n",
    "twu_with_orgs.to_csv(\"../REST/static/filtered_twitter_users.csv\", index_label=\"id\")\n",
    "\n",
    "grouped_cons.to_csv(\"../datasets/filtered_twitter_connections.csv\", index_label=\"id\")\n",
    "grouped_cons.to_csv(\"../REST/static/filtered_twitter_connections.csv\", index_label=\"id\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Construct the network"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['2018.05.08', '2018.05.24']"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates = get_dates(grouped_cons)\n",
    "dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>from_user_id</th>\n",
       "      <th>to_user_id</th>\n",
       "      <th>formation</th>\n",
       "      <th>first_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2373</th>\n",
       "      <td>450639507</td>\n",
       "      <td>151606317</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7802</th>\n",
       "      <td>4576344862</td>\n",
       "      <td>564388225</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5123</th>\n",
       "      <td>1727958896</td>\n",
       "      <td>174415744</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6108</th>\n",
       "      <td>2590180702</td>\n",
       "      <td>1222491402</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6011</th>\n",
       "      <td>2529427087</td>\n",
       "      <td>609301446</td>\n",
       "      <td>{'2018.05.08': True}</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      from_user_id  to_user_id             formation  first_date\n",
       "2373     450639507   151606317  {'2018.05.08': True}        True\n",
       "7802    4576344862   564388225  {'2018.05.08': True}        True\n",
       "5123    1727958896   174415744  {'2018.05.08': True}        True\n",
       "6108    2590180702  1222491402  {'2018.05.08': True}        True\n",
       "6011    2529427087   609301446  {'2018.05.08': True}        True"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grouped_cons[\"first_date\"] = grouped_cons.formation.apply(\n",
    "    lambda ds: present_in_date(ds, dates[0]))\n",
    "grouped_cons.sample(5, random_state=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "G = nx.DiGraph()\n",
    "# for _, row in user_connections.iterrows():\n",
    "for _, row in grouped_cons[grouped_cons.first_date==True].iterrows():    \n",
    "    from_ = truncate(row[\"from_user_id\"])\n",
    "    to = truncate(row[\"to_user_id\"])\n",
    "    if from_ in twu_with_orgs.truncated_id and to in twu_with_orgs.truncated_id:\n",
    "        G.add_edge(from_, to)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "augs = [\"name\", \"screen_name\",\"match_name\", \"followers_count\",\"friends_count\", \"lang\"]\n",
    "for node in G.nodes():\n",
    "    user = twu_with_orgs.loc[node]\n",
    "    for aug in augs:\n",
    "        if aug==\"lang\":\n",
    "            m = user[aug]\n",
    "        elif type(user[aug])==str:\n",
    "            m = clean(user[aug])\n",
    "        else:\n",
    "            m = user[aug]\n",
    "        G.nodes[node][aug] = m"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "722"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(G.nodes())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1280"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(G.edges())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "for ix,deg in G.degree(G.nodes()):\n",
    "    G.node[ix]['degree'] = deg\n",
    "    G.node[ix]['parity'] = (1-deg%2)\n",
    "    \n",
    "for ix,in_deg in G.in_degree(G.nodes()):\n",
    "    G.node[ix]['in_degree'] = in_deg\n",
    "    \n",
    "for ix,out_deg in G.out_degree(G.nodes()):\n",
    "    G.node[ix]['out_degree'] = out_deg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "evc = nx.eigenvector_centrality(G)\n",
    "closeness = nx.closeness_centrality(G)\n",
    "betweenness = nx.betweenness_centrality(G)\n",
    "pagerank = nx.pagerank(G)\n",
    "nxg = G.to_undirected()\n",
    "clustering = nx.clustering(nxg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [],
   "source": [
    "metrics = {\"eigenvector_centrality\":evc,\n",
    "           \"closeness_centrality\":closeness,\n",
    "          \"betweenness\":betweenness,\n",
    "          \"pagerank\":pagerank,\n",
    "          \"clustering_coefficient\":clustering}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "for metric_name, metric in metrics.items():\n",
    "    for ix,v in metric.items():\n",
    "        G.nodes[ix][metric_name] = v"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(396662786,\n",
       " {'betweenness': 0.0,\n",
       "  'closeness_centrality': 0.0,\n",
       "  'clustering_coefficient': 0,\n",
       "  'degree': 1,\n",
       "  'eigenvector_centrality': 6.741618620868637e-27,\n",
       "  'followers_count': 33.0,\n",
       "  'friends_count': 284.0,\n",
       "  'in_degree': 0,\n",
       "  'lang': 'tr',\n",
       "  'match_name': ' sehir mba',\n",
       "  'name': 'nemasehir',\n",
       "  'out_degree': 1,\n",
       "  'pagerank': 0.0002640177962431144,\n",
       "  'parity': 0,\n",
       "  'screen_name': 'nemasehir'})"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(G.nodes(data=True))[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "from networkx.readwrite import json_graph\n",
    "data = nx.node_link_data(G)\n",
    "with open('../REST/static/networks/twitter_users_graph2.json', 'w') as f:\n",
    "    json.dump(data, f, indent=4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculating Homophily"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "def homophily(nw, metric=\"lang\"):\n",
    "    langs_probs = dict()\n",
    "    for n in nw.nodes():\n",
    "        user = nw.nodes[n]\n",
    "        langs_probs.setdefault(user[metric], 0)\n",
    "        langs_probs[user[metric]] += 1\n",
    "    heterogeneity_fraction_norm = 1 - sum(\n",
    "        [(float(i)/len(nw.nodes()))**2 for i in langs_probs.values()])\n",
    "    cross_edges = sum(\n",
    "        [int(nw.nodes[f][metric] != nw.nodes[t][metric] ) for f,t in nw.edges()])\n",
    "    cross_metric_ratio = cross_edges/float(len(nw.edges()))\n",
    "    print(\"cross-metric edges ratio: \", cross_metric_ratio)\n",
    "    print(\"Heterogeneity Fraction Norm\", heterogeneity_fraction_norm)\n",
    "    return cross_metric_ratio < heterogeneity_fraction_norm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cross-metric edges ratio:  0.4578125\n",
      "Heterogeneity Fraction Norm 0.39950199891038285\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "homophily(G)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Transitivity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.024470693113438823"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nx.transitivity(G)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}