Data Preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/dask/dataframe/utils.py:13: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.\n",
      "  import pandas.util.testing as tm\n",
      "/opt/conda/lib/python3.7/site-packages/sklearn/feature_extraction/image.py:167: DeprecationWarning: `np.int` is a deprecated alias for the builtin `int`. To silence this warning, use `int` by itself. Doing this will not modify any behavior and is safe. When replacing `np.int`, you may wish to use e.g. `np.int64` or `np.int32` to specify the precision. If you wish to review your current use, check the release note link for additional information.\n",
      "Deprecated in NumPy 1.20; for more details and guidance: https://numpy.org/devdocs/release/1.20.0-notes.html#deprecations\n",
      "  dtype=np.int):\n"
     ]
    }
   ],
   "source": [
    "from telethon import TelegramClient, events, sync\n",
    "from telethon import functions, types\n",
    "from bs4 import BeautifulSoup\n",
    "import lxml\n",
    "from urllib.request import Request, urlopen\n",
    "from urllib.parse import urlparse\n",
    "\n",
    "import re\n",
    "import os\n",
    "import itertools\n",
    "import random\n",
    "import datetime\n",
    "import json\n",
    "\n",
    "import pandas as pd\n",
    "import dask.dataframe as dd\n",
    "import numpy as np\n",
    "from ast import literal_eval\n",
    "\n",
    "import community\n",
    "import community as community_louvain\n",
    "import networkx as nx\n",
    "import matplotlib.pyplot as plt\n",
    "from scipy.stats.stats import pearsonr   \n",
    "from polyglot.detect import Detector\n",
    "from polyglot.detect.base import UnknownLanguage\n",
    "\n",
    "\n",
    "from telegraph.scraper.utils import extract_channels_from_links_website\n",
    "from telegraph.graph.utils import get_json, who_shared, make_graph_from_df, get_pagerank, graph_to_neo4j\n",
    "\n",
    "from sklearn.feature_extraction.text import CountVectorizer\n",
    "#import lda"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Import Clean Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Specify import types for data\n",
    "dtype = {'channel_id' : float, \n",
    " 'channel_name' : str, \n",
    " 'forward_from' : str, \n",
    " 'forward_from_channel_id' : float,\n",
    " 'forward_from_post_id' : float,\n",
    " 'group_type' : str, \n",
    " 'id' : float,\n",
    " 'is_reply' : bool, \n",
    " 'links': str,\n",
    " 'member_count' : float,\n",
    " 'mentioned_in_link': str,\n",
    " 'mentions' : str,\n",
    " 'participants_count' : float,\n",
    " 'post_author' : str,\n",
    " 'raw_text' : str,\n",
    " 'reply_to_message_id' : float,\n",
    " 'sender' : object,\n",
    " 'sender_id' :  float,\n",
    " 'title' : str,\n",
    " 'views': float}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# directory for imports \n",
    "curr_dir = '/root/repos/telegraph'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Channels with less than 10 messages\n",
    "channels_less_than_10_messages = ['cbsnews', 'newsmax', 'foxnews', 'realdonaldtrump', 'msagd', 'gruene', 'nuerburgring', 'digitaler', 'potus', 'hilton', 'breitbartnews', 'yahoo', 'freiemedien', 'protonmail', 'tuckercarlson', 'posteo', 'querdenken_7141', 'maske', 'freiheitmachtwahr', 'friede_freiheit_demokratie_aut', 'danscavino', 'chrisaresoffiziell', 'archemovie', 'mike_pence', 'shomburg', 'presssec', 'querdenken_361', 'qanondeutschland', 'proudboysuncensored', 'klappstuhlfruehstueck', 'whitehouse', 'dieregierungmussweg', 'chatderfreiheit', 'klagemauer', 'corona', 'twitter', 'punkt', 'pandemie1', 'querdenken351_aktiv', 'demo_kraten', 'querdenken_831', 'thejusticedept', 'sozmi', 'querdenken_571pw', 'querdenken334', 'exfakt', 'ntd_tv', 'secpompeo', 'querdenken40_aktiv', 'polizei', 'updayde', 'gunnarkaiser', 'rosen', 'scharfschuetzenbewegung', 'impfen', 'querdenken241_aktiv', 'sidneypowell1', 'benny_johnson', 'elternstehenauf', 'soziales', 'daniel_prinz_offiziell', 'infokanalberlin', 'querdenken_40', 'querdenken831', 'querdenken_7451', 'corona_jugend_informiert', 'impfenst', 'querdenken_702', 'd_day2_0_button_verteiler_kanal', 'fakealarm', 'diebasis', 'llinwood', 'fluthilfe_portal', 'flutmanager', 'querdenken571pw_aktiv', 'steven_crowder']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2055"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get iterations file (Json keeping track when data was scraped)\n",
    "iterations_path = f'{curr_dir}/data_final/iterations/16-Aug-2021 (08-03-48.974626).json'\n",
    "    \n",
    "with open(iterations_path) as f:\n",
    "    iterations = json.load(f)\n",
    "\n",
    "# Extract all channels from the first 10 iterations\n",
    "subset = {k: v for k, v in iterations.items() if k in [str(i) for i in range(11)]}\n",
    "\n",
    "# Transform to list of channels\n",
    "channels_total = [v.lower() for k, v in subset.items() for v in list(v.keys())]\n",
    "    \n",
    "len(channels_total)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1979"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Clean to small channels \n",
    "channels = [channel for channel in channels_total if channel not in channels_less_than_10_messages]\n",
    "len(channels)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Skipping line 580: unexpected end of data\n"
     ]
    }
   ],
   "source": [
    "# Create dask df for splitting up dataset\n",
    "df_paths = [\"%s/data_final/csvs/%s.csv\" % (curr_dir, channel_name) for channel_name in channels]\n",
    "\n",
    "df = dd.read_csv(df_paths, parse_dates=['datetime'], dtype=dtype, engine=\"python\", error_bad_lines=False, blocksize=None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><strong>Dask DataFrame Structure:</strong></div>\n",
       "<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>channel_name</th>\n",
       "      <th>channel_id</th>\n",
       "      <th>title</th>\n",
       "      <th>datetime</th>\n",
       "      <th>participants_count</th>\n",
       "      <th>raw_text</th>\n",
       "      <th>views</th>\n",
       "      <th>id</th>\n",
       "      <th>sender</th>\n",
       "      <th>post_author</th>\n",
       "      <th>is_reply</th>\n",
       "      <th>links</th>\n",
       "      <th>reply_to_message_id</th>\n",
       "      <th>group_type</th>\n",
       "      <th>member_count</th>\n",
       "      <th>forward_from</th>\n",
       "      <th>forward_from_channel_id</th>\n",
       "      <th>forward_from_post_id</th>\n",
       "      <th>mentions</th>\n",
       "      <th>sender_id</th>\n",
       "      <th>mentioned_in_link</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>npartitions=1979</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",
       "      <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",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>datetime64[ns, UTC]</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>bool</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "<div>Dask Name: from-delayed, 5937 tasks</div>"
      ],
      "text/plain": [
       "Dask DataFrame Structure:\n",
       "                 channel_name channel_id   title             datetime participants_count raw_text    views       id  sender post_author is_reply   links reply_to_message_id group_type member_count forward_from forward_from_channel_id forward_from_post_id mentions sender_id mentioned_in_link\n",
       "npartitions=1979                                                                                                                                                                                                                                                                                   \n",
       "                       object    float64  object  datetime64[ns, UTC]            float64   object  float64  float64  object      object     bool  object             float64     object      float64       object                 float64              float64   object   float64            object\n",
       "                          ...        ...     ...                  ...                ...      ...      ...      ...     ...         ...      ...     ...                 ...        ...          ...          ...                     ...                  ...      ...       ...               ...\n",
       "...                       ...        ...     ...                  ...                ...      ...      ...      ...     ...         ...      ...     ...                 ...        ...          ...          ...                     ...                  ...      ...       ...               ...\n",
       "                          ...        ...     ...                  ...                ...      ...      ...      ...     ...         ...      ...     ...                 ...        ...          ...          ...                     ...                  ...      ...       ...               ...\n",
       "                          ...        ...     ...                  ...                ...      ...      ...      ...     ...         ...      ...     ...                 ...        ...          ...          ...                     ...                  ...      ...       ...               ...\n",
       "Dask Name: from-delayed, 5937 tasks"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Helper functions and dataframes for fast data processing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 29min 53s, sys: 1min 18s, total: 31min 12s\n",
      "Wall time: 30min 10s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Create fast in memory subset dataframe for fast analysis\n",
    "df_pd = df[[\"channel_id\", \"datetime\", \"participants_count\", \"links\", \"group_type\", \"forward_from\", \"mentions\", \"views\"]].compute()\n",
    "d = {'group': True, 'channel': False}\n",
    "df_pd= df_pd.assign(is_group = lambda x: x.group_type.map(d)).drop(columns=['group_type'])\n",
    "\n",
    "pickle_path = f'{curr_dir}/data_final/'\n",
    "\n",
    "df_pd.to_pickle(pickle_path + \"data.pkl\")\n",
    "\n",
    "# Create df to merge id back to name\n",
    "pairs = df[[\"channel_name\", \"channel_id\"]].drop_duplicates(subset=[\"channel_name\"]).compute()\n",
    "pairs.to_pickle(pickle_path + \"pairs.pkl\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Load df_pd lazy sometimes\n",
    "pickle_path = f'{curr_dir}/data_final/'\n",
    "df_pd = pd.read_pickle(f'{pickle_path}/data.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 6min 2s, sys: 1min 10s, total: 7min 12s\n",
      "Wall time: 7min 13s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "# Add links list\n",
    "links = df_pd[[\"channel_id\", \"datetime\", \"forward_from\", \"links\"]].assign(links=df_pd.links.apply(literal_eval)).explode(\"links\").dropna(subset = ['links'])\n",
    "\n",
    "# Get domain\n",
    "def get_domain(x):\n",
    "    try: \n",
    "        return urlparse(str(x)).hostname\n",
    "    except ValueError:\n",
    "        return None\n",
    "\n",
    "links_domain = links.assign(domain = lambda x: [get_domain(x) for x in x.links])\n",
    "\n",
    "links_domain.to_pickle(pickle_path + \"links.pkl\")\n",
    "links_domain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Helper Functions\n",
    "def get_id(name):\n",
    "    return pairs[pairs.channel_name == name].channel_id.iloc[0]\n",
    "\n",
    "def get_name(channel_id):\n",
    "    return pairs[pairs.channel_id == channel_id].channel_name.iloc[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add dataframe metadata (Community, Pagerank, size etc.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calcuclate edges and weights\n",
    "edges_df = (df_pd\n",
    "            .loc[:, [\"channel_id\", \"forward_from\", \"participants_count\"]]\n",
    "            .groupby(by=[\"channel_id\", \"forward_from\"])\n",
    "            .count()\n",
    "            .reset_index()\n",
    "            .merge(pairs, left_on=\"channel_id\", right_on=\"channel_id\")\n",
    "            .drop(columns=[\"channel_id\"])\n",
    "            .reindex(columns=[\"channel_name\", \"forward_from\", \"participants_count\"]))\n",
    "\n",
    "edges_tuple = [tuple(x) for x in edges_df.to_numpy()]\n",
    "\n",
    "# Only core network (channels that have been scraped)\n",
    "edges_tuple_only_scraped = [ele for ele in edges_tuple if ele[0] in channels and ele[1] in channels]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create networkx graph\n",
    "G = nx.DiGraph()\n",
    "G.add_weighted_edges_from(edges_tuple_only_scraped)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate pagerank\n",
    "pagerank_dict = nx.pagerank(G)\n",
    "pagerank = pd.DataFrame.from_dict(pagerank_dict, orient='index', columns=[\"rank\"]).sort_values(by=\"rank\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Undirected Graph for community detection\n",
    "G_und = G.to_undirected()\n",
    "\n",
    "# Compute partition\n",
    "partition = community.best_partition(G_und, random_state=1)\n",
    "\n",
    "# Save communities\n",
    "community_df = pd.DataFrame(partition.items(), columns=[\"channel_name\", \"community\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Language cannot be detectedout of 94/1979 channels is being scrapedddedcraped\n",
      "Channel querdenken_hannover511 out of 201/1979 channels is being scrapedg scraped\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py:2785: DtypeWarning: Columns (9) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Channel argonerd out of 503/1979 channels is being scrapedg scrapededdapeddeddped\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Channel wdchurchat out of 581/1979 channels is being scrapedcraped scrapedcrapeded\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Channel folgedemplan out of 610/1979 channels is being scrapedped being scrapeded\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Language cannot be detected79 channels is being scrapednels is being scrapeddped\n",
      "Channel trumpjrchat out of 755/1979 channels is being scrapedrapedapedrapedscraped\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py:2785: DtypeWarning: Columns (15) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Language cannot be detectedt of 956/1979 channels is being scrapededpedpededcraped\n",
      "Channel innfacted out of 982/1979 channels is being scrapedddcrapedscraped\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Channel breitbart out of 1026/1979 channels is being scrapeds being scraped\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py:2785: DtypeWarning: Columns (18) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Language cannot be detectedf 1030/1979 channels is being scraped\n",
      "Channel kesselbunte out of 1048/1979 channels is being scrapedped scraped\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Channel jewrassicliars out of 1356/1979 channels is being scrapedrapeddscrapedpeded\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Channel thommaxxtv out of 1413/1979 channels is being scrapeddpedscrapedddrapedaped\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Channel voiceofjoyce out of 1536/1979 channels is being scrapedrapedg scrapededed\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Language cannot be detected/1979 channels is being scrapedis being scrapedscraped\n",
      "Language cannot be detected979 channels is being scraped channels is being scraped\n",
      "Channel breaking911_chat out of 1723/1979 channels is being scrapedapededcrapedaped\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Language cannot be detectedof 1760/1979 channels is being scrapedapedcraped\n",
      "Channel weare17a_sky out of 1954/1979 channels is being scrapedg scrapedddrapedped\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Detector is not able to detect the language reliably.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Channel trustandlife out of 1979/1979 channels is being scrapeding scrapedped\r"
     ]
    }
   ],
   "source": [
    "# Find languages of channels\n",
    "channel_languages = []\n",
    "\n",
    "for index, channel_name in enumerate(channels):\n",
    "    print(f'Channel {channel_name} out of {index + 1}/{len(channels)} channels is being scraped', end=\"\\r\")\n",
    "    channel_msgs = ' '.join(pd.read_csv('%s/data_final/csvs/%s.csv' % (curr_dir, channel_name)).raw_text.sample(n=200, random_state=1, replace=True).astype(str))\n",
    "    printable_str = ''.join(x for x in channel_msgs if x.isprintable())\n",
    "    text = re.sub(r'(?:(?:http|https):\\/\\/)?([-a-zA-Z0-9.]{2,256}\\.[a-z]{2,4})\\b(?:\\/[-a-zA-Z0-9@:%_\\+.~#?&//=]*)?', '', printable_str, flags=re.MULTILINE)\n",
    "    \n",
    "    try: \n",
    "        detector = Detector(text)\n",
    "        language = detector.language.code\n",
    "    except UnknownLanguage:\n",
    "        print(\"Language cannot be detected\")\n",
    "        language = np.nan\n",
    "    \n",
    "    channel_languages.append([channel_name, language])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "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>channel_name</th>\n",
       "      <th>category_content</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>querdenken_201</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>querdenken201</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>querdenken201_aktiv</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>querdenken203</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>querdenken_211</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>148</th>\n",
       "      <td>querdenken841</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>149</th>\n",
       "      <td>querdenken841_aktiv</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>querdenken_911</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>querdenken911</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>querdenken911_aktiv</td>\n",
       "      <td>querdenker</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>153 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            channel_name category_content\n",
       "0         querdenken_201       querdenker\n",
       "1          querdenken201       querdenker\n",
       "2    querdenken201_aktiv       querdenker\n",
       "3          querdenken203       querdenker\n",
       "4         querdenken_211       querdenker\n",
       "..                   ...              ...\n",
       "148        querdenken841       querdenker\n",
       "149  querdenken841_aktiv       querdenker\n",
       "150       querdenken_911       querdenker\n",
       "151        querdenken911       querdenker\n",
       "152  querdenken911_aktiv       querdenker\n",
       "\n",
       "[153 rows x 2 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get channels from querdenken verzeichnis to classify automatically\n",
    "req = Request(\"https://app.querdenken-711.de/initiatives-directory\")\n",
    "html_page = urlopen(req)\n",
    "\n",
    "soup = BeautifulSoup(html_page, \"html.parser\")\n",
    "\n",
    "links = []\n",
    "for link in soup.findAll('a'):\n",
    "    links.append(link.get('href'))\n",
    "    \n",
    "querdenken_channels_scraped =  extract_channels_from_links_website(links)\n",
    "querdenken_channels_exisiting = [channel for channel in querdenken_channels_scraped if channel in channels]\n",
    "querdenken_channels_df = (pd.DataFrame(querdenken_channels_exisiting, columns=[\"channel_name\"])\n",
    "                          .assign(category_content=\"querdenker\"))\n",
    "querdenken_channels_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>channel_name</th>\n",
       "      <th>channel_id</th>\n",
       "      <th>link</th>\n",
       "      <th>index</th>\n",
       "      <th>rank</th>\n",
       "      <th>community</th>\n",
       "      <th>participants_count</th>\n",
       "      <th>is_group</th>\n",
       "      <th>language</th>\n",
       "      <th>category_content</th>\n",
       "      <th>category_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>fakepandemie</td>\n",
       "      <td>1.317450e+09</td>\n",
       "      <td>https://t.me/s/fakepandemie</td>\n",
       "      <td>fakepandemie</td>\n",
       "      <td>0.000089</td>\n",
       "      <td>3.0</td>\n",
       "      <td>333.0</td>\n",
       "      <td>False</td>\n",
       "      <td>de</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>querdenken7451</td>\n",
       "      <td>1.188593e+09</td>\n",
       "      <td>https://t.me/s/querdenken7451</td>\n",
       "      <td>querdenken7451</td>\n",
       "      <td>0.000089</td>\n",
       "      <td>3.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>True</td>\n",
       "      <td>de</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>querdenken791</td>\n",
       "      <td>1.298666e+09</td>\n",
       "      <td>https://t.me/s/querdenken791</td>\n",
       "      <td>querdenken791</td>\n",
       "      <td>0.000089</td>\n",
       "      <td>3.0</td>\n",
       "      <td>379.0</td>\n",
       "      <td>True</td>\n",
       "      <td>de</td>\n",
       "      <td>querdenker</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>querdenken_30</td>\n",
       "      <td>1.366609e+09</td>\n",
       "      <td>https://t.me/s/querdenken_30</td>\n",
       "      <td>querdenken_30</td>\n",
       "      <td>0.000105</td>\n",
       "      <td>3.0</td>\n",
       "      <td>733.0</td>\n",
       "      <td>False</td>\n",
       "      <td>de</td>\n",
       "      <td>querdenker</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>querdenken351</td>\n",
       "      <td>1.417909e+09</td>\n",
       "      <td>https://t.me/s/querdenken351</td>\n",
       "      <td>querdenken351</td>\n",
       "      <td>0.000089</td>\n",
       "      <td>3.0</td>\n",
       "      <td>515.0</td>\n",
       "      <td>True</td>\n",
       "      <td>de</td>\n",
       "      <td>querdenker</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1974</th>\n",
       "      <td>rwelt</td>\n",
       "      <td>1.086141e+09</td>\n",
       "      <td>https://t.me/s/rwelt</td>\n",
       "      <td>rwelt</td>\n",
       "      <td>0.000097</td>\n",
       "      <td>3.0</td>\n",
       "      <td>226.0</td>\n",
       "      <td>False</td>\n",
       "      <td>de</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1975</th>\n",
       "      <td>nwointelligence</td>\n",
       "      <td>1.358481e+09</td>\n",
       "      <td>https://t.me/s/nwointelligence</td>\n",
       "      <td>nwointelligence</td>\n",
       "      <td>0.000747</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5404.0</td>\n",
       "      <td>False</td>\n",
       "      <td>en</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1976</th>\n",
       "      <td>emmanuelmacron</td>\n",
       "      <td>1.075445e+09</td>\n",
       "      <td>https://t.me/s/emmanuelmacron</td>\n",
       "      <td>emmanuelmacron</td>\n",
       "      <td>0.000089</td>\n",
       "      <td>0.0</td>\n",
       "      <td>14089.0</td>\n",
       "      <td>False</td>\n",
       "      <td>fr</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1977</th>\n",
       "      <td>antoinerichard_official</td>\n",
       "      <td>1.279255e+09</td>\n",
       "      <td>https://t.me/s/antoinerichard_official</td>\n",
       "      <td>antoinerichard_official</td>\n",
       "      <td>0.000138</td>\n",
       "      <td>3.0</td>\n",
       "      <td>9642.0</td>\n",
       "      <td>False</td>\n",
       "      <td>de</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1978</th>\n",
       "      <td>trustandlife</td>\n",
       "      <td>1.371729e+09</td>\n",
       "      <td>https://t.me/s/trustandlife</td>\n",
       "      <td>trustandlife</td>\n",
       "      <td>0.000096</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2716.0</td>\n",
       "      <td>False</td>\n",
       "      <td>de</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1979 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                 channel_name    channel_id  \\\n",
       "0                fakepandemie  1.317450e+09   \n",
       "1              querdenken7451  1.188593e+09   \n",
       "2               querdenken791  1.298666e+09   \n",
       "3               querdenken_30  1.366609e+09   \n",
       "4               querdenken351  1.417909e+09   \n",
       "...                       ...           ...   \n",
       "1974                    rwelt  1.086141e+09   \n",
       "1975          nwointelligence  1.358481e+09   \n",
       "1976           emmanuelmacron  1.075445e+09   \n",
       "1977  antoinerichard_official  1.279255e+09   \n",
       "1978             trustandlife  1.371729e+09   \n",
       "\n",
       "                                        link                    index  \\\n",
       "0                https://t.me/s/fakepandemie             fakepandemie   \n",
       "1              https://t.me/s/querdenken7451           querdenken7451   \n",
       "2               https://t.me/s/querdenken791            querdenken791   \n",
       "3               https://t.me/s/querdenken_30            querdenken_30   \n",
       "4               https://t.me/s/querdenken351            querdenken351   \n",
       "...                                      ...                      ...   \n",
       "1974                    https://t.me/s/rwelt                    rwelt   \n",
       "1975          https://t.me/s/nwointelligence          nwointelligence   \n",
       "1976           https://t.me/s/emmanuelmacron           emmanuelmacron   \n",
       "1977  https://t.me/s/antoinerichard_official  antoinerichard_official   \n",
       "1978             https://t.me/s/trustandlife             trustandlife   \n",
       "\n",
       "          rank  community  participants_count  is_group language  \\\n",
       "0     0.000089        3.0               333.0     False       de   \n",
       "1     0.000089        3.0                14.0      True       de   \n",
       "2     0.000089        3.0               379.0      True       de   \n",
       "3     0.000105        3.0               733.0     False       de   \n",
       "4     0.000089        3.0               515.0      True       de   \n",
       "...        ...        ...                 ...       ...      ...   \n",
       "1974  0.000097        3.0               226.0     False       de   \n",
       "1975  0.000747        0.0              5404.0     False       en   \n",
       "1976  0.000089        0.0             14089.0     False       fr   \n",
       "1977  0.000138        3.0              9642.0     False       de   \n",
       "1978  0.000096        3.0              2716.0     False       de   \n",
       "\n",
       "     category_content  category_type  \n",
       "0                 NaN            NaN  \n",
       "1                 NaN            NaN  \n",
       "2          querdenker            NaN  \n",
       "3          querdenker            NaN  \n",
       "4          querdenker            NaN  \n",
       "...               ...            ...  \n",
       "1974              NaN            NaN  \n",
       "1975              NaN            NaN  \n",
       "1976              NaN            NaN  \n",
       "1977              NaN            NaN  \n",
       "1978              NaN            NaN  \n",
       "\n",
       "[1979 rows x 11 columns]"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Bring everything togehter for a channels_metadata list\n",
    "\n",
    "community_df = pd.DataFrame(partition.items(), columns=[\"channel_name\", \"community\"])\n",
    "metadata_df = df_pd.drop_duplicates(\"channel_id\")[[\"channel_id\", \"participants_count\", \"is_group\"]]\n",
    "languages_df = pd.DataFrame(channel_languages, columns=[\"channel_name\", \"language\"])\n",
    "\n",
    "channels_df = (pairs\n",
    "            .assign(link=lambda x: [f'https://t.me/s/{channel_name}' for channel_name in x.channel_name])\n",
    "            .merge(pagerank.reset_index(), left_on=\"channel_name\", right_on=\"index\", how=\"outer\")\n",
    "            .merge(community_df, on=\"channel_name\", how=\"outer\")\n",
    "            .merge(metadata_df, on=\"channel_id\", how=\"outer\")\n",
    "            .merge(languages_df, on=\"channel_name\", how=\"outer\")\n",
    "            .merge(querdenken_channels_df, on=\"channel_name\", how=\"outer\")\n",
    "            .assign(category_type=np.nan))\n",
    "\n",
    "# Add and clean channel categories\n",
    "channels_meta = pd.read_csv(f'{pickle_path}communities_meta.csv')\n",
    "\n",
    "channels_meta.loc[channels_meta.category_content == 'spititual ', \"category_content\"] = 'spiritual' \n",
    "channels_meta.loc[channels_meta.category_content == 'spiritual ', \"category_content\"] = 'spiritual'\n",
    "channels_meta.loc[channels_meta.category_content == 'covid-sceptic', \"category_content\"] = 'spiritual'\n",
    "channels_meta.loc[channels_meta.category_content == 'conspiracy ', \"category_content\"] = 'conspiracy'\n",
    "\n",
    "channels_meta.loc[channels_meta.category_type == 'news-aggregator', \"category_type\"] = 'news_aggregator'\n",
    "channels_meta.loc[channels_meta.category_type == 'party', \"category_type\"] = \"political_actor\"\n",
    "channels_meta.loc[channels_meta.category_type == 'organizer'] = \"political_initative\"\n",
    "\n",
    "channels_df = (channels_df\n",
    "             .drop(columns=[\"category_content\", \"category_type\"])\n",
    "             .merge(channels_meta[[\"channel_name\", \"category_content\", \"category_type\"]], on=\"channel_name\", how=\"left\"))\n",
    "\n",
    "\n",
    "channels_df.to_csv(f'{curr_dir}/data_final/metadata.csv')\n",
    "channels_df.to_pickle(f'{pickle_path}metadata.pkl')\n",
    "\n",
    "channels_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [],
   "source": [
    "pickle_path = f'{curr_dir}/data_final/'\n",
    "channels_meta = pd.read_csv(f'{pickle_path}communities_meta.csv')\n",
    "channels_df = pd.read_pickle(f'{pickle_path}/metadata.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "channels_meta.loc[channels_meta.category_content == 'spititual ', \"category_content\"] = 'spiritual' \n",
    "channels_meta.loc[channels_meta.category_content == 'spiritual ', \"category_content\"] = 'spiritual'\n",
    "channels_meta.loc[channels_meta.category_content == 'covid-sceptic', \"category_content\"] = 'spiritual'\n",
    "channels_meta.loc[channels_meta.category_content == 'conspiracy ', \"category_content\"] = 'conspiracy'\n",
    "\n",
    "channels_meta.loc[channels_meta.category_type == 'news-aggregator', \"category_type\"] = 'news_aggregator'\n",
    "channels_meta.loc[channels_meta.category_type == 'party', \"category_type\"] = \"political_actor\"\n",
    "channels_meta.loc[channels_meta.category_type == 'organizer'] = \"political_initative\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "new_df = (channels_df\n",
    " .drop(columns=[\"category_content\", \"category_type\"])\n",
    " .merge(channels_meta[[\"channel_name\", \"category_content\", \"category_type\"]], on=\"channel_name\", how=\"left\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "new_df.to_pickle(f'{pickle_path}metadata.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['media', 'influencer', 'news_aggregator', 'party', nan,\n",
       "       'organizer', 'other', 'telegram-media'], dtype=object)"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "channels_meta.category_type.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "news_aggregator    10\n",
       "influencer          8\n",
       "media               7\n",
       "other               4\n",
       "spiritual           3\n",
       "telegram-media      2\n",
       "news-aggregator     2\n",
       "organizer           1\n",
       "party               1\n",
       "conspiracy          1\n",
       "Name: category_type, dtype: int64"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "channels_meta.category_type.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print([get_name(channel_id) for channel_id in channels_size[channels_size.participants_count < 10].channel_id])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Deprecated Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "curr_dir = '/root/repos/telegraph'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pandas.errors import EmptyDataError\n",
    "\n",
    "# Deprecated\n",
    "# Debug \n",
    "csv_paths = ['%s/csv_data/%s' % (curr_dir, pos_csv) for pos_csv in os.listdir(curr_dir + \"/csv_data\") if pos_csv.endswith('.csv')]\n",
    "\n",
    "error_files = []\n",
    "for i, curr_path in enumerate(csv_paths):\n",
    "    print(f'channel {i}/{len(csv_paths)}', end='\\r')\n",
    "    try: \n",
    "        curr = pd.read_csv(curr_path, low_memory=False)\n",
    "        if len(curr.columns) != 21:\n",
    "            print(curr_path)\n",
    "    except EmptyDataError:\n",
    "        error_files.append(curr_path)\n",
    "        print(curr_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Deprecated\n",
    "# Get all exisiting channels \n",
    "p = re.compile('^.*(?=(\\.csv))')\n",
    "existing_channels = [p.match(channel).group() for channel in os.listdir(curr_dir + \"/csv_data/\") if p.match(channel) is not None]\n",
    "\n",
    "# Get all existing channels from iteration \n",
    "c = [channel for channel in get_n_iterations_channels(11) if channel in existing_channels]\n",
    "len(c)\n",
    "\n",
    "# Import all data \n",
    "df_all = dd.read_csv(f'{curr_dir}/csv_data/*', parse_dates=['datetime'], dtype=dtype, engine=\"python\", error_bad_lines=False, blocksize=None)\n",
    "\n",
    "# Get all exisiting channels \n",
    "p = re.compile('^.*(?=(\\.csv))')\n",
    "existing_channels = [p.match(channel).group() for channel in os.listdir(curr_dir + \"/csv_data/\") if p.match(channel) is not None]\n",
    "\n",
    "# Get all existing channels from iteration \n",
    "channels = [channel for channel in get_n_iterations_channels(11) if channel in existing_channels]\n",
    "len(channels)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get iterations file (Json keeping track when data was scraped)\n",
    "iterations_path = f'{curr_dir}/data_final/iterations/16-Aug-2021 (08-03-48.974626).json'\n",
    "    \n",
    "with open(iterations_path) as f:\n",
    "    iterations = json.load(f)\n",
    "\n",
    "# Extract all channels from the first 10 iterations\n",
    "subset = {k: v for k, v in iterations.items() if k in [str(i) for i in range(11)]}\n",
    "\n",
    "# Transform to list of channels\n",
    "channels = [v.lower() for k, v in subset.items() for v in list(v.keys())]\n",
    "    \n",
    "channels[1:5]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import random\n",
    "\n",
    "# This is a function which resturns random channels for testing of code \n",
    "def get_df(amount_channels):\n",
    "    sample = random.sample(existing_channels, amount_channels)\n",
    "    df_paths_sample = [\"%s/csv_data/%s.csv\" % (curr_dir, channel_name) for channel_name in sample]\n",
    "    return dd.read_csv(df_paths_sample, parse_dates=['datetime'], dtype=dtype, engine=\"python\", error_bad_lines=False, blocksize=None)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# \"+\".join([\"x[%s]\" % c for c in list(pd.DataFrame(channels_df.community.value_counts()).query(\"community < 23\").index)])\n",
    "\n",
    "\n",
    "community_iteration = (pd.DataFrame([[k, channel]\n",
    "                                    for k,v in iterations.items() \n",
    "                                    for channel, count in v.items()], columns=[\"iteration\", \"channel_name\"])\n",
    "                     .merge(channels_df)[[\"iteration\", \"community\", \"channel_name\"]]\n",
    "                     .groupby([\"iteration\", \"community\"])\n",
    "                     .count()\n",
    "                     .rename(columns={\"channel_name\":\"count\"})\n",
    "                     .reset_index()\n",
    "                     .pivot(index='iteration', columns='community', values='count')\n",
    "                     .fillna(0)\n",
    "                     .assign(other=lambda x: x[10.0]+x[16.0]+x[21.0]+x[15.0]+x[12.0]+x[14.0]+x[18.0]+x[19.0]+x[24.0]+x[5.0]+x[27.0]+x[22.0]+x[20.0]+x[13.0]+x[17.0]+x[9.0]+x[7.0]+x[26.0]+x[23.0]+x[28.0]+x[25.0]+x[4.0])\n",
    "                     [[3.0,1.0,0.0,2.0,8.0,6.0,11.0, \"other\"]]\n",
    "                       .apply(lambda x: x/x.sum(), axis=1))\n",
    "\n",
    "\n",
    "community_iteration.index = community_iteration.index.astype(int)\n",
    "community_iteration = community_iteration.sort_index()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create meta df \n",
    "edges_df = (df_pd\n",
    "            .loc[:, [\"channel_id\", \"forward_from\", \"participants_count\"]]\n",
    "            .groupby(by=[\"channel_id\", \"forward_from\"])\n",
    "            .count()\n",
    "            .reset_index()\n",
    "            .merge(pairs, left_on=\"channel_id\", right_on=\"channel_id\")\n",
    "            .drop(columns=[\"channel_id\"])\n",
    "            .reindex(columns=[\"channel_name\", \"forward_from\", \"participants_count\"]))\n",
    "\n",
    "edges_df\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "edges_tuple = [tuple(x) for x in edges_df.to_numpy()]\n",
    "\n",
    "# Only Core Network (Channels that have been scraped)\n",
    "edges_tuple_only_scraped = [ele for ele in edges_tuple if ele[0] in channels and ele[1] in channels]\n",
    "edges_tuple_only_scraped[1:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "G = nx.DiGraph()\n",
    "G.add_weighted_edges_from(edges_tuple_only_scraped)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Wichtigste Kanäle nach Pagerank. \n",
    "pagerank = get_pagerank(G)\n",
    "pagerank"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}