{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Science Practise using ted-talks dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Exploratory Data Analysis is all about answering a specific questions.\n",
    "1. Which talks provoke the most online discussion?\n",
    "2. What are the best events in ted history to attend?\n",
    "3. Which occupations deliver the funniest TED talks on average?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Imports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "%matplotlib inline\n",
    "plt.style.use('fivethirtyeight')\n",
    "sns.set_style('whitegrid')\n",
    "\n",
    "plt.rcParams['figure.figsize'] = [12, 10]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>url</th>\n",
       "      <th>views</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\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>https://www.ted.com/talks/ken_robinson_says_sc...</td>\n",
       "      <td>47227110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\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>https://www.ted.com/talks/al_gore_on_averting_...</td>\n",
       "      <td>3200520</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\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>https://www.ted.com/talks/david_pogue_says_sim...</td>\n",
       "      <td>1636292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\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>https://www.ted.com/talks/majora_carter_s_tale...</td>\n",
       "      <td>1697550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\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>https://www.ted.com/talks/hans_rosling_shows_t...</td>\n",
       "      <td>12005869</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   comments                                        description  duration  \\\n",
       "0      4553  Sir Ken Robinson makes an entertaining and pro...      1164   \n",
       "1       265  With the same humor and humanity he exuded in ...       977   \n",
       "2       124  New York Times columnist David Pogue takes aim...      1286   \n",
       "3       200  In an emotionally charged talk, MacArthur-winn...      1116   \n",
       "4       593  You've never seen data presented like this. Wi...      1190   \n",
       "\n",
       "     event   film_date  languages   main_speaker  \\\n",
       "0  TED2006  1140825600         60   Ken Robinson   \n",
       "1  TED2006  1140825600         43        Al Gore   \n",
       "2  TED2006  1140739200         26    David Pogue   \n",
       "3  TED2006  1140912000         35  Majora Carter   \n",
       "4  TED2006  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  \\\n",
       "0      Do schools kill creativity?   \n",
       "1      Averting the climate crisis   \n",
       "2                 Simplicity sells   \n",
       "3              Greening the ghetto   \n",
       "4  The best stats you've ever seen   \n",
       "\n",
       "                                                 url     views  \n",
       "0  https://www.ted.com/talks/ken_robinson_says_sc...  47227110  \n",
       "1  https://www.ted.com/talks/al_gore_on_averting_...   3200520  \n",
       "2  https://www.ted.com/talks/david_pogue_says_sim...   1636292  \n",
       "3  https://www.ted.com/talks/majora_carter_s_tale...   1697550  \n",
       "4  https://www.ted.com/talks/hans_rosling_shows_t...  12005869  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted = pd.read_csv('../data_library/ted_main.csv')\n",
    "ted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2550 entries, 0 to 2549\n",
      "Data columns (total 17 columns):\n",
      " #   Column              Non-Null Count  Dtype \n",
      "---  ------              --------------  ----- \n",
      " 0   comments            2550 non-null   int64 \n",
      " 1   description         2550 non-null   object\n",
      " 2   duration            2550 non-null   int64 \n",
      " 3   event               2550 non-null   object\n",
      " 4   film_date           2550 non-null   int64 \n",
      " 5   languages           2550 non-null   int64 \n",
      " 6   main_speaker        2550 non-null   object\n",
      " 7   name                2550 non-null   object\n",
      " 8   num_speaker         2550 non-null   int64 \n",
      " 9   published_date      2550 non-null   int64 \n",
      " 10  ratings             2550 non-null   object\n",
      " 11  related_talks       2550 non-null   object\n",
      " 12  speaker_occupation  2544 non-null   object\n",
      " 13  tags                2550 non-null   object\n",
      " 14  title               2550 non-null   object\n",
      " 15  url                 2550 non-null   object\n",
      " 16  views               2550 non-null   int64 \n",
      "dtypes: int64(7), object(10)\n",
      "memory usage: 338.8+ KB\n"
     ]
    }
   ],
   "source": [
    "ted.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "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",
       "url                   0\n",
       "views                 0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Count the number of missing values in each coloum\n",
    "ted.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "ted['speaker_occupation'] = ted.speaker_occupation.fillna(ted.speaker_occupation.mode()[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>comments</th>\n",
       "      <th>duration</th>\n",
       "      <th>film_date</th>\n",
       "      <th>languages</th>\n",
       "      <th>num_speaker</th>\n",
       "      <th>published_date</th>\n",
       "      <th>views</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2550.000000</td>\n",
       "      <td>2550.000000</td>\n",
       "      <td>2.550000e+03</td>\n",
       "      <td>2550.000000</td>\n",
       "      <td>2550.000000</td>\n",
       "      <td>2.550000e+03</td>\n",
       "      <td>2.550000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>191.562353</td>\n",
       "      <td>826.510196</td>\n",
       "      <td>1.321928e+09</td>\n",
       "      <td>27.326275</td>\n",
       "      <td>1.028235</td>\n",
       "      <td>1.343525e+09</td>\n",
       "      <td>1.698297e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>282.315223</td>\n",
       "      <td>374.009138</td>\n",
       "      <td>1.197391e+08</td>\n",
       "      <td>9.563452</td>\n",
       "      <td>0.207705</td>\n",
       "      <td>9.464009e+07</td>\n",
       "      <td>2.498479e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>2.000000</td>\n",
       "      <td>135.000000</td>\n",
       "      <td>7.464960e+07</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.151367e+09</td>\n",
       "      <td>5.044300e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>63.000000</td>\n",
       "      <td>577.000000</td>\n",
       "      <td>1.257466e+09</td>\n",
       "      <td>23.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.268463e+09</td>\n",
       "      <td>7.557928e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>118.000000</td>\n",
       "      <td>848.000000</td>\n",
       "      <td>1.333238e+09</td>\n",
       "      <td>28.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.340935e+09</td>\n",
       "      <td>1.124524e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>221.750000</td>\n",
       "      <td>1046.750000</td>\n",
       "      <td>1.412964e+09</td>\n",
       "      <td>33.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.423432e+09</td>\n",
       "      <td>1.700760e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>6404.000000</td>\n",
       "      <td>5256.000000</td>\n",
       "      <td>1.503792e+09</td>\n",
       "      <td>72.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>1.506092e+09</td>\n",
       "      <td>4.722711e+07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          comments     duration     film_date    languages  num_speaker  \\\n",
       "count  2550.000000  2550.000000  2.550000e+03  2550.000000  2550.000000   \n",
       "mean    191.562353   826.510196  1.321928e+09    27.326275     1.028235   \n",
       "std     282.315223   374.009138  1.197391e+08     9.563452     0.207705   \n",
       "min       2.000000   135.000000  7.464960e+07     0.000000     1.000000   \n",
       "25%      63.000000   577.000000  1.257466e+09    23.000000     1.000000   \n",
       "50%     118.000000   848.000000  1.333238e+09    28.000000     1.000000   \n",
       "75%     221.750000  1046.750000  1.412964e+09    33.000000     1.000000   \n",
       "max    6404.000000  5256.000000  1.503792e+09    72.000000     5.000000   \n",
       "\n",
       "       published_date         views  \n",
       "count    2.550000e+03  2.550000e+03  \n",
       "mean     1.343525e+09  1.698297e+06  \n",
       "std      9.464009e+07  2.498479e+06  \n",
       "min      1.151367e+09  5.044300e+04  \n",
       "25%      1.268463e+09  7.557928e+05  \n",
       "50%      1.340935e+09  1.124524e+06  \n",
       "75%      1.423432e+09  1.700760e+06  \n",
       "max      1.506092e+09  4.722711e+07  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<seaborn.axisgrid.PairGrid at 0x7f4108348ef0>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 442176 characters]\n",
      "text/plain": [
       "<Figure size 1260x1260 with 56 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.pairplot(ted)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q1. Which talks provoke the most online discussion? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 54184 characters]\n",
      "text/plain": [
       "<Figure size 864x720 with 9 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ted.hist(edgecolor='black', linewidth=1.2, figsize=(12,10));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 82032 characters]\n",
      "text/plain": [
       "<Figure size 864x720 with 2 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.figure(figsize=(12,10))\n",
    "sns.heatmap(ted.corr() , annot=True);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 14096 characters]\n",
      "text/plain": [
       "<Figure size 864x720 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ted.languages.value_counts().hist();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>url</th>\n",
       "      <th>views</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1787</th>\n",
       "      <td>2673</td>\n",
       "      <td>Our consciousness is a fundamental aspect of o...</td>\n",
       "      <td>1117</td>\n",
       "      <td>TED2014</td>\n",
       "      <td>1395100800</td>\n",
       "      <td>33</td>\n",
       "      <td>David Chalmers</td>\n",
       "      <td>David Chalmers: How do you explain consciousness?</td>\n",
       "      <td>1</td>\n",
       "      <td>1405350484</td>\n",
       "      <td>[{'id': 25, 'name': 'OK', 'count': 280}, {'id'...</td>\n",
       "      <td>[{'id': 1308, 'hero': 'https://pe.tedcdn.com/i...</td>\n",
       "      <td>Philosopher</td>\n",
       "      <td>['brain', 'consciousness', 'neuroscience', 'ph...</td>\n",
       "      <td>How do you explain consciousness?</td>\n",
       "      <td>https://www.ted.com/talks/david_chalmers_how_d...</td>\n",
       "      <td>2162764</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>201</th>\n",
       "      <td>2877</td>\n",
       "      <td>Jill Bolte Taylor got a research opportunity f...</td>\n",
       "      <td>1099</td>\n",
       "      <td>TED2008</td>\n",
       "      <td>1204070400</td>\n",
       "      <td>49</td>\n",
       "      <td>Jill Bolte Taylor</td>\n",
       "      <td>Jill Bolte Taylor: My stroke of insight</td>\n",
       "      <td>1</td>\n",
       "      <td>1205284200</td>\n",
       "      <td>[{'id': 22, 'name': 'Fascinating', 'count': 14...</td>\n",
       "      <td>[{'id': 184, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Neuroanatomist</td>\n",
       "      <td>['biology', 'brain', 'consciousness', 'global ...</td>\n",
       "      <td>My stroke of insight</td>\n",
       "      <td>https://www.ted.com/talks/jill_bolte_taylor_s_...</td>\n",
       "      <td>21190883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>644</th>\n",
       "      <td>3356</td>\n",
       "      <td>Questions of good and evil, right and wrong ar...</td>\n",
       "      <td>1386</td>\n",
       "      <td>TED2010</td>\n",
       "      <td>1265846400</td>\n",
       "      <td>39</td>\n",
       "      <td>Sam Harris</td>\n",
       "      <td>Sam Harris: Science can answer moral questions</td>\n",
       "      <td>1</td>\n",
       "      <td>1269249180</td>\n",
       "      <td>[{'id': 8, 'name': 'Informative', 'count': 923...</td>\n",
       "      <td>[{'id': 666, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Neuroscientist, philosopher</td>\n",
       "      <td>['culture', 'evolutionary psychology', 'global...</td>\n",
       "      <td>Science can answer moral questions</td>\n",
       "      <td>https://www.ted.com/talks/sam_harris_science_c...</td>\n",
       "      <td>3433437</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\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>https://www.ted.com/talks/ken_robinson_says_sc...</td>\n",
       "      <td>47227110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>6404</td>\n",
       "      <td>Richard Dawkins urges all atheists to openly s...</td>\n",
       "      <td>1750</td>\n",
       "      <td>TED2002</td>\n",
       "      <td>1012608000</td>\n",
       "      <td>42</td>\n",
       "      <td>Richard Dawkins</td>\n",
       "      <td>Richard Dawkins: Militant atheism</td>\n",
       "      <td>1</td>\n",
       "      <td>1176689220</td>\n",
       "      <td>[{'id': 3, 'name': 'Courageous', 'count': 3236...</td>\n",
       "      <td>[{'id': 86, 'hero': 'https://pe.tedcdn.com/ima...</td>\n",
       "      <td>Evolutionary biologist</td>\n",
       "      <td>['God', 'atheism', 'culture', 'religion', 'sci...</td>\n",
       "      <td>Militant atheism</td>\n",
       "      <td>https://www.ted.com/talks/richard_dawkins_on_m...</td>\n",
       "      <td>4374792</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      comments                                        description  duration  \\\n",
       "1787      2673  Our consciousness is a fundamental aspect of o...      1117   \n",
       "201       2877  Jill Bolte Taylor got a research opportunity f...      1099   \n",
       "644       3356  Questions of good and evil, right and wrong ar...      1386   \n",
       "0         4553  Sir Ken Robinson makes an entertaining and pro...      1164   \n",
       "96        6404  Richard Dawkins urges all atheists to openly s...      1750   \n",
       "\n",
       "        event   film_date  languages       main_speaker  \\\n",
       "1787  TED2014  1395100800         33     David Chalmers   \n",
       "201   TED2008  1204070400         49  Jill Bolte Taylor   \n",
       "644   TED2010  1265846400         39         Sam Harris   \n",
       "0     TED2006  1140825600         60       Ken Robinson   \n",
       "96    TED2002  1012608000         42    Richard Dawkins   \n",
       "\n",
       "                                                   name  num_speaker  \\\n",
       "1787  David Chalmers: How do you explain consciousness?            1   \n",
       "201             Jill Bolte Taylor: My stroke of insight            1   \n",
       "644      Sam Harris: Science can answer moral questions            1   \n",
       "0             Ken Robinson: Do schools kill creativity?            1   \n",
       "96                    Richard Dawkins: Militant atheism            1   \n",
       "\n",
       "      published_date                                            ratings  \\\n",
       "1787      1405350484  [{'id': 25, 'name': 'OK', 'count': 280}, {'id'...   \n",
       "201       1205284200  [{'id': 22, 'name': 'Fascinating', 'count': 14...   \n",
       "644       1269249180  [{'id': 8, 'name': 'Informative', 'count': 923...   \n",
       "0         1151367060  [{'id': 7, 'name': 'Funny', 'count': 19645}, {...   \n",
       "96        1176689220  [{'id': 3, 'name': 'Courageous', 'count': 3236...   \n",
       "\n",
       "                                          related_talks  \\\n",
       "1787  [{'id': 1308, 'hero': 'https://pe.tedcdn.com/i...   \n",
       "201   [{'id': 184, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "644   [{'id': 666, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "0     [{'id': 865, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "96    [{'id': 86, 'hero': 'https://pe.tedcdn.com/ima...   \n",
       "\n",
       "               speaker_occupation  \\\n",
       "1787                  Philosopher   \n",
       "201                Neuroanatomist   \n",
       "644   Neuroscientist, philosopher   \n",
       "0                 Author/educator   \n",
       "96         Evolutionary biologist   \n",
       "\n",
       "                                                   tags  \\\n",
       "1787  ['brain', 'consciousness', 'neuroscience', 'ph...   \n",
       "201   ['biology', 'brain', 'consciousness', 'global ...   \n",
       "644   ['culture', 'evolutionary psychology', 'global...   \n",
       "0     ['children', 'creativity', 'culture', 'dance',...   \n",
       "96    ['God', 'atheism', 'culture', 'religion', 'sci...   \n",
       "\n",
       "                                   title  \\\n",
       "1787   How do you explain consciousness?   \n",
       "201                 My stroke of insight   \n",
       "644   Science can answer moral questions   \n",
       "0            Do schools kill creativity?   \n",
       "96                      Militant atheism   \n",
       "\n",
       "                                                    url     views  \n",
       "1787  https://www.ted.com/talks/david_chalmers_how_d...   2162764  \n",
       "201   https://www.ted.com/talks/jill_bolte_taylor_s_...  21190883  \n",
       "644   https://www.ted.com/talks/sam_harris_science_c...   3433437  \n",
       "0     https://www.ted.com/talks/ken_robinson_says_sc...  47227110  \n",
       "96    https://www.ted.com/talks/richard_dawkins_on_m...   4374792  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.sort_values('comments').tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Limitations of this approach:\n",
    "1. sub comments (nested comments).\n",
    "1. how long its being online.\n",
    "### To correct this behavior, one solution is to normalise comments by views"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>url</th>\n",
       "      <th>views</th>\n",
       "      <th>comments_per_view</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>954</th>\n",
       "      <td>2492</td>\n",
       "      <td>Janet Echelman found her true voice as an arti...</td>\n",
       "      <td>566</td>\n",
       "      <td>TED2011</td>\n",
       "      <td>1299110400</td>\n",
       "      <td>35</td>\n",
       "      <td>Janet Echelman</td>\n",
       "      <td>Janet Echelman: Taking imagination seriously</td>\n",
       "      <td>1</td>\n",
       "      <td>1307489760</td>\n",
       "      <td>[{'id': 23, 'name': 'Jaw-dropping', 'count': 3...</td>\n",
       "      <td>[{'id': 453, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Artist</td>\n",
       "      <td>['art', 'cities', 'culture', 'data', 'design',...</td>\n",
       "      <td>Taking imagination seriously</td>\n",
       "      <td>https://www.ted.com/talks/janet_echelman\\n</td>\n",
       "      <td>1832930</td>\n",
       "      <td>0.001360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694</th>\n",
       "      <td>1502</td>\n",
       "      <td>Filmmaker Sharmeen Obaid-Chinoy takes on a ter...</td>\n",
       "      <td>489</td>\n",
       "      <td>TED2010</td>\n",
       "      <td>1265760000</td>\n",
       "      <td>32</td>\n",
       "      <td>Sharmeen Obaid-Chinoy</td>\n",
       "      <td>Sharmeen Obaid-Chinoy: Inside a school for sui...</td>\n",
       "      <td>1</td>\n",
       "      <td>1274865960</td>\n",
       "      <td>[{'id': 23, 'name': 'Jaw-dropping', 'count': 3...</td>\n",
       "      <td>[{'id': 171, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Filmmaker</td>\n",
       "      <td>['TED Fellows', 'children', 'culture', 'film',...</td>\n",
       "      <td>Inside a school for suicide bombers</td>\n",
       "      <td>https://www.ted.com/talks/sharmeen_obaid_chino...</td>\n",
       "      <td>1057238</td>\n",
       "      <td>0.001421</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>6404</td>\n",
       "      <td>Richard Dawkins urges all atheists to openly s...</td>\n",
       "      <td>1750</td>\n",
       "      <td>TED2002</td>\n",
       "      <td>1012608000</td>\n",
       "      <td>42</td>\n",
       "      <td>Richard Dawkins</td>\n",
       "      <td>Richard Dawkins: Militant atheism</td>\n",
       "      <td>1</td>\n",
       "      <td>1176689220</td>\n",
       "      <td>[{'id': 3, 'name': 'Courageous', 'count': 3236...</td>\n",
       "      <td>[{'id': 86, 'hero': 'https://pe.tedcdn.com/ima...</td>\n",
       "      <td>Evolutionary biologist</td>\n",
       "      <td>['God', 'atheism', 'culture', 'religion', 'sci...</td>\n",
       "      <td>Militant atheism</td>\n",
       "      <td>https://www.ted.com/talks/richard_dawkins_on_m...</td>\n",
       "      <td>4374792</td>\n",
       "      <td>0.001464</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>803</th>\n",
       "      <td>834</td>\n",
       "      <td>David Bismark demos a new system for voting th...</td>\n",
       "      <td>422</td>\n",
       "      <td>TEDGlobal 2010</td>\n",
       "      <td>1279065600</td>\n",
       "      <td>36</td>\n",
       "      <td>David Bismark</td>\n",
       "      <td>David Bismark: E-voting without fraud</td>\n",
       "      <td>1</td>\n",
       "      <td>1288685640</td>\n",
       "      <td>[{'id': 25, 'name': 'OK', 'count': 111}, {'id'...</td>\n",
       "      <td>[{'id': 803, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Voting system designer</td>\n",
       "      <td>['culture', 'democracy', 'design', 'global iss...</td>\n",
       "      <td>E-voting without fraud</td>\n",
       "      <td>https://www.ted.com/talks/david_bismark_e_voti...</td>\n",
       "      <td>543551</td>\n",
       "      <td>0.001534</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>744</th>\n",
       "      <td>649</td>\n",
       "      <td>Hours before New York lawmakers rejected a key...</td>\n",
       "      <td>453</td>\n",
       "      <td>New York State Senate</td>\n",
       "      <td>1259712000</td>\n",
       "      <td>0</td>\n",
       "      <td>Diane J. Savino</td>\n",
       "      <td>Diane J. Savino: The case for same-sex marriage</td>\n",
       "      <td>1</td>\n",
       "      <td>1282062180</td>\n",
       "      <td>[{'id': 25, 'name': 'OK', 'count': 100}, {'id'...</td>\n",
       "      <td>[{'id': 217, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Senator</td>\n",
       "      <td>['God', 'LGBT', 'culture', 'government', 'law'...</td>\n",
       "      <td>The case for same-sex marriage</td>\n",
       "      <td>https://www.ted.com/talks/diane_j_savino_the_c...</td>\n",
       "      <td>292395</td>\n",
       "      <td>0.002220</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     comments                                        description  duration  \\\n",
       "954      2492  Janet Echelman found her true voice as an arti...       566   \n",
       "694      1502  Filmmaker Sharmeen Obaid-Chinoy takes on a ter...       489   \n",
       "96       6404  Richard Dawkins urges all atheists to openly s...      1750   \n",
       "803       834  David Bismark demos a new system for voting th...       422   \n",
       "744       649  Hours before New York lawmakers rejected a key...       453   \n",
       "\n",
       "                     event   film_date  languages           main_speaker  \\\n",
       "954                TED2011  1299110400         35         Janet Echelman   \n",
       "694                TED2010  1265760000         32  Sharmeen Obaid-Chinoy   \n",
       "96                 TED2002  1012608000         42        Richard Dawkins   \n",
       "803         TEDGlobal 2010  1279065600         36          David Bismark   \n",
       "744  New York State Senate  1259712000          0        Diane J. Savino   \n",
       "\n",
       "                                                  name  num_speaker  \\\n",
       "954       Janet Echelman: Taking imagination seriously            1   \n",
       "694  Sharmeen Obaid-Chinoy: Inside a school for sui...            1   \n",
       "96                   Richard Dawkins: Militant atheism            1   \n",
       "803              David Bismark: E-voting without fraud            1   \n",
       "744    Diane J. Savino: The case for same-sex marriage            1   \n",
       "\n",
       "     published_date                                            ratings  \\\n",
       "954      1307489760  [{'id': 23, 'name': 'Jaw-dropping', 'count': 3...   \n",
       "694      1274865960  [{'id': 23, 'name': 'Jaw-dropping', 'count': 3...   \n",
       "96       1176689220  [{'id': 3, 'name': 'Courageous', 'count': 3236...   \n",
       "803      1288685640  [{'id': 25, 'name': 'OK', 'count': 111}, {'id'...   \n",
       "744      1282062180  [{'id': 25, 'name': 'OK', 'count': 100}, {'id'...   \n",
       "\n",
       "                                         related_talks  \\\n",
       "954  [{'id': 453, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "694  [{'id': 171, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "96   [{'id': 86, 'hero': 'https://pe.tedcdn.com/ima...   \n",
       "803  [{'id': 803, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "744  [{'id': 217, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "\n",
       "         speaker_occupation  \\\n",
       "954                  Artist   \n",
       "694               Filmmaker   \n",
       "96   Evolutionary biologist   \n",
       "803  Voting system designer   \n",
       "744                 Senator   \n",
       "\n",
       "                                                  tags  \\\n",
       "954  ['art', 'cities', 'culture', 'data', 'design',...   \n",
       "694  ['TED Fellows', 'children', 'culture', 'film',...   \n",
       "96   ['God', 'atheism', 'culture', 'religion', 'sci...   \n",
       "803  ['culture', 'democracy', 'design', 'global iss...   \n",
       "744  ['God', 'LGBT', 'culture', 'government', 'law'...   \n",
       "\n",
       "                                   title  \\\n",
       "954         Taking imagination seriously   \n",
       "694  Inside a school for suicide bombers   \n",
       "96                      Militant atheism   \n",
       "803               E-voting without fraud   \n",
       "744       The case for same-sex marriage   \n",
       "\n",
       "                                                   url    views  \\\n",
       "954         https://www.ted.com/talks/janet_echelman\\n  1832930   \n",
       "694  https://www.ted.com/talks/sharmeen_obaid_chino...  1057238   \n",
       "96   https://www.ted.com/talks/richard_dawkins_on_m...  4374792   \n",
       "803  https://www.ted.com/talks/david_bismark_e_voti...   543551   \n",
       "744  https://www.ted.com/talks/diane_j_savino_the_c...   292395   \n",
       "\n",
       "     comments_per_view  \n",
       "954           0.001360  \n",
       "694           0.001421  \n",
       "96            0.001464  \n",
       "803           0.001534  \n",
       "744           0.002220  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# creating a new column 'comments_per_view'\n",
    "ted['comments_per_view'] = ted.comments / ted.views\n",
    "\n",
    "# interpretation: for every view of the same-sex marriage talk, there are 0.002 comments\n",
    "ted.sort_values('comments_per_view').tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>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>url</th>\n",
       "      <th>views</th>\n",
       "      <th>comments_per_view</th>\n",
       "      <th>views_per_comment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>744</th>\n",
       "      <td>649</td>\n",
       "      <td>Hours before New York lawmakers rejected a key...</td>\n",
       "      <td>453</td>\n",
       "      <td>New York State Senate</td>\n",
       "      <td>1259712000</td>\n",
       "      <td>0</td>\n",
       "      <td>Diane J. Savino</td>\n",
       "      <td>Diane J. Savino: The case for same-sex marriage</td>\n",
       "      <td>1</td>\n",
       "      <td>1282062180</td>\n",
       "      <td>[{'id': 25, 'name': 'OK', 'count': 100}, {'id'...</td>\n",
       "      <td>[{'id': 217, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Senator</td>\n",
       "      <td>['God', 'LGBT', 'culture', 'government', 'law'...</td>\n",
       "      <td>The case for same-sex marriage</td>\n",
       "      <td>https://www.ted.com/talks/diane_j_savino_the_c...</td>\n",
       "      <td>292395</td>\n",
       "      <td>0.002220</td>\n",
       "      <td>450.531587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>803</th>\n",
       "      <td>834</td>\n",
       "      <td>David Bismark demos a new system for voting th...</td>\n",
       "      <td>422</td>\n",
       "      <td>TEDGlobal 2010</td>\n",
       "      <td>1279065600</td>\n",
       "      <td>36</td>\n",
       "      <td>David Bismark</td>\n",
       "      <td>David Bismark: E-voting without fraud</td>\n",
       "      <td>1</td>\n",
       "      <td>1288685640</td>\n",
       "      <td>[{'id': 25, 'name': 'OK', 'count': 111}, {'id'...</td>\n",
       "      <td>[{'id': 803, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Voting system designer</td>\n",
       "      <td>['culture', 'democracy', 'design', 'global iss...</td>\n",
       "      <td>E-voting without fraud</td>\n",
       "      <td>https://www.ted.com/talks/david_bismark_e_voti...</td>\n",
       "      <td>543551</td>\n",
       "      <td>0.001534</td>\n",
       "      <td>651.739808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>6404</td>\n",
       "      <td>Richard Dawkins urges all atheists to openly s...</td>\n",
       "      <td>1750</td>\n",
       "      <td>TED2002</td>\n",
       "      <td>1012608000</td>\n",
       "      <td>42</td>\n",
       "      <td>Richard Dawkins</td>\n",
       "      <td>Richard Dawkins: Militant atheism</td>\n",
       "      <td>1</td>\n",
       "      <td>1176689220</td>\n",
       "      <td>[{'id': 3, 'name': 'Courageous', 'count': 3236...</td>\n",
       "      <td>[{'id': 86, 'hero': 'https://pe.tedcdn.com/ima...</td>\n",
       "      <td>Evolutionary biologist</td>\n",
       "      <td>['God', 'atheism', 'culture', 'religion', 'sci...</td>\n",
       "      <td>Militant atheism</td>\n",
       "      <td>https://www.ted.com/talks/richard_dawkins_on_m...</td>\n",
       "      <td>4374792</td>\n",
       "      <td>0.001464</td>\n",
       "      <td>683.134291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>694</th>\n",
       "      <td>1502</td>\n",
       "      <td>Filmmaker Sharmeen Obaid-Chinoy takes on a ter...</td>\n",
       "      <td>489</td>\n",
       "      <td>TED2010</td>\n",
       "      <td>1265760000</td>\n",
       "      <td>32</td>\n",
       "      <td>Sharmeen Obaid-Chinoy</td>\n",
       "      <td>Sharmeen Obaid-Chinoy: Inside a school for sui...</td>\n",
       "      <td>1</td>\n",
       "      <td>1274865960</td>\n",
       "      <td>[{'id': 23, 'name': 'Jaw-dropping', 'count': 3...</td>\n",
       "      <td>[{'id': 171, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Filmmaker</td>\n",
       "      <td>['TED Fellows', 'children', 'culture', 'film',...</td>\n",
       "      <td>Inside a school for suicide bombers</td>\n",
       "      <td>https://www.ted.com/talks/sharmeen_obaid_chino...</td>\n",
       "      <td>1057238</td>\n",
       "      <td>0.001421</td>\n",
       "      <td>703.886818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>954</th>\n",
       "      <td>2492</td>\n",
       "      <td>Janet Echelman found her true voice as an arti...</td>\n",
       "      <td>566</td>\n",
       "      <td>TED2011</td>\n",
       "      <td>1299110400</td>\n",
       "      <td>35</td>\n",
       "      <td>Janet Echelman</td>\n",
       "      <td>Janet Echelman: Taking imagination seriously</td>\n",
       "      <td>1</td>\n",
       "      <td>1307489760</td>\n",
       "      <td>[{'id': 23, 'name': 'Jaw-dropping', 'count': 3...</td>\n",
       "      <td>[{'id': 453, 'hero': 'https://pe.tedcdn.com/im...</td>\n",
       "      <td>Artist</td>\n",
       "      <td>['art', 'cities', 'culture', 'data', 'design',...</td>\n",
       "      <td>Taking imagination seriously</td>\n",
       "      <td>https://www.ted.com/talks/janet_echelman\\n</td>\n",
       "      <td>1832930</td>\n",
       "      <td>0.001360</td>\n",
       "      <td>735.525682</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     comments                                        description  duration  \\\n",
       "744       649  Hours before New York lawmakers rejected a key...       453   \n",
       "803       834  David Bismark demos a new system for voting th...       422   \n",
       "96       6404  Richard Dawkins urges all atheists to openly s...      1750   \n",
       "694      1502  Filmmaker Sharmeen Obaid-Chinoy takes on a ter...       489   \n",
       "954      2492  Janet Echelman found her true voice as an arti...       566   \n",
       "\n",
       "                     event   film_date  languages           main_speaker  \\\n",
       "744  New York State Senate  1259712000          0        Diane J. Savino   \n",
       "803         TEDGlobal 2010  1279065600         36          David Bismark   \n",
       "96                 TED2002  1012608000         42        Richard Dawkins   \n",
       "694                TED2010  1265760000         32  Sharmeen Obaid-Chinoy   \n",
       "954                TED2011  1299110400         35         Janet Echelman   \n",
       "\n",
       "                                                  name  num_speaker  \\\n",
       "744    Diane J. Savino: The case for same-sex marriage            1   \n",
       "803              David Bismark: E-voting without fraud            1   \n",
       "96                   Richard Dawkins: Militant atheism            1   \n",
       "694  Sharmeen Obaid-Chinoy: Inside a school for sui...            1   \n",
       "954       Janet Echelman: Taking imagination seriously            1   \n",
       "\n",
       "     published_date                                            ratings  \\\n",
       "744      1282062180  [{'id': 25, 'name': 'OK', 'count': 100}, {'id'...   \n",
       "803      1288685640  [{'id': 25, 'name': 'OK', 'count': 111}, {'id'...   \n",
       "96       1176689220  [{'id': 3, 'name': 'Courageous', 'count': 3236...   \n",
       "694      1274865960  [{'id': 23, 'name': 'Jaw-dropping', 'count': 3...   \n",
       "954      1307489760  [{'id': 23, 'name': 'Jaw-dropping', 'count': 3...   \n",
       "\n",
       "                                         related_talks  \\\n",
       "744  [{'id': 217, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "803  [{'id': 803, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "96   [{'id': 86, 'hero': 'https://pe.tedcdn.com/ima...   \n",
       "694  [{'id': 171, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "954  [{'id': 453, 'hero': 'https://pe.tedcdn.com/im...   \n",
       "\n",
       "         speaker_occupation  \\\n",
       "744                 Senator   \n",
       "803  Voting system designer   \n",
       "96   Evolutionary biologist   \n",
       "694               Filmmaker   \n",
       "954                  Artist   \n",
       "\n",
       "                                                  tags  \\\n",
       "744  ['God', 'LGBT', 'culture', 'government', 'law'...   \n",
       "803  ['culture', 'democracy', 'design', 'global iss...   \n",
       "96   ['God', 'atheism', 'culture', 'religion', 'sci...   \n",
       "694  ['TED Fellows', 'children', 'culture', 'film',...   \n",
       "954  ['art', 'cities', 'culture', 'data', 'design',...   \n",
       "\n",
       "                                   title  \\\n",
       "744       The case for same-sex marriage   \n",
       "803               E-voting without fraud   \n",
       "96                      Militant atheism   \n",
       "694  Inside a school for suicide bombers   \n",
       "954         Taking imagination seriously   \n",
       "\n",
       "                                                   url    views  \\\n",
       "744  https://www.ted.com/talks/diane_j_savino_the_c...   292395   \n",
       "803  https://www.ted.com/talks/david_bismark_e_voti...   543551   \n",
       "96   https://www.ted.com/talks/richard_dawkins_on_m...  4374792   \n",
       "694  https://www.ted.com/talks/sharmeen_obaid_chino...  1057238   \n",
       "954         https://www.ted.com/talks/janet_echelman\\n  1832930   \n",
       "\n",
       "     comments_per_view  views_per_comment  \n",
       "744           0.002220         450.531587  \n",
       "803           0.001534         651.739808  \n",
       "96            0.001464         683.134291  \n",
       "694           0.001421         703.886818  \n",
       "954           0.001360         735.525682  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create a column name 'views_per_comment'\n",
    "ted['views_per_comment'] = ted.views / ted.comments\n",
    "\n",
    "# interpretation: 1 out of every 450 people leave a comment\n",
    "ted.sort_values('views_per_comment').head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 41408 characters]\n",
      "text/plain": [
       "<Figure size 864x720 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.plot(list(ted.index) , list(ted.views));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 45260 characters]\n",
      "text/plain": [
       "<Figure size 864x720 with 4 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.subplot(2,2,1)\n",
    "ted.comments.plot(kind='hist')\n",
    "\n",
    "# modifying plot to be more informative\n",
    "plt.subplot(2,2,2)\n",
    "ted[ted.comments < 1000].comments.plot(kind='hist')\n",
    "\n",
    "# increase the number of bins\n",
    "plt.subplot(2,2,3)\n",
    "ted.loc[ted.comments < 1000 , 'comments'].plot(kind='hist' , bins= 20)\n",
    "\n",
    "# boxplot\n",
    "plt.subplot(2,2,4)\n",
    "ted[ted.comments < 1000].comments.plot(kind='box');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1084         TEDMED 2011\n",
       "1905        TEDxCanberra\n",
       "1605      TEDGlobal 2013\n",
       "2359    TEDxBeaconStreet\n",
       "1028            TEDxUIUC\n",
       "260              TED1998\n",
       "2308              TEDNYC\n",
       "2283              TEDNYC\n",
       "1190             TED2012\n",
       "428              EG 2008\n",
       "Name: event, dtype: object"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## Plot number of talks that took place every year\n",
    "ted.event.sample(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can'nt rely on event because most of the events doesn't have the year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0   1970-01-01 00:00:01.140825600\n",
       "1   1970-01-01 00:00:01.140825600\n",
       "2   1970-01-01 00:00:01.140739200\n",
       "3   1970-01-01 00:00:01.140912000\n",
       "4   1970-01-01 00:00:01.140566400\n",
       "Name: film_date, dtype: datetime64[ns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.to_datetime(ted.film_date).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Result does'nt looks good"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>event</th>\n",
       "      <th>film_datetime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>451</th>\n",
       "      <td>EG 2008</td>\n",
       "      <td>2008-12-12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1345</th>\n",
       "      <td>TEDGlobal 2012</td>\n",
       "      <td>2012-06-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1747</th>\n",
       "      <td>TED2014</td>\n",
       "      <td>2014-03-14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>235</th>\n",
       "      <td>TED2008</td>\n",
       "      <td>2008-02-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>983</th>\n",
       "      <td>TEDGlobal 2011</td>\n",
       "      <td>2011-07-13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>TED2002</td>\n",
       "      <td>2002-02-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2039</th>\n",
       "      <td>TED2015</td>\n",
       "      <td>2015-03-17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>906</th>\n",
       "      <td>TED2011</td>\n",
       "      <td>2011-03-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2034</th>\n",
       "      <td>TEDWomen 2015</td>\n",
       "      <td>2015-05-28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1624</th>\n",
       "      <td>TED@BCG Singapore</td>\n",
       "      <td>2013-10-10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  event film_datetime\n",
       "451             EG 2008    2008-12-12\n",
       "1345     TEDGlobal 2012    2012-06-27\n",
       "1747            TED2014    2014-03-14\n",
       "235             TED2008    2008-02-29\n",
       "983      TEDGlobal 2011    2011-07-13\n",
       "194             TED2002    2002-02-02\n",
       "2039            TED2015    2015-03-17\n",
       "906             TED2011    2011-03-02\n",
       "2034      TEDWomen 2015    2015-05-28\n",
       "1624  TED@BCG Singapore    2013-10-10"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted['film_datetime'] = pd.to_datetime(ted.film_date , unit='s')\n",
    "\n",
    "# verify that event name matches film_datetime for a random sample\n",
    "ted[['event' , 'film_datetime']].sample(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2013    270\n",
       "2011    270\n",
       "2010    267\n",
       "2012    267\n",
       "2016    246\n",
       "2015    239\n",
       "2014    237\n",
       "2009    232\n",
       "2007    114\n",
       "2017     98\n",
       "2008     84\n",
       "2005     66\n",
       "2006     50\n",
       "2003     33\n",
       "2004     33\n",
       "2002     27\n",
       "1998      6\n",
       "2001      5\n",
       "1983      1\n",
       "1991      1\n",
       "1994      1\n",
       "1990      1\n",
       "1984      1\n",
       "1972      1\n",
       "Name: film_datetime, dtype: int64"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.film_datetime.dt.year.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 30944 characters]\n",
      "text/plain": [
       "<Figure size 864x720 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ted.film_datetime.dt.year.value_counts().plot();"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1972, 2017)"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 30080 characters]\n",
      "text/plain": [
       "<Figure size 864x720 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ted.film_datetime.dt.year.value_counts().sort_index().plot();\n",
    "plt.xlim(ted.film_datetime.dt.year.min() , ted.film_datetime.dt.year.max())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Q2.What are the best events in ted history to attend?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "TED2014             84\n",
       "TED2009             83\n",
       "TED2013             77\n",
       "TED2016             77\n",
       "TED2015             75\n",
       "                    ..\n",
       "TEDxParis 2012       1\n",
       "TEDxNorrkoping       1\n",
       "DLD 2007             1\n",
       "TED Fellows 2015     1\n",
       "TEDxO'Porto          1\n",
       "Name: event, Length: 355, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.event.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "event\n",
       "AORN Congress                  149818.0\n",
       "Arbejdsglaede Live             971594.0\n",
       "BBC TV                         521974.0\n",
       "Bowery Poetry Club             676741.0\n",
       "Business Innovation Factory    304086.0\n",
       "Name: views, dtype: float64"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.groupby('event').views.mean().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "event\n",
       "TEDxNorrkoping        6569493.0\n",
       "TEDxCreativeCoast     8444981.0\n",
       "TEDxBloomington       9484259.5\n",
       "TEDxHouston          16140250.5\n",
       "TEDxPuget Sound      34309432.0\n",
       "Name: views, dtype: float64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# find the largest values, but we don't know how many talks are being averaged\n",
    "ted.groupby('event').views.mean().sort_values().tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>event</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>TEDxNorrkoping</th>\n",
       "      <td>1</td>\n",
       "      <td>6569493.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEDxCreativeCoast</th>\n",
       "      <td>1</td>\n",
       "      <td>8444981.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEDxBloomington</th>\n",
       "      <td>2</td>\n",
       "      <td>9484259.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEDxHouston</th>\n",
       "      <td>2</td>\n",
       "      <td>16140250.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEDxPuget Sound</th>\n",
       "      <td>1</td>\n",
       "      <td>34309432.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   count        mean\n",
       "event                               \n",
       "TEDxNorrkoping         1   6569493.0\n",
       "TEDxCreativeCoast      1   8444981.0\n",
       "TEDxBloomington        2   9484259.5\n",
       "TEDxHouston            2  16140250.5\n",
       "TEDxPuget Sound        1  34309432.0"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.groupby('event').views.agg(['count' , 'mean']).sort_values('mean').tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>event</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>TED2006</th>\n",
       "      <td>45</td>\n",
       "      <td>3.274345e+06</td>\n",
       "      <td>147345533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TED2015</th>\n",
       "      <td>75</td>\n",
       "      <td>2.011017e+06</td>\n",
       "      <td>150826305</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TEDGlobal 2013</th>\n",
       "      <td>66</td>\n",
       "      <td>2.584163e+06</td>\n",
       "      <td>170554736</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TED2014</th>\n",
       "      <td>84</td>\n",
       "      <td>2.072874e+06</td>\n",
       "      <td>174121423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TED2013</th>\n",
       "      <td>77</td>\n",
       "      <td>2.302700e+06</td>\n",
       "      <td>177307937</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                count          mean        sum\n",
       "event                                         \n",
       "TED2006            45  3.274345e+06  147345533\n",
       "TED2015            75  2.011017e+06  150826305\n",
       "TEDGlobal 2013     66  2.584163e+06  170554736\n",
       "TED2014            84  2.072874e+06  174121423\n",
       "TED2013            77  2.302700e+06  177307937"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.groupby('event').views.agg(['count', 'mean' , 'sum']).sort_values('sum').tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 83964 characters]\n",
      "text/plain": [
       "<Figure size 864x720 with 2 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "plt.subplot(1,2,1)\n",
    "ted.event.value_counts()[:20].plot(kind='bar')\n",
    "\n",
    "plt.subplot(1,2,2)\n",
    "ted.groupby('event').views.mean().sort_values(ascending=False)[:20].plot(kind='bar');"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Unpacking the ratings data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "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",
       "Name: ratings, dtype: object"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.ratings.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "str"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(ted.ratings[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# convert this into something useful using Python's ast module (Abstract Syntax Tree)\n",
    "import ast\n",
    "\n",
    "# literal_eval() allows you to evaluate a string containing a Python literal or container\n",
    "ast.literal_eval('[1, 2, 3]')\n",
    "\n",
    "# if you have a string representation of something, you can retrieve what it actually represents\n",
    "type(ast.literal_eval('[1, 2, 3]'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'id': 7, 'name': 'Funny', 'count': 19645},\n",
       " {'id': 1, 'name': 'Beautiful', 'count': 4573},\n",
       " {'id': 9, 'name': 'Ingenious', 'count': 6073},\n",
       " {'id': 3, 'name': 'Courageous', 'count': 3253},\n",
       " {'id': 11, 'name': 'Longwinded', 'count': 387},\n",
       " {'id': 2, 'name': 'Confusing', 'count': 242},\n",
       " {'id': 8, 'name': 'Informative', 'count': 7346},\n",
       " {'id': 22, 'name': 'Fascinating', 'count': 10581},\n",
       " {'id': 21, 'name': 'Unconvincing', 'count': 300},\n",
       " {'id': 24, 'name': 'Persuasive', 'count': 10704},\n",
       " {'id': 23, 'name': 'Jaw-dropping', 'count': 4439},\n",
       " {'id': 25, 'name': 'OK', 'count': 1174},\n",
       " {'id': 26, 'name': 'Obnoxious', 'count': 209},\n",
       " {'id': 10, 'name': 'Inspiring', 'count': 24924}]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ast.literal_eval(ted.ratings[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a function to convert elements of ratings series from str to list\n",
    "def str_to_list(ratings_str):\n",
    "    return ast.literal_eval(ratings_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'id': 7, 'name': 'Funny', 'count': 19645},\n",
       " {'id': 1, 'name': 'Beautiful', 'count': 4573},\n",
       " {'id': 9, 'name': 'Ingenious', 'count': 6073},\n",
       " {'id': 3, 'name': 'Courageous', 'count': 3253},\n",
       " {'id': 11, 'name': 'Longwinded', 'count': 387},\n",
       " {'id': 2, 'name': 'Confusing', 'count': 242},\n",
       " {'id': 8, 'name': 'Informative', 'count': 7346},\n",
       " {'id': 22, 'name': 'Fascinating', 'count': 10581},\n",
       " {'id': 21, 'name': 'Unconvincing', 'count': 300},\n",
       " {'id': 24, 'name': 'Persuasive', 'count': 10704},\n",
       " {'id': 23, 'name': 'Jaw-dropping', 'count': 4439},\n",
       " {'id': 25, 'name': 'OK', 'count': 1174},\n",
       " {'id': 26, 'name': 'Obnoxious', 'count': 209},\n",
       " {'id': 10, 'name': 'Inspiring', 'count': 24924}]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test the function\n",
    "str_to_list(ted.ratings[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "ted['ratings_list'] = ted.ratings.apply(str_to_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(ted.ratings_list[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'id': 7, 'name': 'Funny', 'count': 19645},\n",
       " {'id': 1, 'name': 'Beautiful', 'count': 4573},\n",
       " {'id': 9, 'name': 'Ingenious', 'count': 6073},\n",
       " {'id': 3, 'name': 'Courageous', 'count': 3253},\n",
       " {'id': 11, 'name': 'Longwinded', 'count': 387},\n",
       " {'id': 2, 'name': 'Confusing', 'count': 242},\n",
       " {'id': 8, 'name': 'Informative', 'count': 7346},\n",
       " {'id': 22, 'name': 'Fascinating', 'count': 10581},\n",
       " {'id': 21, 'name': 'Unconvincing', 'count': 300},\n",
       " {'id': 24, 'name': 'Persuasive', 'count': 10704},\n",
       " {'id': 23, 'name': 'Jaw-dropping', 'count': 4439},\n",
       " {'id': 25, 'name': 'OK', 'count': 1174},\n",
       " {'id': 26, 'name': 'Obnoxious', 'count': 209},\n",
       " {'id': 10, 'name': 'Inspiring', 'count': 24924}]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.ratings_list[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* for each talk, calculate the percentage of ratings that were negative\n",
    "* for each talk, calculate the average number of ratings it received per day since it was published"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_num_ratings(list_of_dicts):\n",
    "    num = 0\n",
    "    for i in list_of_dicts:\n",
    "        num = num + i['count']\n",
    "        \n",
    "    return num"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "93850"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test the fuction\n",
    "get_num_ratings(ted.ratings_list[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    93850\n",
       "1     2936\n",
       "2     2824\n",
       "3     3728\n",
       "4    25620\n",
       "Name: ratings_list, dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# apply it to every element in the Series\n",
    "ted.ratings_list.apply(get_num_ratings).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "ted['num_ratings'] = ted.ratings_list.apply(get_num_ratings)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count     2550.000000\n",
       "mean      2436.408235\n",
       "std       4226.795631\n",
       "min         68.000000\n",
       "25%        870.750000\n",
       "50%       1452.500000\n",
       "75%       2506.750000\n",
       "max      93850.000000\n",
       "Name: num_ratings, dtype: float64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ted.num_ratings.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.Which occupations deliver the funniest TED talks on average?\n",
    "* for each talk, calculate the most frequent rating\n",
    "* for each talk, clean the occupation data so that there's only one occupation per talk"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True    2550\n",
       "Name: ratings, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check ratings (not ratings_list) to see if \"Funny\" is always a rating type\n",
    "ted.ratings.str.contains('Funny').value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define a custom function\n",
    "def get_funny_ratings(list_of_dicts):\n",
    "    for d in list_of_dicts:\n",
    "        if d['name'] == 'Funny':\n",
    "            return d['count']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "59"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "get_funny_ratings(ted.ratings_list[3])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "# apply it to every element in the Series\n",
    "ted['funny_ratings'] = ted.ratings_list.apply(get_funny_ratings)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calculate the percentage of ratings that are funny"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "ted['funny_rate'] = ted.funny_ratings / ted.num_ratings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1849                       Science humorist\n",
       "337                                Comedian\n",
       "124     Performance poet, multimedia artist\n",
       "315                                  Expert\n",
       "1168             Social energy entrepreneur\n",
       "1468                          Ornithologist\n",
       "595                  Comedian, voice artist\n",
       "1534                         Cartoon editor\n",
       "97                                 Satirist\n",
       "2297                          Actor, writer\n",
       "568                                Comedian\n",
       "675                          Data scientist\n",
       "21                     Humorist, web artist\n",
       "194                                Jugglers\n",
       "2273                    Comedian and writer\n",
       "2114                    Comedian and writer\n",
       "173                                Investor\n",
       "747                                Comedian\n",
       "1398                               Comedian\n",
       "685             Actor, comedian, playwright\n",
       "Name: speaker_occupation, dtype: object"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# \"gut check\" that this calculation makes sense by examining the occupations of the funniest talks\n",
    "ted.sort_values('funny_rate').speaker_occupation.tail(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2549               Game designer\n",
       "1612                   Biologist\n",
       "612                     Sculptor\n",
       "998               Penguin expert\n",
       "593                     Engineer\n",
       "284               Space activist\n",
       "1041         Biomedical engineer\n",
       "1618      Spinal cord researcher\n",
       "2132    Computational geneticist\n",
       "442                     Sculptor\n",
       "426              Author, thinker\n",
       "458                     Educator\n",
       "2437      Environmental engineer\n",
       "1491             Photojournalist\n",
       "1893     Forensic anthropologist\n",
       "783             Marine biologist\n",
       "195                    Kenyan MP\n",
       "772             HIV/AIDS fighter\n",
       "788            Building activist\n",
       "936                Neuroengineer\n",
       "Name: speaker_occupation, dtype: object"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# examine the occupations of the least funny talks\n",
    "ted.sort_values('funny_rate').speaker_occupation.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "speaker_occupation\n",
       "Comedian                       0.512457\n",
       "Actor, writer                  0.515152\n",
       "Actor, comedian, playwright    0.558107\n",
       "Jugglers                       0.566828\n",
       "Comedian and writer            0.602085\n",
       "Name: funny_rate, dtype: float64"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate the mean funny rate for each occupation\n",
    "ted.groupby('speaker_occupation').funny_rate.mean().sort_values().tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count       2550\n",
       "unique      1458\n",
       "top       Writer\n",
       "freq          51\n",
       "Name: speaker_occupation, dtype: object"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# however, most of the occupations have a sample size of 1\n",
    "ted.speaker_occupation.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Focus on occupations that are well-represented in the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Writer                                 51\n",
       "Designer                               34\n",
       "Artist                                 34\n",
       "Journalist                             33\n",
       "Entrepreneur                           31\n",
       "                                       ..\n",
       "Artist and technologist                 1\n",
       "Perceptual navigation specialist        1\n",
       "Systems biologist                       1\n",
       "Planetary and atmospheric scientist     1\n",
       "Hedge fund chair                        1\n",
       "Name: speaker_occupation, Length: 1458, dtype: int64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# count how many times each occupation appears\n",
    "ted.speaker_occupation.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "# value_counts() outputs a pandas Series, thus we can use pandas to manipulate the output\n",
    "occupation_counts = ted.speaker_occupation.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Writer            51\n",
       "Designer          34\n",
       "Artist            34\n",
       "Journalist        33\n",
       "Entrepreneur      31\n",
       "                  ..\n",
       "Violinist          5\n",
       "Surgeon            5\n",
       "Data scientist     5\n",
       "Producer           5\n",
       "Researcher         5\n",
       "Name: speaker_occupation, Length: 68, dtype: int64"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# show occupations which appear at least 5 times\n",
    "occupation_counts[occupation_counts >= 5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Writer', 'Designer', 'Artist', 'Journalist', 'Entrepreneur',\n",
       "       'Architect', 'Inventor', 'Psychologist', 'Photographer', 'Filmmaker',\n",
       "       'Economist', 'Neuroscientist', 'Author', 'Educator', 'Roboticist',\n",
       "       'Philosopher', 'Biologist', 'Physicist', 'Marine biologist', 'Musician',\n",
       "       'Technologist', 'Global health expert; data visionary', 'Activist',\n",
       "       'Historian', 'Philanthropist', 'Poet', 'Oceanographer',\n",
       "       'Behavioral economist', 'Graphic designer', 'Singer/songwriter',\n",
       "       'Astronomer', 'Social psychologist', 'Futurist', 'Engineer', 'Novelist',\n",
       "       'Computer scientist', 'Mathematician', 'Astrophysicist',\n",
       "       'Techno-illusionist', 'Singer-songwriter', 'Legal activist',\n",
       "       'Climate advocate', 'Reporter', 'Social entrepreneur',\n",
       "       'Evolutionary biologist', 'Writer, activist', 'Comedian',\n",
       "       'Performance poet, multimedia artist', 'Photojournalist',\n",
       "       'Sound consultant', 'Science writer', 'Chef',\n",
       "       'Environmentalist, futurist',\n",
       "       'Investor and advocate for moral leadership', 'Tech visionary',\n",
       "       'Paleontologist', 'Cartoonist', 'Physician', 'Musician, activist',\n",
       "       'Chemist', 'Sculptor', 'Game designer', 'Social Media Theorist',\n",
       "       'Violinist', 'Surgeon', 'Data scientist', 'Producer', 'Researcher'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# save the index of this Series\n",
    "top_occupations = occupation_counts[occupation_counts >= 5].index\n",
    "top_occupations"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Re-analyze the funny rate by occupation (for top occupations only)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(792, 24)"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter DataFrame to include only those occupations\n",
    "ted_top_occupations = ted[ted.speaker_occupation.isin(top_occupations)]\n",
    "ted_top_occupations.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "speaker_occupation\n",
       "Surgeon                                       0.002465\n",
       "Physician                                     0.004515\n",
       "Photojournalist                               0.004908\n",
       "Investor and advocate for moral leadership    0.005198\n",
       "Photographer                                  0.007152\n",
       "                                                ...   \n",
       "Data scientist                                0.184076\n",
       "Producer                                      0.202531\n",
       "Singer/songwriter                             0.252205\n",
       "Performance poet, multimedia artist           0.306468\n",
       "Comedian                                      0.512457\n",
       "Name: funny_rate, Length: 68, dtype: float64"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# redo the previous groupby\n",
    "ted_top_occupations.groupby('speaker_occupation').funny_rate.mean().sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
