Testing the Excel date import problem in Python

python_excel_dates.ipynb

{
 "metadata": {
  "name": ""
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Python handles Excel dates properly using Pandas"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "There's been some recent discussion of issues with importing the dates from certain types of Excel files into R. It turns out that there are two different ways for Excel to store dates, and in one of these cases importing dates directly from an Excel file into R changes the date by 4 years and 1 day. See the [whole story told by Kara Woo](http://datapub.cdlib.org/2014/04/10/abandon-all-hope-ye-who-enter-dates-in-excel/). It takes a disciplined researcher to catch this kind of issue.\n",
      "\n",
      "I was curious to see if the same problem occurred in Python, so I asked Kara to send me some sample data that triggered the same problem. The actual dates run from 1950-2001. Importing in R using XLConnect gives dates from 1946-1997."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import pandas\n",
      "\n",
      "data = pandas.io.excel.read_excel(\"dummydata.xlsx\", \"Sheet1\")\n",
      "data"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
        "<table border=\"1\" class=\"dataframe\">\n",
        "  <thead>\n",
        "    <tr style=\"text-align: right;\">\n",
        "      <th></th>\n",
        "      <th>date</th>\n",
        "      <th>year</th>\n",
        "      <th>month</th>\n",
        "      <th>day</th>\n",
        "      <th>data</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td>1950-05-03 00:00:00</td>\n",
        "      <td> 1950</td>\n",
        "      <td>  5</td>\n",
        "      <td>  3</td>\n",
        "      <td> 4.5</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td>1950-01-01 00:00:00</td>\n",
        "      <td> 1950</td>\n",
        "      <td>  1</td>\n",
        "      <td>  1</td>\n",
        "      <td> 8.9</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td>1953-12-20 00:00:00</td>\n",
        "      <td> 1953</td>\n",
        "      <td> 12</td>\n",
        "      <td> 20</td>\n",
        "      <td> 2.2</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>3</th>\n",
        "      <td>1976-11-19 00:00:00</td>\n",
        "      <td> 1976</td>\n",
        "      <td> 11</td>\n",
        "      <td> 19</td>\n",
        "      <td> 5.6</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>4</th>\n",
        "      <td>1989-12-31 00:00:00</td>\n",
        "      <td> 1989</td>\n",
        "      <td> 12</td>\n",
        "      <td> 31</td>\n",
        "      <td> 6.6</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>5</th>\n",
        "      <td>2001-04-14 00:00:00</td>\n",
        "      <td> 2001</td>\n",
        "      <td>  4</td>\n",
        "      <td> 14</td>\n",
        "      <td> 4.1</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 1,
       "text": [
        "                 date  year  month  day  data\n",
        "0 1950-05-03 00:00:00  1950      5    3   4.5\n",
        "1 1950-01-01 00:00:00  1950      1    1   8.9\n",
        "2 1953-12-20 00:00:00  1953     12   20   2.2\n",
        "3 1976-11-19 00:00:00  1976     11   19   5.6\n",
        "4 1989-12-31 00:00:00  1989     12   31   6.6\n",
        "5 2001-04-14 00:00:00  2001      4   14   4.1"
       ]
      }
     ],
     "prompt_number": 1
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "Thanks Pandas!"
     ]
    }
   ],
   "metadata": {}
  }
 ]
}