{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Conditional formatting based on other column values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Compound cell values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from ipydatagrid import DataGrid, TextRenderer, VegaExpr\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"column 1\": [{\"key\": 11}, [\"berry\", \"apple\", \"cherry\"]],\n",
    "        \"column 2\": [[\"berry\", \"berry\", \"cherry\"], {\"key\": 10}],\n",
    "    }\n",
    ")\n",
    "\n",
    "renderer = TextRenderer(\n",
    "    background_color=VegaExpr(\n",
    "        \"cell.value[1] == 'berry' && cell.metadata.data['column 1']['key'] == 11 ? 'limegreen' : 'pink'\"\n",
    "    )\n",
    ")\n",
    "\n",
    "DataGrid(\n",
    "    df,\n",
    "    layout={\"height\": \"100px\"},\n",
    "    base_column_size=150,\n",
    "    default_renderer=renderer,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from ipydatagrid import DataGrid, TextRenderer, VegaExpr\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"column 1\": [{\"key\": {\"nestedKey\": 11}}, [\"berry\", \"apple\", \"cherry\"]],\n",
    "        \"column 2\": [[\"berry\", \"berry\", \"cherry\"], {\"key\": 10}],\n",
    "    }\n",
    ")\n",
    "\n",
    "renderer = TextRenderer(\n",
    "    background_color=VegaExpr(\n",
    "        \"cell.value[1] == 'berry' && \\\n",
    "    cell.metadata.data['column 1']['key']['nestedKey'] == 11 ? 'magenta' : 'dodgerblue'\"\n",
    "    )\n",
    ")\n",
    "\n",
    "DataGrid(\n",
    "    df,\n",
    "    layout={\"height\": \"100px\"},\n",
    "    base_column_size=150,\n",
    "    default_renderer=renderer,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from ipydatagrid import DataGrid, TextRenderer, VegaExpr\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"column 1\": [[\"one\", [\"two\"]], [\"berry\", \"apple\", \"cherry\"]],\n",
    "        \"column 2\": [[\"berry\", \"berry\", \"cherry\"], [\"one\", [\"two\"]]],\n",
    "    }\n",
    ")\n",
    "\n",
    "renderer = TextRenderer(\n",
    "    background_color=VegaExpr(\n",
    "        \"cell.value[1] == 'berry' && \\\n",
    "    cell.metadata.data['column 1'][1][0] == 'two' ? 'pink' : 'teal'\"\n",
    "    )\n",
    ")\n",
    "\n",
    "DataGrid(\n",
    "    df,\n",
    "    layout={\"height\": \"100px\"},\n",
    "    base_column_size=150,\n",
    "    default_renderer=renderer,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from ipydatagrid import DataGrid, TextRenderer, VegaExpr\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"column 1\": [[\"one\", [\"two\"]], [\"berry\", \"apple\", \"cherry\"]],\n",
    "        \"column 2\": [[\"berry\", \"berry\", \"cherry\"], [\"one\", [\"two\"]]],\n",
    "    }\n",
    ")\n",
    "\n",
    "renderer = TextRenderer(\n",
    "    background_color=VegaExpr(\"cell.value[1] == 'berry' ? 'pink' : 'teal'\")\n",
    ")\n",
    "\n",
    "DataGrid(\n",
    "    df,\n",
    "    layout={\"height\": \"100px\"},\n",
    "    base_column_size=150,\n",
    "    default_renderer=renderer,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Normal column names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# Imports\n",
    "import json\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from bqplot import DateScale, ColorScale\n",
    "from py2vega.functions.type_coercing import toDate\n",
    "from py2vega.functions.date_time import datetime\n",
    "from ipydatagrid import Expr, DataGrid, TextRenderer\n",
    "\n",
    "# Random data\n",
    "n = 10\n",
    "np.random.seed(0)\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"Column 0\": np.random.randn(n),\n",
    "        \"Column 1\": np.random.randn(n),\n",
    "        \"Column 2\": np.random.randn(n),\n",
    "    }\n",
    ")\n",
    "\n",
    "# Formatting the values in column 1 based on the value of the sibling row in column 2\n",
    "def format_based_on_other_column(cell):\n",
    "    return \"green\" if cell.metadata.data[\"Column 2\"] > 0.0 else \"yellow\"\n",
    "\n",
    "\n",
    "column1_formatting = TextRenderer(\n",
    "    text_color=\"black\",\n",
    "    background_color=Expr(format_based_on_other_column),\n",
    ")\n",
    "\n",
    "renderers = {\n",
    "    \"Column 1\": column1_formatting,\n",
    "}\n",
    "\n",
    "grid = DataGrid(\n",
    "    df,\n",
    "    base_row_size=30,\n",
    "    base_column_size=300,\n",
    "    renderers=renderers,\n",
    "    layout={\"height\": \"350px\"},\n",
    ")\n",
    "grid"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Example with nested columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import ipydatagrid as ipg\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "top_level = [\n",
    "    \"Value Factors\",\n",
    "    \"Value Factors\",\n",
    "    \"Momentum Factors\",\n",
    "    \"Momentum Factors\",\n",
    "]\n",
    "bottom_level = [\"Factor A\", \"Factor B\", \"Factor C\", \"Factor D\"]\n",
    "\n",
    "nested_df = pd.DataFrame(\n",
    "    np.random.randn(4, 4).round(2),\n",
    "    columns=pd.MultiIndex.from_arrays([top_level, bottom_level]),\n",
    "    index=pd.Index(\n",
    "        [\"Security {}\".format(x) for x in [\"A\", \"B\", \"C\", \"D\"]], name=\"Ticker\"\n",
    "    ),\n",
    ")\n",
    "\n",
    "# Formatting Factor B rows based on their siblings in the Factor C column\n",
    "def format_based_on_other_column(cell):\n",
    "    return (\n",
    "        \"green\"\n",
    "        if cell.value > -0\n",
    "        and cell.metadata.data[\"('Value Factors', 'Factor B')\"] > 0.0\n",
    "        else \"yellow\"\n",
    "    )\n",
    "\n",
    "\n",
    "nested_grid = ipg.DataGrid(\n",
    "    nested_df,\n",
    "    base_column_size=90,\n",
    "    layout={\"height\": \"140px\"},\n",
    "    default_renderer=ipg.TextRenderer(\n",
    "        horizontal_alignment=\"right\",\n",
    "        background_color=Expr(format_based_on_other_column),\n",
    "    ),\n",
    ")\n",
    "\n",
    "nested_grid"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def format_based_on_other_column(cell):\n",
    "    return (\n",
    "        \"green\"\n",
    "        if cell.column == 0 and cell.metadata.data[\"Signal\"] == \"Buy\"\n",
    "        else \"red\"\n",
    "    )\n",
    "\n",
    "\n",
    "signal_column_formatting = TextRenderer(\n",
    "    text_color=\"white\",\n",
    "    background_color=Expr(format_based_on_other_column),\n",
    ")\n",
    "\n",
    "renderers = {\n",
    "    \"Stock\": signal_column_formatting,\n",
    "}\n",
    "\n",
    "grid = DataGrid(\n",
    "    pd.DataFrame(\n",
    "        {\"Stock\": \"A B C D\".split(), \"Signal\": [\"Buy\", \"Sell\", \"Buy\", \"Sell\"]}\n",
    "    ),\n",
    "    base_row_size=30,\n",
    "    base_column_size=300,\n",
    "    renderers=renderers,\n",
    "    layout={\"height\": \"150px\"},\n",
    ")\n",
    "grid"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comparing dates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from bqplot import DateScale, ColorScale\n",
    "from py2vega.functions.type_coercing import toDate\n",
    "from py2vega.functions.date_time import datetime\n",
    "\n",
    "from ipydatagrid import Expr, DataGrid, TextRenderer, BarRenderer\n",
    "\n",
    "n = 10\n",
    "np.random.seed(0)\n",
    "\n",
    "\n",
    "def format_based_on_date(cell):\n",
    "    return (\n",
    "        \"magenta\"\n",
    "        if cell.column == 0 and cell.metadata.data[\"Dates\"] >= \"2020-10-21\"\n",
    "        else \"yellow\"\n",
    "    )\n",
    "\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"Value 1\": np.random.randn(n),\n",
    "        \"Value 2\": np.random.randn(n),\n",
    "        \"Dates\": pd.date_range(end=pd.Timestamp(\"2020-10-25\"), periods=n),\n",
    "    }\n",
    ")\n",
    "\n",
    "text_renderer = TextRenderer(\n",
    "    text_color=\"black\", background_color=Expr(format_based_on_date)\n",
    ")\n",
    "\n",
    "\n",
    "def bar_color(cell):\n",
    "    date = toDate(cell.value)\n",
    "    return \"green\" if date > datetime(\"2000\") else \"red\"\n",
    "\n",
    "\n",
    "renderers = {\n",
    "    \"Value 1\": text_renderer,\n",
    "    \"Dates\": BarRenderer(\n",
    "        bar_value=DateScale(),\n",
    "        bar_color=Expr(bar_color),\n",
    "        format=\"%Y/%m/%d\",\n",
    "        format_type=\"time\",\n",
    "    ),\n",
    "}\n",
    "\n",
    "grid = DataGrid(\n",
    "    df,\n",
    "    base_row_size=30,\n",
    "    base_column_size=300,\n",
    "    renderers=renderers,\n",
    "    layout={\"height\": \"350px\"},\n",
    ")\n",
    "grid"
   ]
  }
 ],
 "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.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}