{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Using Pivoted DataFrames with Perspective\n",
    "\n",
    "Perspective tries to infer as much information as possible from already-pivoted dataframes:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from perspective import Table, PerspectiveWidget"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For the dataset, we'll use `superstore.arrow` which is used in various Perspective demos:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "\n",
    "# Download the arrow\n",
    "url = \"https://unpkg.com/@jpmorganchase/perspective-examples@0.2.0-beta.2/build/superstore.arrow\"\n",
    "req = requests.get(url)\n",
    "arrow = req.content"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a table\n",
    "table = Table(arrow)\n",
    "view = table.view()\n",
    "df = view.to_df()\n",
    "display(df.shape, df.dtypes)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Row Pivots\n",
    "\n",
    "Create a row pivoted dataframe, and pass it into `PerspectiveWidget`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pivoted = df.set_index(['Country', 'Region'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_pivoted.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivots will be read from the df and applied\n",
    "PerspectiveWidget(df_pivoted)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Column Pivots\n",
    "\n",
    "The same is true with column pivots:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "arrays = [np.array(['bar', 'bar', 'bar', 'bar', 'baz', 'baz', 'baz', 'baz', 'foo', 'foo', 'foo', 'foo', 'qux', 'qux', 'qux', 'qux']),\n",
    "          np.array(['one', 'one', 'two', 'two', 'one', 'one', 'two', 'two', 'one', 'one', 'two', 'two', 'one', 'one', 'two', 'two']),\n",
    "          np.array(['X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y'])]\n",
    "tuples = list(zip(*arrays))\n",
    "index = pd.MultiIndex.from_tuples(tuples, names=['first', 'second', 'third',])\n",
    "\n",
    "df_col = pd.DataFrame(np.random.randn(3, 16), index=['A', 'B', 'C'], columns=index)\n",
    "df_col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pivots again automatically applied\n",
    "PerspectiveWidget(df_col)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pivot Table (Row and Column Pivots)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pt = pd.pivot_table(df, values = 'Discount', index=['Country','Region'], columns = ['Category', 'Segment'])\n",
    "pt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PerspectiveWidget(pt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More pivot examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "arrays = {'A':['bar', 'bar', 'bar', 'bar', 'baz', 'baz', 'baz', 'baz', 'foo', 'foo', 'foo', 'foo', 'qux', 'qux', 'qux', 'qux'],\n",
    "          'B':['one', 'one', 'two', 'two', 'one', 'one', 'two', 'two', 'one', 'one', 'two', 'two', 'one', 'one', 'two', 'two'],\n",
    "          'C':['X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y', 'X', 'Y'],\n",
    "          'D':np.arange(16)}\n",
    "\n",
    "df2 = pd.DataFrame(arrays)\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df2_pivot = df2.pivot_table(values=['D'], index=['A'], columns=['B','C'], aggfunc={'D':'count'})\n",
    "df2_pivot"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PerspectiveWidget(df2_pivot)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pt = pd.pivot_table(df, values = ['Discount','Sales'], index=['Country','Region'],aggfunc={'Discount':'count','Sales':'sum'},columns=[\"State\",\"Quantity\"])\n",
    "pt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "PerspectiveWidget(pt)"
   ]
  }
 ],
 "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.7.9"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {
     "a9a4112df5ab4063a111fe092dcad2c0": {
      "model_module": "@finos/perspective-jupyterlab",
      "model_module_version": "~0.5",
      "model_name": "PerspectiveModel",
      "state": {
       "_model_module_version": "~0.5",
       "_view_module_version": "~0.5",
       "aggregates": null,
       "column_pivots": [
        "first",
        "second",
        "third"
       ],
       "columns": [
        "value"
       ],
       "dark": null,
       "row_pivots": [
        "index"
       ],
       "sort": null
      }
     },
     "cee49dbaf8bd4aca9a2d28f933707e47": {
      "model_module": "@finos/perspective-jupyterlab",
      "model_module_version": "~0.5",
      "model_name": "PerspectiveModel",
      "state": {
       "_model_module_version": "~0.5",
       "_view_module_version": "~0.5",
       "aggregates": null,
       "column_pivots": [
        "State",
        "Quantity"
       ],
       "columns": [
        "Discount",
        "Sales"
       ],
       "dark": null,
       "row_pivots": [
        "Country",
        "Region"
       ],
       "sort": null
      }
     },
     "d04d6a42b97349cab667619913527df0": {
      "model_module": "@finos/perspective-jupyterlab",
      "model_module_version": "~0.5",
      "model_name": "PerspectiveModel",
      "state": {
       "_model_module_version": "~0.5",
       "_view_module_version": "~0.5",
       "aggregates": null,
       "column_pivots": [
        "Category",
        "Segment"
       ],
       "columns": [
        "value"
       ],
       "dark": null,
       "row_pivots": [
        "Country",
        "Region"
       ],
       "sort": null
      }
     },
     "f3a5f4e1569f431da25c4e2274f8c662": {
      "model_module": "@finos/perspective-jupyterlab",
      "model_module_version": "~0.5",
      "model_name": "PerspectiveModel",
      "state": {
       "_model_module_version": "~0.5",
       "_view_module_version": "~0.5",
       "aggregates": null,
       "column_pivots": null,
       "columns": [
        "index",
        "Country",
        "Region",
        "Row ID",
        "Order ID",
        "Order Date",
        "Ship Date",
        "Ship Mode",
        "Customer ID",
        "Segment",
        "City",
        "State",
        "Postal Code",
        "Product ID",
        "Category",
        "Sub-Category",
        "Sales",
        "Quantity",
        "Discount",
        "Profit"
       ],
       "dark": null,
       "row_pivots": [
        "Country",
        "Region"
       ],
       "sort": null
      }
     },
     "f5ad0e69bd6f4b47b66d7cc079892d28": {
      "model_module": "@finos/perspective-jupyterlab",
      "model_module_version": "~0.5",
      "model_name": "PerspectiveModel",
      "state": {
       "_model_module_version": "~0.5",
       "_view_module_version": "~0.5",
       "aggregates": null,
       "column_pivots": [
        "B",
        "C"
       ],
       "columns": [
        "value"
       ],
       "dark": null,
       "row_pivots": [
        "A"
       ],
       "sort": null
      }
     }
    },
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}