ETL_data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# parse a list of files into a python dictionary\n",
    "datapath = \"./transcripts/\"\n",
    "transcripts = {}\n",
    "\n",
    "for file in os.listdir(datapath):\n",
    "    if file.endswith(\".xml\"):\n",
    "        soup = BeautifulSoup(open(datapath+ file), 'html.parser')\n",
    "        result = [(p['begin'], p['end'], p.text) for p in soup.find_all('p')]\n",
    "        transcripts[file] = result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# load dict into pandas dataframe\n",
    "transcripts_pd = pd.DataFrame()\n",
    "for transcript in sorted(transcripts): # may want to limit the list for convience/testing\n",
    "    df2=pd.DataFrame(transcripts[transcript], columns = ['sTimestamp','eTimestamp','words'])\n",
    "    #words dont always seemm to line up with the video, so rounding is implemented. lets see if this works well overall\n",
    "    df2['sTime'] = pd.to_datetime(df2['sTimestamp']).dt.round('s').dt.strftime(\"%Hh%Mm%Ss\")\n",
    "    df2['videoId'] = transcript\n",
    "    #take the file format off the ID. Why are we keeping this? Not sure, perhaps in order to track the file it came from in case we switch formats\n",
    "    videoId_strip = transcript[:-4]\n",
    "    #create the youtube permalink for sharing at the specified time\n",
    "    df2['share_url'] = \"https://youtu.be/\" + videoId_strip + \"?t=\" + df2['sTime']\n",
    "    transcripts_pd = transcripts_pd.append(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sTimestamp</th>\n",
       "      <th>eTimestamp</th>\n",
       "      <th>words</th>\n",
       "      <th>sTime</th>\n",
       "      <th>videoId</th>\n",
       "      <th>share_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00:00:00.000</td>\n",
       "      <td>00:00:09.960</td>\n",
       "      <td>I have a night class in graph theory</td>\n",
       "      <td>00h00m00s</td>\n",
       "      <td>-Q9iAXpWxjA.xml</td>\n",
       "      <td>https://youtu.be/-Q9iAXpWxjA?t=00h00m00s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00:00:07.830</td>\n",
       "      <td>00:00:12.000</td>\n",
       "      <td>that I hate because it's a night class</td>\n",
       "      <td>00h00m08s</td>\n",
       "      <td>-Q9iAXpWxjA.xml</td>\n",
       "      <td>https://youtu.be/-Q9iAXpWxjA?t=00h00m08s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00:00:09.960</td>\n",
       "      <td>00:00:14.610</td>\n",
       "      <td>and it's filled with algorithms that are</td>\n",
       "      <td>00h00m10s</td>\n",
       "      <td>-Q9iAXpWxjA.xml</td>\n",
       "      <td>https://youtu.be/-Q9iAXpWxjA?t=00h00m10s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>00:00:12.000</td>\n",
       "      <td>00:00:17.460</td>\n",
       "      <td>hard to pronounce but last week's class</td>\n",
       "      <td>00h00m12s</td>\n",
       "      <td>-Q9iAXpWxjA.xml</td>\n",
       "      <td>https://youtu.be/-Q9iAXpWxjA?t=00h00m12s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>00:00:14.610</td>\n",
       "      <td>00:00:19.380</td>\n",
       "      <td>was particularly weird after the lecture</td>\n",
       "      <td>00h00m15s</td>\n",
       "      <td>-Q9iAXpWxjA.xml</td>\n",
       "      <td>https://youtu.be/-Q9iAXpWxjA?t=00h00m15s</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     sTimestamp    eTimestamp                                     words  \\\n",
       "0  00:00:00.000  00:00:09.960      I have a night class in graph theory   \n",
       "1  00:00:07.830  00:00:12.000    that I hate because it's a night class   \n",
       "2  00:00:09.960  00:00:14.610  and it's filled with algorithms that are   \n",
       "3  00:00:12.000  00:00:17.460   hard to pronounce but last week's class   \n",
       "4  00:00:14.610  00:00:19.380  was particularly weird after the lecture   \n",
       "\n",
       "       sTime          videoId                                 share_url  \n",
       "0  00h00m00s  -Q9iAXpWxjA.xml  https://youtu.be/-Q9iAXpWxjA?t=00h00m00s  \n",
       "1  00h00m08s  -Q9iAXpWxjA.xml  https://youtu.be/-Q9iAXpWxjA?t=00h00m08s  \n",
       "2  00h00m10s  -Q9iAXpWxjA.xml  https://youtu.be/-Q9iAXpWxjA?t=00h00m10s  \n",
       "3  00h00m12s  -Q9iAXpWxjA.xml  https://youtu.be/-Q9iAXpWxjA?t=00h00m12s  \n",
       "4  00h00m15s  -Q9iAXpWxjA.xml  https://youtu.be/-Q9iAXpWxjA?t=00h00m15s  "
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transcripts_pd.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# search function\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "def search_string_cols(df, string, col):\n",
    "    \"\"\"searches specified colummn in dataframe for arbitrary string\"\"\"\n",
    "    results = pd.DataFrame(df[df[col].str.contains(string, na=False)])\n",
    "    return results\n",
    "\n",
    "results = search_string_cols(transcripts_pd, 'Chicago', 'words')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sTimestamp</th>\n",
       "      <th>eTimestamp</th>\n",
       "      <th>words</th>\n",
       "      <th>sTime</th>\n",
       "      <th>videoId</th>\n",
       "      <th>share_url</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>00:01:28.350</td>\n",
       "      <td>00:01:33.750</td>\n",
       "      <td>different hats clerks here in Chicago</td>\n",
       "      <td>00h01m28s</td>\n",
       "      <td>-l7k2T1nvow.xml</td>\n",
       "      <td>https://youtu.be/-l7k2T1nvow?t=00h01m28s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>00:05:53.690</td>\n",
       "      <td>00:05:59.030</td>\n",
       "      <td>your office to me in Chicago or even one</td>\n",
       "      <td>00h05m54s</td>\n",
       "      <td>-l7k2T1nvow.xml</td>\n",
       "      <td>https://youtu.be/-l7k2T1nvow?t=00h05m54s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>00:00:37.380</td>\n",
       "      <td>00:00:41.489</td>\n",
       "      <td>general for the city of Chicago and if</td>\n",
       "      <td>00h00m37s</td>\n",
       "      <td>1fQWo1BmwAc.xml</td>\n",
       "      <td>https://youtu.be/1fQWo1BmwAc?t=00h00m37s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>00:00:46.530</td>\n",
       "      <td>00:00:51.809</td>\n",
       "      <td>General of the city of Chicago and my</td>\n",
       "      <td>00h00m47s</td>\n",
       "      <td>1fQWo1BmwAc.xml</td>\n",
       "      <td>https://youtu.be/1fQWo1BmwAc?t=00h00m47s</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>00:01:05.430</td>\n",
       "      <td>00:01:09.390</td>\n",
       "      <td>all of you follow Chicago politics 31</td>\n",
       "      <td>00h01m05s</td>\n",
       "      <td>1fQWo1BmwAc.xml</td>\n",
       "      <td>https://youtu.be/1fQWo1BmwAc?t=00h01m05s</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       sTimestamp    eTimestamp                                     words  \\\n",
       "37   00:01:28.350  00:01:33.750     different hats clerks here in Chicago   \n",
       "151  00:05:53.690  00:05:59.030  your office to me in Chicago or even one   \n",
       "12   00:00:37.380  00:00:41.489    general for the city of Chicago and if   \n",
       "16   00:00:46.530  00:00:51.809     General of the city of Chicago and my   \n",
       "23   00:01:05.430  00:01:09.390     all of you follow Chicago politics 31   \n",
       "\n",
       "         sTime          videoId                                 share_url  \n",
       "37   00h01m28s  -l7k2T1nvow.xml  https://youtu.be/-l7k2T1nvow?t=00h01m28s  \n",
       "151  00h05m54s  -l7k2T1nvow.xml  https://youtu.be/-l7k2T1nvow?t=00h05m54s  \n",
       "12   00h00m37s  1fQWo1BmwAc.xml  https://youtu.be/1fQWo1BmwAc?t=00h00m37s  \n",
       "16   00h00m47s  1fQWo1BmwAc.xml  https://youtu.be/1fQWo1BmwAc?t=00h00m47s  \n",
       "23   00h01m05s  1fQWo1BmwAc.xml  https://youtu.be/1fQWo1BmwAc?t=00h01m05s  "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "37     https://youtu.be/-l7k2T1nvow?t=00h01m28s\n",
       "151    https://youtu.be/-l7k2T1nvow?t=00h05m54s\n",
       "12     https://youtu.be/1fQWo1BmwAc?t=00h00m37s\n",
       "16     https://youtu.be/1fQWo1BmwAc?t=00h00m47s\n",
       "23     https://youtu.be/1fQWo1BmwAc?t=00h01m05s\n",
       "25     https://youtu.be/1fQWo1BmwAc?t=00h01m09s\n",
       "37     https://youtu.be/1fQWo1BmwAc?t=00h01m45s\n",
       "69     https://youtu.be/1fQWo1BmwAc?t=00h03m00s\n",
       "155    https://youtu.be/1fQWo1BmwAc?t=00h06m21s\n",
       "158    https://youtu.be/1fQWo1BmwAc?t=00h06m27s\n",
       "256    https://youtu.be/1fQWo1BmwAc?t=00h09m57s\n",
       "270    https://youtu.be/1fQWo1BmwAc?t=00h10m30s\n",
       "320    https://youtu.be/1fQWo1BmwAc?t=00h12m16s\n",
       "360    https://youtu.be/1fQWo1BmwAc?t=00h13m39s\n",
       "364    https://youtu.be/1fQWo1BmwAc?t=00h13m45s\n",
       "393    https://youtu.be/1fQWo1BmwAc?t=00h14m50s\n",
       "512    https://youtu.be/1fQWo1BmwAc?t=00h19m38s\n",
       "516    https://youtu.be/1fQWo1BmwAc?t=00h19m45s\n",
       "541    https://youtu.be/1fQWo1BmwAc?t=00h20m39s\n",
       "560    https://youtu.be/1fQWo1BmwAc?t=00h21m35s\n",
       "580    https://youtu.be/1fQWo1BmwAc?t=00h22m15s\n",
       "605    https://youtu.be/1fQWo1BmwAc?t=00h23m03s\n",
       "662    https://youtu.be/1fQWo1BmwAc?t=00h25m09s\n",
       "667    https://youtu.be/1fQWo1BmwAc?t=00h25m21s\n",
       "738    https://youtu.be/1fQWo1BmwAc?t=00h28m14s\n",
       "745    https://youtu.be/1fQWo1BmwAc?t=00h28m30s\n",
       "768    https://youtu.be/1fQWo1BmwAc?t=00h29m35s\n",
       "777    https://youtu.be/1fQWo1BmwAc?t=00h29m51s\n",
       "780    https://youtu.be/1fQWo1BmwAc?t=00h29m57s\n",
       "789    https://youtu.be/1fQWo1BmwAc?t=00h30m14s\n",
       "                         ...                   \n",
       "912    https://youtu.be/ulrLTCrHeeo?t=00h37m25s\n",
       "917    https://youtu.be/ulrLTCrHeeo?t=00h37m33s\n",
       "66     https://youtu.be/utgpFx-LhkU?t=00h03m49s\n",
       "89     https://youtu.be/utgpFx-LhkU?t=00h05m04s\n",
       "341    https://youtu.be/utgpFx-LhkU?t=00h18m08s\n",
       "376    https://youtu.be/utgpFx-LhkU?t=00h19m55s\n",
       "465    https://youtu.be/utgpFx-LhkU?t=00h24m24s\n",
       "5      https://youtu.be/vG1nwjwUlqc?t=00h00m19s\n",
       "11     https://youtu.be/vG1nwjwUlqc?t=00h00m33s\n",
       "14     https://youtu.be/vG1nwjwUlqc?t=00h00m40s\n",
       "20     https://youtu.be/vG1nwjwUlqc?t=00h00m55s\n",
       "59     https://youtu.be/vG1nwjwUlqc?t=00h02m42s\n",
       "88     https://youtu.be/vG1nwjwUlqc?t=00h03m46s\n",
       "95     https://youtu.be/vG1nwjwUlqc?t=00h04m06s\n",
       "210    https://youtu.be/w6dq9mfDhU4?t=00h07m55s\n",
       "664    https://youtu.be/w6dq9mfDhU4?t=00h23m31s\n",
       "5      https://youtu.be/wOaw-H_T-is?t=00h00m15s\n",
       "29     https://youtu.be/wOaw-H_T-is?t=00h01m19s\n",
       "85     https://youtu.be/wOaw-H_T-is?t=00h03m54s\n",
       "168    https://youtu.be/wOaw-H_T-is?t=00h07m21s\n",
       "182    https://youtu.be/wOaw-H_T-is?t=00h07m56s\n",
       "279    https://youtu.be/wOaw-H_T-is?t=00h11m26s\n",
       "343    https://youtu.be/wOaw-H_T-is?t=00h13m53s\n",
       "379    https://youtu.be/wOaw-H_T-is?t=00h15m21s\n",
       "398    https://youtu.be/wOaw-H_T-is?t=00h16m07s\n",
       "436    https://youtu.be/wOaw-H_T-is?t=00h17m27s\n",
       "476    https://youtu.be/wOaw-H_T-is?t=00h18m43s\n",
       "34     https://youtu.be/zjxGtP7-elU?t=00h01m18s\n",
       "226    https://youtu.be/zjxGtP7-elU?t=00h09m54s\n",
       "387    https://youtu.be/zjxGtP7-elU?t=00h17m01s\n",
       "Name: share_url, Length: 623, dtype: object"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results['share_url']"
   ]
  },
  {
   "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.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}