Recommender System Mem to Grp SVD.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#import statements\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "from scipy import misc as smi\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import os\n",
    "import pymysql\n",
    "import plotly.plotly as py\n",
    "import plotly.graph_objs as go\n",
    "from plotly.graph_objs import *\n",
    "import folium\n",
    "import sklearn\n",
    "from sklearn.decomposition import TruncatedSVD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Open database connection\n",
    "db = pymysql.connect(\"host\",\"uname\",\"pswrd\",\"schema\" )\n",
    "\n",
    "# prepare a cursor object using cursor() method\n",
    "cursor = db.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#!/usr/bin/python3\n",
    "\n",
    "# Open database connection\n",
    "db = pymysql.connect(\"host\",\"uname\",\"pswrd\",\"schema\" )\n",
    "\n",
    "# prepare a cursor object using cursor() method\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql = \"SELECT member_id,city, state,upper(country),joined,member_name,group_id   \\\n",
    "    from members                                                                 \\\n",
    "    where state = 'IL' and member_status = 'active' LIMIT 1000;\"                \n",
    "           \n",
    "try:\n",
    "   # Execute the SQL command\n",
    "   cursor.execute(sql)\n",
    "    \n",
    "   # Fetch all the rows in a list of lists.\n",
    "   rows = cursor.fetchall()\n",
    "except:\n",
    "    print (\"Error: unable to fetch data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "members = pd.DataFrame( [[ij for ij in i] for i in rows] )\n",
    "members.rename(columns={0:'member_id',1:'City',2:'State',3:'Country',4:'Joined',5:'member_name',6:'group_id'}, inplace=True);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>member_id</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Joined</th>\n",
       "      <th>member_name</th>\n",
       "      <th>group_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>819</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-03-19 04:00:42</td>\n",
       "      <td>James</td>\n",
       "      <td>514628</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>819</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-03-19 03:49:30</td>\n",
       "      <td>James</td>\n",
       "      <td>1294245</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>819</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-03-19 04:02:45</td>\n",
       "      <td>James</td>\n",
       "      <td>1455470</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>819</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-03-19 03:51:59</td>\n",
       "      <td>James</td>\n",
       "      <td>1576866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>819</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-03-19 03:58:14</td>\n",
       "      <td>James</td>\n",
       "      <td>1681402</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id     City State Country              Joined member_name  group_id\n",
       "0        819  Chicago    IL      US 2015-03-19 04:00:42       James    514628\n",
       "1        819  Chicago    IL      US 2015-03-19 03:49:30       James   1294245\n",
       "2        819  Chicago    IL      US 2015-03-19 04:02:45       James   1455470\n",
       "3        819  Chicago    IL      US 2015-03-19 03:51:59       James   1576866\n",
       "4        819  Chicago    IL      US 2015-03-19 03:58:14       James   1681402"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "members.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#!/usr/bin/python3\n",
    "\n",
    "# Open database connection\n",
    "db = pymysql.connect(\"host\",\"uname\",\"pswrd\",\"schema\" )\n",
    "\n",
    "# prepare a cursor object using cursor() method\n",
    "cursor = db.cursor()\n",
    "\n",
    "sql = \"SELECT *   \\\n",
    "    from groups                                                                 \\\n",
    "    where state = 'IL';\"                \n",
    "           \n",
    "try:\n",
    "   # Execute the SQL command\n",
    "   cursor.execute(sql)\n",
    "    \n",
    "   # Fetch all the rows in a list of lists.\n",
    "   rows = cursor.fetchall()\n",
    "except:\n",
    "    print (\"Error: unable to fetch data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "groups = pd.DataFrame( [[ij for ij in i] for i in rows] )\n",
    "#members.rename(columns={0:'member_id',1:'City',2:'State',3:'Country',4:'Joined',5:'member_name',6:'group_id'}, inplace=True);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3180, 13)"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups_upd.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>group_id</th>\n",
       "      <th>Group Name</th>\n",
       "      <th>Urlname</th>\n",
       "      <th>Category ID</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Country</th>\n",
       "      <th>Created</th>\n",
       "      <th>Total members count</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Organizer ID</th>\n",
       "      <th>Organizer Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>15324</td>\n",
       "      <td>The Chicago Backpackers Meetup Group</td>\n",
       "      <td>Chicago-Backpackers</td>\n",
       "      <td>23</td>\n",
       "      <td>outdoors/adventure</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2003-10-22 21:39:49</td>\n",
       "      <td>1369</td>\n",
       "      <td>4.79</td>\n",
       "      <td>10175045</td>\n",
       "      <td>Mary R.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>22211</td>\n",
       "      <td>Chicago Game Lovers</td>\n",
       "      <td>chicagogames</td>\n",
       "      <td>11</td>\n",
       "      <td>games</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2003-04-17 19:31:25</td>\n",
       "      <td>4146</td>\n",
       "      <td>4.72</td>\n",
       "      <td>9470872</td>\n",
       "      <td>Jeff Engel</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>24765</td>\n",
       "      <td>The Chicago Boston Terrier Meetup Group</td>\n",
       "      <td>ChicagoBostonTerriers</td>\n",
       "      <td>26</td>\n",
       "      <td>pets/animals</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2003-04-29 20:53:16</td>\n",
       "      <td>695</td>\n",
       "      <td>4.53</td>\n",
       "      <td>3640389</td>\n",
       "      <td>CHERYL EITZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>25709</td>\n",
       "      <td>Socialize, Network, and Connect</td>\n",
       "      <td>Socialize-Network-and-Connect</td>\n",
       "      <td>16</td>\n",
       "      <td>language/ethnic identity</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2003-04-30 20:06:30</td>\n",
       "      <td>1261</td>\n",
       "      <td>4.51</td>\n",
       "      <td>223406285</td>\n",
       "      <td>Kelly S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>26154</td>\n",
       "      <td>The Chicago Freethinkers Meetup Group</td>\n",
       "      <td>chicago-freethought</td>\n",
       "      <td>28</td>\n",
       "      <td>religion/beliefs</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2003-10-22 21:39:49</td>\n",
       "      <td>1835</td>\n",
       "      <td>4.43</td>\n",
       "      <td>12130423</td>\n",
       "      <td>Ivan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   group_id                               Group Name  \\\n",
       "0     15324     The Chicago Backpackers Meetup Group   \n",
       "1     22211                      Chicago Game Lovers   \n",
       "2     24765  The Chicago Boston Terrier Meetup Group   \n",
       "3     25709          Socialize, Network, and Connect   \n",
       "4     26154    The Chicago Freethinkers Meetup Group   \n",
       "\n",
       "                         Urlname  Category ID             Category Name  \\\n",
       "0            Chicago-Backpackers           23        outdoors/adventure   \n",
       "1                   chicagogames           11                     games   \n",
       "2          ChicagoBostonTerriers           26              pets/animals   \n",
       "3  Socialize-Network-and-Connect           16  language/ethnic identity   \n",
       "4            chicago-freethought           28          religion/beliefs   \n",
       "\n",
       "      City State Country             Created  Total members count Rating  \\\n",
       "0  Chicago    IL      US 2003-10-22 21:39:49                 1369   4.79   \n",
       "1  Chicago    IL      US 2003-04-17 19:31:25                 4146   4.72   \n",
       "2  Chicago    IL      US 2003-04-29 20:53:16                  695   4.53   \n",
       "3  Chicago    IL      US 2003-04-30 20:06:30                 1261   4.51   \n",
       "4  Chicago    IL      US 2003-10-22 21:39:49                 1835   4.43   \n",
       "\n",
       "   Organizer ID Organizer Name  \n",
       "0      10175045        Mary R.  \n",
       "1       9470872     Jeff Engel  \n",
       "2       3640389    CHERYL EITZ  \n",
       "3     223406285        Kelly S  \n",
       "4      12130423           Ivan  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "groups_upd = pd.DataFrame(groups[[0,20,32,1,2,5,30,6,7,19,29,21,22]])\n",
    "groups_upd.rename(columns={0:'group_id',20:'Group Name',32:'Urlname',1:'Category ID',2:'Category Name',5:'City',30:'State',6:'Country',7:'Created',19:'Total members count',29:'Rating',21:'Organizer ID',22:'Organizer Name'}, inplace=True);\n",
    "groups_upd.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>member_id</th>\n",
       "      <th>City_x</th>\n",
       "      <th>State_x</th>\n",
       "      <th>Country_x</th>\n",
       "      <th>Joined</th>\n",
       "      <th>member_name</th>\n",
       "      <th>group_id</th>\n",
       "      <th>Group Name</th>\n",
       "      <th>Urlname</th>\n",
       "      <th>Category ID</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>City_y</th>\n",
       "      <th>State_y</th>\n",
       "      <th>Country_y</th>\n",
       "      <th>Created</th>\n",
       "      <th>Total members count</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Organizer ID</th>\n",
       "      <th>Organizer Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>819</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-03-19 04:00:42</td>\n",
       "      <td>James</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4295</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2009-07-31 21:33:09</td>\n",
       "      <td>John Stoner</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10600</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-02-28 03:40:51</td>\n",
       "      <td>Kevin Buckstiegel</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>152870</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-07-13 00:30:07</td>\n",
       "      <td>Antis Thelapilly</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>300185</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-02-25 18:44:02</td>\n",
       "      <td>Nels Wadycki</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id   City_x State_x Country_x              Joined  \\\n",
       "0        819  Chicago      IL        US 2015-03-19 04:00:42   \n",
       "1       4295  Chicago      IL        US 2009-07-31 21:33:09   \n",
       "2      10600  Chicago      IL        US 2013-02-28 03:40:51   \n",
       "3     152870  Chicago      IL        US 2015-07-13 00:30:07   \n",
       "4     300185  Chicago      IL        US 2013-02-25 18:44:02   \n",
       "\n",
       "         member_name  group_id                            Group Name  \\\n",
       "0              James    514628  Chicago Droid: Machine Learning, IoT   \n",
       "1        John Stoner    514628  Chicago Droid: Machine Learning, IoT   \n",
       "2  Kevin Buckstiegel    514628  Chicago Droid: Machine Learning, IoT   \n",
       "3   Antis Thelapilly    514628  Chicago Droid: Machine Learning, IoT   \n",
       "4       Nels Wadycki    514628  Chicago Droid: Machine Learning, IoT   \n",
       "\n",
       "           Urlname  Category ID Category Name   City_y State_y Country_y  \\\n",
       "0  chicago-android           34          tech  Chicago      IL        US   \n",
       "1  chicago-android           34          tech  Chicago      IL        US   \n",
       "2  chicago-android           34          tech  Chicago      IL        US   \n",
       "3  chicago-android           34          tech  Chicago      IL        US   \n",
       "4  chicago-android           34          tech  Chicago      IL        US   \n",
       "\n",
       "              Created  Total members count Rating  Organizer ID  \\\n",
       "0 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "1 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "2 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "3 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "4 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "\n",
       "       Organizer Name  \n",
       "0  Uki Dominque Lucas  \n",
       "1  Uki Dominque Lucas  \n",
       "2  Uki Dominque Lucas  \n",
       "3  Uki Dominque Lucas  \n",
       "4  Uki Dominque Lucas  "
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_mem_grps = pd.merge(members, groups_upd, on='group_id')\n",
    "combined_mem_grps.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "combined_mem_grps['dummy'] = 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(986, 20)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_mem_grps.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>member_id</th>\n",
       "      <th>City_x</th>\n",
       "      <th>State_x</th>\n",
       "      <th>Country_x</th>\n",
       "      <th>Joined</th>\n",
       "      <th>member_name</th>\n",
       "      <th>group_id</th>\n",
       "      <th>Group Name</th>\n",
       "      <th>Urlname</th>\n",
       "      <th>Category ID</th>\n",
       "      <th>Category Name</th>\n",
       "      <th>City_y</th>\n",
       "      <th>State_y</th>\n",
       "      <th>Country_y</th>\n",
       "      <th>Created</th>\n",
       "      <th>Total members count</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Organizer ID</th>\n",
       "      <th>Organizer Name</th>\n",
       "      <th>dummy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>819</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-03-19 04:00:42</td>\n",
       "      <td>James</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4295</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2009-07-31 21:33:09</td>\n",
       "      <td>John Stoner</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10600</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-02-28 03:40:51</td>\n",
       "      <td>Kevin Buckstiegel</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>152870</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2015-07-13 00:30:07</td>\n",
       "      <td>Antis Thelapilly</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>300185</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2013-02-25 18:44:02</td>\n",
       "      <td>Nels Wadycki</td>\n",
       "      <td>514628</td>\n",
       "      <td>Chicago Droid: Machine Learning, IoT</td>\n",
       "      <td>chicago-android</td>\n",
       "      <td>34</td>\n",
       "      <td>tech</td>\n",
       "      <td>Chicago</td>\n",
       "      <td>IL</td>\n",
       "      <td>US</td>\n",
       "      <td>2007-05-30 17:56:17</td>\n",
       "      <td>1079</td>\n",
       "      <td>4.36</td>\n",
       "      <td>4250882</td>\n",
       "      <td>Uki Dominque Lucas</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_id   City_x State_x Country_x              Joined  \\\n",
       "0        819  Chicago      IL        US 2015-03-19 04:00:42   \n",
       "1       4295  Chicago      IL        US 2009-07-31 21:33:09   \n",
       "2      10600  Chicago      IL        US 2013-02-28 03:40:51   \n",
       "3     152870  Chicago      IL        US 2015-07-13 00:30:07   \n",
       "4     300185  Chicago      IL        US 2013-02-25 18:44:02   \n",
       "\n",
       "         member_name  group_id                            Group Name  \\\n",
       "0              James    514628  Chicago Droid: Machine Learning, IoT   \n",
       "1        John Stoner    514628  Chicago Droid: Machine Learning, IoT   \n",
       "2  Kevin Buckstiegel    514628  Chicago Droid: Machine Learning, IoT   \n",
       "3   Antis Thelapilly    514628  Chicago Droid: Machine Learning, IoT   \n",
       "4       Nels Wadycki    514628  Chicago Droid: Machine Learning, IoT   \n",
       "\n",
       "           Urlname  Category ID Category Name   City_y State_y Country_y  \\\n",
       "0  chicago-android           34          tech  Chicago      IL        US   \n",
       "1  chicago-android           34          tech  Chicago      IL        US   \n",
       "2  chicago-android           34          tech  Chicago      IL        US   \n",
       "3  chicago-android           34          tech  Chicago      IL        US   \n",
       "4  chicago-android           34          tech  Chicago      IL        US   \n",
       "\n",
       "              Created  Total members count Rating  Organizer ID  \\\n",
       "0 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "1 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "2 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "3 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "4 2007-05-30 17:56:17                 1079   4.36       4250882   \n",
       "\n",
       "       Organizer Name  dummy  \n",
       "0  Uki Dominque Lucas      1  \n",
       "1  Uki Dominque Lucas      1  \n",
       "2  Uki Dominque Lucas      1  \n",
       "3  Uki Dominque Lucas      1  \n",
       "4  Uki Dominque Lucas      1  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combined_mem_grps.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>Group Name</th>\n",
       "      <th>20 and 30 Somethings Chicago</th>\n",
       "      <th>20s and 30s On The Town</th>\n",
       "      <th>312 Professionals  20's,30's,40's Social Life Chicago</th>\n",
       "      <th>Adventure Travel Meetup</th>\n",
       "      <th>AgiLe AngLe Chicago</th>\n",
       "      <th>Alcohol Free Kick A$$ Events!</th>\n",
       "      <th>Art Appreciation and Philosophy</th>\n",
       "      <th>Augmented Reality Chicago</th>\n",
       "      <th>Authentic Connections Networking</th>\n",
       "      <th>Beach Tennis Chicago</th>\n",
       "      <th>...</th>\n",
       "      <th>Tribe Of Singles Meetup Group</th>\n",
       "      <th>Vegan Chicago</th>\n",
       "      <th>Vim Chicago</th>\n",
       "      <th>Windy City Boarders</th>\n",
       "      <th>Windy City Explorers</th>\n",
       "      <th>Windy City Hikers Meetup Group</th>\n",
       "      <th>Windy City Skaters</th>\n",
       "      <th>Women in Business Connect</th>\n",
       "      <th>Young Professionals of Chicago</th>\n",
       "      <th>Zettabyte  (formally Chicago TranceFormation)</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>member_id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>819</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1945</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3705</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4295</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4368</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 254 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Group Name  20 and 30 Somethings Chicago  20s and 30s On The Town  \\\n",
       "member_id                                                           \n",
       "819                                    0                        0   \n",
       "1945                                   0                        0   \n",
       "3705                                   0                        0   \n",
       "4295                                   0                        0   \n",
       "4368                                   0                        0   \n",
       "\n",
       "Group Name  312 Professionals  20's,30's,40's Social Life Chicago  \\\n",
       "member_id                                                           \n",
       "819                                                         0       \n",
       "1945                                                        0       \n",
       "3705                                                        0       \n",
       "4295                                                        0       \n",
       "4368                                                        0       \n",
       "\n",
       "Group Name  Adventure Travel Meetup  AgiLe AngLe Chicago  \\\n",
       "member_id                                                  \n",
       "819                               0                    0   \n",
       "1945                              0                    0   \n",
       "3705                              0                    0   \n",
       "4295                              0                    0   \n",
       "4368                              0                    0   \n",
       "\n",
       "Group Name  Alcohol Free Kick A$$ Events!  Art Appreciation and Philosophy  \\\n",
       "member_id                                                                    \n",
       "819                                     0                                0   \n",
       "1945                                    0                                0   \n",
       "3705                                    0                                0   \n",
       "4295                                    0                                0   \n",
       "4368                                    0                                0   \n",
       "\n",
       "Group Name  Augmented Reality Chicago  Authentic Connections Networking  \\\n",
       "member_id                                                                 \n",
       "819                                 1                                 0   \n",
       "1945                                0                                 0   \n",
       "3705                                0                                 0   \n",
       "4295                                0                                 0   \n",
       "4368                                0                                 0   \n",
       "\n",
       "Group Name  Beach Tennis Chicago  \\\n",
       "member_id                          \n",
       "819                            0   \n",
       "1945                           0   \n",
       "3705                           0   \n",
       "4295                           0   \n",
       "4368                           0   \n",
       "\n",
       "Group Name                      ...                        \\\n",
       "member_id                       ...                         \n",
       "819                             ...                         \n",
       "1945                            ...                         \n",
       "3705                            ...                         \n",
       "4295                            ...                         \n",
       "4368                            ...                         \n",
       "\n",
       "Group Name  Tribe Of Singles Meetup Group  Vegan Chicago  Vim Chicago  \\\n",
       "member_id                                                               \n",
       "819                                     0              0            0   \n",
       "1945                                    0              0            0   \n",
       "3705                                    0              0            0   \n",
       "4295                                    0              0            0   \n",
       "4368                                    0              0            0   \n",
       "\n",
       "Group Name  Windy City Boarders  Windy City Explorers  \\\n",
       "member_id                                               \n",
       "819                           0                     0   \n",
       "1945                          0                     0   \n",
       "3705                          0                     0   \n",
       "4295                          0                     0   \n",
       "4368                          0                     0   \n",
       "\n",
       "Group Name  Windy City Hikers Meetup Group  Windy City Skaters  \\\n",
       "member_id                                                        \n",
       "819                                      0                   0   \n",
       "1945                                     0                   0   \n",
       "3705                                     0                   0   \n",
       "4295                                     0                   0   \n",
       "4368                                     0                   0   \n",
       "\n",
       "Group Name  Women in Business Connect  Young Professionals of Chicago  \\\n",
       "member_id                                                               \n",
       "819                                 0                               0   \n",
       "1945                                0                               0   \n",
       "3705                                0                               0   \n",
       "4295                                0                               0   \n",
       "4368                                0                               0   \n",
       "\n",
       "Group Name  Zettabyte  (formally Chicago TranceFormation)  \n",
       "member_id                                                  \n",
       "819                                                     0  \n",
       "1945                                                    0  \n",
       "3705                                                    0  \n",
       "4295                                                    0  \n",
       "4368                                                    0  \n",
       "\n",
       "[5 rows x 254 columns]"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "membership_crosstab = combined_mem_grps.pivot_table(values='dummy', index= 'member_id', columns='Group Name', fill_value=0)\n",
    "membership_crosstab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(252, 254)"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "membership_crosstab.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(254, 252)"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "X = membership_crosstab.T\n",
    "X.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(254, 12)"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SVD = TruncatedSVD(n_components=12, random_state=17)\n",
    "\n",
    "resultant_matrix = SVD.fit_transform(X)\n",
    "\n",
    "resultant_matrix.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(254, 254)"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr_mat = np.corrcoef(resultant_matrix)\n",
    "corr_mat.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "test_group = 'Chicago Big Data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "30"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "group_names = membership_crosstab.columns\n",
    "groups_list = list(group_names)\n",
    "\n",
    "big_data = groups_list.index(test_group)\n",
    "big_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(254,)"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "corr_big_data = corr_mat[big_data]\n",
    "corr_big_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "similar_groups = list(group_names[(corr_big_data<1.0) & (corr_big_data > 0.95)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"SELECT group_id from groups where group_name = '\"\"\"+str(test_group)+\"\"\"';\"\"\"\n",
    "try:\n",
    "   # Execute the SQL command\n",
    "   cursor.execute(sql)\n",
    "    \n",
    "   # Fetch all the rows in a list of lists.\n",
    "   rows = cursor.fetchall()\n",
    "except:\n",
    "    print (\"Error: unable to fetch data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1775479"
      ]
     },
     "execution_count": 111,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_group_id = rows[0][0]\n",
    "test_group_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Chicago R User Group (Chicago RUG, CRUG) Data and Statistics'"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "similar_groups[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sql = \"\"\"select distinct mm.member_id,mm.member_name from                                 \\\n",
    "    (select m.member_id from members m join groups g                                    \\\n",
    "    on m.group_id=g.group_id                                                            \\\n",
    "    where g.group_name = '\"\"\"+str(similar_groups[0])+\"\"\"') tmp                          \\\n",
    "    join members mm                                                                     \\\n",
    "    on tmp.member_id = mm.member_id                                                     \\\n",
    "    where tmp.member_id not in                                                          \\\n",
    "    (select del.member_id from members del                                              \\\n",
    "    where del.group_id = \"\"\"+str(test_group_id)+\"\"\") and mm.member_status='active'       \\\n",
    "    and mm.state = 'IL' LIMIT 100;\"\"\"\n",
    "try:\n",
    "   #Execute the SQL command\n",
    "    cursor.execute(sql)\n",
    "\n",
    "   #Fetch all the rows in a list of lists.\n",
    "    rows = cursor.fetchall()\n",
    "except:\n",
    "    print (\"Error: unable to fetch data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Member_id</th>\n",
       "      <th>Member Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>38566</td>\n",
       "      <td>jhe1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>145317</td>\n",
       "      <td>ElAleph</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>462692</td>\n",
       "      <td>David Flint</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>466031</td>\n",
       "      <td>Joe Germuska</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>707720</td>\n",
       "      <td>Jennifer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>765135</td>\n",
       "      <td>David Leigh</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>805238</td>\n",
       "      <td>Ed S.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>877417</td>\n",
       "      <td>John Tongelidis</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1404398</td>\n",
       "      <td>Josh K</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1565275</td>\n",
       "      <td>llieaay</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>1654156</td>\n",
       "      <td>Nick</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>1794114</td>\n",
       "      <td>Mike Malecki</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>1899818</td>\n",
       "      <td>Forest</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>1981292</td>\n",
       "      <td>erica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2089797</td>\n",
       "      <td>Melanie Murray</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2423577</td>\n",
       "      <td>Mark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>2437211</td>\n",
       "      <td>Adrian</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>2482081</td>\n",
       "      <td>Paula</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>2492638</td>\n",
       "      <td>bhavi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>2568776</td>\n",
       "      <td>Lilith</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>2576847</td>\n",
       "      <td>Matthew Pearson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2664751</td>\n",
       "      <td>K S</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2845218</td>\n",
       "      <td>KT9000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2849829</td>\n",
       "      <td>Kelly</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2872910</td>\n",
       "      <td>Crystal Campbell</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2975864</td>\n",
       "      <td>rhea</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>3158448</td>\n",
       "      <td>Harry G. Osoff</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>3252286</td>\n",
       "      <td>Roger Moore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>3360749</td>\n",
       "      <td>Yunhui Wang</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>3524406</td>\n",
       "      <td>Masahiko</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>9677775</td>\n",
       "      <td>Pavel Kusch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71</th>\n",
       "      <td>9682718</td>\n",
       "      <td>Michelle Moriarty</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72</th>\n",
       "      <td>9682760</td>\n",
       "      <td>Nick Altmann</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73</th>\n",
       "      <td>9743970</td>\n",
       "      <td>zachary fietsch</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>74</th>\n",
       "      <td>9777724</td>\n",
       "      <td>Chanz</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>9826858</td>\n",
       "      <td>Toby Tripp</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>9920990</td>\n",
       "      <td>Ricardo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77</th>\n",
       "      <td>9946322</td>\n",
       "      <td>Dylan Stark</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78</th>\n",
       "      <td>9997014</td>\n",
       "      <td>Christopher Giorno</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>79</th>\n",
       "      <td>10087757</td>\n",
       "      <td>Ugochi Acholonu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>10251478</td>\n",
       "      <td>Tim Knight</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>10337397</td>\n",
       "      <td>Tom Carter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>82</th>\n",
       "      <td>10337739</td>\n",
       "      <td>Benjamin Haley</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>10587379</td>\n",
       "      <td>Steve</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>84</th>\n",
       "      <td>10706882</td>\n",
       "      <td>john kinstler</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>10773655</td>\n",
       "      <td>Matthew Young</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>10825871</td>\n",
       "      <td>Flori Manning</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>10874291</td>\n",
       "      <td>Shad Thomas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>10971484</td>\n",
       "      <td>Kenneth Apa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>11088739</td>\n",
       "      <td>Juli</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>11092759</td>\n",
       "      <td>Mark Robinson</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>11138134</td>\n",
       "      <td>Qiong Wu</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>92</th>\n",
       "      <td>11230685</td>\n",
       "      <td>Andrew</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>93</th>\n",
       "      <td>11235674</td>\n",
       "      <td>Marc Witham</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>94</th>\n",
       "      <td>11237154</td>\n",
       "      <td>Stephen Boisvert</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>95</th>\n",
       "      <td>11347007</td>\n",
       "      <td>Yakov</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>96</th>\n",
       "      <td>11466392</td>\n",
       "      <td>Galen Burghardt</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>97</th>\n",
       "      <td>11618751</td>\n",
       "      <td>Justine Lee</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>11658491</td>\n",
       "      <td>Vivek</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99</th>\n",
       "      <td>11712921</td>\n",
       "      <td>Lance Levenson</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>100 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Member_id         Member Name\n",
       "0       38566                jhe1\n",
       "1      145317             ElAleph\n",
       "2      462692         David Flint\n",
       "3      466031        Joe Germuska\n",
       "4      707720            Jennifer\n",
       "5      765135         David Leigh\n",
       "6      805238               Ed S.\n",
       "7      877417     John Tongelidis\n",
       "8     1404398              Josh K\n",
       "9     1565275             llieaay\n",
       "10    1654156                Nick\n",
       "11    1794114        Mike Malecki\n",
       "12    1899818              Forest\n",
       "13    1981292               erica\n",
       "14    2089797      Melanie Murray\n",
       "15    2423577                Mark\n",
       "16    2437211              Adrian\n",
       "17    2482081               Paula\n",
       "18    2492638               bhavi\n",
       "19    2568776              Lilith\n",
       "20    2576847     Matthew Pearson\n",
       "21    2664751                 K S\n",
       "22    2845218              KT9000\n",
       "23    2849829               Kelly\n",
       "24    2872910    Crystal Campbell\n",
       "25    2975864                rhea\n",
       "26    3158448      Harry G. Osoff\n",
       "27    3252286         Roger Moore\n",
       "28    3360749         Yunhui Wang\n",
       "29    3524406            Masahiko\n",
       "..        ...                 ...\n",
       "70    9677775         Pavel Kusch\n",
       "71    9682718   Michelle Moriarty\n",
       "72    9682760        Nick Altmann\n",
       "73    9743970     zachary fietsch\n",
       "74    9777724               Chanz\n",
       "75    9826858          Toby Tripp\n",
       "76    9920990             Ricardo\n",
       "77    9946322         Dylan Stark\n",
       "78    9997014  Christopher Giorno\n",
       "79   10087757     Ugochi Acholonu\n",
       "80   10251478          Tim Knight\n",
       "81   10337397          Tom Carter\n",
       "82   10337739      Benjamin Haley\n",
       "83   10587379               Steve\n",
       "84   10706882       john kinstler\n",
       "85   10773655       Matthew Young\n",
       "86   10825871       Flori Manning\n",
       "87   10874291         Shad Thomas\n",
       "88   10971484         Kenneth Apa\n",
       "89   11088739                Juli\n",
       "90   11092759       Mark Robinson\n",
       "91   11138134            Qiong Wu\n",
       "92   11230685              Andrew\n",
       "93   11235674         Marc Witham\n",
       "94   11237154    Stephen Boisvert\n",
       "95   11347007               Yakov\n",
       "96   11466392     Galen Burghardt\n",
       "97   11618751         Justine Lee\n",
       "98   11658491               Vivek\n",
       "99   11712921      Lance Levenson\n",
       "\n",
       "[100 rows x 2 columns]"
      ]
     },
     "execution_count": 126,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mem_recco = pd.DataFrame( [[ij for ij in i] for i in rows] )\n",
    "mem_recco.rename(columns={0:'Member_id',1:'Member Name'}, inplace=True);\n",
    "mem_recco"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}