Excel-like data grid editor for Pandas in the IPython notebook with Handsontable

handsondataframe.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:7e1bf2265434d19735d8e813be517ec7d20d139e38139b9eb1df2fb8ffe5dc1d"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "# Excel-like data grid editor for Pandas in the IPython notebook"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "This proof-of-concept brings an **interactive Excel-like data grid editor** in the IPython notebook, compatible with Pandas' *DataFrame*. It means that whenever you have a *DataFrame* in the IPython notebook, you can edit it within an integrated GUI in the notebook, and the corresponding Python object will be automatically updated in real-time."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "This proof-of-concept uses the new widget machinery of IPython 2.0. You need the latest development version of IPython (or v2.0beta, or v2.0 when it's released within the next few weeks). You also need the [Handsontable](http://handsontable.com/) Javascript library. Other data grid editors could probably be used as well."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## Getting started"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "There are multiple steps to make this example work (assuming you have the latest IPython).\n",
      "\n",
      "1. Go [here](https://github.com/warpech/jquery-handsontable/tree/master/dist).\n",
      "2. Download `jquery.handsontable.full.css` and `jquery.handsontable.full.js`, and put these two files in `~\\.ipython\\profile_default\\static\\custom\\`.\n",
      "3. In this folder, add the following line in `custom.js`:\n",
      "```javascript\n",
      "require(['/static/custom/jquery.handsontable.full.js']);\n",
      "```\n",
      "4. In this folder, add the following line in `custom.css`:\n",
      "```css\n",
      "@import \"/static/custom/jquery.handsontable.full.css\"\n",
      "```\n",
      "5. Execute this notebook."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "## How to do it..."
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "* Let's import a few functions and classes."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from __future__ import print_function # For py 2.7 compat\n",
      "\n",
      "from IPython.html import widgets # Widget definitions\n",
      "from IPython.display import display # Used to display widgets in the notebook\n",
      "from IPython.utils.traitlets import Unicode # Used to declare attributes of our widget"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "* We create a new widget. The `value` trait will contain the JSON representation of the entire table. This trait will be synchronized between Python and Javascript by IPython 2.0's widget machinery."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "class HandsonTableWidget(widgets.DOMWidget):\n",
      "    _view_name = Unicode('HandsonTableView', sync=True)\n",
      "    value = Unicode(sync=True)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "* Now we write the Javascript code for the widget. There is a tiny bit of boilerplate code, but have a look at the three important functions that are responsible for the synchronization:\n",
      "\n",
      "    * `render` for the widget initialization\n",
      "    * `update` for Python --> JS update\n",
      "    * `handle_table_change` for JS --> Python update\n",
      "\n",
      "This is a bit oversimplified, of course. You will find more information on this [tutorial](http://nbviewer.ipython.org/github/ipython/ipython/blob/master/examples/widgets/Part%206%20-%20Custom%20Widget.ipynb)."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "%%javascript\n",
      "var table_id = 0;\n",
      "require([\"widgets/js/widget\"], function(WidgetManager){    \n",
      "    // Define the HandsonTableView\n",
      "    var HandsonTableView = IPython.DOMWidgetView.extend({\n",
      "        \n",
      "        render: function(){\n",
      "            // CREATION OF THE WIDGET IN THE NOTEBOOK.\n",
      "            \n",
      "            // Add a <div> in the widget area.\n",
      "            this.$table = $('<div />')\n",
      "                .attr('id', 'table_' + (table_id++))\n",
      "                .appendTo(this.$el);\n",
      "            // Create the Handsontable table.\n",
      "            this.$table.handsontable({\n",
      "            });\n",
      "            \n",
      "        },\n",
      "        \n",
      "        update: function() {\n",
      "            // PYTHON --> JS UPDATE.\n",
      "            \n",
      "            // Get the model's JSON string, and parse it.\n",
      "            var data = $.parseJSON(this.model.get('value'));\n",
      "            // Give it to the Handsontable widget.\n",
      "            this.$table.handsontable({data: data});\n",
      "            \n",
      "            // Don't touch this...\n",
      "            return HandsonTableView.__super__.update.apply(this);\n",
      "        },\n",
      "        \n",
      "        // Tell Backbone to listen to the change event of input controls.\n",
      "        events: {\"change\": \"handle_table_change\"},\n",
      "        \n",
      "        handle_table_change: function(event) {\n",
      "            // JS --> PYTHON UPDATE.\n",
      "            \n",
      "            // Get the table instance.\n",
      "            var ht = this.$table.handsontable('getInstance');\n",
      "            // Get the data, and serialize it in JSON.\n",
      "            var json = JSON.stringify(ht.getData());\n",
      "            // Update the model with the JSON string.\n",
      "            this.model.set('value', json);\n",
      "            \n",
      "            // Don't touch this...\n",
      "            this.touch();\n",
      "        },\n",
      "    });\n",
      "    \n",
      "    // Register the HandsonTableView with the widget manager.\n",
      "    WidgetManager.register_widget_view('HandsonTableView', HandsonTableView);\n",
      "});"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "javascript": [
        "var table_id = 0;\n",
        "require([\"widgets/js/widget\"], function(WidgetManager){    \n",
        "    // Define the HandsonTableView\n",
        "    var HandsonTableView = IPython.DOMWidgetView.extend({\n",
        "        \n",
        "        render: function(){\n",
        "            // CREATION OF THE WIDGET IN THE NOTEBOOK.\n",
        "            \n",
        "            // Add a <div> in the widget area.\n",
        "            this.$table = $('<div />')\n",
        "                .attr('id', 'table_' + (table_id++))\n",
        "                .appendTo(this.$el);\n",
        "            // Create the Handsontable table.\n",
        "            this.$table.handsontable({\n",
        "            });\n",
        "            \n",
        "        },\n",
        "        \n",
        "        update: function() {\n",
        "            // PYTHON --> JS UPDATE.\n",
        "            \n",
        "            // Get the model's JSON string, and parse it.\n",
        "            var data = $.parseJSON(this.model.get('value'));\n",
        "            // Give it to the Handsontable widget.\n",
        "            this.$table.handsontable({data: data});\n",
        "            \n",
        "            // Don't touch this...\n",
        "            return HandsonTableView.__super__.update.apply(this);\n",
        "        },\n",
        "        \n",
        "        // Tell Backbone to listen to the change event of input controls.\n",
        "        events: {\"change\": \"handle_table_change\"},\n",
        "        \n",
        "        handle_table_change: function(event) {\n",
        "            // JS --> PYTHON UPDATE.\n",
        "            \n",
        "            // Get the table instance.\n",
        "            var ht = this.$table.handsontable('getInstance');\n",
        "            // Get the data, and serialize it in JSON.\n",
        "            var json = JSON.stringify(ht.getData());\n",
        "            // Update the model with the JSON string.\n",
        "            this.model.set('value', json);\n",
        "            \n",
        "            // Don't touch this...\n",
        "            this.touch();\n",
        "        },\n",
        "    });\n",
        "    \n",
        "    // Register the HandsonTableView with the widget manager.\n",
        "    WidgetManager.register_widget_view('HandsonTableView', HandsonTableView);\n",
        "});"
       ],
       "metadata": {},
       "output_type": "display_data",
       "text": [
        "<IPython.core.display.Javascript at 0x7a8f518>"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "* Now, we have a synchronized table widget that we can already use. But we'd like to integrate it with Pandas. To do this, we create a light wrapper around a `DataFrame` instance. We create two callback functions for synchronizing the Pandas object with the IPython widget. Changes in the GUI will automatically trigger a change in the `DataFrame`, but the converse is not true. You'll need to re-display the widget if you change the `DataFrame` in Python."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import StringIO\n",
      "import numpy as np\n",
      "import pandas as pd\n",
      "\n",
      "class HandsonDataFrame(object):\n",
      "    def __init__(self, df):\n",
      "        self._df = df\n",
      "        self._widget = HandsonTableWidget()\n",
      "        self._widget.on_trait_change(self._on_data_changed, 'value')\n",
      "        self._widget.on_displayed(self._on_displayed)\n",
      "        \n",
      "    def _on_displayed(self, e):\n",
      "        # DataFrame ==> Widget (upon initialization only)\n",
      "        json = self._df.to_json(orient='values')\n",
      "        self._widget.value = json\n",
      "        \n",
      "    def _on_data_changed(self, e, val):\n",
      "        # Widget ==> DataFrame (called every time the user\n",
      "        # changes a value in the graphical widget)\n",
      "        buf = StringIO.StringIO(val)\n",
      "        self._df = pd.read_json(buf, orient='values')\n",
      "        \n",
      "    def to_dataframe(self):\n",
      "        return self._df\n",
      "        \n",
      "    def show(self):\n",
      "        display(self._widget)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 4
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "* Now, let's test all that! We first create a random `DataFrame`."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "data = np.random.randint(size=(3, 5), low=100, high=900)\n",
      "df = pd.DataFrame(data)\n",
      "df"
     ],
     "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>0</th>\n",
        "      <th>1</th>\n",
        "      <th>2</th>\n",
        "      <th>3</th>\n",
        "      <th>4</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td> 661</td>\n",
        "      <td> 435</td>\n",
        "      <td> 308</td>\n",
        "      <td> 244</td>\n",
        "      <td> 778</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 330</td>\n",
        "      <td> 463</td>\n",
        "      <td> 687</td>\n",
        "      <td> 149</td>\n",
        "      <td> 417</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 317</td>\n",
        "      <td> 154</td>\n",
        "      <td> 155</td>\n",
        "      <td> 504</td>\n",
        "      <td> 549</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 5,
       "text": [
        "     0    1    2    3    4\n",
        "0  661  435  308  244  778\n",
        "1  330  463  687  149  417\n",
        "2  317  154  155  504  549"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "* We wrap it in a `HandsonDataFrame` and show it."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ht = HandsonDataFrame(df)\n",
      "ht.show()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 6
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "We can now *change* the values interactively, and they will be changed in Python automaticall."
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "ht.to_dataframe()"
     ],
     "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>0</th>\n",
        "      <th>1</th>\n",
        "      <th>2</th>\n",
        "      <th>3</th>\n",
        "      <th>4</th>\n",
        "    </tr>\n",
        "  </thead>\n",
        "  <tbody>\n",
        "    <tr>\n",
        "      <th>0</th>\n",
        "      <td>   1</td>\n",
        "      <td>   2</td>\n",
        "      <td>   3</td>\n",
        "      <td> 244</td>\n",
        "      <td> 778</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>1</th>\n",
        "      <td> 330</td>\n",
        "      <td> 463</td>\n",
        "      <td> 687</td>\n",
        "      <td> 149</td>\n",
        "      <td> 417</td>\n",
        "    </tr>\n",
        "    <tr>\n",
        "      <th>2</th>\n",
        "      <td> 317</td>\n",
        "      <td> 154</td>\n",
        "      <td> 155</td>\n",
        "      <td>   4</td>\n",
        "      <td> 549</td>\n",
        "    </tr>\n",
        "  </tbody>\n",
        "</table>\n",
        "</div>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 7,
       "text": [
        "     0    1    2    3    4\n",
        "0    1    2    3  244  778\n",
        "1  330  463  687  149  417\n",
        "2  317  154  155    4  549"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "There are many ways this proof-of-concept could be improved.\n",
      "\n",
      "* Synchronize only deltas instead of synchronizing the whole array every time (i.e. the method here would be slow on large tables).\n",
      "* Also, avoid recreating a new `DataFrame` at very change, but update the same `DataFrame` instance in-place.\n",
      "* Support for named columns.\n",
      "* Hide the wrapper, i.e. make it so that the default rich representation of the `DataFrame` in the notebook is the `HandsonDataFrame`.\n",
      "* Implement everything in an easy-to-use extension.\n",
      "* etc."
     ]
    }
   ],
   "metadata": {}
  }
 ]
}

I did a slight rewrite of your notebook to avoid having to change my IPython profile: https://gist.github.com/rdhyee/9897356

I believe the Handsontable package supports inserting rows and columns. Is it at all feasible to expose this functionality in Python?

Cyrille are you planning to continue this? I would like to help. let me know

An interactive tabular widget would be awsome helpful. Some frequently used task/operations are done way faster by clicking instead of writing df[df.x >= 3][['x','y']].sort... : (Un)Folding/Hiding columns, sorting, slice rows.
Editing features could range from single cell editing to selected cells are simultaniously edited (same value), or a kind of autofill of cells.
More viewing features would be even more useful, like filtering rows via checkbox for each unique column value, custom lambda expression (*).
Very very large tables should also be handled, e.g. by support scrolling; such tables need to be processed (filtered) to be easier to handle.

Does anyone know related efforts?

Would like to contribute, know how python works, no clue about java script and packages/framework for interactive widgets.

Hello, has someone managed to make this run on ipython 4? I am running into issues on javascript both with the original notebook and with the one from rdhyee.

see the more recent "qgrid" project: https://github.com/quantopian/qgrid

qgrid doesnt let you change data.
(It does, I only realised after double clicking)

This is a great idea and should definitely exist.