{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import panel as pn\n",
    "\n",
    "pn.extension()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ``DataFrame`` widget allows displaying and editing a pandas DataFrame. Note that editing is not possible for multi-indexed DataFrames, in which case you will need to reduce the DataFrame to a single index. Also note that the `DataFrame` widget will eventually be replaced with the [`Tabulator`](./Tabulator.ipynb) widget, and so new code should be written to use Tabulator instead.\n",
    "\n",
    "For more information about listening to widget events and laying out widgets refer to the [widgets user guide](../../user_guide/Widgets.ipynb). Alternatively you can learn how to build GUIs by declaring parameters independently of any specific widgets in the [param user guide](../../user_guide/Param.ipynb). To express interactivity entirely using Javascript without the need for a Python server take a look at the [links user guide](../../user_guide/Param.ipynb).\n",
    "\n",
    "#### Parameters:\n",
    "\n",
    "For layout and styling related parameters see the [customization user guide](../../user_guide/Customization.ipynb).\n",
    "\n",
    "##### Core\n",
    "\n",
    "* **``aggregators``** (``dict``): A dictionary mapping from index name to an aggregator to be used for hierarchical multi-indexes (valid aggregators include 'min', 'max', 'mean' and 'sum'). If separate aggregators for different columns are required the dictionary may be nested as `{index_name: {column_name: aggregator}}`\n",
    "* **``auto_edit``** (``boolean``): Whether clicking on a table cell automatically starts edit mode.\n",
    "* **``autosize_mode``** (str): Describes the column autosizing mode with one of the following options: \n",
    "  * ``\"fit_columns\"``\n",
    "  Compute columns widths based on cell contents but ensure the\n",
    "  table fits into the available viewport. This results in no\n",
    "  horizontal scrollbar showing up, but data can get unreadable\n",
    "  if there is not enough space available.\n",
    "\n",
    "  * ``\"fit_viewport\"``\n",
    "  Adjust the viewport size after computing columns widths based\n",
    "  on cell contents.\n",
    "\n",
    "  * ``\"force_fit\"``\n",
    "  Fit columns into available space dividing the table width across\n",
    "  the columns equally (equivalent to `fit_columns=True`).\n",
    "  This results in no horizontal scrollbar showing up, but data\n",
    "  can get unreadable if there is not enough space available.\n",
    "\n",
    "  * ``\"none\"``\n",
    "  Do not automatically compute column widths.\n",
    "* **``editors``** (``dict``):  A dictionary mapping from column name to a bokeh `CellEditor` instance, which overrides the default.\n",
    "* **``hierarchical``** (boolean, default=False): Whether to render multi-indexes as hierarchical index (note hierarchical must be enabled during instantiation and cannot be modified later)\n",
    "* **``fit_columns``** (``boolean``, default=True): Whether columns should expand to the available width. \n",
    "* **``formatters``** (``dict``): A dictionary mapping from column name to a bokeh `CellFormatter` instance, which overrides the default.\n",
    "* **``frozen_columns``** (int): Integer indicating the number of columns to freeze. If set the  first N columns will be frozen which prevents them from scrolling out of frame.\n",
    "* **``frozen_rows``**: (int): Integer indicating the number of rows to freeze. If set the first N rows will be frozen which prevents them from scrolling out of frame, if set to a negative value last N rows will be frozen.\n",
    "* **``reorderable``** (``boolean``): Allows the reordering of a table's columns. To reorder a column, click and drag a table's header to the desired location in the table.  The columns on either side will remain in their previous order.\n",
    "* **``row_height``** (``int``): The height of each table row.\n",
    "* **``selection``** (``list``) The currently selected rows.\n",
    "* **``show_index``** (``boolean``): Whether to show the index column.\n",
    "* **``sortable``** (``sortable``): Allows to sort table's contents. By default natural order is preserved.  To sort a column, click on it's header. Clicking one more time changes sort direction. Use Ctrl + click to return to natural order. Use Shift + click to sort multiple columns simultaneously\n",
    "* **``text_align``** (``dict`` or ``str``): A mapping from column name to alignment or a fixed column alignment, which should be one of 'left', 'center', 'right'.\n",
    "* **`titles`** (``dict``): A mapping from column name to a title to override the name with.\n",
    "* **``value``** (``pd.DataFrame``): The pandas DataFrame to display and edit\n",
    "* **``widths``** (``dict``): A dictionary mapping from column name to column width in the rendered table.\n",
    "\n",
    "##### Display\n",
    "\n",
    "* **``disabled``** (boolean): Whether the widget is editable\n",
    "* **``name``** (str): The title of the widget\n",
    "\n",
    "___"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ``DataFrame`` widget renders an table which allows directly editing the contents of the dataframe with any changes being synced with Python. Note that it modifies the ``pd.DataFrame`` in place."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame({'int': [1, 2, 3], 'float': [3.14, 6.28, 9.42], 'str': ['A', 'B', 'C'], 'bool': [True, False, True]}, index=[1, 2, 3])\n",
    "\n",
    "df_widget = pn.widgets.DataFrame(df, name='DataFrame')\n",
    "\n",
    "df_widget"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By default the widget will pick bokeh ``CellFormatter`` and ``CellEditor`` types appropriate to the dtype of the column. These may be overriden by explicit dictionaries mapping from the column name to the editor or formatter instance. For example below we create a ``SelectEditor`` instance to pick from four options in the ``str`` column and a ``NumberFormatter`` to customize the formatting of the float values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bokeh.models.widgets.tables import SelectEditor, NumberFormatter\n",
    "\n",
    "editor = SelectEditor(options=['A', 'B', 'C', 'D'])\n",
    "formatter = NumberFormatter(format='0.00000') \n",
    "\n",
    "table = pn.widgets.DataFrame(df, editors={'str': editor}, formatters={'float': formatter})\n",
    "table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once initialized the ``selection`` property will return the integer indexes of the selected rows and the ``selected_dataframe`` property will return a new DataFrame containing just the selected rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "table.selection = [0, 2]\n",
    "\n",
    "table.selected_dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Column layouts\n",
    "\n",
    "By default the DataFrame widget will equally split the available horizontal space between the columns, reflecting the default value of the parameter: `autosize_mode=\"force_fit\"`. Alternatively modes allow manually specifying the widths of the columns or automatically adjusting the column widths or overall table width to match the contents of the table.\n",
    "\n",
    "#### Manual column widths\n",
    "\n",
    "To manually adjust column widths set the `autosize_mode` to `\"none\"` and provide explicit `widths`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custom_df = pd._testing.makeMixedDataFrame()\n",
    "\n",
    "pn.widgets.DataFrame(custom_df, autosize_mode='none', widths={'index': 50, 'A': 50, 'B': 50, 'C': 70, 'D': 130}, width=350)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Autosize columns\n",
    "\n",
    "To automatically adjust the columns dependending on their content set `autosize_mode='fit_columns'`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pn.widgets.DataFrame(custom_df, autosize_mode='fit_columns', width=300)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Autosize width\n",
    "\n",
    "To automatically adjust the width of the columns **and** the overall table use `autosize_mode='fit_viewport'`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pn.widgets.DataFrame(custom_df, autosize_mode='fit_viewport')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Freezing rows and columns\n",
    "\n",
    "Often times your table will be larger than can be displayed in a single viewport and scroll bars will be enabled. The issue with this is that you might want to make sure that certain information is always visible. This is where the `frozen_columns` and `frozen_rows` options come in.\n",
    "\n",
    "#### Frozen columns\n",
    "\n",
    "When you have a large number of columns and can't fit them all on the screen you might still want to make sure that certain columns do not scroll out of view. The `frozen_columns` option makes this possible by specifying the number of columns, counting from the left, that should be frozen, e.g. `frozen_columns=1` will freeze the last column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_df = pd._testing.makeTimeDataFrame()\n",
    "\n",
    "pn.widgets.DataFrame(date_df, height=400, widths=150, frozen_columns=1, autosize_mode='none')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Frozen rows\n",
    "\n",
    "Another common scenario is when you have certain rows with special meaning, e.g. aggregates which summarize the information in the rest of the table. In this case you may want to freeze those rows so they do not scroll out of view. You can achieve this by setting `frozen_columns` to an integer value. If the value is positive the first N rows will be frozen, if the value is negative the last N rows will be frozen:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "agg_df = pd.concat([date_df, date_df.median().to_frame('Median').T, date_df.mean().to_frame('Mean').T])\n",
    "agg_df.index= agg_df.index.map(str)\n",
    "\n",
    "pn.widgets.DataFrame(agg_df, frozen_rows=-2, height=400)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Hierarchical Multi-index\n",
    "\n",
    "The `DataFrame` widget can also render a hierarchical multi-index and aggregate over specific categories. If a DataFrame with a hierarchical multi-index is supplied and the `hierarchical` is enabled the widget will group data by the categories in the order they are defined in. Additionally for each group in the multi-index an aggregator may be provided which will aggregate over the values in that category.\n",
    "\n",
    "For example we may load population data for locations around the world broken down by sex and age-group. If we specify aggregators over the 'AgeGrp' and 'Sex' indexes we can see the aggregated values for each of those groups (note that we do not have to specify an aggregator for the outer index since we specify the aggregators over the subgroups in this case the 'Sex'):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from bokeh.sampledata.population import data as population_data \n",
    "\n",
    "pop_df = population_data[population_data.Year == 2020].set_index(['Location', 'AgeGrp', 'Sex'])[['Value']]\n",
    "\n",
    "pn.widgets.DataFrame(value=pop_df, hierarchical=True, aggregators={'Sex': 'sum', 'AgeGrp': 'sum'}, height=400)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Streaming\n",
    "\n",
    "When we are monitoring a source of data that updates over time, we may want to update the table with the newly arriving data. However, we do not want to re-transmit the entire dataset each time. To handle efficient transfer of just the latest data, we can use the `.stream` method on the `DataFrame` object:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "stream_df = pd.DataFrame(np.random.randn(10, 5), columns=list('ABCDE'))\n",
    "\n",
    "stream_table = pn.widgets.DataFrame(stream_df, autosize_mode='fit_columns', width=450)\n",
    "stream_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As example, we will schedule a periodic callback that streams new data every 1000ms (i.e. 1s), five times in a row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def stream_data():\n",
    "    stream_df = pd.DataFrame(np.random.randn(10, 5), columns=list('ABCDE'))\n",
    "    stream_table.stream(stream_df)\n",
    "\n",
    "pn.state.add_periodic_callback(stream_data, period=1000, count=5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Patching\n",
    "\n",
    "In certain cases we don't want to update the table with new data but just patch existing data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patch_table = pn.widgets.DataFrame(df[['int', 'float', 'str', 'bool']])\n",
    "patch_table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The easiest way to patch the data is by supplying a dictionary as the patch value. The dictionary should have the following structure:\n",
    "\n",
    "    {\n",
    "        column: [\n",
    "            (index: int or slice, value),\n",
    "            ...\n",
    "        ],\n",
    "        ...\n",
    "    }\n",
    "    \n",
    "As an example, below we will patch the 'bool' and 'int' columns. On the `'bool'` column we will replace the 0th and 2nd row and on the `'int'` column we replace the first two rows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "patch_table.patch({\n",
    "    'bool': [\n",
    "        (0, False),\n",
    "        (2, False)\n",
    "    ],\n",
    "    'int': [\n",
    "        (slice(0, 2), [3, 2])\n",
    "    ]\n",
    "})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Controls\n",
    "\n",
    "The `DataFrame` widget exposes a number of options which can be changed from both Python and Javascript. Try out the effect of these parameters interactively:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pn.Row(df_widget.controls(jslink=True), df_widget)"
   ]
  }
 ],
 "metadata": {
  "language_info": {
   "name": "python",
   "pygments_lexer": "ipython3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}