Working with Excel from Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "***\n",
    "# Working with Excel Spreadsheets from Python\n",
    "***"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### About this notebook: \n",
    "Notebook prepared by **Jesus Perez Colino** Version 0.1, First Released: 01/10/2014, Alpha.  \n",
    "\n",
    "- This work is licensed under a [Creative Commons Attribution-ShareAlike 3.0 Unported License](http://creativecommons.org/licenses/by-sa/3.0/deed.en_US). This work is offered for free, with the hope that it will be useful.\n",
    "\n",
    "\n",
    "- **Summary**: This notebook contains a brief introduction about how to work with **Excel Spreadsheets** from Python using **openpyxl** and **pandas**.\n",
    "\n",
    "\n",
    "- **Python & packages versions** to reproduce the results of this notebook: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--------- Least-Squares MC for American Options: Conditions for Replication ---------\n",
      "Python version:     2.7.10 |Anaconda 2.4.0 (x86_64)| (default, Oct 19 2015, 18:31:17) \n",
      "[GCC 4.2.1 (Apple Inc. build 5577)]\n",
      "Numpy version:      1.10.1\n",
      "Pandas version:     0.17.0\n",
      "Openpyxl version:   2.2.6\n",
      "IPython version:    4.0.0\n",
      "-------------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "import IPython\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "import openpyxl\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "from sys import version \n",
    "print ' Least-Squares MC for American Options: Conditions for Replication '.center(85,\"-\")\n",
    "print 'Python version:     ' + version \n",
    "print 'Numpy version:      ' + np.__version__\n",
    "print 'Pandas version:     ' + pd.__version__\n",
    "print 'Openpyxl version:   ' + openpyxl.__version__\n",
    "print 'IPython version:    ' + IPython.__version__\n",
    "print '-'*85"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Openpyxl"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.compat import range\n",
    "from openpyxl.cell import get_column_letter"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1. Write a workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "wb = Workbook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "ws1 = wb.active\n",
    "ws1.title = 'range names'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for row in range(1,10):\n",
    "    ws1.append(range(100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "ws2 = wb.create_sheet(title=\"Pi\")\n",
    "ws3 = wb.create_sheet(title=\"Data\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['range names', 'Pi', 'Data']"
      ]
     },
     "execution_count": 165,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wb.get_sheet_names()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from math import pi\n",
    "ws2['A1'] = pi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "for row in range(1,20):\n",
    "    for col in range(1,27):\n",
    "        _ = ws3.cell(column=col, row=row, value=\"{0}\".format(get_column_letter(col)) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "3.14159265359\n",
      "A\n"
     ]
    }
   ],
   "source": [
    "print ws1['A1'].value\n",
    "print ws2['A1'].value\n",
    "print ws3['A1'].value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Cell Pi.A1>\n",
      "3.14159265359\n"
     ]
    }
   ],
   "source": [
    "print ws2.cell(row = 1, column = 1)\n",
    "print ws2.cell(row = 1, column = 1).value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Save the workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 170,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "wb.save(filename = 'my_book.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 3. Read the workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['range names', 'Pi', 'Data']\n"
     ]
    }
   ],
   "source": [
    "wb_new = load_workbook(filename = 'my_book1.xlsx')\n",
    "print(wb_new.get_sheet_names())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 173,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3.14159265359\n"
     ]
    }
   ],
   "source": [
    "Pi = wb_new['Pi']\n",
    "print Pi['A1'].value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 174,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<generator object get_squared_range at 0x105cded20>\n"
     ]
    }
   ],
   "source": [
    "range_numbers = wb_new['range names']\n",
    "cell_range = range_numbers['A1':'C4']\n",
    "print cell_range"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Cell range names.A1> :  0 :  General\n",
      "<Cell range names.B1> :  1 :  General\n",
      "<Cell range names.C1> :  2 :  General\n",
      "<Cell range names.A2> :  0 :  General\n",
      "<Cell range names.B2> :  1 :  General\n",
      "<Cell range names.C2> :  2 :  General\n",
      "<Cell range names.A3> :  0 :  General\n",
      "<Cell range names.B3> :  1 :  General\n",
      "<Cell range names.C3> :  2 :  General\n",
      "<Cell range names.A4> :  0 :  General\n",
      "<Cell range names.B4> :  1 :  General\n",
      "<Cell range names.C4> :  2 :  General\n"
     ]
    }
   ],
   "source": [
    "for row in cell_range:\n",
    "    for cell in row:\n",
    "        print cell,': ',(cell.value), ': ', (cell.number_format)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas and xlswriter"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 192,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.440658</td>\n",
       "      <td>0.382333</td>\n",
       "      <td>-1.221716</td>\n",
       "      <td>-0.115529</td>\n",
       "      <td>0.919715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.423453</td>\n",
       "      <td>-0.148458</td>\n",
       "      <td>1.526270</td>\n",
       "      <td>-0.126653</td>\n",
       "      <td>1.212850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.873953</td>\n",
       "      <td>1.380482</td>\n",
       "      <td>0.992620</td>\n",
       "      <td>0.103763</td>\n",
       "      <td>-0.775660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.704033</td>\n",
       "      <td>1.218312</td>\n",
       "      <td>-0.156292</td>\n",
       "      <td>-0.331982</td>\n",
       "      <td>-1.438742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.002500</td>\n",
       "      <td>-0.504881</td>\n",
       "      <td>0.502553</td>\n",
       "      <td>-1.357074</td>\n",
       "      <td>-1.244172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.014213</td>\n",
       "      <td>-0.637136</td>\n",
       "      <td>0.019642</td>\n",
       "      <td>0.956059</td>\n",
       "      <td>1.280212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.135952</td>\n",
       "      <td>1.143647</td>\n",
       "      <td>1.109336</td>\n",
       "      <td>-0.972992</td>\n",
       "      <td>0.066483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.188338</td>\n",
       "      <td>-1.207682</td>\n",
       "      <td>0.433817</td>\n",
       "      <td>1.321672</td>\n",
       "      <td>0.458912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.219797</td>\n",
       "      <td>0.084692</td>\n",
       "      <td>1.214438</td>\n",
       "      <td>-0.432551</td>\n",
       "      <td>2.511804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2.859288</td>\n",
       "      <td>1.191991</td>\n",
       "      <td>1.975414</td>\n",
       "      <td>1.009345</td>\n",
       "      <td>1.422283</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c         d         e\n",
       "0  0.440658  0.382333 -1.221716 -0.115529  0.919715\n",
       "1  0.423453 -0.148458  1.526270 -0.126653  1.212850\n",
       "2  0.873953  1.380482  0.992620  0.103763 -0.775660\n",
       "3  1.704033  1.218312 -0.156292 -0.331982 -1.438742\n",
       "4  1.002500 -0.504881  0.502553 -1.357074 -1.244172\n",
       "5  1.014213 -0.637136  0.019642  0.956059  1.280212\n",
       "6  0.135952  1.143647  1.109336 -0.972992  0.066483\n",
       "7  0.188338 -1.207682  0.433817  1.321672  0.458912\n",
       "8  0.219797  0.084692  1.214438 -0.432551  2.511804\n",
       "9  2.859288  1.191991  1.975414  1.009345  1.422283"
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.DataFrame(np.random.randn(10, 5),\n",
    "                   columns=['a', 'b', 'c', 'd', 'e'])\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "writer = pd.ExcelWriter('my_book2.xlsx', engine='xlsxwriter')\n",
    "df1.to_excel(writer,'random numbers')\n",
    "writer.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.440658</td>\n",
       "      <td>0.382333</td>\n",
       "      <td>-1.221716</td>\n",
       "      <td>-0.115529</td>\n",
       "      <td>0.919715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.423453</td>\n",
       "      <td>-0.148458</td>\n",
       "      <td>1.526270</td>\n",
       "      <td>-0.126653</td>\n",
       "      <td>1.212850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.873953</td>\n",
       "      <td>1.380482</td>\n",
       "      <td>0.992620</td>\n",
       "      <td>0.103763</td>\n",
       "      <td>-0.775660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1.704033</td>\n",
       "      <td>1.218312</td>\n",
       "      <td>-0.156292</td>\n",
       "      <td>-0.331982</td>\n",
       "      <td>-1.438742</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1.002500</td>\n",
       "      <td>-0.504881</td>\n",
       "      <td>0.502553</td>\n",
       "      <td>-1.357074</td>\n",
       "      <td>-1.244172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.014213</td>\n",
       "      <td>-0.637136</td>\n",
       "      <td>0.019642</td>\n",
       "      <td>0.956059</td>\n",
       "      <td>1.280212</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0.135952</td>\n",
       "      <td>1.143647</td>\n",
       "      <td>1.109336</td>\n",
       "      <td>-0.972992</td>\n",
       "      <td>0.066483</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0.188338</td>\n",
       "      <td>-1.207682</td>\n",
       "      <td>0.433817</td>\n",
       "      <td>1.321672</td>\n",
       "      <td>0.458912</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.219797</td>\n",
       "      <td>0.084692</td>\n",
       "      <td>1.214438</td>\n",
       "      <td>-0.432551</td>\n",
       "      <td>2.511804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2.859288</td>\n",
       "      <td>1.191991</td>\n",
       "      <td>1.975414</td>\n",
       "      <td>1.009345</td>\n",
       "      <td>1.422283</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          a         b         c         d         e\n",
       "0  0.440658  0.382333 -1.221716 -0.115529  0.919715\n",
       "1  0.423453 -0.148458  1.526270 -0.126653  1.212850\n",
       "2  0.873953  1.380482  0.992620  0.103763 -0.775660\n",
       "3  1.704033  1.218312 -0.156292 -0.331982 -1.438742\n",
       "4  1.002500 -0.504881  0.502553 -1.357074 -1.244172\n",
       "5  1.014213 -0.637136  0.019642  0.956059  1.280212\n",
       "6  0.135952  1.143647  1.109336 -0.972992  0.066483\n",
       "7  0.188338 -1.207682  0.433817  1.321672  0.458912\n",
       "8  0.219797  0.084692  1.214438 -0.432551  2.511804\n",
       "9  2.859288  1.191991  1.975414  1.009345  1.422283"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfread = pd.read_excel('my_book2.xlsx')\n",
    "dfread"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}