ted_talks_2_elastic_slides.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Advancing your natural language processing with Elasticsearch \n",
    "DEVFEST DC 2019  <br>   Summer Rankin, PhD <br> Lead Data Scientist <br> Booz | Allen | Hamilton <br> www.summerrankin.com www.github.com/1fmusic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "After cleaning, and topic modeling our data, we will now \n",
    "\n",
    "> 1. clean up and normalize this data (not the same as cleaning for NLP analysis)\n",
    "> 2. push to our database using a low level client for elasticsearch:  **elasticsearch-py** https://elasticsearch-py.readthedocs.io/en/master/ \n",
    "\n",
    "+ Elasticsearch is an open-source search engine built on top of Apache Lucene.  https://www.elastic.co/\n",
    "+ Built in Java\n",
    "+ A noSQL database\n",
    "+ Data stored in JSON format. \n",
    "\n",
    "Data from https://www.kaggle.com/rounakbanik"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-15T20:08:30.547215Z",
     "start_time": "2017-11-15T20:08:28.639903Z"
    },
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [],
   "source": [
    "import pickle\n",
    "import pandas as pd\n",
    "\n",
    "path = '/Volumes/ext200/Dropbox/metis/p4_fletcher/pick/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Load the raw (original) text \n",
    "+ should include the results (strongest topic for each document) mapped back onto this original dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2017-11-15T20:08:32.451011Z",
     "start_time": "2017-11-15T20:08:31.800142Z"
    },
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>topic</th>\n",
       "      <th>transcript</th>\n",
       "      <th>url</th>\n",
       "      <th>comments</th>\n",
       "      <th>description</th>\n",
       "      <th>duration</th>\n",
       "      <th>event</th>\n",
       "      <th>film_date</th>\n",
       "      <th>languages</th>\n",
       "      <th>main_speaker</th>\n",
       "      <th>name</th>\n",
       "      <th>num_speaker</th>\n",
       "      <th>published_date</th>\n",
       "      <th>ratings</th>\n",
       "      <th>related_talks</th>\n",
       "      <th>speaker_occupation</th>\n",
       "      <th>tags</th>\n",
       "      <th>title</th>\n",
       "      <th>views</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>memories</td>\n",
       "      <td>Good morning. How are you?(Laughter)It's been ...</td>\n",
       "      <td>https://www.ted.com/talks/ken_robinson_says_sc...</td>\n",
       "      <td>4553</td>\n",
       "      <td>Sir Ken Robinson makes an entertaining and pro...</td>\n",
       "      <td>1164</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>1140825600</td>\n",
       "      <td>60</td>\n",
       "      <td>Ken Robinson</td>\n",
       "      <td>Ken Robinson: Do schools kill creativity?</td>\n",
       "      <td>1</td>\n",
       "      <td>1151367060</td>\n",
       "      <td>[{'id': 7, 'name': 'Funny', 'count': 19645}, {...</td>\n",
       "      <td>[{'id': 865, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Author/educator</td>\n",
       "      <td>['children', 'creativity', 'culture', 'dance',...</td>\n",
       "      <td>Do schools kill creativity?</td>\n",
       "      <td>47227110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>climate</td>\n",
       "      <td>Thank you so much, Chris. And it's truly a gre...</td>\n",
       "      <td>https://www.ted.com/talks/al_gore_on_averting_...</td>\n",
       "      <td>265</td>\n",
       "      <td>With the same humor and humanity he exuded in ...</td>\n",
       "      <td>977</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>1140825600</td>\n",
       "      <td>43</td>\n",
       "      <td>Al Gore</td>\n",
       "      <td>Al Gore: Averting the climate crisis</td>\n",
       "      <td>1</td>\n",
       "      <td>1151367060</td>\n",
       "      <td>[{'id': 7, 'name': 'Funny', 'count': 544}, {'i...</td>\n",
       "      <td>[{'id': 243, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Climate advocate</td>\n",
       "      <td>['alternative energy', 'cars', 'climate change...</td>\n",
       "      <td>Averting the climate crisis</td>\n",
       "      <td>3200520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>technology</td>\n",
       "      <td>(Music: \"The Sound of Silence,\" Simon &amp; Garfun...</td>\n",
       "      <td>https://www.ted.com/talks/david_pogue_says_sim...</td>\n",
       "      <td>124</td>\n",
       "      <td>New York Times columnist David Pogue takes aim...</td>\n",
       "      <td>1286</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>1140739200</td>\n",
       "      <td>26</td>\n",
       "      <td>David Pogue</td>\n",
       "      <td>David Pogue: Simplicity sells</td>\n",
       "      <td>1</td>\n",
       "      <td>1151367060</td>\n",
       "      <td>[{'id': 7, 'name': 'Funny', 'count': 964}, {'i...</td>\n",
       "      <td>[{'id': 1725, 'hero': 'https://pe.tedcdn.com/i...</td>\n",
       "      <td>Technology columnist</td>\n",
       "      <td>['computers', 'entertainment', 'interface desi...</td>\n",
       "      <td>Simplicity sells</td>\n",
       "      <td>1636292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>architecture</td>\n",
       "      <td>If you're here today — and I'm very happy that...</td>\n",
       "      <td>https://www.ted.com/talks/majora_carter_s_tale...</td>\n",
       "      <td>200</td>\n",
       "      <td>In an emotionally charged talk, MacArthur-winn...</td>\n",
       "      <td>1116</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>1140912000</td>\n",
       "      <td>35</td>\n",
       "      <td>Majora Carter</td>\n",
       "      <td>Majora Carter: Greening the ghetto</td>\n",
       "      <td>1</td>\n",
       "      <td>1151367060</td>\n",
       "      <td>[{'id': 3, 'name': 'Courageous', 'count': 760}...</td>\n",
       "      <td>[{'id': 1041, 'hero': 'https://pe.tedcdn.com/i...</td>\n",
       "      <td>Activist for environmental justice</td>\n",
       "      <td>['MacArthur grant', 'activism', 'business', 'c...</td>\n",
       "      <td>Greening the ghetto</td>\n",
       "      <td>1697550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>economics</td>\n",
       "      <td>About 10 years ago, I took on the task to teac...</td>\n",
       "      <td>https://www.ted.com/talks/hans_rosling_shows_t...</td>\n",
       "      <td>593</td>\n",
       "      <td>You've never seen data presented like this. Wi...</td>\n",
       "      <td>1190</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>1140566400</td>\n",
       "      <td>48</td>\n",
       "      <td>Hans Rosling</td>\n",
       "      <td>Hans Rosling: The best stats you've ever seen</td>\n",
       "      <td>1</td>\n",
       "      <td>1151440680</td>\n",
       "      <td>[{'id': 9, 'name': 'Ingenious', 'count': 3202}...</td>\n",
       "      <td>[{'id': 2056, 'hero': 'https://pe.tedcdn.com/i...</td>\n",
       "      <td>Global health expert; data visionary</td>\n",
       "      <td>['Africa', 'Asia', 'Google', 'demo', 'economic...</td>\n",
       "      <td>The best stats you've ever seen</td>\n",
       "      <td>12005869</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          topic                                         transcript  \\\n",
       "0      memories  Good morning. How are you?(Laughter)It's been ...   \n",
       "1       climate  Thank you so much, Chris. And it's truly a gre...   \n",
       "2    technology  (Music: \"The Sound of Silence,\" Simon & Garfun...   \n",
       "3  architecture  If you're here today — and I'm very happy that...   \n",
       "4     economics  About 10 years ago, I took on the task to teac...   \n",
       "\n",
       "                                                 url  comments  \\\n",
       "0  https://www.ted.com/talks/ken_robinson_says_sc...      4553   \n",
       "1  https://www.ted.com/talks/al_gore_on_averting_...       265   \n",
       "2  https://www.ted.com/talks/david_pogue_says_sim...       124   \n",
       "3  https://www.ted.com/talks/majora_carter_s_tale...       200   \n",
       "4  https://www.ted.com/talks/hans_rosling_shows_t...       593   \n",
       "\n",
       "                                         description  duration    event  \\\n",
       "0  Sir Ken Robinson makes an entertaining and pro...      1164  TED2006   \n",
       "1  With the same humor and humanity he exuded in ...       977  TED2006   \n",
       "2  New York Times columnist David Pogue takes aim...      1286  TED2006   \n",
       "3  In an emotionally charged talk, MacArthur-winn...      1116  TED2006   \n",
       "4  You've never seen data presented like this. Wi...      1190  TED2006   \n",
       "\n",
       "    film_date  languages   main_speaker  \\\n",
       "0  1140825600         60   Ken Robinson   \n",
       "1  1140825600         43        Al Gore   \n",
       "2  1140739200         26    David Pogue   \n",
       "3  1140912000         35  Majora Carter   \n",
       "4  1140566400         48   Hans Rosling   \n",
       "\n",
       "                                            name  num_speaker  published_date  \\\n",
       "0      Ken Robinson: Do schools kill creativity?            1      1151367060   \n",
       "1           Al Gore: Averting the climate crisis            1      1151367060   \n",
       "2                  David Pogue: Simplicity sells            1      1151367060   \n",
       "3             Majora Carter: Greening the ghetto            1      1151367060   \n",
       "4  Hans Rosling: The best stats you've ever seen            1      1151440680   \n",
       "\n",
       "                                             ratings  \\\n",
       "0  [{'id': 7, 'name': 'Funny', 'count': 19645}, {...   \n",
       "1  [{'id': 7, 'name': 'Funny', 'count': 544}, {'i...   \n",
       "2  [{'id': 7, 'name': 'Funny', 'count': 964}, {'i...   \n",
       "3  [{'id': 3, 'name': 'Courageous', 'count': 760}...   \n",
       "4  [{'id': 9, 'name': 'Ingenious', 'count': 3202}...   \n",
       "\n",
       "                                       related_talks  \\\n",
       "0  [{'id': 865, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "1  [{'id': 243, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "2  [{'id': 1725, 'hero': 'https://pe.tedcdn.com/i...   \n",
       "3  [{'id': 1041, 'hero': 'https://pe.tedcdn.com/i...   \n",
       "4  [{'id': 2056, 'hero': 'https://pe.tedcdn.com/i...   \n",
       "\n",
       "                     speaker_occupation  \\\n",
       "0                       Author/educator   \n",
       "1                      Climate advocate   \n",
       "2                  Technology columnist   \n",
       "3    Activist for environmental justice   \n",
       "4  Global health expert; data visionary   \n",
       "\n",
       "                                                tags  \\\n",
       "0  ['children', 'creativity', 'culture', 'dance',...   \n",
       "1  ['alternative energy', 'cars', 'climate change...   \n",
       "2  ['computers', 'entertainment', 'interface desi...   \n",
       "3  ['MacArthur grant', 'activism', 'business', 'c...   \n",
       "4  ['Africa', 'Asia', 'Google', 'demo', 'economic...   \n",
       "\n",
       "                             title     views  \n",
       "0      Do schools kill creativity?  47227110  \n",
       "1      Averting the climate crisis   3200520  \n",
       "2                 Simplicity sells   1636292  \n",
       "3              Greening the ghetto   1697550  \n",
       "4  The best stats you've ever seen  12005869  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open(path + 'ted_w_topic.pkl', 'rb') as picklefile:\n",
    "    ted_w_topic = pickle.load(picklefile)\n",
    "    \n",
    "ted_w_topic.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Dates need to be converted to datetime objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "ted_w_topic['film_date'] = pd.to_datetime(ted_w_topic['film_date'],unit='s')\n",
    "ted_w_topic['published_date'] = pd.to_datetime(ted_w_topic['published_date'],unit='s')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Lists need to have strings separated by a comma. \n",
    "No extra brackets, quotations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    ['children', 'creativity', 'culture', 'dance',...\n",
       "1    ['alternative energy', 'cars', 'climate change...\n",
       "2    ['computers', 'entertainment', 'interface desi...\n",
       "3    ['MacArthur grant', 'activism', 'business', 'c...\n",
       "4    ['Africa', 'Asia', 'Google', 'demo', 'economic...\n",
       "Name: tags, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted_w_topic.tags.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "ted_w_topic['tags'] = ted_w_topic['tags'].replace([r\"\\[\",\"\\]\",\"'\"],\"\",regex=True).str.lower()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### This list has multiple types of separators, replace them all with commas and lowercase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                                Author/educator\n",
       "1                               Climate advocate\n",
       "2                           Technology columnist\n",
       "3             Activist for environmental justice\n",
       "4           Global health expert; data visionary\n",
       "5    Life coach; expert in leadership psychology\n",
       "6                    Actor, comedian, playwright\n",
       "7                                      Architect\n",
       "Name: speaker_occupation, dtype: object"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted_w_topic.speaker_occupation.head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "ted_w_topic['speaker_occupation'] = ted_w_topic['speaker_occupation'].replace(['\\/','\\;'],', ',regex=True).str.lower()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "#### Nans are bad, find them and  destroy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "topic                 0\n",
       "transcript            0\n",
       "url                   0\n",
       "comments              0\n",
       "description           0\n",
       "duration              0\n",
       "event                 0\n",
       "film_date             0\n",
       "languages             0\n",
       "main_speaker          0\n",
       "name                  0\n",
       "num_speaker           0\n",
       "published_date        0\n",
       "ratings               0\n",
       "related_talks         0\n",
       "speaker_occupation    6\n",
       "tags                  0\n",
       "title                 0\n",
       "views                 0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted_w_topic.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "ted_w_topic.speaker_occupation.fillna('None',inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Clean up for ingestion into database \n",
    "all the cleaning tasks in one place\n",
    "\n",
    "+ dates converted to datetime objects\n",
    "+ fill NaNs with something (srting or number depending on Column dtype)\n",
    "+ remove brackets at the ends\n",
    "+ make separators of words in lists the same (i.e. all commas), so Elastic will treat as an array \n",
    "+ lowercase these lists"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true,
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>topic</th>\n",
       "      <th>transcript</th>\n",
       "      <th>url</th>\n",
       "      <th>comments</th>\n",
       "      <th>description</th>\n",
       "      <th>duration</th>\n",
       "      <th>event</th>\n",
       "      <th>film_date</th>\n",
       "      <th>languages</th>\n",
       "      <th>main_speaker</th>\n",
       "      <th>name</th>\n",
       "      <th>num_speaker</th>\n",
       "      <th>published_date</th>\n",
       "      <th>speaker_occupation</th>\n",
       "      <th>tags</th>\n",
       "      <th>title</th>\n",
       "      <th>views</th>\n",
       "      <th>speaker_occupation_raw</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>memories</td>\n",
       "      <td>Good morning. How are you?(Laughter)It's been ...</td>\n",
       "      <td>https://www.ted.com/talks/ken_robinson_says_sc...</td>\n",
       "      <td>4553</td>\n",
       "      <td>Sir Ken Robinson makes an entertaining and pro...</td>\n",
       "      <td>1164</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>2006-02-25</td>\n",
       "      <td>60</td>\n",
       "      <td>Ken Robinson</td>\n",
       "      <td>Ken Robinson: Do schools kill creativity?</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-06-27 00:11:00</td>\n",
       "      <td>author, educator</td>\n",
       "      <td>children, creativity, culture, dance, educatio...</td>\n",
       "      <td>Do schools kill creativity?</td>\n",
       "      <td>47227110</td>\n",
       "      <td>Author/educator</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>climate</td>\n",
       "      <td>Thank you so much, Chris. And it's truly a gre...</td>\n",
       "      <td>https://www.ted.com/talks/al_gore_on_averting_...</td>\n",
       "      <td>265</td>\n",
       "      <td>With the same humor and humanity he exuded in ...</td>\n",
       "      <td>977</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>2006-02-25</td>\n",
       "      <td>43</td>\n",
       "      <td>Al Gore</td>\n",
       "      <td>Al Gore: Averting the climate crisis</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-06-27 00:11:00</td>\n",
       "      <td>climate advocate</td>\n",
       "      <td>alternative energy, cars, climate change, cult...</td>\n",
       "      <td>Averting the climate crisis</td>\n",
       "      <td>3200520</td>\n",
       "      <td>Climate advocate</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>technology</td>\n",
       "      <td>(Music: \"The Sound of Silence,\" Simon &amp; Garfun...</td>\n",
       "      <td>https://www.ted.com/talks/david_pogue_says_sim...</td>\n",
       "      <td>124</td>\n",
       "      <td>New York Times columnist David Pogue takes aim...</td>\n",
       "      <td>1286</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>2006-02-24</td>\n",
       "      <td>26</td>\n",
       "      <td>David Pogue</td>\n",
       "      <td>David Pogue: Simplicity sells</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-06-27 00:11:00</td>\n",
       "      <td>technology columnist</td>\n",
       "      <td>computers, entertainment, interface design, me...</td>\n",
       "      <td>Simplicity sells</td>\n",
       "      <td>1636292</td>\n",
       "      <td>Technology columnist</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>architecture</td>\n",
       "      <td>If you're here today — and I'm very happy that...</td>\n",
       "      <td>https://www.ted.com/talks/majora_carter_s_tale...</td>\n",
       "      <td>200</td>\n",
       "      <td>In an emotionally charged talk, MacArthur-winn...</td>\n",
       "      <td>1116</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>2006-02-26</td>\n",
       "      <td>35</td>\n",
       "      <td>Majora Carter</td>\n",
       "      <td>Majora Carter: Greening the ghetto</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-06-27 00:11:00</td>\n",
       "      <td>activist for environmental justice</td>\n",
       "      <td>macarthur grant, activism, business, cities, e...</td>\n",
       "      <td>Greening the ghetto</td>\n",
       "      <td>1697550</td>\n",
       "      <td>Activist for environmental justice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>economics</td>\n",
       "      <td>About 10 years ago, I took on the task to teac...</td>\n",
       "      <td>https://www.ted.com/talks/hans_rosling_shows_t...</td>\n",
       "      <td>593</td>\n",
       "      <td>You've never seen data presented like this. Wi...</td>\n",
       "      <td>1190</td>\n",
       "      <td>TED2006</td>\n",
       "      <td>2006-02-22</td>\n",
       "      <td>48</td>\n",
       "      <td>Hans Rosling</td>\n",
       "      <td>Hans Rosling: The best stats you've ever seen</td>\n",
       "      <td>1</td>\n",
       "      <td>2006-06-27 20:38:00</td>\n",
       "      <td>global health expert,  data visionary</td>\n",
       "      <td>africa, asia, google, demo, economics, global ...</td>\n",
       "      <td>The best stats you've ever seen</td>\n",
       "      <td>12005869</td>\n",
       "      <td>Global health expert; data visionary</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          topic                                         transcript  \\\n",
       "0      memories  Good morning. How are you?(Laughter)It's been ...   \n",
       "1       climate  Thank you so much, Chris. And it's truly a gre...   \n",
       "2    technology  (Music: \"The Sound of Silence,\" Simon & Garfun...   \n",
       "3  architecture  If you're here today — and I'm very happy that...   \n",
       "4     economics  About 10 years ago, I took on the task to teac...   \n",
       "\n",
       "                                                 url  comments  \\\n",
       "0  https://www.ted.com/talks/ken_robinson_says_sc...      4553   \n",
       "1  https://www.ted.com/talks/al_gore_on_averting_...       265   \n",
       "2  https://www.ted.com/talks/david_pogue_says_sim...       124   \n",
       "3  https://www.ted.com/talks/majora_carter_s_tale...       200   \n",
       "4  https://www.ted.com/talks/hans_rosling_shows_t...       593   \n",
       "\n",
       "                                         description  duration    event  \\\n",
       "0  Sir Ken Robinson makes an entertaining and pro...      1164  TED2006   \n",
       "1  With the same humor and humanity he exuded in ...       977  TED2006   \n",
       "2  New York Times columnist David Pogue takes aim...      1286  TED2006   \n",
       "3  In an emotionally charged talk, MacArthur-winn...      1116  TED2006   \n",
       "4  You've never seen data presented like this. Wi...      1190  TED2006   \n",
       "\n",
       "   film_date  languages   main_speaker  \\\n",
       "0 2006-02-25         60   Ken Robinson   \n",
       "1 2006-02-25         43        Al Gore   \n",
       "2 2006-02-24         26    David Pogue   \n",
       "3 2006-02-26         35  Majora Carter   \n",
       "4 2006-02-22         48   Hans Rosling   \n",
       "\n",
       "                                            name  num_speaker  \\\n",
       "0      Ken Robinson: Do schools kill creativity?            1   \n",
       "1           Al Gore: Averting the climate crisis            1   \n",
       "2                  David Pogue: Simplicity sells            1   \n",
       "3             Majora Carter: Greening the ghetto            1   \n",
       "4  Hans Rosling: The best stats you've ever seen            1   \n",
       "\n",
       "       published_date                     speaker_occupation  \\\n",
       "0 2006-06-27 00:11:00                       author, educator   \n",
       "1 2006-06-27 00:11:00                       climate advocate   \n",
       "2 2006-06-27 00:11:00                   technology columnist   \n",
       "3 2006-06-27 00:11:00     activist for environmental justice   \n",
       "4 2006-06-27 20:38:00  global health expert,  data visionary   \n",
       "\n",
       "                                                tags  \\\n",
       "0  children, creativity, culture, dance, educatio...   \n",
       "1  alternative energy, cars, climate change, cult...   \n",
       "2  computers, entertainment, interface design, me...   \n",
       "3  macarthur grant, activism, business, cities, e...   \n",
       "4  africa, asia, google, demo, economics, global ...   \n",
       "\n",
       "                             title     views  \\\n",
       "0      Do schools kill creativity?  47227110   \n",
       "1      Averting the climate crisis   3200520   \n",
       "2                 Simplicity sells   1636292   \n",
       "3              Greening the ghetto   1697550   \n",
       "4  The best stats you've ever seen  12005869   \n",
       "\n",
       "                 speaker_occupation_raw  \n",
       "0                       Author/educator  \n",
       "1                      Climate advocate  \n",
       "2                  Technology columnist  \n",
       "3    Activist for environmental justice  \n",
       "4  Global health expert; data visionary  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted_w_topic['film_date'] = pd.to_datetime(ted_w_topic['film_date'],unit='s')\n",
    "ted_w_topic['published_date'] = pd.to_datetime(ted_w_topic['published_date'],unit='s')\n",
    "\n",
    "ted_w_topic.speaker_occupation.fillna('None',inplace=True)\n",
    "\n",
    "\n",
    "ted_w_topic['speaker_occupation'] = ted_w_topic['speaker_occupation'].replace(['\\/','\\;'],', ',regex=True).str.lower()\n",
    "\n",
    "ted_w_topic['tags'] = ted_w_topic['tags'].replace([r\"\\[\",\"\\]\",\"'\"],\"\",regex=True).str.lower()\n",
    "\n",
    "# drop cols I don't want\n",
    "ted_ingest = ted_w_topic.drop(columns=['ratings','related_talks'])\n",
    "ted_ingest.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Save dataframe as a python Dictionary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "ted_ingest_d = ted_ingest.to_dict(orient='records')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Here are a few ways to ingest our data: "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "source": [
    "# Option 1: Elasticsearch-py as one object\n",
    "### save as a python dictionary and push to our elasticsearch instance\n",
    "+ can run Elastic locally, from a docker container, or in the cloud. We connect like we would any other server\n",
    "+ elastic is already running, so we just connect to it with the elastic-py library"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Connect to Elasticsearch locally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'name': 'elasticsearch',\n",
       " 'cluster_name': 'docker-cluster',\n",
       " 'cluster_uuid': 'pe6uVqX0SruLz4VRKmqaGA',\n",
       " 'version': {'number': '7.1.1',\n",
       "  'build_flavor': 'default',\n",
       "  'build_type': 'docker',\n",
       "  'build_hash': '7a013de',\n",
       "  'build_date': '2019-05-23T14:04:00.380842Z',\n",
       "  'build_snapshot': False,\n",
       "  'lucene_version': '8.0.0',\n",
       "  'minimum_wire_compatibility_version': '6.8.0',\n",
       "  'minimum_index_compatibility_version': '6.0.0-beta1'},\n",
       " 'tagline': 'You Know, for Search'}"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from elasticsearch import helpers, Elasticsearch\n",
    "\n",
    "es = Elasticsearch(\"localhost:9200\")\n",
    "es_client = Elasticsearch(http_compress=True)\n",
    "Elasticsearch.info(es)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "### Example of how to connect to Elasticsearch non-local\n",
    "+ don't expose your passwords though"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    es=Elasticsearch(\n",
    "    ['https://yourcloudaddress.aws'],\n",
    "    http_auth=('username','password'),\n",
    "    port=8080,\n",
    "    use_ssl=True,\n",
    "    ca_certs=certifi.where(),\n",
    "    )\n",
    "    print(\"Connected\", es.info())\n",
    "    \n",
    "except Exception as ex:\n",
    "    print( \"Error:\", ex)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2467, [])"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "helpers.bulk(es_client, ted_ingest_d, index='ted_talks_1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Option 2: Elasticsearch-py one document at a time\n",
    "### save as dict/dataframe and use a generator to ingest with our elasticsearch client\n",
    "+ this is useful if you have a large dataset that you don't want to hold in memory (or can't)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "def doc_generator(df):\n",
    "    df_iter = df.iterrows()\n",
    "    for index, document in df_iter:\n",
    "        yield {\n",
    "                \"_index\": 'ted_talks_1',\n",
    "                \"_source\": document,\n",
    "            }\n",
    "    raise StopIteration\n",
    "    \n",
    "helpers.bulk(es_client, doc_generator(ted_ingest),index = 'ted_talks_1')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "source": [
    "### toy example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "slideshow": {
     "slide_type": "skip"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3, [])"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def gendata():\n",
    "    mywords = ['foo', 'bar', 'baz']\n",
    "    for word in mywords:\n",
    "        yield {\n",
    "            \"_index\": \"mywords\",\n",
    "            \"_type\": \"document\",\n",
    "            \"doc\": {\"word\": word},\n",
    "        }\n",
    "\n",
    "helpers.bulk(es, gendata())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Option 3: Kibana GUI\n",
    "## save as a delimited file (CSV, TSV, XML, JSON...) and ingest using the Kibana GUI (under \"machine learning => data visualizer\" \n",
    "+ There is a limit to the size of the file you can ingest\n",
    "+ tends to be a little fussier about things being in the right format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "ted_ingest.to_csv('~/ted_w_topic.csv',index=False, encoding='utf8')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Option 4: Requests library\n",
    "getting the pandas df into the correct json format that elastic expects is kind of convoluted. but this function works just fine.  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "import json\n",
    "import requests\n",
    "\n",
    "ted_ingest['_id'] = ted_ingest.index\n",
    "\n",
    "ted_as_json = ted_ingest.to_json(orient='records', lines=True)\n",
    "\n",
    "final_json_string = ''\n",
    "for json_document in ted_as_json.split('\\n'):\n",
    "    jdict = json.loads(json_document)\n",
    "    metadata = json.dumps({'index': {'_id': jdict['_id']}})\n",
    "    jdict.pop('_id')\n",
    "    final_json_string += metadata + '\\n' + json.dumps(jdict) + '\\n'\n",
    "    \n",
    "headers = {'Content-type': 'application/json', 'Accept': 'text/plain'}\n",
    "\n",
    "r = requests.post('http://localhost:9200/ted_req/_bulk', data=final_json_string, headers=headers, timeout=120)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Option 5: CURL\n",
    "## Similar to the above method, we will  save the JSON string we just created and ingest using the command line. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "\n",
    "with open(\"ted_w_topic.json\",\"w\") as tedf:\n",
    "    json.dump(final_json_string, tedf)\n",
    "\n",
    "\n",
    "#curl -X POST \"http://localhost:9200/index/ted_curl\" -H \"content-type: application/json\" --data-binary \"@ted_w_topic.json\"\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Option 6: Logstash \n",
    "https://www.elastic.co/products/logstash\n",
    "\n",
    "Good tutorial from Catherine Ordun about ingesting a CSV using Logstash https://tm3.ghost.io/2017/10/12/kibana-dashboard-for-amazon-food-reviews/\n",
    "\n",
    "Logstash is an open source server-side data processing pipeline. It is the 'L' in the ELK stack that you may have heard of. It has nothing to do with python and is made for mutating/converting data upon ingestion into an elasticsearch index. It is a separate piece of software that you will install and then have to spend a little time learning. I find that if i'm just ingesting one large document, one time, that this is overkill. This is made more for streaming data the comes in (i.e. server logs). \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "ted_ingest.to_csv('~/ted_w_topic.csv',index=False, encoding='utf8')"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}