7_Web_Apps.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Web Apps with Spyre\n",
    "---\n",
    "\n",
    "Once you've collected data and done some analysis you'll probably want to present it to others. As we've already seen, Jupyter notebooks are a great way to do this, but if we wanted to make our results more interactive we could put the data in a web application.\n",
    "\n",
    "Spyre is a python library that is used to quickly build data focussed web applications. Spyre does not come pre-installed with the Anaconda distribution of Python so checkout the [Spyre github page and README](https://github.com/adamhajari/spyre) for instruction on how to install it. The README also contains a couple of short examples that you should try yourself before continuing with this module.\n",
    "\n",
    "Once you've successfully gotten the examples for the spyre README to work, let's again turn to the `nbs.db` sqlite database to make a new app. We'll be pulling in data from two tables and joining the results in pandas. Start by querying all rows from the `artists` and `social_data` tables. Let's work on the components of the app here and then put them into a separate script."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>500</td>\n",
       "      <td>The Scenic</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>710</td>\n",
       "      <td>Our Family Name</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>744</td>\n",
       "      <td>Khamelien</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>786</td>\n",
       "      <td>Death in the Park</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>818</td>\n",
       "      <td>enter the collector</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id                 name\n",
       "0  500           The Scenic\n",
       "1  710      Our Family Name\n",
       "2  744            Khamelien\n",
       "3  786    Death in the Park\n",
       "4  818  enter the collector"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import sqlite3 as lite\n",
    "import pandas as pd\n",
    "con = lite.connect('nbs.db')\n",
    "artists_query = \"select id, name from artists\"\n",
    "artists_df = pd.read_sql(artists_query, con)\n",
    "\n",
    "socail_query = \"select * from social_data\"\n",
    "social_df = pd.read_sql(socail_query, con)\n",
    "artists_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>artist_id</th>\n",
       "      <th>FaceBookPageLikes</th>\n",
       "      <th>TwitterFollowers</th>\n",
       "      <th>YouTubeViews</th>\n",
       "      <th>InstagramLikes</th>\n",
       "      <th>InstagramFollowers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>500</td>\n",
       "      <td>4797.0</td>\n",
       "      <td>1395.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>744</td>\n",
       "      <td>NaN</td>\n",
       "      <td>224.0</td>\n",
       "      <td>44287.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>952</td>\n",
       "      <td>4142.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>130279.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2966.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1086</td>\n",
       "      <td>28980.0</td>\n",
       "      <td>43405.0</td>\n",
       "      <td>402054.0</td>\n",
       "      <td>56832.0</td>\n",
       "      <td>7653.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   artist_id  FaceBookPageLikes  TwitterFollowers  YouTubeViews  \\\n",
       "0        500             4797.0            1395.0           NaN   \n",
       "1        744                NaN             224.0       44287.0   \n",
       "2        952             4142.0              64.0      130279.0   \n",
       "3       1017                NaN               NaN        2966.0   \n",
       "4       1086            28980.0           43405.0      402054.0   \n",
       "\n",
       "   InstagramLikes  InstagramFollowers  \n",
       "0             NaN                 NaN  \n",
       "1             NaN                 NaN  \n",
       "2             NaN                 NaN  \n",
       "3             NaN                 NaN  \n",
       "4         56832.0              7653.0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "social_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can now use pandas' `merge` function to merge these two dataframes so we have the social data and the artists' names in one place. The `artist_id`s from social_df match the `id`s from artist_df so we'll merge on those two columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>artist_id</th>\n",
       "      <th>FaceBookPageLikes</th>\n",
       "      <th>TwitterFollowers</th>\n",
       "      <th>YouTubeViews</th>\n",
       "      <th>InstagramLikes</th>\n",
       "      <th>InstagramFollowers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>500</td>\n",
       "      <td>The Scenic</td>\n",
       "      <td>500</td>\n",
       "      <td>4797.0</td>\n",
       "      <td>1395.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>744</td>\n",
       "      <td>Khamelien</td>\n",
       "      <td>744</td>\n",
       "      <td>NaN</td>\n",
       "      <td>224.0</td>\n",
       "      <td>44287.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>952</td>\n",
       "      <td>Shorelines End</td>\n",
       "      <td>952</td>\n",
       "      <td>4142.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>130279.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1017</td>\n",
       "      <td>Hollywood Ave</td>\n",
       "      <td>1017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2966.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1086</td>\n",
       "      <td>Play for keeps</td>\n",
       "      <td>1086</td>\n",
       "      <td>28980.0</td>\n",
       "      <td>43405.0</td>\n",
       "      <td>402054.0</td>\n",
       "      <td>56832.0</td>\n",
       "      <td>7653.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id            name  artist_id  FaceBookPageLikes  TwitterFollowers  \\\n",
       "0   500      The Scenic        500             4797.0            1395.0   \n",
       "1   744       Khamelien        744                NaN             224.0   \n",
       "2   952  Shorelines End        952             4142.0              64.0   \n",
       "3  1017   Hollywood Ave       1017                NaN               NaN   \n",
       "4  1086  Play for keeps       1086            28980.0           43405.0   \n",
       "\n",
       "   YouTubeViews  InstagramLikes  InstagramFollowers  \n",
       "0           NaN             NaN                 NaN  \n",
       "1       44287.0             NaN                 NaN  \n",
       "2      130279.0             NaN                 NaN  \n",
       "3        2966.0             NaN                 NaN  \n",
       "4      402054.0         56832.0              7653.0  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_merged = pd.merge(artists_df, social_df, left_on='id', right_on='artist_id')\n",
    "df_merged.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's put these steps in a function so that we can use them again later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlite3 as lite\n",
    "import pandas as pd\n",
    "\n",
    "def get_social_data():\n",
    "    con = lite.connect('nbs.db')\n",
    "    artists_query = \"select id, name from artists\"\n",
    "    artists_df = pd.read_sql(artists_query, con)\n",
    "\n",
    "    socail_query = \"select * from social_data\"\n",
    "    social_df = pd.read_sql(socail_query, con)\n",
    "    df_merged = pd.merge(artists_df, social_df, left_on='id', right_on='artist_id')\n",
    "    return df_merged.drop(['id', 'artist_id'], axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's create a second function that takes the merged results, sorts them by one of the featuers, and returns the top artist limited by a paramater n."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>TwitterFollowers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1881</th>\n",
       "      <td>The Amp Jackers</td>\n",
       "      <td>97933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1049</th>\n",
       "      <td>DaveRose</td>\n",
       "      <td>95106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2043</th>\n",
       "      <td>Dez Duron</td>\n",
       "      <td>89117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>649</th>\n",
       "      <td>Pauly Shore</td>\n",
       "      <td>84642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1525</th>\n",
       "      <td>Doyin aka Lyrical</td>\n",
       "      <td>80197</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3571</th>\n",
       "      <td>Chantelle Lee</td>\n",
       "      <td>78375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3425</th>\n",
       "      <td>Billi. B</td>\n",
       "      <td>71055</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name  TwitterFollowers\n",
       "1881    The Amp Jackers             97933\n",
       "1049           DaveRose             95106\n",
       "2043          Dez Duron             89117\n",
       "649         Pauly Shore             84642\n",
       "1525  Doyin aka Lyrical             80197\n",
       "3571      Chantelle Lee             78375\n",
       "3425           Billi. B             71055"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def top_artists(network, n=10):\n",
    "    \"\"\"return the top n artists from network\"\"\"\n",
    "    df_social = get_social_data()  # get the data from sql\n",
    "    \n",
    "    # sort the result by the given network in descending order\n",
    "    df_sorted = df_social.sort_values(by=network, ascending=False)\n",
    "    \n",
    "    # limit the results to the top n artists\n",
    "    df_limited = df_sorted.head(n).copy()\n",
    "    \n",
    "    # convert the values to integers so they look nicer\n",
    "    df_limited.loc[:, network] = df_limited.loc[:, network].astype('int')\n",
    "    \n",
    "    # return the results but just include the values for the network of interest\n",
    "    return df_limited.loc[:, ['name', network]]\n",
    "\n",
    "top_artists('TwitterFollowers', n=7)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Exercise:**\n",
    "Now try to add these two functions to a spyre app that has a drop down menu for selecting a feature type and a slider that allows you to limit your results to some values between 0 and 100.\n",
    "\n",
    "If you get stuck check out `nbs_top_artists_app.py` in this repo. You should be able to run the app from a terminal window by running \n",
    "\n",
    "```\n",
    "$ python nbs_top_artists_app.py\n",
    "```\n",
    "\n",
    "Paste `http://127.0.0.1:8080` into your browser url box to try the app out."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}