03-city_and_country.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": [
    "dunham_df = pd.read_csv('data/out/dunham_1947-60.csv')\n",
    "dunham_df.fillna('', inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10, 13)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dunham_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>DATE</th>\n",
       "      <th>CITY1</th>\n",
       "      <th>COUNTRY1</th>\n",
       "      <th>CITY2</th>\n",
       "      <th>COUNTRY2</th>\n",
       "      <th>WORKING</th>\n",
       "      <th>HOTEL_ADDRESS</th>\n",
       "      <th>VENUE1</th>\n",
       "      <th>VENUE_TYPE1</th>\n",
       "      <th>VENUE2</th>\n",
       "      <th>VENUE_TYPE2</th>\n",
       "      <th>NOTES</th>\n",
       "      <th>SOURCE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1947-03-14</td>\n",
       "      <td>New York City</td>\n",
       "      <td>USA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>y</td>\n",
       "      <td></td>\n",
       "      <td>Hotel Delmonico</td>\n",
       "      <td>n</td>\n",
       "      <td>Roxy</td>\n",
       "      <td>n</td>\n",
       "      <td>Diary--\"All Star Show for the Wounded\" between...</td>\n",
       "      <td>Display Ad New York Times (1923-Current file);...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1947-04-07</td>\n",
       "      <td>New York City</td>\n",
       "      <td>USA</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>y</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Diary--rehearsal</td>\n",
       "      <td>SIU Box 142 Folder 7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1947-06-28</td>\n",
       "      <td>Mexico City</td>\n",
       "      <td>Mexico</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>y</td>\n",
       "      <td>Arenal #7, San Angel, Mexico, D.F.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1947-07-01</td>\n",
       "      <td>Mexico City</td>\n",
       "      <td>Mexico</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>y</td>\n",
       "      <td>Arenal #7, San Angel, Mexico, D.F.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>Rehearsing every day at Dunham's rented house;...</td>\n",
       "      <td>SIU Box 10 Folder 4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1947-07-06</td>\n",
       "      <td>Mexico City</td>\n",
       "      <td>Mexico</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>y</td>\n",
       "      <td>Arenal #7, San Angel, Mexico, D.F.</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         DATE          CITY1 COUNTRY1 CITY2 COUNTRY2 WORKING  \\\n",
       "0  1947-03-14  New York City      USA                      y   \n",
       "1  1947-04-07  New York City      USA                      y   \n",
       "2  1947-06-28    Mexico City   Mexico                      y   \n",
       "3  1947-07-01    Mexico City   Mexico                      y   \n",
       "4  1947-07-06    Mexico City   Mexico                      y   \n",
       "\n",
       "                        HOTEL_ADDRESS           VENUE1 VENUE_TYPE1 VENUE2  \\\n",
       "0                                      Hotel Delmonico           n   Roxy   \n",
       "1                                                                           \n",
       "2  Arenal #7, San Angel, Mexico, D.F.                                       \n",
       "3  Arenal #7, San Angel, Mexico, D.F.                                       \n",
       "4  Arenal #7, San Angel, Mexico, D.F.                                       \n",
       "\n",
       "  VENUE_TYPE2                                              NOTES  \\\n",
       "0           n  Diary--\"All Star Show for the Wounded\" between...   \n",
       "1                                               Diary--rehearsal   \n",
       "2                                                                  \n",
       "3              Rehearsing every day at Dunham's rented house;...   \n",
       "4                                                                  \n",
       "\n",
       "                                              SOURCE  \n",
       "0  Display Ad New York Times (1923-Current file);...  \n",
       "1                               SIU Box 142 Folder 7  \n",
       "2                                                     \n",
       "3                                SIU Box 10 Folder 4  \n",
       "4                                                     "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dunham_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Processing data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def pick_city(row):\n",
    "    if row['CITY1'] and 'transit' not in row['CITY1'] and '[[' not in row['CITY1']:\n",
    "        r = row['CITY1']\n",
    "    elif row['CITY2'] and 'transit' not in row['CITY2'] and '[[' not in row['CITY2']:\n",
    "        r = row['CITY2']\n",
    "    else:\n",
    "        r = ''\n",
    "    return r\n",
    "\n",
    "def pick_country(row):\n",
    "    if row['CITY1'] and 'transit' not in row['CITY1'] and '[[' not in row['CITY1']:\n",
    "        r = row['COUNTRY1']\n",
    "    elif row['CITY2'] and 'transit' not in row['CITY2'] and '[[' not in row['CITY2']:\n",
    "        r = row['COUNTRY2']\n",
    "    else:\n",
    "        r = ''\n",
    "    return r"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "dunham_df2 = dunham_df[['DATE', 'CITY1', 'COUNTRY1', 'CITY2', 'COUNTRY2']].copy()\n",
    "dunham_df2['CITY'] = dunham_df2.apply(pick_city, axis=1)\n",
    "dunham_df2['COUNTRY'] = dunham_df2.apply(pick_country, axis=1)\n",
    "dunham_df2 = dunham_df2[dunham_df2.CITY != ''][['DATE', 'CITY', 'COUNTRY']]\n",
    "dunham_df2.set_index('DATE', inplace=True)\n",
    "dunham_df2.index.name = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10, 2)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dunham_df2.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_df2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Saving data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "dunham_df2.to_csv('data/out/everyday_city_country.csv')"
   ]
  }
 ],
 "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
}