Upload_Raw_Summary_To_Database.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "import psycopg2\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# set up database connection\n",
    "user = 'lindsay'          \n",
    "host = 'localhost'\n",
    "dbname = 'podcast'\n",
    "con = None\n",
    "con = psycopg2.connect(database = dbname, user = user)\n",
    "cursor = con.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# load data\n",
    "scrapeDf = pd.read_pickle('pkl/scraped_podcasts_COMPLETE.pkl')\n",
    "apiDf = pd.read_pickle('pkl/itunes_podcasts_v3_COMPLETE.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# clean column names because SQL doesn't like camelCase\n",
    "def convert(name):\n",
    "    \"\"\"Converts camelCase to camel_case.\"\"\"\n",
    "    s1 = re.sub('(.)([A-Z][a-z]+)', r'\\1_\\2', name)\n",
    "    return re.sub('([a-z0-9])([A-Z])', r'\\1_\\2', s1).lower()\n",
    "\n",
    "scrapeDf.columns = [convert(x) for x in scrapeDf.columns]\n",
    "apiDf.columns = [convert(x) for x in apiDf.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# join df\n",
    "podcastDf = pd.merge(apiDf, scrapeDf, how = 'inner', on = 'collection_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# convert to int\n",
    "podcastDf['collection_id'] = [int(x) for x in podcastDf['collection_id']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# query db for urls\n",
    "query = 'SELECT artwork_url30, view_url, name, id FROM podcast;'\n",
    "query_results = pd.read_sql_query(query, con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>artwork_url30</th>\n",
       "      <th>view_url</th>\n",
       "      <th>name</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>http://is5.mzstatic.com/image/thumb/Music2/v4/...</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/your-voice...</td>\n",
       "      <td>Your Voice For Hope</td>\n",
       "      <td>18489</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>http://is5.mzstatic.com/image/thumb/Music/v4/1...</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/a.m-best-r...</td>\n",
       "      <td>A.M Best Radio Podcast</td>\n",
       "      <td>12350</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>http://is2.mzstatic.com/image/thumb/Music/v4/2...</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/a-move-rad...</td>\n",
       "      <td>A-move Radio</td>\n",
       "      <td>12351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>http://is5.mzstatic.com/image/thumb/Music3/v4/...</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/new-beginn...</td>\n",
       "      <td>A New Beginning with Greg Laurie</td>\n",
       "      <td>12352</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>http://is5.mzstatic.com/image/thumb/Music/v4/1...</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/a.t.tipsca...</td>\n",
       "      <td>A.T.TIPScast</td>\n",
       "      <td>12355</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                       artwork_url30  \\\n",
       "0  http://is5.mzstatic.com/image/thumb/Music2/v4/...   \n",
       "1  http://is5.mzstatic.com/image/thumb/Music/v4/1...   \n",
       "2  http://is2.mzstatic.com/image/thumb/Music/v4/2...   \n",
       "3  http://is5.mzstatic.com/image/thumb/Music3/v4/...   \n",
       "4  http://is5.mzstatic.com/image/thumb/Music/v4/1...   \n",
       "\n",
       "                                            view_url  \\\n",
       "0  https://itunes.apple.com/us/podcast/your-voice...   \n",
       "1  https://itunes.apple.com/us/podcast/a.m-best-r...   \n",
       "2  https://itunes.apple.com/us/podcast/a-move-rad...   \n",
       "3  https://itunes.apple.com/us/podcast/new-beginn...   \n",
       "4  https://itunes.apple.com/us/podcast/a.t.tipsca...   \n",
       "\n",
       "                               name     id  \n",
       "0               Your Voice For Hope  18489  \n",
       "1            A.M Best Radio Podcast  12350  \n",
       "2                      A-move Radio  12351  \n",
       "3  A New Beginning with Greg Laurie  12352  \n",
       "4                      A.T.TIPScast  12355  "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_results.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>artwork_url30</th>\n",
       "      <th>view_url</th>\n",
       "      <th>name</th>\n",
       "      <th>id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5803</th>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music2/v4/...</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/no-title/i...</td>\n",
       "      <td>No Title</td>\n",
       "      <td>16392</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          artwork_url30  \\\n",
       "5803  http://is4.mzstatic.com/image/thumb/Music2/v4/...   \n",
       "\n",
       "                                               view_url      name     id  \n",
       "5803  https://itunes.apple.com/us/podcast/no-title/i...  No Title  16392  "
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_results[query_results['id']==16392]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "podcast_db = pd.merge(podcastDf, query_results, how = 'inner', left_on=['collection_view_url', 'collection_censored_name'], right_on=['view_url', 'name'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6284, 39)"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "podcastDf.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5804, 4)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_results.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5494, 43)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "podcast_db.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "' UPDATE podcast SET raw_summary = (%s), collection_id = (%s) WHERE id = (%s); '"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"\"\"\n",
    "UPDATE podcast\n",
    "SET raw_summary = (%s),\n",
    "collection_id = (%s)\n",
    "WHERE id = (%s);\n",
    "\"\"\"\n",
    "query = query.replace('\\n', ' ')\n",
    "query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": []
    }
   ],
   "source": [
    "for ind, row in tqdm(podcast_db.iterrows(), total=podcast_db.shape[0]):\n",
    "    data = ((row['podcast_summary'], ), (row['collection_id'], ), (row['id'], ))\n",
    "    cursor.execute(query, data)\n",
    "    \n",
    "con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "artist_id                    object\n",
       "artist_name                  object\n",
       "artist_view_url              object\n",
       "artwork_url100               object\n",
       "artwork_url30_x              object\n",
       "artwork_url60                object\n",
       "artwork_url600               object\n",
       "collection_censored_name     object\n",
       "collection_explicitness      object\n",
       "collection_hd_price         float64\n",
       "collection_id                 int64\n",
       "collection_name              object\n",
       "collection_price            float64\n",
       "collection_view_url          object\n",
       "content_advisory_rating      object\n",
       "country                      object\n",
       "currency                     object\n",
       "feed_url                     object\n",
       "genre_ids                    object\n",
       "genres                       object\n",
       "kind                         object\n",
       "primary_genre_name           object\n",
       "radio_station_url            object\n",
       "release_date                 object\n",
       "track_censored_name          object\n",
       "track_count                 float64\n",
       "track_explicitness           object\n",
       "track_hd_price              float64\n",
       "track_hd_rental_price       float64\n",
       "track_id                    float64\n",
       "track_name                   object\n",
       "track_price                 float64\n",
       "track_rental_price          float64\n",
       "track_view_url               object\n",
       "wrapper_type                 object\n",
       "also_subscribed              object\n",
       "episode_descriptions         object\n",
       "episode_names                object\n",
       "podcast_summary              object\n",
       "artwork_url30_y              object\n",
       "view_url                     object\n",
       "name                         object\n",
       "id                            int64\n",
       "dtype: object"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "podcast_db.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test = podcast_db['podcast_summary']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "unicode"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(test[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'A quasi-weekly discussion of the plots, themes and highlights of the HBO series \"Game of Thrones.\"  Your hosts Dave Chen (The /Filmcast and The Tobolowsky Files) and Joanna Robinson (contributor for VanityFair.com) will dish up both praise and criticism and will try not to sound too pompous if the book was better. Questions, comments, oaths of fealty?  Email acastofkings@gmail.com'"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(test[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"A quasi-weekly discussion of the plots, themes and highlights of the HBO series 'Game of Thrones.'  Your hosts Dave Chen (The /Filmcast and The Tobolowsky Files) and Joanna Robinson (contributor for VanityFair.com) will dish up both praise and criticism and will try not to sound too pompous if the book was better. Questions, comments, oaths of fealty?  Email acastofkings@gmail.com\""
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(test[0]).replace('\"', \"'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Get from db podcasts with no collection_id\n",
    "query = \"SELECT * FROM podcast WHERE raw_summary ISNULL;\"\n",
    "query_null = pd.read_sql_query(query, con)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(361, 14)"
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_null.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#query_null['collection_id'] = [int(re.findall(r'/id(\\d+)', x)[0]) for x in query_null['view_url']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index([u'id', u'artwork_url30', u'artwork_url60', u'artwork_url100',\n",
       "       u'artwork_url600', u'explicit', u'name', u'view_url', u'summary',\n",
       "       u'artist_id', u'episode_descriptions', u'episode_names', u'raw_summary',\n",
       "       u'collection_id'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_null.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query_null_merge = pd.merge(query_null, podcastDf, how = 'inner', left_on=['collection_id', 'artwork_url30'], right_on = ['collection_id', 'artwork_url30'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>artwork_url30</th>\n",
       "      <th>artwork_url60_x</th>\n",
       "      <th>artwork_url100_x</th>\n",
       "      <th>artwork_url600_x</th>\n",
       "      <th>explicit</th>\n",
       "      <th>name</th>\n",
       "      <th>view_url</th>\n",
       "      <th>summary</th>\n",
       "      <th>artist_id_x</th>\n",
       "      <th>...</th>\n",
       "      <th>track_id</th>\n",
       "      <th>track_name</th>\n",
       "      <th>track_price</th>\n",
       "      <th>track_rental_price</th>\n",
       "      <th>track_view_url</th>\n",
       "      <th>wrapper_type</th>\n",
       "      <th>also_subscribed</th>\n",
       "      <th>episode_descriptions_y</th>\n",
       "      <th>episode_names_y</th>\n",
       "      <th>podcast_summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>13105</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>Miss Rikki presents lost music live bands spec...</td>\n",
       "      <td>10346</td>\n",
       "      <td>...</td>\n",
       "      <td>666137437</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>track</td>\n",
       "      <td>[]</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>Miss Rikki presents lost music, live bands, sp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>13105</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>Miss Rikki presents lost music live bands spec...</td>\n",
       "      <td>10346</td>\n",
       "      <td>...</td>\n",
       "      <td>666137437</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>track</td>\n",
       "      <td>[]</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>Miss Rikki presents lost music, live bands, sp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>13105</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>Miss Rikki presents lost music live bands spec...</td>\n",
       "      <td>10346</td>\n",
       "      <td>...</td>\n",
       "      <td>666137437</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>track</td>\n",
       "      <td>[]</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>Miss Rikki presents lost music, live bands, sp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>15375</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Podcast – Kitchen Sink WordPress</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/podcast-ki...</td>\n",
       "      <td>Listen to Adam share his knowledge and passion...</td>\n",
       "      <td>12194</td>\n",
       "      <td>...</td>\n",
       "      <td>862210489</td>\n",
       "      <td>Podcast – Kitchen Sink WordPress</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/podcast-ki...</td>\n",
       "      <td>track</td>\n",
       "      <td>[885696994, 858903353, 882349295, 566009415, 6...</td>\n",
       "      <td>['This week the show is hijacked by Dustin Har...</td>\n",
       "      <td>['Podcast E100 - The KSWP Show is Hijacked!', ...</td>\n",
       "      <td>Listen to Adam share his knowledge and passion...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>202</th>\n",
       "      <td>15375</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Podcast – Kitchen Sink WordPress</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/podcast-ki...</td>\n",
       "      <td>Listen to Adam share his knowledge and passion...</td>\n",
       "      <td>12194</td>\n",
       "      <td>...</td>\n",
       "      <td>862210489</td>\n",
       "      <td>Podcast – Kitchen Sink WordPress</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/podcast-ki...</td>\n",
       "      <td>track</td>\n",
       "      <td>[885696994, 858903353, 882349295, 566009415, 6...</td>\n",
       "      <td>['This week the show is hijacked by Dustin Har...</td>\n",
       "      <td>['Podcast E100 - The KSWP Show is Hijacked!', ...</td>\n",
       "      <td>Listen to Adam share his knowledge and passion...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>203</th>\n",
       "      <td>15375</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Podcast – Kitchen Sink WordPress</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/podcast-ki...</td>\n",
       "      <td>Listen to Adam share his knowledge and passion...</td>\n",
       "      <td>12194</td>\n",
       "      <td>...</td>\n",
       "      <td>862210489</td>\n",
       "      <td>Podcast – Kitchen Sink WordPress</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/podcast-ki...</td>\n",
       "      <td>track</td>\n",
       "      <td>[885696994, 858903353, 882349295, 566009415, 6...</td>\n",
       "      <td>['This week the show is hijacked by Dustin Har...</td>\n",
       "      <td>['Podcast E100 - The KSWP Show is Hijacked!', ...</td>\n",
       "      <td>Listen to Adam share his knowledge and passion...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                      artwork_url30  \\\n",
       "74   13105  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "75   13105  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "76   13105  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "201  15375  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "202  15375  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "203  15375  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "\n",
       "                                       artwork_url60_x  \\\n",
       "74   http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "75   http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "76   http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "201  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "202  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "203  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "\n",
       "                                      artwork_url100_x  \\\n",
       "74   http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "75   http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "76   http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "201  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "202  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "203  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "\n",
       "                                      artwork_url600_x explicit  \\\n",
       "74   http://is1.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "75   http://is1.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "76   http://is1.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "201  http://is4.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "202  http://is4.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "203  http://is4.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "\n",
       "                                     name  \\\n",
       "74   Closet Radio – BlasphuphmusRadio.com   \n",
       "75   Closet Radio – BlasphuphmusRadio.com   \n",
       "76   Closet Radio – BlasphuphmusRadio.com   \n",
       "201      Podcast – Kitchen Sink WordPress   \n",
       "202      Podcast – Kitchen Sink WordPress   \n",
       "203      Podcast – Kitchen Sink WordPress   \n",
       "\n",
       "                                              view_url  \\\n",
       "74   https://itunes.apple.com/us/podcast/closet-rad...   \n",
       "75   https://itunes.apple.com/us/podcast/closet-rad...   \n",
       "76   https://itunes.apple.com/us/podcast/closet-rad...   \n",
       "201  https://itunes.apple.com/us/podcast/podcast-ki...   \n",
       "202  https://itunes.apple.com/us/podcast/podcast-ki...   \n",
       "203  https://itunes.apple.com/us/podcast/podcast-ki...   \n",
       "\n",
       "                                               summary  artist_id_x  \\\n",
       "74   Miss Rikki presents lost music live bands spec...        10346   \n",
       "75   Miss Rikki presents lost music live bands spec...        10346   \n",
       "76   Miss Rikki presents lost music live bands spec...        10346   \n",
       "201  Listen to Adam share his knowledge and passion...        12194   \n",
       "202  Listen to Adam share his knowledge and passion...        12194   \n",
       "203  Listen to Adam share his knowledge and passion...        12194   \n",
       "\n",
       "                           ...                           track_id  \\\n",
       "74                         ...                          666137437   \n",
       "75                         ...                          666137437   \n",
       "76                         ...                          666137437   \n",
       "201                        ...                          862210489   \n",
       "202                        ...                          862210489   \n",
       "203                        ...                          862210489   \n",
       "\n",
       "                               track_name track_price  track_rental_price  \\\n",
       "74   Closet Radio – BlasphuphmusRadio.com           0                   0   \n",
       "75   Closet Radio – BlasphuphmusRadio.com           0                   0   \n",
       "76   Closet Radio – BlasphuphmusRadio.com           0                   0   \n",
       "201      Podcast – Kitchen Sink WordPress           0                   0   \n",
       "202      Podcast – Kitchen Sink WordPress           0                   0   \n",
       "203      Podcast – Kitchen Sink WordPress           0                   0   \n",
       "\n",
       "                                        track_view_url wrapper_type  \\\n",
       "74   https://itunes.apple.com/us/podcast/closet-rad...        track   \n",
       "75   https://itunes.apple.com/us/podcast/closet-rad...        track   \n",
       "76   https://itunes.apple.com/us/podcast/closet-rad...        track   \n",
       "201  https://itunes.apple.com/us/podcast/podcast-ki...        track   \n",
       "202  https://itunes.apple.com/us/podcast/podcast-ki...        track   \n",
       "203  https://itunes.apple.com/us/podcast/podcast-ki...        track   \n",
       "\n",
       "                                       also_subscribed  \\\n",
       "74                                                  []   \n",
       "75                                                  []   \n",
       "76                                                  []   \n",
       "201  [885696994, 858903353, 882349295, 566009415, 6...   \n",
       "202  [885696994, 858903353, 882349295, 566009415, 6...   \n",
       "203  [885696994, 858903353, 882349295, 566009415, 6...   \n",
       "\n",
       "                                episode_descriptions_y  \\\n",
       "74   ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "75   ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "76   ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "201  ['This week the show is hijacked by Dustin Har...   \n",
       "202  ['This week the show is hijacked by Dustin Har...   \n",
       "203  ['This week the show is hijacked by Dustin Har...   \n",
       "\n",
       "                                       episode_names_y  \\\n",
       "74   ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "75   ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "76   ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "201  ['Podcast E100 - The KSWP Show is Hijacked!', ...   \n",
       "202  ['Podcast E100 - The KSWP Show is Hijacked!', ...   \n",
       "203  ['Podcast E100 - The KSWP Show is Hijacked!', ...   \n",
       "\n",
       "                                       podcast_summary  \n",
       "74   Miss Rikki presents lost music, live bands, sp...  \n",
       "75   Miss Rikki presents lost music, live bands, sp...  \n",
       "76   Miss Rikki presents lost music, live bands, sp...  \n",
       "201  Listen to Adam share his knowledge and passion...  \n",
       "202  Listen to Adam share his knowledge and passion...  \n",
       "203  Listen to Adam share his knowledge and passion...  \n",
       "\n",
       "[6 rows x 51 columns]"
      ]
     },
     "execution_count": 161,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_null_merge[query_null_merge.duplicated('collection_id')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'NoneType' object has no attribute 'shape'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-162-e2c2ee4e86aa>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mquery_null_drop_dup\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery_null_merge\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdrop\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery_null_merge\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m75\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m76\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m202\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;36m203\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0minplace\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mquery_null_drop_dup\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mshape\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m: 'NoneType' object has no attribute 'shape'"
     ]
    }
   ],
   "source": [
    "query_null_merge.drop(query_null_merge.index[[75, 76, 202, 203]], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(363, 51)"
      ]
     },
     "execution_count": 163,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_null_merge.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>artwork_url30</th>\n",
       "      <th>artwork_url60_x</th>\n",
       "      <th>artwork_url100_x</th>\n",
       "      <th>artwork_url600_x</th>\n",
       "      <th>explicit</th>\n",
       "      <th>name</th>\n",
       "      <th>view_url</th>\n",
       "      <th>summary</th>\n",
       "      <th>artist_id_x</th>\n",
       "      <th>...</th>\n",
       "      <th>track_id</th>\n",
       "      <th>track_name</th>\n",
       "      <th>track_price</th>\n",
       "      <th>track_rental_price</th>\n",
       "      <th>track_view_url</th>\n",
       "      <th>wrapper_type</th>\n",
       "      <th>also_subscribed</th>\n",
       "      <th>episode_descriptions_y</th>\n",
       "      <th>episode_names_y</th>\n",
       "      <th>podcast_summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>13105</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>Miss Rikki presents lost music live bands spec...</td>\n",
       "      <td>10346</td>\n",
       "      <td>...</td>\n",
       "      <td>666137437</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>track</td>\n",
       "      <td>[]</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>Miss Rikki presents lost music, live bands, sp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>15375</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is4.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Podcast – Kitchen Sink WordPress</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/podcast-ki...</td>\n",
       "      <td>Listen to Adam share his knowledge and passion...</td>\n",
       "      <td>12194</td>\n",
       "      <td>...</td>\n",
       "      <td>862210489</td>\n",
       "      <td>Podcast – Kitchen Sink WordPress</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/podcast-ki...</td>\n",
       "      <td>track</td>\n",
       "      <td>[885696994, 858903353, 882349295, 566009415, 6...</td>\n",
       "      <td>['This week the show is hijacked by Dustin Har...</td>\n",
       "      <td>['Podcast E100 - The KSWP Show is Hijacked!', ...</td>\n",
       "      <td>Listen to Adam share his knowledge and passion...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        id                                      artwork_url30  \\\n",
       "74   13105  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "201  15375  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "\n",
       "                                       artwork_url60_x  \\\n",
       "74   http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "201  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "\n",
       "                                      artwork_url100_x  \\\n",
       "74   http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "201  http://is4.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "\n",
       "                                      artwork_url600_x explicit  \\\n",
       "74   http://is1.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "201  http://is4.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "\n",
       "                                     name  \\\n",
       "74   Closet Radio – BlasphuphmusRadio.com   \n",
       "201      Podcast – Kitchen Sink WordPress   \n",
       "\n",
       "                                              view_url  \\\n",
       "74   https://itunes.apple.com/us/podcast/closet-rad...   \n",
       "201  https://itunes.apple.com/us/podcast/podcast-ki...   \n",
       "\n",
       "                                               summary  artist_id_x  \\\n",
       "74   Miss Rikki presents lost music live bands spec...        10346   \n",
       "201  Listen to Adam share his knowledge and passion...        12194   \n",
       "\n",
       "                           ...                           track_id  \\\n",
       "74                         ...                          666137437   \n",
       "201                        ...                          862210489   \n",
       "\n",
       "                               track_name track_price  track_rental_price  \\\n",
       "74   Closet Radio – BlasphuphmusRadio.com           0                   0   \n",
       "201      Podcast – Kitchen Sink WordPress           0                   0   \n",
       "\n",
       "                                        track_view_url wrapper_type  \\\n",
       "74   https://itunes.apple.com/us/podcast/closet-rad...        track   \n",
       "201  https://itunes.apple.com/us/podcast/podcast-ki...        track   \n",
       "\n",
       "                                       also_subscribed  \\\n",
       "74                                                  []   \n",
       "201  [885696994, 858903353, 882349295, 566009415, 6...   \n",
       "\n",
       "                                episode_descriptions_y  \\\n",
       "74   ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "201  ['This week the show is hijacked by Dustin Har...   \n",
       "\n",
       "                                       episode_names_y  \\\n",
       "74   ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "201  ['Podcast E100 - The KSWP Show is Hijacked!', ...   \n",
       "\n",
       "                                       podcast_summary  \n",
       "74   Miss Rikki presents lost music, live bands, sp...  \n",
       "201  Listen to Adam share his knowledge and passion...  \n",
       "\n",
       "[2 rows x 51 columns]"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_null_merge[query_null_merge.duplicated(['artwork_url30', 'collection_id']) == True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>artwork_url30</th>\n",
       "      <th>artwork_url60_x</th>\n",
       "      <th>artwork_url100_x</th>\n",
       "      <th>artwork_url600_x</th>\n",
       "      <th>explicit</th>\n",
       "      <th>name</th>\n",
       "      <th>view_url</th>\n",
       "      <th>summary</th>\n",
       "      <th>artist_id_x</th>\n",
       "      <th>...</th>\n",
       "      <th>track_id</th>\n",
       "      <th>track_name</th>\n",
       "      <th>track_price</th>\n",
       "      <th>track_rental_price</th>\n",
       "      <th>track_view_url</th>\n",
       "      <th>wrapper_type</th>\n",
       "      <th>also_subscribed</th>\n",
       "      <th>episode_descriptions_y</th>\n",
       "      <th>episode_names_y</th>\n",
       "      <th>podcast_summary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>13105</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>Miss Rikki presents lost music live bands spec...</td>\n",
       "      <td>10346</td>\n",
       "      <td>...</td>\n",
       "      <td>666137437</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>track</td>\n",
       "      <td>[]</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>Miss Rikki presents lost music, live bands, sp...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>13105</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>http://is1.mzstatic.com/image/thumb/Music4/v4/...</td>\n",
       "      <td>Clean</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>Miss Rikki presents lost music live bands spec...</td>\n",
       "      <td>10346</td>\n",
       "      <td>...</td>\n",
       "      <td>666137437</td>\n",
       "      <td>Closet Radio – BlasphuphmusRadio.com</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>https://itunes.apple.com/us/podcast/closet-rad...</td>\n",
       "      <td>track</td>\n",
       "      <td>[]</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>['Closet Radio Episode 204: The Left One Is Fa...</td>\n",
       "      <td>Miss Rikki presents lost music, live bands, sp...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       id                                      artwork_url30  \\\n",
       "73  13105  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "74  13105  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "\n",
       "                                      artwork_url60_x  \\\n",
       "73  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "74  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "\n",
       "                                     artwork_url100_x  \\\n",
       "73  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "74  http://is1.mzstatic.com/image/thumb/Music4/v4/...   \n",
       "\n",
       "                                     artwork_url600_x explicit  \\\n",
       "73  http://is1.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "74  http://is1.mzstatic.com/image/thumb/Music4/v4/...    Clean   \n",
       "\n",
       "                                    name  \\\n",
       "73  Closet Radio – BlasphuphmusRadio.com   \n",
       "74  Closet Radio – BlasphuphmusRadio.com   \n",
       "\n",
       "                                             view_url  \\\n",
       "73  https://itunes.apple.com/us/podcast/closet-rad...   \n",
       "74  https://itunes.apple.com/us/podcast/closet-rad...   \n",
       "\n",
       "                                              summary  artist_id_x  \\\n",
       "73  Miss Rikki presents lost music live bands spec...        10346   \n",
       "74  Miss Rikki presents lost music live bands spec...        10346   \n",
       "\n",
       "                          ...                           track_id  \\\n",
       "73                        ...                          666137437   \n",
       "74                        ...                          666137437   \n",
       "\n",
       "                              track_name track_price  track_rental_price  \\\n",
       "73  Closet Radio – BlasphuphmusRadio.com           0                   0   \n",
       "74  Closet Radio – BlasphuphmusRadio.com           0                   0   \n",
       "\n",
       "                                       track_view_url wrapper_type  \\\n",
       "73  https://itunes.apple.com/us/podcast/closet-rad...        track   \n",
       "74  https://itunes.apple.com/us/podcast/closet-rad...        track   \n",
       "\n",
       "   also_subscribed                             episode_descriptions_y  \\\n",
       "73              []  ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "74              []  ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "\n",
       "                                      episode_names_y  \\\n",
       "73  ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "74  ['Closet Radio Episode 204: The Left One Is Fa...   \n",
       "\n",
       "                                      podcast_summary  \n",
       "73  Miss Rikki presents lost music, live bands, sp...  \n",
       "74  Miss Rikki presents lost music, live bands, sp...  \n",
       "\n",
       "[2 rows x 51 columns]"
      ]
     },
     "execution_count": 168,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query_null_merge[query_null_merge['id']==13105]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "query_null_merge.drop(query_null_merge.index[[74, 201]], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": []
    }
   ],
   "source": [
    "# update db with collection ids and summaries\n",
    "query = \"\"\"\n",
    "UPDATE podcast\n",
    "SET raw_summary = (%s)\n",
    "WHERE id = (%s);\n",
    "\"\"\"\n",
    "query = query.replace('\\n', ' ')\n",
    "for ind, row in tqdm(query_null_merge.iterrows(), total=query_null_merge.shape[0]):\n",
    "    data = ((row['podcast_summary'], ),  (row['id'], ))\n",
    "    cursor.execute(query, data)\n",
    "    \n",
    "con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}