05-sankey.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "# import community as community2 # pip install python-louvain\n",
    "# import networkx as nx\n",
    "# from networkx.algorithms import community\n",
    "import pandas as pd\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pd.options.display.max_rows = 1000\n",
    "# pd.options.display.max_columns = 1000"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/out/checkins.csv', index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Source', 'Source Type', 'Notes', 'confidence',\n",
       "       'Comprehensive Check In', 'Aikens, Vanoye', 'Alexander, James',\n",
       "       'Aul, Ronnie', 'Bradley, Wilbert', 'Brooks, Jay', 'Burton, Miriam',\n",
       "       'Canto, Umberto', 'Clay, Eddy', 'Destine, Jean Leon', 'Ellis, Lucille',\n",
       "       'Frasier, Awilda', 'Gomez, Tommy'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10, 17)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Source</th>\n",
       "      <th>Source Type</th>\n",
       "      <th>Notes</th>\n",
       "      <th>confidence</th>\n",
       "      <th>Comprehensive Check In</th>\n",
       "      <th>Aikens, Vanoye</th>\n",
       "      <th>Alexander, James</th>\n",
       "      <th>Aul, Ronnie</th>\n",
       "      <th>Bradley, Wilbert</th>\n",
       "      <th>Brooks, Jay</th>\n",
       "      <th>Burton, Miriam</th>\n",
       "      <th>Canto, Umberto</th>\n",
       "      <th>Clay, Eddy</th>\n",
       "      <th>Destine, Jean Leon</th>\n",
       "      <th>Ellis, Lucille</th>\n",
       "      <th>Frasier, Awilda</th>\n",
       "      <th>Gomez, Tommy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mar 14, 1947</th>\n",
       "      <td>SIU Box 9 Folder 9</td>\n",
       "      <td>correspondence</td>\n",
       "      <td>Eddy Clay injured</td>\n",
       "      <td>1</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Apr 07, 1947</th>\n",
       "      <td>SIU Box 10 Folder 1</td>\n",
       "      <td>administration</td>\n",
       "      <td>signed contracts for Mexico tour; Vicente sign...</td>\n",
       "      <td>2</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 28, 1947</th>\n",
       "      <td>SIU Box 100 Folder 3</td>\n",
       "      <td>administration</td>\n",
       "      <td>expenses</td>\n",
       "      <td>1</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jul 01, 1947</th>\n",
       "      <td>SIU Box 10 Folder 4</td>\n",
       "      <td>administration</td>\n",
       "      <td>performer contact list for Mexico City</td>\n",
       "      <td>4</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jul 06, 1947</th>\n",
       "      <td>SIU Box 100 Folder 3</td>\n",
       "      <td>stage</td>\n",
       "      <td>performer list - no date but with July 1947 pa...</td>\n",
       "      <td>4</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            Source     Source Type  \\\n",
       "Mar 14, 1947    SIU Box 9 Folder 9  correspondence   \n",
       "Apr 07, 1947   SIU Box 10 Folder 1  administration   \n",
       "Jun 28, 1947  SIU Box 100 Folder 3  administration   \n",
       "Jul 01, 1947   SIU Box 10 Folder 4  administration   \n",
       "Jul 06, 1947  SIU Box 100 Folder 3           stage   \n",
       "\n",
       "                                                          Notes  confidence  \\\n",
       "Mar 14, 1947                                  Eddy Clay injured           1   \n",
       "Apr 07, 1947  signed contracts for Mexico tour; Vicente sign...           2   \n",
       "Jun 28, 1947                                           expenses           1   \n",
       "Jul 01, 1947             performer contact list for Mexico City           4   \n",
       "Jul 06, 1947  performer list - no date but with July 1947 pa...           4   \n",
       "\n",
       "             Comprehensive Check In Aikens, Vanoye Alexander, James  \\\n",
       "Mar 14, 1947                      n              n                n   \n",
       "Apr 07, 1947                      y              y                y   \n",
       "Jun 28, 1947                      n              y                y   \n",
       "Jul 01, 1947                      y              y                y   \n",
       "Jul 06, 1947                      n              y                y   \n",
       "\n",
       "             Aul, Ronnie Bradley, Wilbert Brooks, Jay Burton, Miriam  \\\n",
       "Mar 14, 1947           n                n           n              n   \n",
       "Apr 07, 1947           y                y           n              y   \n",
       "Jun 28, 1947           y                y           n              y   \n",
       "Jul 01, 1947           y                y           n              n   \n",
       "Jul 06, 1947           y                y           n              n   \n",
       "\n",
       "             Canto, Umberto Clay, Eddy Destine, Jean Leon Ellis, Lucille  \\\n",
       "Mar 14, 1947              n          y                  n              n   \n",
       "Apr 07, 1947              n          y                  y              y   \n",
       "Jun 28, 1947              n          y                  y              y   \n",
       "Jul 01, 1947              y          n                  y              y   \n",
       "Jul 06, 1947              y          n                  y              y   \n",
       "\n",
       "             Frasier, Awilda Gomez, Tommy  \n",
       "Mar 14, 1947               n            n  \n",
       "Apr 07, 1947               n            n  \n",
       "Jun 28, 1947               n            n  \n",
       "Jul 01, 1947               n            n  \n",
       "Jul 06, 1947               n            n  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "dunham_df = pd.read_csv('data/out/everyday_city_country.csv', index_col=0)\n",
    "dunham_df.fillna('', inplace=True)\n",
    "dunham_df.index = pd.to_datetime(dunham_df.index)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10, 2)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dunham_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CITY</th>\n",
       "      <th>COUNTRY</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1947-03-14</th>\n",
       "      <td>New York City</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1947-04-07</th>\n",
       "      <td>New York City</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1947-06-28</th>\n",
       "      <td>Mexico City</td>\n",
       "      <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1947-07-01</th>\n",
       "      <td>Mexico City</td>\n",
       "      <td>Mexico</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1947-07-06</th>\n",
       "      <td>Mexico City</td>\n",
       "      <td>Mexico</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     CITY COUNTRY\n",
       "1947-03-14  New York City     USA\n",
       "1947-04-07  New York City     USA\n",
       "1947-06-28    Mexico City  Mexico\n",
       "1947-07-01    Mexico City  Mexico\n",
       "1947-07-06    Mexico City  Mexico"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dunham_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-processing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "def format_name(name):\n",
    "    if ',' in name and not ', ' in name:\n",
    "        print('ERROR NAME')\n",
    "    if ', ' in name:\n",
    "        last, first = name.split(', ')\n",
    "        fullname = first + ' ' + last\n",
    "    else:\n",
    "        fullname = name\n",
    "    return fullname"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_columns = [format_name(c) for c in df.columns]\n",
    "df.columns = new_columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "dunham_df2 = dunham_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "comprehensive_sr = df2['Comprehensive Check In']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 5\n",
    "df2 = df2.iloc[:, N:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10, 12)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Vanoye Aikens</th>\n",
       "      <th>James Alexander</th>\n",
       "      <th>Ronnie Aul</th>\n",
       "      <th>Wilbert Bradley</th>\n",
       "      <th>Jay Brooks</th>\n",
       "      <th>Miriam Burton</th>\n",
       "      <th>Umberto Canto</th>\n",
       "      <th>Eddy Clay</th>\n",
       "      <th>Jean Leon Destine</th>\n",
       "      <th>Lucille Ellis</th>\n",
       "      <th>Awilda Frasier</th>\n",
       "      <th>Tommy Gomez</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mar 14, 1947</th>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Apr 07, 1947</th>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 28, 1947</th>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jul 01, 1947</th>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jul 06, 1947</th>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>y</td>\n",
       "      <td>y</td>\n",
       "      <td>n</td>\n",
       "      <td>n</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Vanoye Aikens James Alexander Ronnie Aul Wilbert Bradley  \\\n",
       "Mar 14, 1947             n               n          n               n   \n",
       "Apr 07, 1947             y               y          y               y   \n",
       "Jun 28, 1947             y               y          y               y   \n",
       "Jul 01, 1947             y               y          y               y   \n",
       "Jul 06, 1947             y               y          y               y   \n",
       "\n",
       "             Jay Brooks Miriam Burton Umberto Canto Eddy Clay  \\\n",
       "Mar 14, 1947          n             n             n         y   \n",
       "Apr 07, 1947          n             y             n         y   \n",
       "Jun 28, 1947          n             y             n         y   \n",
       "Jul 01, 1947          n             n             y         n   \n",
       "Jul 06, 1947          n             n             y         n   \n",
       "\n",
       "             Jean Leon Destine Lucille Ellis Awilda Frasier Tommy Gomez  \n",
       "Mar 14, 1947                 n             n              n           n  \n",
       "Apr 07, 1947                 y             y              n           n  \n",
       "Jun 28, 1947                 y             y              n           n  \n",
       "Jul 01, 1947                 y             y              n           n  \n",
       "Jul 06, 1947                 y             y              n           n  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mar 14, 1947</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Apr 07, 1947</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 28, 1947</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jul 01, 1947</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jul 06, 1947</th>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: [Mar 14, 1947, Apr 07, 1947, Jun 28, 1947, Jul 01, 1947, Jul 06, 1947]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.loc[:, (df2 == 'n').all(axis=0)].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2.loc[:, (df2 == 'y').any(axis=0)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Mar 14, 1947</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Apr 07, 1947</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jun 28, 1947</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jul 01, 1947</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Jul 06, 1947</th>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: []\n",
       "Index: [Mar 14, 1947, Apr 07, 1947, Jun 28, 1947, Jul 01, 1947, Jul 06, 1947]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.loc[:, (df2 == 'n').all(axis=0)].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Processing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3 = df2.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "cast = df3.columns\n",
    "cast2 = [c + ' 2' for c in df3.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, name in enumerate(cast2):\n",
    "    yn_lst = ['n'] * len(cast2)\n",
    "    yn_lst[i] = 'y'\n",
    "    df3.loc[name] = yn_lst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Vanoye Aikens', 'James Alexander', 'Ronnie Aul', 'Wilbert Bradley',\n",
       "       'Jay Brooks'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cast[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = list(df3.index.astype(str))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Mar 14, 1947',\n",
       " 'Apr 07, 1947',\n",
       " 'Jun 28, 1947',\n",
       " 'Jul 01, 1947',\n",
       " 'Jul 06, 1947']"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates[:5]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Eddy Clay 2',\n",
       " 'Jean Leon Destine 2',\n",
       " 'Lucille Ellis 2',\n",
       " 'Awilda Frasier 2',\n",
       " 'Tommy Gomez 2']"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dates[-5:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAq8AAABICAYAAADYvxyOAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAACgklEQVR4nO3cTUocURiG0a/EYQbiDzgQsonsx2W4hOwhgywie8jMDbiCTBQCBmy7tW82oGCBRfX7cc60G7kvVTTPoKxpjFEAAJDgaO0DAADAR4lXAABiiFcAAGKIVwAAYohXAABiiFcAAGIcz/nyl5PTcXZ5tdRZVrev3q8N2zee1/2Nb91fadf53qzqf3/umw/svM5vS7bO1+/p4U9t//2d3vpsVryeXV7Vzc9fn3OqA7R52a99hEVtdn1v8u1r82v30vfaVVVtdr2v33Pz35bn3evaR1jUtvH12zX/7XRv5vr9/frdzzw2AABADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBADPEKAEAM8QoAQAzxCgBAjGmM8fEvT9NjVd0td5zVnVfV/dqHWEjnbVX2pbMvV+dtVfalsy/X1zHGxVsfHM/8Q3djjG+fcKCDNE3Tbdd9nbdV2ZfOvlydt1XZl86+njw2AABADPEKAECMufH6Y5FTHI7O+zpvq7IvnX25Om+rsi+dfQ3N+octAABYk8cGAACIIV4BAIghXgEAiCFeAQCIIV4BAIjxH3T9lPYkmvqqAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 864x72 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "palette = sns.color_palette('Blues', len(cast) + 40).as_hex()[20:-20]\n",
    "sns.palplot(palette)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#95c5df\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAEkAAABICAYAAAC6L9h5AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAABF0lEQVR4nO3csQnCQBxG8TuxtBCDkCIg7mDnSA7gLhYO4SpWKbW2c4C/vYi8w4Qk+H7lkeLjwaVLckQkfTcbesAUGAkwEmAkwEiAkYB5ycOL5Sqquulry+Du7fUREev386JIVd2k4/nS3aqROey3t0/nXjfASICRACMBRgKMBBgJMBJgJMBIgJEAIwFGAowEGAkwEmAkwEiAkQAjAUYCjAQYCTASYCTASICRACMBRgKMBBgJMBJgJMBIgJEAIwFGAowEGAkwEmAkwEiAkQAjAUYCjAQYCTASYCTASICRACMBueSHLjnnZ0qp7W/O4DY/f82dUmojYtfRoMnwugFGAkojnXpZMXJFL+5/5XUDjAQYCTASYCTgBTgIHuXduvTpAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 72x72 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(palette[0])\n",
    "sns.palplot([palette[0]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "#4997c9\n"
     ]
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAEkAAABICAYAAAC6L9h5AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjMuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8vihELAAAACXBIWXMAAAsTAAALEwEAmpwYAAABF0lEQVR4nO3csQnCQBxG8TsRIWAhBItULiFYOJEjuIqdU7iKKbW20gH+9iLyDg1J8P3KI8XHg0uX5IhI+mzS94AxMBJgJMBIgJEAIwHTkodn80VUddPVlt7dr+dbRCxfz4siVXWTtvvj71YNzGm3ubw797oBRgKMBBgJMBJgJMBIgJEAIwFGAowEGAkwEmAkwEiAkQAjAUYCjAQYCTASYCTASICRACMBRgKMBBgJMBJgJMBIgJEAIwFGAowEGAkwEmAkwEiAkQAjAUYCjAQYCTASYCTASICRACMBRgKMBBgJyCU/dMk5P1JKbXdzerf6+mvulFIbEesfDRoNrxtgJKA00qGTFQNX9OL+V143wEiAkQAjAUYCng7hHvFK4MPOAAAAAElFTkSuQmCC\n",
      "text/plain": [
       "<Figure size 72x72 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "print(palette[-1])\n",
    "sns.palplot([palette[-1]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_first_checkin_date(name):\n",
    "    sr = df2[name]\n",
    "    if 'y' in sr.unique():\n",
    "        r = sr[sr == 'y'].index[0]\n",
    "    else:\n",
    "        r = ''\n",
    "    return r\n",
    "\n",
    "def get_last_checkin_date(name):\n",
    "    sr = df2[name]\n",
    "    if 'y' in sr.unique():\n",
    "        r = sr[sr == 'y'].index[-1]\n",
    "    else:\n",
    "        r = ''\n",
    "    return r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes_df = pd.DataFrame(columns=['name', 'label', 'color', 'nodeType__2', 'city__2', 'country__2'])\n",
    "k = 0\n",
    "for name in cast:\n",
    "#     nodes_df.loc[k] = [name, palette[k]]\n",
    "    nodes_df.loc[k] = [\n",
    "        name, name, 'green', 'cast',\n",
    "        get_first_checkin_date(name),\n",
    "        get_last_checkin_date(name),\n",
    "    ] # use city__2 and country__2 for first and last date respectively\n",
    "    k += 1\n",
    "for date in dates:\n",
    "    if k < sum(df2.shape): # date\n",
    "        date_date = pd.to_datetime(date)\n",
    "        if date_date in dunham_df2.index:\n",
    "            city = dunham_df2.loc[date_date, 'CITY']\n",
    "            country = dunham_df2.loc[date_date, 'COUNTRY']\n",
    "        else:\n",
    "            city = ''\n",
    "            country = ''\n",
    "        if comprehensive_sr[date] == 'y':\n",
    "            nodes_df.loc[k] = [date, date, 'black', 'checkin', city, country]\n",
    "        else: # comprehensive_sr[date] == 'n':\n",
    "            nodes_df.loc[k] = [date, date, 'lightgrey', 'checkin', city, country]\n",
    "    else: # cast\n",
    "        name = date\n",
    "        label = name[:-2]\n",
    "        nodes_df.loc[k] = [\n",
    "            name, label, 'green', 'cast',\n",
    "            get_first_checkin_date(label),\n",
    "            get_last_checkin_date(label),\n",
    "        ] # use city__2 and country__2 for first and last date respectively\n",
    "    k += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(34, 6)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>label</th>\n",
       "      <th>color</th>\n",
       "      <th>nodeType__2</th>\n",
       "      <th>city__2</th>\n",
       "      <th>country__2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vanoye Aikens</td>\n",
       "      <td>Vanoye Aikens</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Apr 07, 1947</td>\n",
       "      <td>Jan 04, 1948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James Alexander</td>\n",
       "      <td>James Alexander</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Apr 07, 1947</td>\n",
       "      <td>Jan 04, 1948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ronnie Aul</td>\n",
       "      <td>Ronnie Aul</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Apr 07, 1947</td>\n",
       "      <td>Sep 01, 1947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wilbert Bradley</td>\n",
       "      <td>Wilbert Bradley</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Apr 07, 1947</td>\n",
       "      <td>Jan 04, 1948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jay Brooks</td>\n",
       "      <td>Jay Brooks</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Dec 29, 1947</td>\n",
       "      <td>Jan 04, 1948</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name            label  color nodeType__2       city__2  \\\n",
       "0    Vanoye Aikens    Vanoye Aikens  green        cast  Apr 07, 1947   \n",
       "1  James Alexander  James Alexander  green        cast  Apr 07, 1947   \n",
       "2       Ronnie Aul       Ronnie Aul  green        cast  Apr 07, 1947   \n",
       "3  Wilbert Bradley  Wilbert Bradley  green        cast  Apr 07, 1947   \n",
       "4       Jay Brooks       Jay Brooks  green        cast  Dec 29, 1947   \n",
       "\n",
       "     country__2  \n",
       "0  Jan 04, 1948  \n",
       "1  Jan 04, 1948  \n",
       "2  Sep 01, 1947  \n",
       "3  Jan 04, 1948  \n",
       "4  Jan 04, 1948  "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>label</th>\n",
       "      <th>color</th>\n",
       "      <th>nodeType__2</th>\n",
       "      <th>city__2</th>\n",
       "      <th>country__2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>Eddy Clay 2</td>\n",
       "      <td>Eddy Clay</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Mar 14, 1947</td>\n",
       "      <td>Jan 04, 1948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Jean Leon Destine 2</td>\n",
       "      <td>Jean Leon Destine</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Apr 07, 1947</td>\n",
       "      <td>Sep 01, 1947</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>Lucille Ellis 2</td>\n",
       "      <td>Lucille Ellis</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Apr 07, 1947</td>\n",
       "      <td>Jan 04, 1948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>Awilda Frasier 2</td>\n",
       "      <td>Awilda Frasier</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Jan 04, 1948</td>\n",
       "      <td>Jan 04, 1948</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>Tommy Gomez 2</td>\n",
       "      <td>Tommy Gomez</td>\n",
       "      <td>green</td>\n",
       "      <td>cast</td>\n",
       "      <td>Dec 29, 1947</td>\n",
       "      <td>Jan 04, 1948</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   name              label  color nodeType__2       city__2  \\\n",
       "29          Eddy Clay 2          Eddy Clay  green        cast  Mar 14, 1947   \n",
       "30  Jean Leon Destine 2  Jean Leon Destine  green        cast  Apr 07, 1947   \n",
       "31      Lucille Ellis 2      Lucille Ellis  green        cast  Apr 07, 1947   \n",
       "32     Awilda Frasier 2     Awilda Frasier  green        cast  Jan 04, 1948   \n",
       "33        Tommy Gomez 2        Tommy Gomez  green        cast  Dec 29, 1947   \n",
       "\n",
       "      country__2  \n",
       "29  Jan 04, 1948  \n",
       "30  Sep 01, 1947  \n",
       "31  Jan 04, 1948  \n",
       "32  Jan 04, 1948  \n",
       "33  Jan 04, 1948  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nodes_df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_n_checkins(name):\n",
    "    return df3[name].value_counts()['y']\n",
    "\n",
    "def get_first_time(name):\n",
    "    return list(df3[name]).index('y')\n",
    "\n",
    "def get_duration(name):\n",
    "    try:\n",
    "        indices = [j for j, v in enumerate(df3[name]) if v == 'y']\n",
    "        first_index = indices[0]\n",
    "        last_index = indices[-2]\n",
    "        first_date = pd.to_datetime(df3.index[first_index])\n",
    "        last_date = pd.to_datetime(df3.index[last_index])\n",
    "        r = (last_date - first_date).days\n",
    "    except:\n",
    "        r = 0\n",
    "    return r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "cast_to_sort = [(\n",
    "    name,\n",
    "    get_n_checkins(name),\n",
    "    get_first_time(name),\n",
    "    get_duration(name),\n",
    ") for name in cast]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "cast_df = pd.DataFrame(cast_to_sort, columns=['NAME', 'N_CHECKINGS', 'FIRST_TIME', 'DURATION'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>NAME</th>\n",
       "      <th>N_CHECKINGS</th>\n",
       "      <th>FIRST_TIME</th>\n",
       "      <th>DURATION</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Vanoye Aikens</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James Alexander</td>\n",
       "      <td>10</td>\n",
       "      <td>1</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Ronnie Aul</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>147</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Wilbert Bradley</td>\n",
       "      <td>9</td>\n",
       "      <td>1</td>\n",
       "      <td>272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Jay Brooks</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              NAME  N_CHECKINGS  FIRST_TIME  DURATION\n",
       "0    Vanoye Aikens           10           1       272\n",
       "1  James Alexander           10           1       272\n",
       "2       Ronnie Aul            7           1       147\n",
       "3  Wilbert Bradley            9           1       272\n",
       "4       Jay Brooks            4           7         6"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cast_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_index(v):\n",
    "    return nodes_df[nodes_df.name == v].index[0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Coloring by first time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted_cast = cast_df.sort_values('FIRST_TIME', ascending=False).NAME"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "10    Awilda Frasier\n",
       "4         Jay Brooks\n",
       "11       Tommy Gomez\n",
       "6      Umberto Canto\n",
       "0      Vanoye Aikens\n",
       "Name: NAME, dtype: object"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sorted_cast.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 205 ms, sys: 1.87 ms, total: 206 ms\n",
      "Wall time: 205 ms\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "\n",
    "links_df = pd.DataFrame(columns=['source', 'target', 'value', 'label', 'color'])\n",
    "k = 0\n",
    "for i, name in enumerate(sorted_cast):\n",
    "    sr = df3[name]\n",
    "    last_yes = None\n",
    "    for j in range(sr.size):\n",
    "        if sr[j] == 'y':\n",
    "            new_yes = dates[j]\n",
    "            if not last_yes:\n",
    "                links_df.loc[k] = [get_index(name), get_index(new_yes), 1, name, palette[i]]\n",
    "            else:\n",
    "                links_df.loc[k] = [get_index(last_yes), get_index(new_yes), 1, name, palette[i]]\n",
    "            last_yes = new_yes\n",
    "            k += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(81, 5)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "links_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>source</th>\n",
       "      <th>target</th>\n",
       "      <th>value</th>\n",
       "      <th>label</th>\n",
       "      <th>color</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>Awilda Frasier</td>\n",
       "      <td>#95c5df</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>21</td>\n",
       "      <td>32</td>\n",
       "      <td>1</td>\n",
       "      <td>Awilda Frasier</td>\n",
       "      <td>#95c5df</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>Jay Brooks</td>\n",
       "      <td>#8dc1dd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>19</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>Jay Brooks</td>\n",
       "      <td>#8dc1dd</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>1</td>\n",
       "      <td>Jay Brooks</td>\n",
       "      <td>#8dc1dd</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  source target value           label    color\n",
       "0     10     21     1  Awilda Frasier  #95c5df\n",
       "1     21     32     1  Awilda Frasier  #95c5df\n",
       "2      4     19     1      Jay Brooks  #8dc1dd\n",
       "3     19     20     1      Jay Brooks  #8dc1dd\n",
       "4     20     21     1      Jay Brooks  #8dc1dd"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "links_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "nodes_df.to_csv('web/data/nodes_by_first_time.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [],
   "source": [
    "links_df.to_csv('web/data/links_by_first_time.csv', index=False)"
   ]
  }
 ],
 "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}