friendscombined.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# This is where the magic happens\n",
    "\n",
    "- We assign characters to laughter instances (decide who was responsible for the laughter)\n",
    "- We create a custom subtitle file for each episode to visually inspect (demo) the output\n",
    "- We create charts and other visualizations for the writeup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "import numpy as np\n",
    "import plotly.graph_objects as go\n",
    "import plotly.express as px\n",
    "import chart_studio.plotly as py\n",
    "import chart_studio.tools as tls\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Choosing colors to represent each main character for the graphs later on"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# graph colors\n",
    "chandlercolor = 'indianred'\n",
    "joeycolor = 'dodgerblue'\n",
    "monicacolor = 'gold'\n",
    "phoebecolor = 'orchid'\n",
    "rachelcolor = 'lightskyblue'\n",
    "rosscolor = 'limegreen'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plotly sign-in"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plotly sign-in\n",
    "username = 'redacted'\n",
    "api_key = 'redacted'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Building dict with seasons and episodes in each season to loop through"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seasepdict = {}\n",
    "\n",
    "conn = sqlite3.connect('/Users/Jack/Developer/friends/friendsdb.sqlite')\n",
    "cur = conn.cursor()\n",
    "\n",
    "cur.execute('''SELECT DISTINCT season FROM laughs''')\n",
    "for value in cur:\n",
    "    seasepdict[value[0]] = 0\n",
    "\n",
    "for season, episodes in seasepdict.items():\n",
    "    cur.execute('''SELECT MAX(episode) FROM laughs WHERE season=?''', (season,))\n",
    "    for value in cur:\n",
    "        seasepdict[season] = value[0]\n",
    "print(seasepdict)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Utility functions for processing SQLite data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def timeconvert(string):\n",
    "    hours = int(string[0:2])\n",
    "    mins = int(string[3:5])\n",
    "    secs = int(string[6:8])\n",
    "    ms = int(string[9:])\n",
    "    intoms = ms + (secs * 1000) + (mins * 60 * 1000) + (hours * 60 * 60 * 1000)\n",
    "    return intoms\n",
    "\n",
    "def linesplit(linenum, begtime, endtime):\n",
    "    if not isinstance(linenum, int) and \"A\" in linenum:\n",
    "        endtime = begtime + ((endtime - begtime)/2)\n",
    "    elif not isinstance(linenum, int) and \"B\" in linenum:\n",
    "        begtime = endtime - ((endtime - begtime)/2)\n",
    "    return begtime, endtime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Laughter Attribution\n",
    "\n",
    "- We process the SQLite data then we use a minimization function to decide which character is responsible for each laugh\n",
    "- The minimization function takes the ending timestamp of each subtitle line and the beginning timestamp of each laugh and finds the subtitle line with the smallest distance to the beginning of a laugh, then assigns that character to the laugh"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "speakdict = {}\n",
    "laughdict = {}\n",
    "\n",
    "for season, episodes in seasepdict.items():\n",
    "    for epnum in range(0, episodes):\n",
    "        epnum += 1\n",
    "#         if season == 1 and epnum == 1: # remove later\n",
    "        speaklist = []\n",
    "        laughlist = []\n",
    "        cur.execute('''SELECT starttime, endtime, x, y, linenum FROM subs WHERE season=? AND episode=?''', (season, epnum))\n",
    "        for info in cur:\n",
    "            begtime = timeconvert(info[0])\n",
    "            endtime = timeconvert(info[1])\n",
    "            x = info[2].lower()\n",
    "            y = info[3].lower()\n",
    "            linenum = info[4]\n",
    "            begtime, endtime = linesplit(linenum, begtime, endtime)\n",
    "            if y != '':\n",
    "                templist = [begtime, endtime, y, linenum]\n",
    "            else:\n",
    "                templist = [begtime, endtime, x, linenum]\n",
    "            speaklist.append(templist)\n",
    "#             print(speaklist)\n",
    "        cur.execute('''SELECT beg, end FROM laughs WHERE season=? AND episode=?''', (season, epnum))\n",
    "        for laughtimes in cur:\n",
    "            templist = [laughtimes[0], laughtimes[1], None]\n",
    "            laughlist.append(templist)\n",
    "#             print(laughlist)\n",
    "        # need to fix lines with A and B\n",
    "        for laugh in laughlist:\n",
    "            closest = 5000\n",
    "            for info in speaklist:\n",
    "                laughstart = laugh[0]\n",
    "                subend = info[1]\n",
    "                char = info[2]\n",
    "                proximity = abs(laughstart - subend)\n",
    "                if proximity < closest:\n",
    "                    closest = proximity\n",
    "                    laugh[2] = char\n",
    "        speakdict[str(season).zfill(2) + str(epnum).zfill(2)] = speaklist\n",
    "        laughdict[str(season).zfill(2) + str(epnum).zfill(2)] = laughlist             \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Inputting laughter attributions into SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for seasep, list in laughdict.items():\n",
    "    season = int(seasep[0:2])\n",
    "    episode = int(seasep[2:4])\n",
    "    for items in list:\n",
    "        begtime = items[0]\n",
    "        endtime = items[1]\n",
    "        char = items[2]\n",
    "        if char:\n",
    "#             print(char)\n",
    "            cur.execute('''UPDATE laughs SET char=? WHERE season=? AND episode=? AND beg=? ''',\n",
    "                (char, season, episode, begtime))\n",
    "            conn.commit()\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Preprocessing for custom subtitles inputs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "customsubdict = {}\n",
    "\n",
    "for seasep, speaklist in speakdict.items():\n",
    "#     if seasep == \"0101\":\n",
    "    epsubdict = {}\n",
    "    for speakinfo in speaklist:\n",
    "        speakbeg = str(int(speakinfo[0]))\n",
    "        speakend = str(int(speakinfo[1]))\n",
    "        speakchar = speakinfo[2]\n",
    "        if speakbeg in epsubdict:\n",
    "            speakbeg = str(int(speakbeg) + 1)\n",
    "        epsubdict[speakbeg] = [\"speak\", speakend, speakchar]\n",
    "    for laughinfo in laughdict[seasep]:\n",
    "        laughstart = str(int(laughinfo[0]))\n",
    "        laughend = str(int(laughinfo[1]))\n",
    "        laughchar = laughinfo[2]\n",
    "        if laughstart in epsubdict:\n",
    "            laughstart = str(int(laughstart) + 1)\n",
    "        epsubdict[laughstart] = ['laugh', laughend, laughchar]\n",
    "    customsubdict[seasep] = epsubdict\n",
    "# print(customsubdict)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Utility function to convert timestamps into SRT file (subtitle file) format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def converttosubtime(timestring):\n",
    "    ms = timestring[-3:]\n",
    "    rawsecs = timestring[:-3]\n",
    "    # using int to round down after dividing by 60\n",
    "    mins = int(int(rawsecs) / 60)\n",
    "    secs = int(rawsecs) % 60\n",
    "    hours = int(int(mins) / 60)\n",
    "    subtime = str(hours).zfill(2) + ':' + str(mins).zfill(2) + ':' + str(secs).zfill(2) + ',' + str(ms).zfill(3)\n",
    "    return subtime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating custom subtitles file for each episode\n",
    "\n",
    "SRT files are just text files with a standardized format for displaying subtitles on a video. We convert our timestamps and speaking lines into SRT format and we can feed it right in to any video file. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "folder = '/Users/Jack/Developer/friends/customsubs/'\n",
    "\n",
    "for seasep, epsubdict in customsubdict.items():\n",
    "    beglist = []\n",
    "    for begtimekey, _ in epsubdict.items():\n",
    "        begtimeint = int(begtimekey)\n",
    "        beglist.append(begtimeint)\n",
    "    beglist.sort()\n",
    "    file = open(folder + seasep + \".srt\", \"w\")\n",
    "    count = 1\n",
    "    for begtime in beglist:\n",
    "        begtime = str(begtime)\n",
    "        linetype = epsubdict[begtime][0]\n",
    "        endtime = epsubdict[begtime][1]\n",
    "        char = epsubdict[begtime][2]\n",
    "        if char:\n",
    "            char = char.capitalize()\n",
    "        else:\n",
    "            char = \"Undetermined\"\n",
    "        file.write(str(count))\n",
    "        count += 1\n",
    "        file.write('\\n')\n",
    "        file.write(converttosubtime(begtime))\n",
    "        file.write(' --> ')\n",
    "        file.write(converttosubtime(endtime))\n",
    "        file.write('\\n')\n",
    "        if linetype == 'speak':\n",
    "            file.write(char + ' is speaking')\n",
    "        elif linetype == 'laugh':\n",
    "            file.write('Laughter (' + char + ' is funny)')\n",
    "        file.write('\\n')\n",
    "        file.write('\\n')\n",
    "    file.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Getting the actual statistics for the article and graphs\n",
    "\n",
    "### Getting total number of laughs per character"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('''SELECT char FROM laughs''')\n",
    "\n",
    "laughscount = { 'chandler' : 0,\n",
    "                'ross' : 0,\n",
    "                'phoebe' : 0,\n",
    "                'monica' : 0,\n",
    "                'rachel' : 0,\n",
    "                'joey' : 0}\n",
    "\n",
    "for row in cur:\n",
    "    dbchar = row[0]\n",
    "    for char, _ in laughscount.items():\n",
    "        if dbchar == char:\n",
    "            laughscount[char] += 1\n",
    "print(laughscount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting total amount of laughter generated by each character"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('''SELECT char, beg, end FROM laughs''')\n",
    "\n",
    "laughtime = { 'chandler' : 0.0,\n",
    "                'ross' : 0.0,\n",
    "                'phoebe' : 0.0,\n",
    "                'monica' : 0.0,\n",
    "                'rachel' : 0.0,\n",
    "                'joey' : 0.0}\n",
    "\n",
    "for row in cur:\n",
    "    dbchar = row[0]\n",
    "    begtime = float(row[1]) / 1000\n",
    "    endtime = float(row[2]) / 1000\n",
    "    for char, _ in laughtime.items():\n",
    "        if dbchar == char:\n",
    "            laughtime[char] += (endtime - begtime)\n",
    "print(laughtime)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting length of laughter on average after each funny character line"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "avglaughlen = {}\n",
    "\n",
    "for char, laughcount in laughscount.items():\n",
    "    laughlen = laughtime[char]\n",
    "    lenperlaugh = laughlen / laughcount\n",
    "    avglaughlen[char] = lenperlaugh\n",
    "print(avglaughlen)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting funniest, least funny episodes by total amount of laughter and total number of laughs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('''SELECT season, episode, beg, end FROM laughs''')\n",
    "eplaughamount = {}\n",
    "eplaughnumber = {}\n",
    "\n",
    "for row in cur:\n",
    "    season = str(row[0]).zfill(2)\n",
    "    episode = str(row[1]).zfill(2)\n",
    "    seasep = season + episode\n",
    "    begtime = int(row[2]) / 1000\n",
    "    endtime = int(row[3]) / 1000\n",
    "    laughlen = endtime - begtime\n",
    "    \n",
    "    if seasep not in eplaughnumber:\n",
    "        eplaughnumber[seasep] = 1\n",
    "    else:\n",
    "        eplaughnumber[seasep] += 1\n",
    "        \n",
    "    if seasep not in eplaughamount:\n",
    "        eplaughamount[seasep] = laughlen\n",
    "    else:\n",
    "        eplaughamount[seasep] += laughlen\n",
    "# print(eplaughamount)\n",
    "# print(eplaughnumber)\n",
    "\n",
    "fewestlaughs = None\n",
    "mostlaughs = None\n",
    "leastlaughter = None\n",
    "mostlaughter = None\n",
    "\n",
    "for seasep, laughnumber in eplaughnumber.items():\n",
    "    \n",
    "    if not fewestlaughs:\n",
    "        fewestlaughs = laughnumber\n",
    "    elif laughnumber < fewestlaughs:\n",
    "        fewestlaughs = laughnumber\n",
    "        fewestlaughep = seasep\n",
    "    if not mostlaughs:\n",
    "        mostlaughs = laughnumber\n",
    "    elif laughnumber > mostlaughs and seasep not in ['0212', '0923', '1017']:\n",
    "        mostlaughs = laughnumber\n",
    "        mostlaughsep = seasep\n",
    "        \n",
    "\n",
    "for seasep, laughamount in eplaughamount.items():       \n",
    "    if not leastlaughter:\n",
    "        leastlaughter = laughamount\n",
    "    elif laughamount < leastlaughter:\n",
    "        leastlaughter = laughamount\n",
    "        leastlaughterep = seasep\n",
    "    if not mostlaughter:\n",
    "        mostlaughter = laughamount\n",
    "    elif laughamount > mostlaughter and seasep not in ['0212', '0923', '1017']:\n",
    "        mostlaughter = laughamount\n",
    "        mostlaughterep = seasep\n",
    "        \n",
    "# print(fewestlaughep, fewestlaughs)\n",
    "# print(mostlaughsep, mostlaughs)\n",
    "\n",
    "# print(leastlaughterep, leastlaughter)\n",
    "# print(mostlaughterep, mostlaughter)\n",
    "    \n",
    "    \n",
    "    \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting how funny each season was (by total laughter amount)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('''SELECT season, beg, end FROM laughs''')\n",
    "seasonlaughamount = {}\n",
    "\n",
    "for row in cur:\n",
    "    season = str(row[0]).zfill(2)\n",
    "    begtime = int(row[1]) / 1000\n",
    "    endtime = int(row[2]) / 1000\n",
    "    laughlen = endtime - begtime\n",
    "    \n",
    "    if season not in seasonlaughamount:\n",
    "        seasonlaughamount[season] = laughlen\n",
    "    else:\n",
    "        seasonlaughamount[season] += laughlen\n",
    "# print(seasonlaughamount)\n",
    "\n",
    "seasepcount = []\n",
    "for season, numepisodes in seasepdict.items():\n",
    "    seasepcount.append(numepisodes)\n",
    "medianepcount = np.median(seasepcount)\n",
    "# print(medianepcount)\n",
    "\n",
    "# Normalizing season 10 because it was shorter\n",
    "# subtracting 1 because season 10 already had its double episode counted as ep 17 (don't want to extrapolate that)\n",
    "season10multiplier = (medianepcount -1) / seasepdict[10]\n",
    "oldseason10laughamount = seasonlaughamount['10']\n",
    "seasonlaughamount['10'] = oldseason10laughamount * season10multiplier\n",
    "print(seasonlaughamount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plotting funniest and least funny seasons"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "seasonlaughamountlist = []\n",
    "for key, value in seasonlaughamount.items():\n",
    "    templist = [int(key), int(round(value, 0))]\n",
    "    seasonlaughamountlist.append(templist)\n",
    "seasonlaughamountlist.sort(key = lambda x: x[0], reverse=False)\n",
    "# print(seasonlaughamountlist)\n",
    "seasonlist = [x[0] for x in seasonlaughamountlist]\n",
    "laughamountlist = [x[1] for x in seasonlaughamountlist]\n",
    "\n",
    "# colors = ['yellow'] * 10\n",
    "\n",
    "\n",
    "seasonlaughrank = go.Figure(data=[go.Bar(\n",
    "    x=seasonlist,\n",
    "    y=laughamountlist,\n",
    "    marker={'color': laughamountlist,\n",
    "               'colorscale': 'Tealgrn'}\n",
    ")])\n",
    "\n",
    "\n",
    "seasonlaughrank.update_xaxes(dtick=1)\n",
    "seasonlaughrank.update_yaxes(dtick=2000)\n",
    "seasonlaughrank.update_layout(yaxis_tickformat = 's')\n",
    "seasonlaughrank.update_layout(title_text='Seconds of Laughter per Season')\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Signing in to plotly and uploading chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tls.set_credentials_file(username=username, api_key=api_key)\n",
    "seasonlaughrankcode = py.plot(seasonlaughrank, filename = 'season_laugh_rank', auto_open=True)\n",
    "tls.get_embed(seasonlaughrankcode) #change to your url"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Getting longest continuous laughter in the show (funniest moment)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "cur.execute('''SELECT season, episode, char, beg, end FROM laughs''')\n",
    "\n",
    "longestlaugh = 0\n",
    "for row in cur:\n",
    "    season = row[0]\n",
    "    episode = row[1]\n",
    "    char = row[2]\n",
    "    beg = int(row[3])\n",
    "    end = int(row[4])\n",
    "    laughlen = end - beg\n",
    "    if laughlen > longestlaugh:\n",
    "        longestlaugh = laughlen\n",
    "        longlaughinfo = [season, episode, char, beg, end]\n",
    "print(longestlaugh)\n",
    "print(longlaughinfo)\n",
    "# Its actually Joey who is funny but the laughter is really 30 secs long and it is Joey chugging a gallon of milk\n",
    "# because he put it on his resume"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding who spoke the most lines in subtitles file (out of the 6 main characters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "subslinecount = { 'chandler' : 0,\n",
    "                'ross' : 0,\n",
    "                'phoebe' : 0,\n",
    "                'monica' : 0,\n",
    "                'rachel' : 0,\n",
    "                'joey' : 0}\n",
    "\n",
    "cur.execute('''SELECT x, y FROM subs''')\n",
    "\n",
    "for row in cur:\n",
    "    char = 'None'\n",
    "    x = row[0].lower()\n",
    "    y = row[1].lower()\n",
    "    if y:\n",
    "        char = y\n",
    "    else:\n",
    "        char = x\n",
    "    if char in subslinecount:\n",
    "        subslinecount[char] += 1\n",
    "print(subslinecount)\n",
    "        \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding who spoke the most lines in script file (out of the 6 main characters)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "scriptlinecount = { 'chandler' : 0,\n",
    "                'ross' : 0,\n",
    "                'phoebe' : 0,\n",
    "                'monica' : 0,\n",
    "                'rachel' : 0,\n",
    "                'joey' : 0}\n",
    "\n",
    "cur.execute('''SELECT char FROM script''')\n",
    "\n",
    "for row in cur:\n",
    "    char = row[0].lower()\n",
    "\n",
    "    if char in scriptlinecount:\n",
    "        scriptlinecount[char] += 1\n",
    "print(scriptlinecount)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plotting who spoke most lines in the script file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "scriptlinecountlist = []\n",
    "for key, value in scriptlinecount.items():\n",
    "    templist = [key.capitalize(), value]\n",
    "    scriptlinecountlist.append(templist)\n",
    "scriptlinecountlist.sort(key = lambda x: x[1], reverse=True)\n",
    "# print(scriptlinecountlist)\n",
    "charlist = [x[0] for x in scriptlinecountlist]\n",
    "linecountlist = [x[1] for x in scriptlinecountlist]\n",
    "\n",
    "colors = [rachelcolor, rosscolor, chandlercolor, monicacolor, joeycolor, phoebecolor]\n",
    "\n",
    "\n",
    "charlinecount = go.Figure(data=[go.Bar(\n",
    "    x=charlist,\n",
    "    y=linecountlist,\n",
    "    marker_color=colors # marker color can be a single color value or an iterable\n",
    ")])\n",
    "charlinecount.update_layout(title_text='Total Lines in Script')\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Signing in to plotly and uploading chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tls.set_credentials_file(username=username, api_key=api_key)\n",
    "charlinecountcode = py.plot(charlinecount, filename = 'char_line_count', auto_open=True)\n",
    "tls.get_embed(charlinecountcode) #change to your url"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding who is funniest (by laughter time) per line spoken\n",
    "\n",
    "(using script for now as it is 100% accurate instead of 98% accurate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "funniestperline = {}\n",
    "\n",
    "for char, linecount in scriptlinecount.items():\n",
    "    secslaughperline = laughtime[char] / linecount\n",
    "    funniestperline[char] = secslaughperline\n",
    "print(funniestperline)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### FUNNIEST CHARACTER (seconds of laughter per line spoken)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "laughsecsperlinelist = []\n",
    "for key, value in funniestperline.items():\n",
    "    templist = [key.capitalize(), value]\n",
    "    laughsecsperlinelist.append(templist)\n",
    "laughsecsperlinelist.sort(key = lambda x: x[1], reverse=True)\n",
    "# print(laughsecsperlinelist)\n",
    "charlist = [x[0] for x in laughsecsperlinelist]\n",
    "laughsecslist = [x[1] for x in laughsecsperlinelist]\n",
    "\n",
    "colors = [joeycolor, chandlercolor, phoebecolor, rosscolor, rachelcolor, monicacolor]\n",
    "\n",
    "\n",
    "laughsecsperline = go.Figure(data=[go.Bar(\n",
    "    x=charlist,\n",
    "    y=laughsecslist,\n",
    "    marker_color=colors # marker color can be a single color value or an iterable\n",
    ")])\n",
    "laughsecsperline.update_layout(title_text='Seconds of Laughter per Line')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Signing in to plotly and uploading chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tls.set_credentials_file(username=username, api_key=api_key)\n",
    "laughsecsperlinecode = py.plot(laughsecsperline, filename = 'laughsecsperline', auto_open=True)\n",
    "tls.get_embed(laughsecsperlinecode) #change to your url"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding who is funniest (by num of laughs) per LINE spoken\n",
    "\n",
    "(using script for now as it is 100% accurate compared to 98%)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "laughsperline = {}\n",
    "\n",
    "for char, linecount in scriptlinecount.items():\n",
    "    secslaughperline = laughscount[char] / linecount\n",
    "    laughsperline[char] = secslaughperline\n",
    "print(laughsperline)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How much non-verbal humor goes unaccounted for?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('''SELECT char FROM laughs''')\n",
    "\n",
    "nonverbalcount = 0\n",
    "totalcount = 0\n",
    "for row in cur:\n",
    "    totalcount += 1\n",
    "    if row[0] == None:\n",
    "        nonverbalcount += 1\n",
    "print(nonverbalcount / totalcount)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Finding confidence intervals for our final prediction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pchar = 0.948 # probability of attributing correct character to laughter instance\n",
    "plaugh = 0.946 # probability of correctly labeling each timestep of audio\n",
    "ptotal = pchar * plaugh # probability of getting both correct at each timestep\n",
    "timesteplen = 11.8 # ms per timestep\n",
    "z = 1.96 # constant for 95% confidence interval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Confidence Intervals for Predicted Values\n",
    "# https://www.coursera.org/lecture/inferential-statistics/3-08-ci-and-pi-for-predicted-values-77RCt\n",
    "# How to calculate residual standard deviation\n",
    "# https://www.investopedia.com/terms/r/residual-standard-deviation.asp\n",
    "\n",
    "# Reworking formula (1 - 0.9025) squared for all samples doesn't have same magnitude as 1 squared for negative samples\n",
    "# I think reworking the formula in this way is how the measurements are intended to be\n",
    "# using avgvalue instead of 1 for the error because if we miss a laugh, we miss by the avgvalue (~ 2 secs), not 1\n",
    "for char, laughtotal in laughscount.items():\n",
    "    avglaughvalue = avglaughlen[char]\n",
    "    part1 = (avglaughvalue) ** 2\n",
    "    sumofpart1s = part1 * (laughtotal * (1 - ptotal))\n",
    "#     print(sumofpart1s)\n",
    "    resSD = np.sqrt(sumofpart1s / (laughtotal - 2))\n",
    "#     print(resSD)\n",
    "    CI = z * (resSD / np.sqrt(laughtotal))\n",
    "#     print(CI)\n",
    "    print(\"Confidence interval for avg laughter for \" + char + \" is plus or minus \" + str(CI) + \" seconds\")\n",
    "    for funniestlist in laughsecsperlinelist:\n",
    "        if funniestlist[0].lower() == char:\n",
    "            funniestlist.append(CI)\n",
    "print(laughsecsperlinelist)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# For laughter by season\n",
    "avgvalue = 1 # when we get a binary value wrong, by definition we are off by 1\n",
    "errorlist = []\n",
    "\n",
    "# Reworking formula (1 - 0.95) squared for all samples doesn't have same magnitude as 1 squared for negative samples\n",
    "# I think reworking the formula in this way is how the measurements are intended to be\n",
    "for i, seasonlaughter in enumerate(laughamountlist):\n",
    "    season = i + 1\n",
    "    laughtertimesteps = seasonlaughter * (1000 / timesteplen) # taking seconds and turning them into 11.8 ms timesteps\n",
    "    part1 = (avgvalue - 0) ** 2\n",
    "    sumofpart1s = part1 * (laughtertimesteps * (1 - .95))\n",
    "#     print(sumofpart1s)\n",
    "    resSD = np.sqrt(sumofpart1s / (laughtertimesteps - 2))\n",
    "#     print(resSD)\n",
    "    CI = z * (resSD / np.sqrt(laughtertimesteps))\n",
    "#     print(CI)\n",
    "\n",
    "    # CI for sum is equal to CI for mean * number of timesteps\n",
    "    # https://stats.stackexchange.com/questions/304999/confidence-intervals-of-the-sum\n",
    "\n",
    "    sumCI = CI * laughtertimesteps\n",
    "#     print(sumCI)\n",
    "    sumCIseconds = sumCI * timesteplen / 1000\n",
    "    print(\"CI in seconds for laughter for season \" + str(season) + \" is \" + str(sumCIseconds))\n",
    "    errorlist.append(sumCIseconds)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Graphing the Confidence Intervals"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "seasonlaughrankerrorbars = go.Figure(data=[go.Bar(\n",
    "    x=seasonlist,\n",
    "    y=laughamountlist,\n",
    "    marker={'color': laughamountlist,\n",
    "               'colorscale': 'Tealgrn'},\n",
    "    error_y=dict(type='data', array=errorlist)\n",
    ")])\n",
    "\n",
    "\n",
    "seasonlaughrankerrorbars.update_xaxes(dtick=1)\n",
    "seasonlaughrankerrorbars.update_yaxes(dtick=2000)\n",
    "seasonlaughrankerrorbars.update_layout(yaxis_tickformat = 's')\n",
    "seasonlaughrankerrorbars.update_layout(title_text='Seconds of Laughter per Season')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tls.set_credentials_file(username=username, api_key=api_key)\n",
    "seasonlaughrankerrorbarscode = py.plot(seasonlaughrankerrorbars, filename = 'seasonlaughrankerrorbars', auto_open=True)\n",
    "tls.get_embed(seasonlaughrankerrorbarscode) #change to your url"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Funniest character chart with error bars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "colors = [joeycolor, chandlercolor, phoebecolor, rosscolor, rachelcolor, monicacolor]\n",
    "errors = [x[2] for x in laughsecsperlinelist]\n",
    "\n",
    "laughsecsperlineerrorbars = go.Figure(data=[go.Bar(\n",
    "    x=charlist,\n",
    "    y=laughsecslist,\n",
    "    marker_color=colors, # marker color can be a single color value or an iterable\n",
    "    error_y=dict(type='data', array=errors)\n",
    ")])\n",
    "laughsecsperlineerrorbars.update_layout(title_text='Seconds of Laughter per Line')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tls.set_credentials_file(username=username, api_key=api_key)\n",
    "laughsecsperlineerrorbarscode = py.plot(laughsecsperlineerrorbars, filename = 'laughsecsperlineerrorbars', auto_open=True)\n",
    "tls.get_embed(laughsecsperlineerrorbarscode) #change to your url"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Testing Character Laughter by Season"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cur.execute('''SELECT season, char, beg, end FROM laughs''')\n",
    "\n",
    "laughtimebyseason = { 'chandler' : {},\n",
    "                'ross' : {},\n",
    "                'phoebe' : {},\n",
    "                'monica' : {},\n",
    "                'rachel' : {},\n",
    "                'joey' : {}}\n",
    "\n",
    "for row in cur:\n",
    "    season = str(row[0])\n",
    "    dbchar = row[1]\n",
    "    begtime = float(row[2]) / 1000\n",
    "    endtime = float(row[3]) / 1000\n",
    "    for char, _ in laughtimebyseason.items():\n",
    "        if dbchar == char:\n",
    "            if season not in laughtimebyseason[char]:\n",
    "                laughtimebyseason[char][season] = 0\n",
    "            laughlen = endtime - begtime\n",
    "            laughtimebyseason[char][season] += laughlen\n",
    "# print(laughtimebyseason)\n",
    "\n",
    "cur.execute('''SELECT season, char FROM script''')\n",
    "    \n",
    "scriptlinesbyseason = { 'chandler' : {},\n",
    "                'ross' : {},\n",
    "                'phoebe' : {},\n",
    "                'monica' : {},\n",
    "                'rachel' : {},\n",
    "                'joey' : {}} \n",
    "\n",
    "for row in cur:\n",
    "    season = str(row[0])\n",
    "    dbchar = row[1].lower()\n",
    "    if dbchar in scriptlinesbyseason:\n",
    "        if season not in scriptlinesbyseason[dbchar]:\n",
    "            scriptlinesbyseason[dbchar][season] = 0\n",
    "        scriptlinesbyseason[dbchar][season] += 1\n",
    "# print(scriptlinesbyseason)\n",
    "\n",
    "for char, seasondict in laughtimebyseason.items():\n",
    "    for season, value in seasondict.items():\n",
    "        laughtimebyseason[char][season] = round(value / scriptlinesbyseason[char][season], 4)\n",
    "# print(laughtimebyseason)\n",
    "\n",
    "# Getting laugh time proportions\n",
    "seasontotals = {}\n",
    "for char, seasondict in laughtimebyseason.items():\n",
    "    for season, value in seasondict.items():\n",
    "        if season not in seasontotals:\n",
    "            seasontotals[season] = 0\n",
    "        seasontotals[season] += value\n",
    "# print(seasontotals)\n",
    "for char, seasondict in laughtimebyseason.items():\n",
    "    for season, value in seasondict.items():\n",
    "        laughtimebyseason[char][season] = round(value / seasontotals[season], 4)\n",
    "# print(laughtimebyseason)\n",
    "\n",
    "# Convert to DF\n",
    "dictforDF = {\"Season\" : [], \n",
    "             \"Friend\" : [], \n",
    "             \"% Share of Laughter per Line\" : []}\n",
    "for char, seasondict in laughtimebyseason.items():\n",
    "    for season, value in seasondict.items():\n",
    "        dictforDF[\"Season\"].append(int(season))\n",
    "        dictforDF[\"Friend\"].append(char.capitalize())\n",
    "        dictforDF[\"% Share of Laughter per Line\"].append(value)\n",
    "# print(dictforDF)\n",
    "df = pd.DataFrame(dictforDF)\n",
    "dfsorted = df.sort_values(by=['Season'])\n",
    "# print(dfsorted)\n",
    "\n",
    "# Build and show graph\n",
    "laughterperlineovertime = px.line(dfsorted, x='Season', y='% Share of Laughter per Line', color='Friend', \n",
    "              color_discrete_map={\"Joey\":joeycolor,\n",
    "                                  \"Chandler\":chandlercolor,\n",
    "                                  \"Monica\":monicacolor,\n",
    "                                  \"Rachel\":rachelcolor,\n",
    "                                  \"Phoebe\":phoebecolor,\n",
    "                                  \"Ross\":rosscolor,\n",
    "                                 })\n",
    "laughterperlineovertime.update_layout({\n",
    "'plot_bgcolor': 'rgba(0, 0, 0, 0)',\n",
    "'paper_bgcolor': 'rgba(0, 0, 0, 0)',\n",
    "'title_text':'Laughter Generated By Season',\n",
    "'legend_title_text' : None,\n",
    "'legend_orientation' : 'v',\n",
    "'yaxis': {\n",
    "    'tickformat': ',.0%'\n",
    "  }\n",
    "})\n",
    "laughterperlineovertime.update_xaxes(dtick=1)\n",
    "laughterperlineovertime.show()\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tls.set_credentials_file(username=username, api_key=api_key)\n",
    "laughterperlineovertimecode = py.plot(laughterperlineovertime, filename = 'laughterperlineovertime', auto_open=True)\n",
    "tls.get_embed(laughterperlineovertimecode) #change to your url"
   ]
  },
  {
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}