reassemble_gdf.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reassemble .gdf\n",
    "\n",
    "Take the annotated data and reassemble it to read into `R` and gephi."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-14T13:49:12.570457Z",
     "start_time": "2019-05-14T13:49:12.068829Z"
    }
   },
   "outputs": [],
   "source": [
    "from langdetect import detect"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-14T13:49:19.557227Z",
     "start_time": "2019-05-14T13:49:12.576827Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import math"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After annotating video data, we want to reassemble gdf files for visualization in gephi or for loading back into `R` in `parse_load_gdf.ipynb`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def df_row_to_csv_row(row_list):\n",
    "    # for writing out gdf file\n",
    "    row_list_new = []\n",
    "    for x in row_list:\n",
    "        if isinstance(x, float) and math.isnan(x):\n",
    "            x = ''\n",
    "        row_list_new.append(x)\n",
    "    return ','.join((str(x) for x in row_list_new))\n",
    "        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_reassemble_annotated_video_data(metatdata_filename, rels_filename):\n",
    "    # load annotated metadata and relations and recreate gdf file for exporting to gephi \n",
    "    annotated_metadata = pd.read_csv(metatdata_filename)\n",
    "    annotated_rels = pd.read_csv(rels_filename)\n",
    "    # drop redundant row index column\n",
    "    annotated_metadata = annotated_metadata.drop(\"Unnamed: 0\", axis=1)\n",
    "    annotated_rels = annotated_rels.drop(\"Unnamed: 0\", axis=1)\n",
    "    \n",
    "    annotated_metadata.rename(columns={\n",
    "        'nodeid': 'nodedef>name VARCHAR',\n",
    "        'label': 'label VARCHAR',\n",
    "        'subscriberCount': 'subscriberCount INT',\n",
    "        'label_clean': 'labelClean VARCHAR',\n",
    "        'title_language': 'titleLanguage VARCHAR',\n",
    "        'url': 'url VARCHAR',\n",
    "        'seed_channel': 'seedChannel BOOLEAN'\n",
    "\n",
    "    },\n",
    "        inplace=True)\n",
    "\n",
    "    # todo set label VARCHAR to  is_inappropriate , only label if inappropriate :(  - this is for videos\n",
    "    annotated_metadata['label_old VARCHAR'] = annotated_metadata['label VARCHAR']\n",
    "    annotated_metadata['label VARCHAR'] = annotated_metadata.is_inappropriate.apply(lambda x : 'X' if x == 'YES' else '_')\n",
    "\n",
    "    # write to .gdf file\n",
    "    gdf_metadata_header = ','.join((str(x) for x in annotated_metadata.columns))\n",
    "    gdf_rels_header = ','.join((str(x) for x in annotated_rels.columns))\n",
    "    \n",
    "    with open('annotated_videos_gdf.gdf', 'w') as f:\n",
    "        f.write(gdf_metadata_header + '\\n')\n",
    "        for row in annotated_metadata.values:\n",
    "            f.write(df_row_to_csv_row(row) + '\\n')\n",
    "        f.write(gdf_rels_header + '\\n')\n",
    "        for row in annotated_rels.values:\n",
    "            f.write(','.join((str(x) for x in row))+ '\\n')\n",
    "    return annotated_metadata, annotated_rels\n",
    "            \n",
    "annotated_metadata, annotated_rels = load_reassemble_annotated_video_data('annotated_videonet_seeds_elsa_spiderman_2019_08_18_metadata.csv',\n",
    "                                    'videonet_seeds_elsa_spiderman_2019_08_18_relations_orig.csv')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Join annotation of videos to channel level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "channel_ids = {}\n",
    "for row in annotated_metadata.to_dict(orient='records'):\n",
    "    channel_id = row['channelId VARCHAR']\n",
    "    if channel_id in channel_ids:\n",
    "        channel_ids[channel_id ].append(row['is_inappropriate'])\n",
    "    else:\n",
    "        channel_ids[channel_id] = [row['is_inappropriate']]\n",
    "\n",
    "# get the unanimous video annotations of each channel\n",
    "channels_videos_annotated = {k: list(set(v))[0] for k,v in channel_ids.items() if len(set(v)) == 1 }\n",
    "channels_videos_annotated = pd.Series(channels_videos_annotated).to_frame()\n",
    "channels_videos_annotated.index.names = ['channelId']\n",
    "channels_videos_annotated.rename(columns={\n",
    "        0: 'is_inappropriate VARCHAR'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['nodeid', 'label', 'isSeed VARCHAR', 'seedRank INT', 'subscriberCount',\n",
      "       'videoCount INT', 'viewCount(100s) INT', 'country VARCHAR',\n",
      "       'publishedAt VARCHAR', 'daysactive INT', 'url'],\n",
      "      dtype='object')\n"
     ]
    },
    {
     "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>nodedef&gt;name VARCHAR</th>\n",
       "      <th>label VARCHAR</th>\n",
       "      <th>isSeed VARCHAR</th>\n",
       "      <th>seedRank INT</th>\n",
       "      <th>subscriberCount INT</th>\n",
       "      <th>videoCount INT</th>\n",
       "      <th>viewCount(100s) INT</th>\n",
       "      <th>country VARCHAR</th>\n",
       "      <th>publishedAt VARCHAR</th>\n",
       "      <th>daysactive INT</th>\n",
       "      <th>url VARCHAR</th>\n",
       "      <th>is_inappropriate VARCHAR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>UCir-ay37PYv8-lQOk_dxzFA</td>\n",
       "      <td>wai bom</td>\n",
       "      <td>yes</td>\n",
       "      <td>1</td>\n",
       "      <td>5346</td>\n",
       "      <td>1</td>\n",
       "      <td>24099</td>\n",
       "      <td>not set</td>\n",
       "      <td>2017-08-22T10:42:40.000Z</td>\n",
       "      <td>634</td>\n",
       "      <td>https://www.youtube.com/channel/UCir-ay37PYv8-...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>UC96vy6lHoIKWSevWJEC47xA</td>\n",
       "      <td>Mathias Jost</td>\n",
       "      <td>yes</td>\n",
       "      <td>2</td>\n",
       "      <td>4311</td>\n",
       "      <td>26</td>\n",
       "      <td>14301</td>\n",
       "      <td>not set</td>\n",
       "      <td>2016-12-25T14:30:29.000Z</td>\n",
       "      <td>874</td>\n",
       "      <td>https://www.youtube.com/channel/UC96vy6lHoIKWS...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>UC0xH3QQdLAIu7srkkbTG8CQ</td>\n",
       "      <td>Eve My Tube</td>\n",
       "      <td>yes</td>\n",
       "      <td>3</td>\n",
       "      <td>1673550</td>\n",
       "      <td>1036</td>\n",
       "      <td>3306465</td>\n",
       "      <td>TH</td>\n",
       "      <td>2016-01-29T15:24:19.000Z</td>\n",
       "      <td>1205</td>\n",
       "      <td>https://www.youtube.com/channel/UC0xH3QQdLAIu7...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>UCY9eEaH19ulZmdusctU1ejA</td>\n",
       "      <td>Glamour</td>\n",
       "      <td>yes</td>\n",
       "      <td>4</td>\n",
       "      <td>2109252</td>\n",
       "      <td>1654</td>\n",
       "      <td>6585187</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-04-03T15:03:38.000Z</td>\n",
       "      <td>4428</td>\n",
       "      <td>https://www.youtube.com/channel/UCY9eEaH19ulZm...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>UCD4MbghmkGS3aF5ymsc8_bw</td>\n",
       "      <td>Post Hostel</td>\n",
       "      <td>yes</td>\n",
       "      <td>5</td>\n",
       "      <td>8652</td>\n",
       "      <td>5</td>\n",
       "      <td>78937</td>\n",
       "      <td>not set</td>\n",
       "      <td>2015-02-19T09:33:52.000Z</td>\n",
       "      <td>1549</td>\n",
       "      <td>https://www.youtube.com/channel/UCD4MbghmkGS3a...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>UCp_CBY2rEWs_CPI3HaC6tbg</td>\n",
       "      <td>Alex Ramirès</td>\n",
       "      <td>yes</td>\n",
       "      <td>6</td>\n",
       "      <td>324934</td>\n",
       "      <td>62</td>\n",
       "      <td>664676</td>\n",
       "      <td>not set</td>\n",
       "      <td>2011-07-19T10:28:08.000Z</td>\n",
       "      <td>2860</td>\n",
       "      <td>https://www.youtube.com/channel/UCp_CBY2rEWs_C...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>UCN6rjYMkYv5eUAHbFwDbQmg</td>\n",
       "      <td>ClubEngendros</td>\n",
       "      <td>yes</td>\n",
       "      <td>7</td>\n",
       "      <td>19446</td>\n",
       "      <td>40</td>\n",
       "      <td>196866</td>\n",
       "      <td>not set</td>\n",
       "      <td>2008-10-01T14:29:38.000Z</td>\n",
       "      <td>3881</td>\n",
       "      <td>https://www.youtube.com/channel/UCN6rjYMkYv5eU...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>UCRBhx25KHV4wUhKsO29BNgw</td>\n",
       "      <td>Way to Hero</td>\n",
       "      <td>yes</td>\n",
       "      <td>8</td>\n",
       "      <td>3700</td>\n",
       "      <td>62</td>\n",
       "      <td>6652</td>\n",
       "      <td>BR</td>\n",
       "      <td>2015-10-27T19:57:23.000Z</td>\n",
       "      <td>1299</td>\n",
       "      <td>https://www.youtube.com/channel/UCRBhx25KHV4wU...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>UCYDKUHScAVoI_UTVuA7ThcQ</td>\n",
       "      <td>Crafty Panda DIY</td>\n",
       "      <td>yes</td>\n",
       "      <td>9</td>\n",
       "      <td>0</td>\n",
       "      <td>65</td>\n",
       "      <td>256182</td>\n",
       "      <td>US</td>\n",
       "      <td>2018-04-17T11:07:20.000Z</td>\n",
       "      <td>396</td>\n",
       "      <td>https://www.youtube.com/channel/UCYDKUHScAVoI_...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>UCfaZw8XH_zmAVkBst_MPD6w</td>\n",
       "      <td>CKN Toys</td>\n",
       "      <td>yes</td>\n",
       "      <td>10</td>\n",
       "      <td>12488147</td>\n",
       "      <td>529</td>\n",
       "      <td>109306937</td>\n",
       "      <td>AU</td>\n",
       "      <td>2015-03-28T04:11:34.000Z</td>\n",
       "      <td>1513</td>\n",
       "      <td>https://www.youtube.com/channel/UCfaZw8XH_zmAV...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>UCE3cM6f-H0mZlnvQafTqCyQ</td>\n",
       "      <td>The Super Zeros</td>\n",
       "      <td>yes</td>\n",
       "      <td>11</td>\n",
       "      <td>729035</td>\n",
       "      <td>158</td>\n",
       "      <td>4604463</td>\n",
       "      <td>US</td>\n",
       "      <td>2006-02-06T22:53:06.000Z</td>\n",
       "      <td>4849</td>\n",
       "      <td>https://www.youtube.com/channel/UCE3cM6f-H0mZl...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>UCEvLUnQiH-i1XUNikh0Zm4g</td>\n",
       "      <td>The Sean Ward Show</td>\n",
       "      <td>yes</td>\n",
       "      <td>12</td>\n",
       "      <td>3471486</td>\n",
       "      <td>429</td>\n",
       "      <td>17770940</td>\n",
       "      <td>not set</td>\n",
       "      <td>2010-05-03T22:27:07.000Z</td>\n",
       "      <td>3302</td>\n",
       "      <td>https://www.youtube.com/channel/UCEvLUnQiH-i1X...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>UCfBZ5Bgo2F2wRiy68XCWpow</td>\n",
       "      <td>The 86th Floor: Cosplay and Cons</td>\n",
       "      <td>yes</td>\n",
       "      <td>13</td>\n",
       "      <td>572120</td>\n",
       "      <td>111</td>\n",
       "      <td>2152268</td>\n",
       "      <td>GB</td>\n",
       "      <td>2014-05-27T08:49:57.000Z</td>\n",
       "      <td>1817</td>\n",
       "      <td>https://www.youtube.com/channel/UCfBZ5Bgo2F2wR...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>UCRqzi1fLE5az1Br6sbSdGuQ</td>\n",
       "      <td>Klara</td>\n",
       "      <td>yes</td>\n",
       "      <td>14</td>\n",
       "      <td>1587</td>\n",
       "      <td>5</td>\n",
       "      <td>10893</td>\n",
       "      <td>DE</td>\n",
       "      <td>2018-10-19T10:12:02.000Z</td>\n",
       "      <td>211</td>\n",
       "      <td>https://www.youtube.com/channel/UCRqzi1fLE5az1...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>UCePgEtPIBVVKELuW7NkadFA</td>\n",
       "      <td>123 Go Girly</td>\n",
       "      <td>yes</td>\n",
       "      <td>15</td>\n",
       "      <td>0</td>\n",
       "      <td>188</td>\n",
       "      <td>212086</td>\n",
       "      <td>US</td>\n",
       "      <td>2017-01-22T10:58:28.000Z</td>\n",
       "      <td>846</td>\n",
       "      <td>https://www.youtube.com/channel/UCePgEtPIBVVKE...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>UC5SYKAP880X9TDtpg3TZCIA</td>\n",
       "      <td>Magnetic Man</td>\n",
       "      <td>yes</td>\n",
       "      <td>16</td>\n",
       "      <td>219909</td>\n",
       "      <td>34</td>\n",
       "      <td>453258</td>\n",
       "      <td>US</td>\n",
       "      <td>2018-08-28T17:25:45.000Z</td>\n",
       "      <td>263</td>\n",
       "      <td>https://www.youtube.com/channel/UC5SYKAP880X9T...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>UCjeRo0NuOQ8SZT_MBGV4NrQ</td>\n",
       "      <td>Super Hero Videos lozaus2</td>\n",
       "      <td>yes</td>\n",
       "      <td>17</td>\n",
       "      <td>370933</td>\n",
       "      <td>9</td>\n",
       "      <td>907698</td>\n",
       "      <td>AU</td>\n",
       "      <td>2016-03-12T01:43:49.000Z</td>\n",
       "      <td>1163</td>\n",
       "      <td>https://www.youtube.com/channel/UCjeRo0NuOQ8SZ...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>UCg12HA5UiFKAupPA6Lbd-eA</td>\n",
       "      <td>GameArt Production</td>\n",
       "      <td>yes</td>\n",
       "      <td>18</td>\n",
       "      <td>126556</td>\n",
       "      <td>203</td>\n",
       "      <td>544220</td>\n",
       "      <td>not set</td>\n",
       "      <td>2011-08-28T06:45:25.000Z</td>\n",
       "      <td>2820</td>\n",
       "      <td>https://www.youtube.com/channel/UCg12HA5UiFKAu...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>UC1J5R-2zsrrICAYmkt5g_6Q</td>\n",
       "      <td>Patty Shukla Kids TV - Children s songs</td>\n",
       "      <td>yes</td>\n",
       "      <td>19</td>\n",
       "      <td>762162</td>\n",
       "      <td>274</td>\n",
       "      <td>5269882</td>\n",
       "      <td>US</td>\n",
       "      <td>2006-12-24T00:15:55.000Z</td>\n",
       "      <td>4529</td>\n",
       "      <td>https://www.youtube.com/channel/UC1J5R-2zsrrIC...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>UChCBHudFoBnsF0O1flPXqiQ</td>\n",
       "      <td>Nick Pro</td>\n",
       "      <td>yes</td>\n",
       "      <td>20</td>\n",
       "      <td>1872440</td>\n",
       "      <td>354</td>\n",
       "      <td>4187961</td>\n",
       "      <td>CA</td>\n",
       "      <td>2010-09-16T18:16:16.000Z</td>\n",
       "      <td>3166</td>\n",
       "      <td>https://www.youtube.com/channel/UChCBHudFoBnsF...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>UCOEYO2x_UJLXRUyoYQJ178A</td>\n",
       "      <td>BuzzPranks</td>\n",
       "      <td>yes</td>\n",
       "      <td>21</td>\n",
       "      <td>2998044</td>\n",
       "      <td>1309</td>\n",
       "      <td>11649285</td>\n",
       "      <td>US</td>\n",
       "      <td>2012-09-08T05:47:04.000Z</td>\n",
       "      <td>2443</td>\n",
       "      <td>https://www.youtube.com/channel/UCOEYO2x_UJLXR...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>UCKqx9r4mrFglauNBJc1L_eg</td>\n",
       "      <td>[토이푸딩] ToyPudding TV</td>\n",
       "      <td>yes</td>\n",
       "      <td>22</td>\n",
       "      <td>23828610</td>\n",
       "      <td>2561</td>\n",
       "      <td>139777459</td>\n",
       "      <td>US</td>\n",
       "      <td>2014-05-13T14:31:31.000Z</td>\n",
       "      <td>1831</td>\n",
       "      <td>https://www.youtube.com/channel/UCKqx9r4mrFgla...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>UCNOYoV6u5JWn74sIVe3FPaA</td>\n",
       "      <td>SuperHeroes Real Life</td>\n",
       "      <td>yes</td>\n",
       "      <td>23</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>394</td>\n",
       "      <td>not set</td>\n",
       "      <td>2015-06-03T04:12:24.000Z</td>\n",
       "      <td>1446</td>\n",
       "      <td>https://www.youtube.com/channel/UCNOYoV6u5JWn7...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>UC41kSpAQe1ABLUQbGP_WBVA</td>\n",
       "      <td>Life as a Mommy</td>\n",
       "      <td>yes</td>\n",
       "      <td>24</td>\n",
       "      <td>378169</td>\n",
       "      <td>264</td>\n",
       "      <td>717885</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-04-10T16:50:12.000Z</td>\n",
       "      <td>2229</td>\n",
       "      <td>https://www.youtube.com/channel/UC41kSpAQe1ABL...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>UCPgtKvRfZ74M0KqKcA39LNQ</td>\n",
       "      <td>MVP Entertainment ID</td>\n",
       "      <td>yes</td>\n",
       "      <td>25</td>\n",
       "      <td>1226550</td>\n",
       "      <td>3379</td>\n",
       "      <td>4538086</td>\n",
       "      <td>ID</td>\n",
       "      <td>2013-03-21T09:43:36.000Z</td>\n",
       "      <td>2249</td>\n",
       "      <td>https://www.youtube.com/channel/UCPgtKvRfZ74M0...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>UC1D26ByrbZVwuMgjVWmsOuw</td>\n",
       "      <td>Frame Order</td>\n",
       "      <td>yes</td>\n",
       "      <td>26</td>\n",
       "      <td>1686171</td>\n",
       "      <td>172</td>\n",
       "      <td>2436480</td>\n",
       "      <td>NL</td>\n",
       "      <td>2013-05-15T14:45:02.000Z</td>\n",
       "      <td>2194</td>\n",
       "      <td>https://www.youtube.com/channel/UC1D26ByrbZVwu...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>UCZL_mZiRnR42hg1IjV6lQ8w</td>\n",
       "      <td>;} Swxmz</td>\n",
       "      <td>yes</td>\n",
       "      <td>27</td>\n",
       "      <td>387</td>\n",
       "      <td>30</td>\n",
       "      <td>633</td>\n",
       "      <td>GB</td>\n",
       "      <td>2014-10-22T15:03:41.000Z</td>\n",
       "      <td>1669</td>\n",
       "      <td>https://www.youtube.com/channel/UCZL_mZiRnR42h...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>UCwRE527CMIU5yTxYeccBGwA</td>\n",
       "      <td>Australian Reborn Nursery</td>\n",
       "      <td>yes</td>\n",
       "      <td>28</td>\n",
       "      <td>7980</td>\n",
       "      <td>398</td>\n",
       "      <td>38242</td>\n",
       "      <td>not set</td>\n",
       "      <td>2017-09-04T04:32:09.000Z</td>\n",
       "      <td>622</td>\n",
       "      <td>https://www.youtube.com/channel/UCwRE527CMIU5y...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>UCtCU59RozAjBnamOFF66IvA</td>\n",
       "      <td>Crafty Panda Girly</td>\n",
       "      <td>yes</td>\n",
       "      <td>29</td>\n",
       "      <td>0</td>\n",
       "      <td>44</td>\n",
       "      <td>189001</td>\n",
       "      <td>US</td>\n",
       "      <td>2016-09-25T14:52:13.000Z</td>\n",
       "      <td>965</td>\n",
       "      <td>https://www.youtube.com/channel/UCtCU59RozAjBn...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>UCgmgq_ZL3R4UN1L4IUJ7hWQ</td>\n",
       "      <td>AmvAnimation</td>\n",
       "      <td>yes</td>\n",
       "      <td>30</td>\n",
       "      <td>29475</td>\n",
       "      <td>265</td>\n",
       "      <td>128200</td>\n",
       "      <td>not set</td>\n",
       "      <td>2014-06-23T05:39:54.000Z</td>\n",
       "      <td>1790</td>\n",
       "      <td>https://www.youtube.com/channel/UCgmgq_ZL3R4UN...</td>\n",
       "      <td>YES</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>UCVIWBQIuIvqFSIVztsuV2Aw</td>\n",
       "      <td>Bhaity Music Company</td>\n",
       "      <td>yes</td>\n",
       "      <td>39</td>\n",
       "      <td>251101</td>\n",
       "      <td>86</td>\n",
       "      <td>745021</td>\n",
       "      <td>IN</td>\n",
       "      <td>2016-11-22T18:50:27.000Z</td>\n",
       "      <td>907</td>\n",
       "      <td>https://www.youtube.com/channel/UCVIWBQIuIvqFS...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>UCHFA6Nt11kE_8OWIya0SQDQ</td>\n",
       "      <td>PlayToys</td>\n",
       "      <td>yes</td>\n",
       "      <td>40</td>\n",
       "      <td>5788316</td>\n",
       "      <td>774</td>\n",
       "      <td>24538602</td>\n",
       "      <td>GB</td>\n",
       "      <td>2015-02-19T16:24:02.000Z</td>\n",
       "      <td>1549</td>\n",
       "      <td>https://www.youtube.com/channel/UCHFA6Nt11kE_8...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>UCNyJk0eoESO3CxAmrktAB5w</td>\n",
       "      <td>Whitney Avalon</td>\n",
       "      <td>yes</td>\n",
       "      <td>41</td>\n",
       "      <td>1583221</td>\n",
       "      <td>59</td>\n",
       "      <td>5223280</td>\n",
       "      <td>not set</td>\n",
       "      <td>2007-02-15T20:28:49.000Z</td>\n",
       "      <td>4475</td>\n",
       "      <td>https://www.youtube.com/channel/UCNyJk0eoESO3C...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>UCOhtMAg7xh8wv_wUHMgFc-Q</td>\n",
       "      <td>PostMaloneVEVO</td>\n",
       "      <td>yes</td>\n",
       "      <td>42</td>\n",
       "      <td>4104242</td>\n",
       "      <td>16</td>\n",
       "      <td>31779587</td>\n",
       "      <td>US</td>\n",
       "      <td>2014-09-30T19:46:15.000Z</td>\n",
       "      <td>1691</td>\n",
       "      <td>https://www.youtube.com/channel/UCOhtMAg7xh8wv...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>UC0w1KPBe9jf0li30esMtCOw</td>\n",
       "      <td>FrozenxFairytale</td>\n",
       "      <td>yes</td>\n",
       "      <td>43</td>\n",
       "      <td>0</td>\n",
       "      <td>205</td>\n",
       "      <td>720707</td>\n",
       "      <td>CH</td>\n",
       "      <td>2012-06-04T17:34:43.000Z</td>\n",
       "      <td>2539</td>\n",
       "      <td>https://www.youtube.com/channel/UC0w1KPBe9jf0l...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>UC6sAaaqgXLx4vmwiV3t_gvw</td>\n",
       "      <td>World of Android games</td>\n",
       "      <td>yes</td>\n",
       "      <td>44</td>\n",
       "      <td>157251</td>\n",
       "      <td>2664</td>\n",
       "      <td>925266</td>\n",
       "      <td>CZ</td>\n",
       "      <td>2013-12-28T23:21:13.000Z</td>\n",
       "      <td>1967</td>\n",
       "      <td>https://www.youtube.com/channel/UC6sAaaqgXLx4v...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>UC-hw177Ge-0U4obnEotP0AA</td>\n",
       "      <td>zoomzayfound</td>\n",
       "      <td>yes</td>\n",
       "      <td>45</td>\n",
       "      <td>7772</td>\n",
       "      <td>53</td>\n",
       "      <td>90278</td>\n",
       "      <td>not set</td>\n",
       "      <td>2013-07-05T13:12:13.000Z</td>\n",
       "      <td>2143</td>\n",
       "      <td>https://www.youtube.com/channel/UC-hw177Ge-0U4...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>UCQ00zWTLrgRQJUb8MHQg21A</td>\n",
       "      <td>Come Play With Me</td>\n",
       "      <td>yes</td>\n",
       "      <td>46</td>\n",
       "      <td>5499145</td>\n",
       "      <td>223</td>\n",
       "      <td>50430697</td>\n",
       "      <td>not set</td>\n",
       "      <td>2014-01-24T00:17:52.000Z</td>\n",
       "      <td>1941</td>\n",
       "      <td>https://www.youtube.com/channel/UCQ00zWTLrgRQJ...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>UCs2r92fTgKzxdn-vmmWnXIg</td>\n",
       "      <td>108Life Hacks</td>\n",
       "      <td>yes</td>\n",
       "      <td>47</td>\n",
       "      <td>362806</td>\n",
       "      <td>95</td>\n",
       "      <td>198396</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-07-29T02:41:06.000Z</td>\n",
       "      <td>1390</td>\n",
       "      <td>https://www.youtube.com/channel/UCs2r92fTgKzxd...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>UCzNZlJzmjomyl00ewlxHqOA</td>\n",
       "      <td>Cool 3D World</td>\n",
       "      <td>yes</td>\n",
       "      <td>48</td>\n",
       "      <td>422969</td>\n",
       "      <td>59</td>\n",
       "      <td>1097143</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-09-06T23:09:45.000Z</td>\n",
       "      <td>1350</td>\n",
       "      <td>https://www.youtube.com/channel/UCzNZlJzmjomyl...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>UCB5mq0ucfGe9dNCIC0s41QQ</td>\n",
       "      <td>Superdoll adventures</td>\n",
       "      <td>yes</td>\n",
       "      <td>49</td>\n",
       "      <td>490764</td>\n",
       "      <td>130</td>\n",
       "      <td>2827504</td>\n",
       "      <td>not set</td>\n",
       "      <td>2015-02-10T13:38:09.000Z</td>\n",
       "      <td>1558</td>\n",
       "      <td>https://www.youtube.com/channel/UCB5mq0ucfGe9d...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>UCgMjhGGLYCNGNUJfDTaStew</td>\n",
       "      <td>Lolipapi</td>\n",
       "      <td>yes</td>\n",
       "      <td>50</td>\n",
       "      <td>49731</td>\n",
       "      <td>162</td>\n",
       "      <td>175367</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-02-16T15:18:08.000Z</td>\n",
       "      <td>1552</td>\n",
       "      <td>https://www.youtube.com/channel/UCgMjhGGLYCNGN...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>UCU3V6u-NFi4TshSXiaWxQbA</td>\n",
       "      <td>OlafVids</td>\n",
       "      <td>yes</td>\n",
       "      <td>51</td>\n",
       "      <td>1434466</td>\n",
       "      <td>139</td>\n",
       "      <td>11523657</td>\n",
       "      <td>not set</td>\n",
       "      <td>2013-12-06T18:49:25.000Z</td>\n",
       "      <td>1989</td>\n",
       "      <td>https://www.youtube.com/channel/UCU3V6u-NFi4Ts...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>UCdCGlnnqiwUcHJ_lPTZc2rw</td>\n",
       "      <td>Sky Kids Toys</td>\n",
       "      <td>yes</td>\n",
       "      <td>52</td>\n",
       "      <td>1343279</td>\n",
       "      <td>3727</td>\n",
       "      <td>7100107</td>\n",
       "      <td>US</td>\n",
       "      <td>2016-09-09T20:19:05.000Z</td>\n",
       "      <td>981</td>\n",
       "      <td>https://www.youtube.com/channel/UCdCGlnnqiwUcH...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>UCQphRgAhj5UxktrQNP3WF5g</td>\n",
       "      <td>Disney UK</td>\n",
       "      <td>yes</td>\n",
       "      <td>53</td>\n",
       "      <td>4016404</td>\n",
       "      <td>1233</td>\n",
       "      <td>31003244</td>\n",
       "      <td>GB</td>\n",
       "      <td>2007-12-07T10:50:04.000Z</td>\n",
       "      <td>4180</td>\n",
       "      <td>https://www.youtube.com/channel/UCQphRgAhj5Uxk...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52</th>\n",
       "      <td>UCB0d0JLn1WcGYcwwZ87d2LA</td>\n",
       "      <td>Rclbeauty101</td>\n",
       "      <td>yes</td>\n",
       "      <td>54</td>\n",
       "      <td>14104208</td>\n",
       "      <td>411</td>\n",
       "      <td>33114245</td>\n",
       "      <td>not set</td>\n",
       "      <td>2010-12-14T20:57:21.000Z</td>\n",
       "      <td>3077</td>\n",
       "      <td>https://www.youtube.com/channel/UCB0d0JLn1WcGY...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53</th>\n",
       "      <td>UCNMtM6Nf54U7jcvdQqpv8nw</td>\n",
       "      <td>Numberer1</td>\n",
       "      <td>yes</td>\n",
       "      <td>55</td>\n",
       "      <td>179610</td>\n",
       "      <td>186</td>\n",
       "      <td>601592</td>\n",
       "      <td>not set</td>\n",
       "      <td>2007-10-02T00:31:47.000Z</td>\n",
       "      <td>4247</td>\n",
       "      <td>https://www.youtube.com/channel/UCNMtM6Nf54U7j...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54</th>\n",
       "      <td>UCviIx9YkgPfh3SFEEp5jvRQ</td>\n",
       "      <td>Toys In Action</td>\n",
       "      <td>yes</td>\n",
       "      <td>56</td>\n",
       "      <td>192351</td>\n",
       "      <td>833</td>\n",
       "      <td>1694227</td>\n",
       "      <td>not set</td>\n",
       "      <td>2015-07-19T14:45:56.000Z</td>\n",
       "      <td>1399</td>\n",
       "      <td>https://www.youtube.com/channel/UCviIx9YkgPfh3...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>UCjxq9YFOGXx_wPr5Z8jidEA</td>\n",
       "      <td>Working with Lemons</td>\n",
       "      <td>yes</td>\n",
       "      <td>57</td>\n",
       "      <td>3229443</td>\n",
       "      <td>297</td>\n",
       "      <td>16079588</td>\n",
       "      <td>US</td>\n",
       "      <td>2008-10-27T17:04:07.000Z</td>\n",
       "      <td>3855</td>\n",
       "      <td>https://www.youtube.com/channel/UCjxq9YFOGXx_w...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>UCzIdYMdAtTsWucGCZyZvN6w</td>\n",
       "      <td>Toys Unlimited</td>\n",
       "      <td>yes</td>\n",
       "      <td>58</td>\n",
       "      <td>7378141</td>\n",
       "      <td>1744</td>\n",
       "      <td>48824868</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-02-07T18:58:59.000Z</td>\n",
       "      <td>1561</td>\n",
       "      <td>https://www.youtube.com/channel/UCzIdYMdAtTsWu...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>UCzbjbyFF-80PC5kDuY-pqKg</td>\n",
       "      <td>divernic doll adventures</td>\n",
       "      <td>yes</td>\n",
       "      <td>59</td>\n",
       "      <td>159571</td>\n",
       "      <td>189</td>\n",
       "      <td>989134</td>\n",
       "      <td>not set</td>\n",
       "      <td>2015-04-22T08:19:46.000Z</td>\n",
       "      <td>1487</td>\n",
       "      <td>https://www.youtube.com/channel/UCzbjbyFF-80PC...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>UCFsLEAbWprS7OHuGDLlP-DQ</td>\n",
       "      <td>Gerti ToysReview</td>\n",
       "      <td>yes</td>\n",
       "      <td>60</td>\n",
       "      <td>353514</td>\n",
       "      <td>258</td>\n",
       "      <td>1337838</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-09-06T09:31:49.000Z</td>\n",
       "      <td>2080</td>\n",
       "      <td>https://www.youtube.com/channel/UCFsLEAbWprS7O...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>UCqdGW_m8Rim4FeMM29keDEg</td>\n",
       "      <td>FunToys Collector Disney Toys Review</td>\n",
       "      <td>yes</td>\n",
       "      <td>61</td>\n",
       "      <td>11690697</td>\n",
       "      <td>2705</td>\n",
       "      <td>146542904</td>\n",
       "      <td>US</td>\n",
       "      <td>2011-04-13T22:31:49.000Z</td>\n",
       "      <td>2957</td>\n",
       "      <td>https://www.youtube.com/channel/UCqdGW_m8Rim4F...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>UCfuUjajqbwH_10g4Soc1PXg</td>\n",
       "      <td>AnimationSensei</td>\n",
       "      <td>yes</td>\n",
       "      <td>62</td>\n",
       "      <td>145017</td>\n",
       "      <td>188</td>\n",
       "      <td>643325</td>\n",
       "      <td>not set</td>\n",
       "      <td>2010-02-03T16:13:43.000Z</td>\n",
       "      <td>3391</td>\n",
       "      <td>https://www.youtube.com/channel/UCfuUjajqbwH_1...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>UCR41wiSu8b8ThBjs6PZ-pRQ</td>\n",
       "      <td>Maya the Bee</td>\n",
       "      <td>yes</td>\n",
       "      <td>63</td>\n",
       "      <td>55622</td>\n",
       "      <td>299</td>\n",
       "      <td>238733</td>\n",
       "      <td>GB</td>\n",
       "      <td>2015-01-22T10:31:36.000Z</td>\n",
       "      <td>1577</td>\n",
       "      <td>https://www.youtube.com/channel/UCR41wiSu8b8Th...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>UCiBvuoKHWkW62kM0H5OakRA</td>\n",
       "      <td>Baby Dolls &amp; Little Girls</td>\n",
       "      <td>yes</td>\n",
       "      <td>64</td>\n",
       "      <td>892579</td>\n",
       "      <td>1068</td>\n",
       "      <td>5573572</td>\n",
       "      <td>US</td>\n",
       "      <td>2014-10-10T05:49:20.000Z</td>\n",
       "      <td>1681</td>\n",
       "      <td>https://www.youtube.com/channel/UCiBvuoKHWkW62...</td>\n",
       "      <td>NO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>UCwFu9nhHb3ce4ipAof2zgCw</td>\n",
       "      <td>CichyCooLover</td>\n",
       "      <td>yes</td>\n",
       "      <td>65</td>\n",
       "      <td>5387</td>\n",
       "      <td>94</td>\n",
       "      <td>82471</td>\n",
       "      <td>not set</td>\n",
       "      <td>2007-10-15T15:19:39.000Z</td>\n",
       "      <td>4233</td>\n",
       "      <td>https://www.youtube.com/channel/UCwFu9nhHb3ce4...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>UCGydrkfIhUDNCotYQI8TJhA</td>\n",
       "      <td>Cartoon Network UK</td>\n",
       "      <td>yes</td>\n",
       "      <td>66</td>\n",
       "      <td>3426481</td>\n",
       "      <td>3054</td>\n",
       "      <td>24982913</td>\n",
       "      <td>GB</td>\n",
       "      <td>2007-04-12T10:50:04.000Z</td>\n",
       "      <td>4419</td>\n",
       "      <td>https://www.youtube.com/channel/UCGydrkfIhUDNC...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>UC-yjqfeEglTnNih6uBVUgBg</td>\n",
       "      <td>minco2</td>\n",
       "      <td>yes</td>\n",
       "      <td>67</td>\n",
       "      <td>2129</td>\n",
       "      <td>709</td>\n",
       "      <td>47335</td>\n",
       "      <td>not set</td>\n",
       "      <td>2017-03-26T19:26:06.000Z</td>\n",
       "      <td>783</td>\n",
       "      <td>https://www.youtube.com/channel/UC-yjqfeEglTnN...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66</th>\n",
       "      <td>UC2ZLbIQxqsS6T5X1jpeGIgg</td>\n",
       "      <td>Emmett Chatouille</td>\n",
       "      <td>yes</td>\n",
       "      <td>68</td>\n",
       "      <td>860</td>\n",
       "      <td>21</td>\n",
       "      <td>8678</td>\n",
       "      <td>not set</td>\n",
       "      <td>2013-11-25T21:16:18.000Z</td>\n",
       "      <td>2000</td>\n",
       "      <td>https://www.youtube.com/channel/UC2ZLbIQxqsS6T...</td>\n",
       "      <td>YES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>67 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        nodedef>name VARCHAR                            label VARCHAR  \\\n",
       "0   UCir-ay37PYv8-lQOk_dxzFA                                  wai bom   \n",
       "1   UC96vy6lHoIKWSevWJEC47xA                             Mathias Jost   \n",
       "2   UC0xH3QQdLAIu7srkkbTG8CQ                              Eve My Tube   \n",
       "3   UCY9eEaH19ulZmdusctU1ejA                                  Glamour   \n",
       "4   UCD4MbghmkGS3aF5ymsc8_bw                              Post Hostel   \n",
       "5   UCp_CBY2rEWs_CPI3HaC6tbg                             Alex Ramirès   \n",
       "6   UCN6rjYMkYv5eUAHbFwDbQmg                            ClubEngendros   \n",
       "7   UCRBhx25KHV4wUhKsO29BNgw                              Way to Hero   \n",
       "8   UCYDKUHScAVoI_UTVuA7ThcQ                         Crafty Panda DIY   \n",
       "9   UCfaZw8XH_zmAVkBst_MPD6w                                 CKN Toys   \n",
       "10  UCE3cM6f-H0mZlnvQafTqCyQ                          The Super Zeros   \n",
       "11  UCEvLUnQiH-i1XUNikh0Zm4g                       The Sean Ward Show   \n",
       "12  UCfBZ5Bgo2F2wRiy68XCWpow         The 86th Floor: Cosplay and Cons   \n",
       "13  UCRqzi1fLE5az1Br6sbSdGuQ                                    Klara   \n",
       "14  UCePgEtPIBVVKELuW7NkadFA                             123 Go Girly   \n",
       "15  UC5SYKAP880X9TDtpg3TZCIA                             Magnetic Man   \n",
       "16  UCjeRo0NuOQ8SZT_MBGV4NrQ                Super Hero Videos lozaus2   \n",
       "17  UCg12HA5UiFKAupPA6Lbd-eA                       GameArt Production   \n",
       "18  UC1J5R-2zsrrICAYmkt5g_6Q  Patty Shukla Kids TV - Children s songs   \n",
       "19  UChCBHudFoBnsF0O1flPXqiQ                                 Nick Pro   \n",
       "20  UCOEYO2x_UJLXRUyoYQJ178A                               BuzzPranks   \n",
       "21  UCKqx9r4mrFglauNBJc1L_eg                     [토이푸딩] ToyPudding TV   \n",
       "22  UCNOYoV6u5JWn74sIVe3FPaA                    SuperHeroes Real Life   \n",
       "23  UC41kSpAQe1ABLUQbGP_WBVA                          Life as a Mommy   \n",
       "24  UCPgtKvRfZ74M0KqKcA39LNQ                     MVP Entertainment ID   \n",
       "25  UC1D26ByrbZVwuMgjVWmsOuw                              Frame Order   \n",
       "26  UCZL_mZiRnR42hg1IjV6lQ8w                                 ;} Swxmz   \n",
       "27  UCwRE527CMIU5yTxYeccBGwA                Australian Reborn Nursery   \n",
       "28  UCtCU59RozAjBnamOFF66IvA                       Crafty Panda Girly   \n",
       "29  UCgmgq_ZL3R4UN1L4IUJ7hWQ                             AmvAnimation   \n",
       "..                       ...                                      ...   \n",
       "37  UCVIWBQIuIvqFSIVztsuV2Aw                     Bhaity Music Company   \n",
       "38  UCHFA6Nt11kE_8OWIya0SQDQ                                 PlayToys   \n",
       "39  UCNyJk0eoESO3CxAmrktAB5w                           Whitney Avalon   \n",
       "40  UCOhtMAg7xh8wv_wUHMgFc-Q                           PostMaloneVEVO   \n",
       "41  UC0w1KPBe9jf0li30esMtCOw                         FrozenxFairytale   \n",
       "42  UC6sAaaqgXLx4vmwiV3t_gvw                   World of Android games   \n",
       "43  UC-hw177Ge-0U4obnEotP0AA                             zoomzayfound   \n",
       "44  UCQ00zWTLrgRQJUb8MHQg21A                        Come Play With Me   \n",
       "45  UCs2r92fTgKzxdn-vmmWnXIg                            108Life Hacks   \n",
       "46  UCzNZlJzmjomyl00ewlxHqOA                            Cool 3D World   \n",
       "47  UCB5mq0ucfGe9dNCIC0s41QQ                     Superdoll adventures   \n",
       "48  UCgMjhGGLYCNGNUJfDTaStew                                 Lolipapi   \n",
       "49  UCU3V6u-NFi4TshSXiaWxQbA                                 OlafVids   \n",
       "50  UCdCGlnnqiwUcHJ_lPTZc2rw                            Sky Kids Toys   \n",
       "51  UCQphRgAhj5UxktrQNP3WF5g                                Disney UK   \n",
       "52  UCB0d0JLn1WcGYcwwZ87d2LA                             Rclbeauty101   \n",
       "53  UCNMtM6Nf54U7jcvdQqpv8nw                                Numberer1   \n",
       "54  UCviIx9YkgPfh3SFEEp5jvRQ                           Toys In Action   \n",
       "55  UCjxq9YFOGXx_wPr5Z8jidEA                      Working with Lemons   \n",
       "56  UCzIdYMdAtTsWucGCZyZvN6w                           Toys Unlimited   \n",
       "57  UCzbjbyFF-80PC5kDuY-pqKg                 divernic doll adventures   \n",
       "58  UCFsLEAbWprS7OHuGDLlP-DQ                         Gerti ToysReview   \n",
       "59  UCqdGW_m8Rim4FeMM29keDEg     FunToys Collector Disney Toys Review   \n",
       "60  UCfuUjajqbwH_10g4Soc1PXg                          AnimationSensei   \n",
       "61  UCR41wiSu8b8ThBjs6PZ-pRQ                             Maya the Bee   \n",
       "62  UCiBvuoKHWkW62kM0H5OakRA                Baby Dolls & Little Girls   \n",
       "63  UCwFu9nhHb3ce4ipAof2zgCw                            CichyCooLover   \n",
       "64  UCGydrkfIhUDNCotYQI8TJhA                       Cartoon Network UK   \n",
       "65  UC-yjqfeEglTnNih6uBVUgBg                                   minco2   \n",
       "66  UC2ZLbIQxqsS6T5X1jpeGIgg                        Emmett Chatouille   \n",
       "\n",
       "   isSeed VARCHAR  seedRank INT  subscriberCount INT  videoCount INT  \\\n",
       "0             yes             1                 5346               1   \n",
       "1             yes             2                 4311              26   \n",
       "2             yes             3              1673550            1036   \n",
       "3             yes             4              2109252            1654   \n",
       "4             yes             5                 8652               5   \n",
       "5             yes             6               324934              62   \n",
       "6             yes             7                19446              40   \n",
       "7             yes             8                 3700              62   \n",
       "8             yes             9                    0              65   \n",
       "9             yes            10             12488147             529   \n",
       "10            yes            11               729035             158   \n",
       "11            yes            12              3471486             429   \n",
       "12            yes            13               572120             111   \n",
       "13            yes            14                 1587               5   \n",
       "14            yes            15                    0             188   \n",
       "15            yes            16               219909              34   \n",
       "16            yes            17               370933               9   \n",
       "17            yes            18               126556             203   \n",
       "18            yes            19               762162             274   \n",
       "19            yes            20              1872440             354   \n",
       "20            yes            21              2998044            1309   \n",
       "21            yes            22             23828610            2561   \n",
       "22            yes            23                    0               3   \n",
       "23            yes            24               378169             264   \n",
       "24            yes            25              1226550            3379   \n",
       "25            yes            26              1686171             172   \n",
       "26            yes            27                  387              30   \n",
       "27            yes            28                 7980             398   \n",
       "28            yes            29                    0              44   \n",
       "29            yes            30                29475             265   \n",
       "..            ...           ...                  ...             ...   \n",
       "37            yes            39               251101              86   \n",
       "38            yes            40              5788316             774   \n",
       "39            yes            41              1583221              59   \n",
       "40            yes            42              4104242              16   \n",
       "41            yes            43                    0             205   \n",
       "42            yes            44               157251            2664   \n",
       "43            yes            45                 7772              53   \n",
       "44            yes            46              5499145             223   \n",
       "45            yes            47               362806              95   \n",
       "46            yes            48               422969              59   \n",
       "47            yes            49               490764             130   \n",
       "48            yes            50                49731             162   \n",
       "49            yes            51              1434466             139   \n",
       "50            yes            52              1343279            3727   \n",
       "51            yes            53              4016404            1233   \n",
       "52            yes            54             14104208             411   \n",
       "53            yes            55               179610             186   \n",
       "54            yes            56               192351             833   \n",
       "55            yes            57              3229443             297   \n",
       "56            yes            58              7378141            1744   \n",
       "57            yes            59               159571             189   \n",
       "58            yes            60               353514             258   \n",
       "59            yes            61             11690697            2705   \n",
       "60            yes            62               145017             188   \n",
       "61            yes            63                55622             299   \n",
       "62            yes            64               892579            1068   \n",
       "63            yes            65                 5387              94   \n",
       "64            yes            66              3426481            3054   \n",
       "65            yes            67                 2129             709   \n",
       "66            yes            68                  860              21   \n",
       "\n",
       "    viewCount(100s) INT country VARCHAR       publishedAt VARCHAR  \\\n",
       "0                 24099         not set  2017-08-22T10:42:40.000Z   \n",
       "1                 14301         not set  2016-12-25T14:30:29.000Z   \n",
       "2               3306465              TH  2016-01-29T15:24:19.000Z   \n",
       "3               6585187              US  2007-04-03T15:03:38.000Z   \n",
       "4                 78937         not set  2015-02-19T09:33:52.000Z   \n",
       "5                664676         not set  2011-07-19T10:28:08.000Z   \n",
       "6                196866         not set  2008-10-01T14:29:38.000Z   \n",
       "7                  6652              BR  2015-10-27T19:57:23.000Z   \n",
       "8                256182              US  2018-04-17T11:07:20.000Z   \n",
       "9             109306937              AU  2015-03-28T04:11:34.000Z   \n",
       "10              4604463              US  2006-02-06T22:53:06.000Z   \n",
       "11             17770940         not set  2010-05-03T22:27:07.000Z   \n",
       "12              2152268              GB  2014-05-27T08:49:57.000Z   \n",
       "13                10893              DE  2018-10-19T10:12:02.000Z   \n",
       "14               212086              US  2017-01-22T10:58:28.000Z   \n",
       "15               453258              US  2018-08-28T17:25:45.000Z   \n",
       "16               907698              AU  2016-03-12T01:43:49.000Z   \n",
       "17               544220         not set  2011-08-28T06:45:25.000Z   \n",
       "18              5269882              US  2006-12-24T00:15:55.000Z   \n",
       "19              4187961              CA  2010-09-16T18:16:16.000Z   \n",
       "20             11649285              US  2012-09-08T05:47:04.000Z   \n",
       "21            139777459              US  2014-05-13T14:31:31.000Z   \n",
       "22                  394         not set  2015-06-03T04:12:24.000Z   \n",
       "23               717885              US  2013-04-10T16:50:12.000Z   \n",
       "24              4538086              ID  2013-03-21T09:43:36.000Z   \n",
       "25              2436480              NL  2013-05-15T14:45:02.000Z   \n",
       "26                  633              GB  2014-10-22T15:03:41.000Z   \n",
       "27                38242         not set  2017-09-04T04:32:09.000Z   \n",
       "28               189001              US  2016-09-25T14:52:13.000Z   \n",
       "29               128200         not set  2014-06-23T05:39:54.000Z   \n",
       "..                  ...             ...                       ...   \n",
       "37               745021              IN  2016-11-22T18:50:27.000Z   \n",
       "38             24538602              GB  2015-02-19T16:24:02.000Z   \n",
       "39              5223280         not set  2007-02-15T20:28:49.000Z   \n",
       "40             31779587              US  2014-09-30T19:46:15.000Z   \n",
       "41               720707              CH  2012-06-04T17:34:43.000Z   \n",
       "42               925266              CZ  2013-12-28T23:21:13.000Z   \n",
       "43                90278         not set  2013-07-05T13:12:13.000Z   \n",
       "44             50430697         not set  2014-01-24T00:17:52.000Z   \n",
       "45               198396              US  2015-07-29T02:41:06.000Z   \n",
       "46              1097143              US  2015-09-06T23:09:45.000Z   \n",
       "47              2827504         not set  2015-02-10T13:38:09.000Z   \n",
       "48               175367              US  2015-02-16T15:18:08.000Z   \n",
       "49             11523657         not set  2013-12-06T18:49:25.000Z   \n",
       "50              7100107              US  2016-09-09T20:19:05.000Z   \n",
       "51             31003244              GB  2007-12-07T10:50:04.000Z   \n",
       "52             33114245         not set  2010-12-14T20:57:21.000Z   \n",
       "53               601592         not set  2007-10-02T00:31:47.000Z   \n",
       "54              1694227         not set  2015-07-19T14:45:56.000Z   \n",
       "55             16079588              US  2008-10-27T17:04:07.000Z   \n",
       "56             48824868              US  2015-02-07T18:58:59.000Z   \n",
       "57               989134         not set  2015-04-22T08:19:46.000Z   \n",
       "58              1337838              US  2013-09-06T09:31:49.000Z   \n",
       "59            146542904              US  2011-04-13T22:31:49.000Z   \n",
       "60               643325         not set  2010-02-03T16:13:43.000Z   \n",
       "61               238733              GB  2015-01-22T10:31:36.000Z   \n",
       "62              5573572              US  2014-10-10T05:49:20.000Z   \n",
       "63                82471         not set  2007-10-15T15:19:39.000Z   \n",
       "64             24982913              GB  2007-04-12T10:50:04.000Z   \n",
       "65                47335         not set  2017-03-26T19:26:06.000Z   \n",
       "66                 8678         not set  2013-11-25T21:16:18.000Z   \n",
       "\n",
       "    daysactive INT                                        url VARCHAR  \\\n",
       "0              634  https://www.youtube.com/channel/UCir-ay37PYv8-...   \n",
       "1              874  https://www.youtube.com/channel/UC96vy6lHoIKWS...   \n",
       "2             1205  https://www.youtube.com/channel/UC0xH3QQdLAIu7...   \n",
       "3             4428  https://www.youtube.com/channel/UCY9eEaH19ulZm...   \n",
       "4             1549  https://www.youtube.com/channel/UCD4MbghmkGS3a...   \n",
       "5             2860  https://www.youtube.com/channel/UCp_CBY2rEWs_C...   \n",
       "6             3881  https://www.youtube.com/channel/UCN6rjYMkYv5eU...   \n",
       "7             1299  https://www.youtube.com/channel/UCRBhx25KHV4wU...   \n",
       "8              396  https://www.youtube.com/channel/UCYDKUHScAVoI_...   \n",
       "9             1513  https://www.youtube.com/channel/UCfaZw8XH_zmAV...   \n",
       "10            4849  https://www.youtube.com/channel/UCE3cM6f-H0mZl...   \n",
       "11            3302  https://www.youtube.com/channel/UCEvLUnQiH-i1X...   \n",
       "12            1817  https://www.youtube.com/channel/UCfBZ5Bgo2F2wR...   \n",
       "13             211  https://www.youtube.com/channel/UCRqzi1fLE5az1...   \n",
       "14             846  https://www.youtube.com/channel/UCePgEtPIBVVKE...   \n",
       "15             263  https://www.youtube.com/channel/UC5SYKAP880X9T...   \n",
       "16            1163  https://www.youtube.com/channel/UCjeRo0NuOQ8SZ...   \n",
       "17            2820  https://www.youtube.com/channel/UCg12HA5UiFKAu...   \n",
       "18            4529  https://www.youtube.com/channel/UC1J5R-2zsrrIC...   \n",
       "19            3166  https://www.youtube.com/channel/UChCBHudFoBnsF...   \n",
       "20            2443  https://www.youtube.com/channel/UCOEYO2x_UJLXR...   \n",
       "21            1831  https://www.youtube.com/channel/UCKqx9r4mrFgla...   \n",
       "22            1446  https://www.youtube.com/channel/UCNOYoV6u5JWn7...   \n",
       "23            2229  https://www.youtube.com/channel/UC41kSpAQe1ABL...   \n",
       "24            2249  https://www.youtube.com/channel/UCPgtKvRfZ74M0...   \n",
       "25            2194  https://www.youtube.com/channel/UC1D26ByrbZVwu...   \n",
       "26            1669  https://www.youtube.com/channel/UCZL_mZiRnR42h...   \n",
       "27             622  https://www.youtube.com/channel/UCwRE527CMIU5y...   \n",
       "28             965  https://www.youtube.com/channel/UCtCU59RozAjBn...   \n",
       "29            1790  https://www.youtube.com/channel/UCgmgq_ZL3R4UN...   \n",
       "..             ...                                                ...   \n",
       "37             907  https://www.youtube.com/channel/UCVIWBQIuIvqFS...   \n",
       "38            1549  https://www.youtube.com/channel/UCHFA6Nt11kE_8...   \n",
       "39            4475  https://www.youtube.com/channel/UCNyJk0eoESO3C...   \n",
       "40            1691  https://www.youtube.com/channel/UCOhtMAg7xh8wv...   \n",
       "41            2539  https://www.youtube.com/channel/UC0w1KPBe9jf0l...   \n",
       "42            1967  https://www.youtube.com/channel/UC6sAaaqgXLx4v...   \n",
       "43            2143  https://www.youtube.com/channel/UC-hw177Ge-0U4...   \n",
       "44            1941  https://www.youtube.com/channel/UCQ00zWTLrgRQJ...   \n",
       "45            1390  https://www.youtube.com/channel/UCs2r92fTgKzxd...   \n",
       "46            1350  https://www.youtube.com/channel/UCzNZlJzmjomyl...   \n",
       "47            1558  https://www.youtube.com/channel/UCB5mq0ucfGe9d...   \n",
       "48            1552  https://www.youtube.com/channel/UCgMjhGGLYCNGN...   \n",
       "49            1989  https://www.youtube.com/channel/UCU3V6u-NFi4Ts...   \n",
       "50             981  https://www.youtube.com/channel/UCdCGlnnqiwUcH...   \n",
       "51            4180  https://www.youtube.com/channel/UCQphRgAhj5Uxk...   \n",
       "52            3077  https://www.youtube.com/channel/UCB0d0JLn1WcGY...   \n",
       "53            4247  https://www.youtube.com/channel/UCNMtM6Nf54U7j...   \n",
       "54            1399  https://www.youtube.com/channel/UCviIx9YkgPfh3...   \n",
       "55            3855  https://www.youtube.com/channel/UCjxq9YFOGXx_w...   \n",
       "56            1561  https://www.youtube.com/channel/UCzIdYMdAtTsWu...   \n",
       "57            1487  https://www.youtube.com/channel/UCzbjbyFF-80PC...   \n",
       "58            2080  https://www.youtube.com/channel/UCFsLEAbWprS7O...   \n",
       "59            2957  https://www.youtube.com/channel/UCqdGW_m8Rim4F...   \n",
       "60            3391  https://www.youtube.com/channel/UCfuUjajqbwH_1...   \n",
       "61            1577  https://www.youtube.com/channel/UCR41wiSu8b8Th...   \n",
       "62            1681  https://www.youtube.com/channel/UCiBvuoKHWkW62...   \n",
       "63            4233  https://www.youtube.com/channel/UCwFu9nhHb3ce4...   \n",
       "64            4419  https://www.youtube.com/channel/UCGydrkfIhUDNC...   \n",
       "65             783  https://www.youtube.com/channel/UC-yjqfeEglTnN...   \n",
       "66            2000  https://www.youtube.com/channel/UC2ZLbIQxqsS6T...   \n",
       "\n",
       "   is_inappropriate VARCHAR  \n",
       "0                       YES  \n",
       "1                       YES  \n",
       "2                       YES  \n",
       "3                        NO  \n",
       "4                        NO  \n",
       "5                        NO  \n",
       "6                        NO  \n",
       "7                       YES  \n",
       "8                       YES  \n",
       "9                        NO  \n",
       "10                      YES  \n",
       "11                       NO  \n",
       "12                       NO  \n",
       "13                       NO  \n",
       "14                      YES  \n",
       "15                       NO  \n",
       "16                      YES  \n",
       "17                      YES  \n",
       "18                       NO  \n",
       "19                      YES  \n",
       "20                      YES  \n",
       "21                       NO  \n",
       "22                       NO  \n",
       "23                       NO  \n",
       "24                       NO  \n",
       "25                       NO  \n",
       "26                       NO  \n",
       "27                       NO  \n",
       "28                       NO  \n",
       "29                      YES  \n",
       "..                      ...  \n",
       "37                       NO  \n",
       "38                       NO  \n",
       "39                       NO  \n",
       "40                       NO  \n",
       "41                       NO  \n",
       "42                       NO  \n",
       "43                      YES  \n",
       "44                       NO  \n",
       "45                      YES  \n",
       "46                      YES  \n",
       "47                      YES  \n",
       "48                       NO  \n",
       "49                       NO  \n",
       "50                      YES  \n",
       "51                       NO  \n",
       "52                       NO  \n",
       "53                      YES  \n",
       "54                       NO  \n",
       "55                       NO  \n",
       "56                      YES  \n",
       "57                       NO  \n",
       "58                       NO  \n",
       "59                       NO  \n",
       "60                      YES  \n",
       "61                       NO  \n",
       "62                       NO  \n",
       "63                      YES  \n",
       "64                      YES  \n",
       "65                      YES  \n",
       "66                      YES  \n",
       "\n",
       "[67 rows x 12 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def load_reassemble_annotated_channel_data(metatdata_filename, rels_filename, channel_video_annotations):\n",
    "    # load annotated metadata and relations and recreate gdf file for exporting to gephi \n",
    "    annotated_metadata = pd.read_csv(metatdata_filename)\n",
    "    annotated_rels = pd.read_csv(rels_filename)\n",
    "    # drop redundant row index column\n",
    "    annotated_metadata = annotated_metadata.drop(\"Unnamed: 0\", axis=1)\n",
    "    annotated_rels = annotated_rels.drop(\"Unnamed: 0\", axis=1)\n",
    "    \n",
    "    # merge channel video annotations to channels\n",
    "    print(annotated_metadata.columns)\n",
    "    annotated_metadata = annotated_metadata.merge(channel_video_annotations, left_on='nodeid', right_on='channelId')\n",
    "    annotated_metadata.rename(columns={\n",
    "        'nodeid': 'nodedef>name VARCHAR',\n",
    "        'label': 'label VARCHAR',\n",
    "        'subscriberCount': 'subscriberCount INT',\n",
    "        'label_clean': 'labelClean VARCHAR',\n",
    "        'title_language': 'titleLanguage VARCHAR',\n",
    "        'url': 'url VARCHAR',\n",
    "        'seed_channel': 'seedChannel BOOLEAN'\n",
    "\n",
    "    },\n",
    "        inplace=True)\n",
    "    \n",
    "    annotated_rels.rename(columns={\n",
    "        'node1':'edgedef>node1 VARCHAR',\n",
    "        'node2': 'node2 VARCHAR',\n",
    "        'directed': 'directed BOOLEAN'\n",
    "    }, inplace=True)\n",
    "\n",
    "\n",
    "    # write to .gdf file\n",
    "    gdf_metadata_header = ','.join((str(x) for x in annotated_metadata.columns))\n",
    "    gdf_rels_header = ','.join((str(x) for x in annotated_rels.columns))\n",
    "    annotated_rels = annotated_rels.dropna()\n",
    "    with open('annotated_channels_gdf.gdf', 'w') as f:\n",
    "        f.write(gdf_metadata_header + '\\n')\n",
    "        for row in annotated_metadata.values:\n",
    "            f.write(df_row_to_csv_row(row) + '\\n')\n",
    "        f.write(gdf_rels_header + '\\n')\n",
    "        for row in annotated_rels.values:\n",
    "            f.write(','.join((str(x) for x in row))+ '\\n')\n",
    "    return annotated_metadata, annotated_rels\n",
    "\n",
    "annotated_channels_metadata, annotated_channels_rels = load_reassemble_annotated_channel_data('bad_channels_2019_05_18_metadata.csv',\n",
    "                                                                            'bad_channels_2019_05_18_relations.csv',\n",
    "                                                                           channels_videos_annotated)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-14T13:49:19.585538Z",
     "start_time": "2019-05-14T13:49:19.562240Z"
    }
   },
   "outputs": [],
   "source": [
    "def parse_gdf_channel(file_name):\n",
    "    with open(file_name) as f:\n",
    "        rows = [line.strip().split(',') for line in f.readlines()]\n",
    "        # print(rows)\n",
    "        metadata = [row for row in rows if len(row) > 3]\n",
    "        graph_data = [row for row in rows if len(row) == 3]\n",
    "        return pd.DataFrame(metadata[1:], columns=metadata[0]), pd.DataFrame(\n",
    "            graph_data[1:], columns=graph_data[0])\n",
    "\n",
    "def parse_gdf_video(file_name):\n",
    "    with open(file_name, encoding='utf-8') as f:\n",
    "        rows = [line.strip().split(',') for line in f.readlines()]\n",
    "        # print(rows)\n",
    "        metadata = [row[:14] for row in rows if len(row) > 3]\n",
    "        graph_data = [row for row in rows if len(row) == 3]\n",
    "        \n",
    "        return pd.DataFrame(metadata[1:], columns=metadata[0]), pd.DataFrame(\n",
    "            graph_data[1:], columns=graph_data[0])\n",
    "    \n",
    "\n",
    "def all_caps_to_proper(string):\n",
    "    out_string = ''\n",
    "    for token in string.split(' '):\n",
    "        if token.isupper():\n",
    "            out_string += ' ' + token.capitalize()\n",
    "        else:\n",
    "            out_string += ' ' + token\n",
    "    return out_string.strip()\n",
    "            "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-05-14T14:29:38.796249Z",
     "start_time": "2019-05-14T14:29:35.059913Z"
    }
   },
   "outputs": [],
   "source": [
    "def join_update_clean_dfs(df_graph, df_metadata, title, replace_node_ids=True):\n",
    "    df_metadata.rename(columns={\n",
    "        'nodedef>name VARCHAR': 'nodeid',\n",
    "        'label VARCHAR': 'label',\n",
    "        'subscriberCount INT': 'subscriberCount'\n",
    "    },\n",
    "        inplace=True)\n",
    "    df_graph.rename(columns={\n",
    "        'edgedef>node1 VARCHAR': 'nodeid_1',\n",
    "        'node2 VARCHAR': 'nodeid_2',\n",
    "        'directed BOOLEAN': 'directed'\n",
    "    },\n",
    "        inplace=True)\n",
    "    if replace_node_ids is True:\n",
    "        df_graph = pd.merge(df_graph,\n",
    "                            df_metadata[['label', 'nodeid']],\n",
    "                            left_on='nodeid_1',\n",
    "                            right_on='nodeid',\n",
    "                            how='right')[['label', 'nodeid_2', 'directed'\n",
    "                                          ]].rename(columns={'label': 'node1'})\n",
    "\n",
    "        df_graph = pd.merge(df_graph,\n",
    "                            df_metadata[['label', 'nodeid']],\n",
    "                            left_on='nodeid_2',\n",
    "                            right_on='nodeid',\n",
    "                            how='right')[['node1', 'label', 'directed'\n",
    "                                          ]].rename(columns={'label': 'node2'})\n",
    "    return df_metadata, df_graph\n",
    "\n",
    "\n",
    "def load_video_data(filename):\n",
    "    df_video_metadata, df_video_rel = parse_gdf_video(filename + '.gdf')        \n",
    "    # clean up titles for language classification\n",
    "    df_video_metadata['label_clean'] = df_video_metadata['label VARCHAR'].apply(all_caps_to_proper)\n",
    "    # detect video title language \n",
    "    df_video_metadata['title_language'] = df_video_metadata['label_clean'].apply(detect)\n",
    "    # clean column names and update node ids\n",
    "    df_video_metadata, df_video_graph = join_update_clean_dfs(df_video_rel, df_video_metadata, filename)\n",
    "    df_video_metadata['url'] = 'https://www.youtube.com/watch?v=' + df_video_metadata['nodeid']\n",
    "    # get channel ids for seed videos\n",
    "    seed_channel_ids = list(df_video_metadata[df_video_metadata['isSeed VARCHAR'] == 'yes']['channelId VARCHAR'])\n",
    "    # label whether or not all videos are in the seed channel\n",
    "    df_video_metadata['seed_channel'] = df_video_metadata['channelId VARCHAR'].apply(lambda x : True if x in seed_channel_ids else False)\n",
    "    df_video_metadata.to_csv(filename + '_metadata.csv')\n",
    "    df_video_graph.to_csv(filename + '_relations.csv')\n",
    "    return df_video_metadata, df_video_graph\n",
    "    \n",
    "\n",
    "def load_channel_data(filename):\n",
    "    df_metadata, df_rel = parse_gdf_channel(filename + '.gdf')        \n",
    "    # clean column names and update node ids\n",
    "    df_metadata, df_graph = join_update_clean_dfs(df_rel, df_metadata, filename)\n",
    "    df_metadata['url'] = 'https://www.youtube.com/channel/' + df_metadata['nodeid']\n",
    "    df_metadata.to_csv(filename + '_metadata.csv')\n",
    "    df_graph.to_csv(filename + '_relations.csv')\n",
    "    return df_metadata, df_graph\n",
    "    \n",
    "\n",
    "    \n",
    "df_video_metadata, df_video_graph = load_video_data('masha_and_shark_2019_05_14')"
   ]
  }
 ],
 "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.6.8"
  },
  "latex_envs": {
   "LaTeX_envs_menu_present": true,
   "autoclose": false,
   "autocomplete": true,
   "bibliofile": "biblio.bib",
   "cite_by": "apalike",
   "current_citInitial": 1,
   "eqLabelWithNumbers": true,
   "eqNumInitial": 1,
   "hotkeys": {
    "equation": "Ctrl-E",
    "itemize": "Ctrl-I"
   },
   "labels_anchors": false,
   "latex_user_defs": false,
   "report_style_numbering": false,
   "user_envs_cfg": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}