interactive-pivot-table.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Untitled0.ipynb",
      "provenance": [],
      "collapsed_sections": [
        "cO7JEeVwwvW0",
        "C7FLTutpw05D"
      ]
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cO7JEeVwwvW0",
        "colab_type": "text"
      },
      "source": [
        "# Pivot Table Using Pandas"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "cXA_Vizqjq19",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import numpy as np\n",
        "import pandas as pd\n",
        "from IPython.display import HTML"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "BWehXMWEpRIx",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df = pd.DataFrame({\"A\": [\"boy\", \"boy\", \"boy\", \"boy\", \"boy\",\n",
        "                         \"girl\", \"girl\", \"girl\", \"girl\"],\n",
        "                   \"B\": [\"one\", \"one\", \"one\", \"two\", \"two\",\n",
        "                         \"one\", \"one\", \"two\", \"two\"],\n",
        "                   \"C\" : [\"sports\", \"non_sports\", \"sports\", \"sports\", \"sports\",\n",
        "                          \"non_sports\", \"non_sports\", \"sports\", \"sports\"],\n",
        "                   \"D\": [50, 55, 67, 85, 44, 84, 65, 56, 87]})"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KwalRA6xzDSd",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "2f1aa981-dd17-43e8-d2bc-d83fae281156"
      },
      "source": [
        "df.shape"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "(9, 4)"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 3
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "zPqtDNtIy0TN",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 203
        },
        "outputId": "ae765627-a28c-4d0a-ccb1-ff4691d2a520"
      },
      "source": [
        "df.head()"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>A</th>\n",
              "      <th>B</th>\n",
              "      <th>C</th>\n",
              "      <th>D</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>boy</td>\n",
              "      <td>one</td>\n",
              "      <td>sports</td>\n",
              "      <td>50</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>boy</td>\n",
              "      <td>one</td>\n",
              "      <td>non_sports</td>\n",
              "      <td>55</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>boy</td>\n",
              "      <td>one</td>\n",
              "      <td>sports</td>\n",
              "      <td>67</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>boy</td>\n",
              "      <td>two</td>\n",
              "      <td>sports</td>\n",
              "      <td>85</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>boy</td>\n",
              "      <td>two</td>\n",
              "      <td>sports</td>\n",
              "      <td>44</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "     A    B           C   D\n",
              "0  boy  one      sports  50\n",
              "1  boy  one  non_sports  55\n",
              "2  boy  one      sports  67\n",
              "3  boy  two      sports  85\n",
              "4  boy  two      sports  44"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 4
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "vD38CrVJqTAi",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "table = pd.pivot_table(df, values='D', index=['A', 'B'], \n",
        "                       columns=['C'], aggfunc=np.mean)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "wfqP8mOQqUtU",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 203
        },
        "outputId": "3f608efa-e541-4a1f-d850-1e79bf1acd4b"
      },
      "source": [
        "table"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "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>C</th>\n",
              "      <th>non_sports</th>\n",
              "      <th>sports</th>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>A</th>\n",
              "      <th>B</th>\n",
              "      <th></th>\n",
              "      <th></th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th rowspan=\"2\" valign=\"top\">boy</th>\n",
              "      <th>one</th>\n",
              "      <td>55.0</td>\n",
              "      <td>58.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>two</th>\n",
              "      <td>NaN</td>\n",
              "      <td>64.5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th rowspan=\"2\" valign=\"top\">girl</th>\n",
              "      <th>one</th>\n",
              "      <td>74.5</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>two</th>\n",
              "      <td>NaN</td>\n",
              "      <td>71.5</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "C         non_sports  sports\n",
              "A    B                      \n",
              "boy  one        55.0    58.5\n",
              "     two         NaN    64.5\n",
              "girl one        74.5     NaN\n",
              "     two         NaN    71.5"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "C7FLTutpw05D",
        "colab_type": "text"
      },
      "source": [
        "# Interactive Pivot Table Using PivotTableJS in Jupyter Notebook"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "IJH8mdwywpjS",
        "colab_type": "code",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "860ee1da-99b8-4ac5-b36a-496d4cd1aea1"
      },
      "source": [
        "!pip install pivottablejs\n",
        "from pivottablejs import pivot_ui"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Requirement already satisfied: pivottablejs in /usr/local/lib/python3.6/dist-packages (0.9.0)\n"
          ],
          "name": "stdout"
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Qjt5xmmGj1Sc",
        "colab_type": "code",
        "outputId": "54e1a3f1-be6d-4a7f-d686-e5e6526835ef",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 293
        }
      },
      "source": [
        "pivot_ui(df,outfile_path='pivottablejs.html')\n",
        "HTML('pivottablejs.html')"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/html": [
              "\n",
              "<!DOCTYPE html>\n",
              "<html>\n",
              "    <head>\n",
              "        <meta charset=\"UTF-8\">\n",
              "        <title>PivotTable.js</title>\n",
              "\n",
              "        <!-- external libs from cdnjs -->\n",
              "        <link rel=\"stylesheet\" type=\"text/css\" href=\"https://cdnjs.cloudflare.com/ajax/libs/c3/0.4.11/c3.min.css\">\n",
              "        <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/d3/3.5.5/d3.min.js\"></script>\n",
              "        <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/c3/0.4.11/c3.min.js\"></script>\n",
              "        <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.2/jquery.min.js\"></script>\n",
              "        <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/jqueryui/1.11.4/jquery-ui.min.js\"></script>\n",
              "        <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/jquery-csv/0.71/jquery.csv-0.71.min.js\"></script>\n",
              "\n",
              "\n",
              "        <link rel=\"stylesheet\" type=\"text/css\" href=\"https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/pivot.min.css\">\n",
              "        <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/pivot.min.js\"></script>\n",
              "        <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/d3_renderers.min.js\"></script>\n",
              "        <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/c3_renderers.min.js\"></script>\n",
              "        <script type=\"text/javascript\" src=\"https://cdnjs.cloudflare.com/ajax/libs/pivottable/2.19.0/export_renderers.min.js\"></script>\n",
              "\n",
              "        <style>\n",
              "            body {font-family: Verdana;}\n",
              "            .node {\n",
              "              border: solid 1px white;\n",
              "              font: 10px sans-serif;\n",
              "              line-height: 12px;\n",
              "              overflow: hidden;\n",
              "              position: absolute;\n",
              "              text-indent: 2px;\n",
              "            }\n",
              "            .c3-line, .c3-focused {stroke-width: 3px !important;}\n",
              "            .c3-bar {stroke: white !important; stroke-width: 1;}\n",
              "            .c3 text { font-size: 12px; color: grey;}\n",
              "            .tick line {stroke: white;}\n",
              "            .c3-axis path {stroke: grey;}\n",
              "            .c3-circle { opacity: 1 !important; }\n",
              "            .c3-xgrid-focus {visibility: hidden !important;}\n",
              "        </style>\n",
              "    </head>\n",
              "    <body>\n",
              "        <script type=\"text/javascript\">\n",
              "            $(function(){\n",
              "                if(window.location != window.parent.location)\n",
              "                    $(\"<a>\", {target:\"_blank\", href:\"\"})\n",
              "                        .text(\"[pop out]\").prependTo($(\"body\"));\n",
              "\n",
              "                $(\"#output\").pivotUI(\n",
              "                    $.csv.toArrays($(\"#output\").text()),\n",
              "                    $.extend({\n",
              "                        renderers: $.extend(\n",
              "                            $.pivotUtilities.renderers,\n",
              "                            $.pivotUtilities.c3_renderers,\n",
              "                            $.pivotUtilities.d3_renderers,\n",
              "                            $.pivotUtilities.export_renderers\n",
              "                            ),\n",
              "                        hiddenAttributes: [\"\"]\n",
              "                    }, {})\n",
              "                ).show();\n",
              "             });\n",
              "        </script>\n",
              "        <div id=\"output\" style=\"display: none;\">,A,B,C,D\n",
              "0,boy,one,sports,50\n",
              "1,boy,one,non_sports,55\n",
              "2,boy,one,sports,67\n",
              "3,boy,two,sports,85\n",
              "4,boy,two,sports,44\n",
              "5,girl,one,non_sports,84\n",
              "6,girl,one,non_sports,65\n",
              "7,girl,two,sports,56\n",
              "8,girl,two,sports,87\n",
              "</div>\n",
              "    </body>\n",
              "</html>\n"
            ],
            "text/plain": [
              "<IPython.core.display.HTML object>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 8
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "L831gQaaj1Ve",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}