reports/superstore-reporting/report.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hCFyUZoQmgUX"
   },
   "source": [
    "## E-Commerce Dashboard\n",
    "\n",
    "A dashboard built using Datapane."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:39.570064Z",
     "start_time": "2020-10-28T19:01:37.782552Z"
    },
    "id": "G6PCuE038cH8",
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import plotly.graph_objs as go\n",
    "from plotly.subplots import make_subplots\n",
    "import datapane as dp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.136218Z",
     "start_time": "2020-10-28T19:01:39.571535Z"
    },
    "id": "mcw1-ut5Ksh0",
    "tags": []
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv(\"./superstore_orders.csv.gz\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.307473Z",
     "start_time": "2020-10-28T19:01:40.137757Z"
    },
    "id": "EO20dV2YPbJ6",
    "tags": []
   },
   "outputs": [],
   "source": [
    "df[\"Order Date\"] = pd.to_datetime(df[\"Order Date\"], dayfirst=True)\n",
    "df[\"Ship Date\"] = pd.to_datetime(df[\"Ship Date\"], dayfirst=True)\n",
    "\n",
    "daily_quantity = df.groupby(\"Order Date\")[\"Sales\"].sum().reset_index()\n",
    "\n",
    "trace0 = go.Scatter(x=daily_quantity[\"Order Date\"], y=df[\"Sales\"], name=\"nflx\")\n",
    "fig0 = go.Figure([trace0])\n",
    "fig0.update_layout(title={\"text\": \"Total Sales by Day\", \"x\": 0.5, \"xanchor\": \"center\"})\n",
    "fig0.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.350629Z",
     "start_time": "2020-10-28T19:01:40.308942Z"
    },
    "id": "p-hCtwYtPbP3",
    "tags": []
   },
   "outputs": [],
   "source": [
    "df[\"year\"] = df[\"Order Date\"].dt.year\n",
    "df[\"month\"] = df[\"Order Date\"].dt.month\n",
    "df[\"dow\"] = df[\"Order Date\"].dt.dayofweek\n",
    "df[\"day\"] = df[\"Order Date\"].dt.day\n",
    "\n",
    "trace0 = go.Scatter(x=df.groupby(\"year\")[\"Profit\"].sum().index, y=df.groupby(\"year\")[\"Profit\"].sum().values)\n",
    "trace1 = go.Scatter(x=df.groupby(\"month\")[\"Profit\"].sum().index, y=df.groupby(\"month\")[\"Profit\"].sum().values)\n",
    "trace2 = go.Scatter(x=df.groupby(\"dow\")[\"Profit\"].sum().index, y=df.groupby(\"dow\")[\"Profit\"].sum().values)\n",
    "trace3 = go.Scatter(x=df.groupby(\"day\")[\"Profit\"].sum().index, y=df.groupby(\"day\")[\"Profit\"].sum().values)\n",
    "\n",
    "fig1 = make_subplots(\n",
    "    rows=4,\n",
    "    cols=1,\n",
    "    subplot_titles=(\n",
    "        \"Total Profit by Year\",\n",
    "        \"Total Profit by Month\",\n",
    "        \"Total Profit by Day of Week\",\n",
    "        \"Total Profit by Day of Month\",\n",
    "    ),\n",
    ")\n",
    "fig1.append_trace(trace0, 1, 1)\n",
    "fig1.append_trace(trace1, 2, 1)\n",
    "fig1.append_trace(trace2, 3, 1)\n",
    "fig1.append_trace(trace3, 4, 1)\n",
    "fig1[\"layout\"].update(title=\"\", showlegend=False)\n",
    "\n",
    "fig1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.390224Z",
     "start_time": "2020-10-28T19:01:40.352265Z"
    },
    "id": "eDhYYI_ePbS-",
    "tags": []
   },
   "outputs": [],
   "source": [
    "trace1 = go.Bar(\n",
    "    x=df[df.Segment == \"Consumer\"].groupby(\"month\")[\"Sales\"].sum().index,\n",
    "    y=df[df.Segment == \"Consumer\"].groupby(\"month\")[\"Sales\"].sum().values,\n",
    "    name=\"Consumer\",\n",
    ")\n",
    "\n",
    "trace2 = go.Bar(\n",
    "    x=df[df.Segment == \"Corporate\"].groupby(\"month\")[\"Sales\"].sum().index,\n",
    "    y=df[df.Segment == \"Corporate\"].groupby(\"month\")[\"Sales\"].sum().values,\n",
    "    name=\"Corporate\",\n",
    ")\n",
    "\n",
    "trace3 = go.Bar(\n",
    "    x=df[df.Segment == \"Home Office\"].groupby(\"month\")[\"Sales\"].sum().index,\n",
    "    y=df[df.Segment == \"Home Office\"].groupby(\"month\")[\"Sales\"].sum().values,\n",
    "    name=\"Home Office\",\n",
    ")\n",
    "\n",
    "layout = go.Layout(title={\"text\": \"Total Sales by Segment\", \"x\": 0.5, \"xanchor\": \"center\"}, xaxis=dict(title=\"Month\"))\n",
    "data = [trace1, trace2, trace3]\n",
    "fig2 = go.Figure(data=data, layout=layout)\n",
    "fig2.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.420473Z",
     "start_time": "2020-10-28T19:01:40.391636Z"
    },
    "id": "aO_ErklqbkAG",
    "tags": []
   },
   "outputs": [],
   "source": [
    "trace1 = go.Bar(\n",
    "    x=df[df.Segment == \"Consumer\"].groupby(\"month\")[\"Profit\"].sum().index,\n",
    "    y=df[df.Segment == \"Consumer\"].groupby(\"month\")[\"Profit\"].sum().values,\n",
    "    name=\"Consumer\",\n",
    ")\n",
    "\n",
    "trace2 = go.Bar(\n",
    "    x=df[df.Segment == \"Corporate\"].groupby(\"month\")[\"Profit\"].sum().index,\n",
    "    y=df[df.Segment == \"Corporate\"].groupby(\"month\")[\"Profit\"].sum().values,\n",
    "    name=\"Corporate\",\n",
    ")\n",
    "\n",
    "trace3 = go.Bar(\n",
    "    x=df[df.Segment == \"Home Office\"].groupby(\"month\")[\"Profit\"].sum().index,\n",
    "    y=df[df.Segment == \"Home Office\"].groupby(\"month\")[\"Profit\"].sum().values,\n",
    "    name=\"Home-Office\",\n",
    ")\n",
    "\n",
    "data = [trace1, trace2, trace3]\n",
    "layout = go.Layout(\n",
    "    title={\"text\": \"Total Profit by Segment\", \"x\": 0.5, \"xanchor\": \"center\"}, xaxis=dict(title=\"Month\"), barmode=\"stack\"\n",
    ")\n",
    "\n",
    "fig3 = go.Figure(data=data, layout=layout)\n",
    "fig3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.458413Z",
     "start_time": "2020-10-28T19:01:40.421902Z"
    },
    "id": "6cN6NnpWfoxM",
    "tags": []
   },
   "outputs": [],
   "source": [
    "def gen_scatter(region, col):\n",
    "    return go.Scatter(\n",
    "        x=df[df.Region == region].groupby(\"month\")[col].sum().index,\n",
    "        y=df[df.Region == region].groupby(\"month\")[col].sum().values,\n",
    "        name=region,\n",
    "        mode=\"markers\",\n",
    "    )\n",
    "\n",
    "\n",
    "data = [\n",
    "    gen_scatter(\"West\", \"Sales\"),\n",
    "    gen_scatter(\"East\", \"Sales\"),\n",
    "    gen_scatter(\"Central\", \"Sales\"),\n",
    "    gen_scatter(\"South\", \"Sales\"),\n",
    "]\n",
    "\n",
    "layout = go.Layout(title={\"text\": \"Total Sales by Region\", \"x\": 0.5, \"xanchor\": \"center\"}, xaxis=dict(title=\"Month\"))\n",
    "\n",
    "fig4 = go.Figure(data=data, layout=layout)\n",
    "fig4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.503821Z",
     "start_time": "2020-10-28T19:01:40.461198Z"
    },
    "id": "SVidsWgWfo51",
    "tags": []
   },
   "outputs": [],
   "source": [
    "data = [\n",
    "    gen_scatter(\"West\", \"Profit\"),\n",
    "    gen_scatter(\"East\", \"Profit\"),\n",
    "    gen_scatter(\"Central\", \"Profit\"),\n",
    "    gen_scatter(\"South\", \"Profit\"),\n",
    "]\n",
    "\n",
    "layout = go.Layout(title={\"text\": \"Total Profit by Region\", \"x\": 0.5, \"xanchor\": \"center\"}, xaxis=dict(title=\"Month\"))\n",
    "\n",
    "fig5 = go.Figure(data=data, layout=layout)\n",
    "fig5"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.517944Z",
     "start_time": "2020-10-28T19:01:40.506456Z"
    },
    "id": "lpb0zAgCRLUE",
    "tags": []
   },
   "outputs": [],
   "source": [
    "data = go.Pie(\n",
    "    labels=df.groupby(\"Category\")[\"Sales\"].sum().index, values=df.groupby(\"Category\")[\"Sales\"].sum().values, hole=0.2\n",
    ")\n",
    "layout = go.Layout(title={\"text\": \"Total Sales by Category\", \"x\": 0.5, \"xanchor\": \"center\"})\n",
    "fig6 = go.Figure(data=data, layout=layout)\n",
    "\n",
    "fig6"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.531968Z",
     "start_time": "2020-10-28T19:01:40.519855Z"
    },
    "id": "h1Ar_c-5QyuV",
    "tags": []
   },
   "outputs": [],
   "source": [
    "data = go.Pie(\n",
    "    labels=df.groupby(\"Sub-Category\")[\"Profit\"].sum().index,\n",
    "    values=df.groupby(\"Sub-Category\")[\"Profit\"].sum().values,\n",
    "    hole=0.2,\n",
    ")\n",
    "layout = go.Layout(\n",
    "    title={\"text\": \"Total Profit by Sub-Category\", \"x\": 0.5, \"xanchor\": \"center\"}, legend=dict(orientation=\"h\")\n",
    ")\n",
    "fig7 = go.Figure(data=data, layout=layout)\n",
    "\n",
    "fig7"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.543401Z",
     "start_time": "2020-10-28T19:01:40.533392Z"
    },
    "id": "yTLnX-dIlOMv",
    "tags": []
   },
   "outputs": [],
   "source": [
    "data = go.Bar(\n",
    "    y=df.groupby(\"Category\")[\"Sales\"].sum().index, x=df.groupby(\"Category\")[\"Sales\"].sum().values, orientation=\"h\"\n",
    ")\n",
    "\n",
    "layout = go.Layout(\n",
    "    title={\"text\": \"Total Sales by Category\", \"x\": 0.5, \"xanchor\": \"center\"}, legend=dict(orientation=\"h\")\n",
    ")\n",
    "fig8 = go.Figure(data=data, layout=layout)\n",
    "\n",
    "fig8"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.554141Z",
     "start_time": "2020-10-28T19:01:40.544871Z"
    },
    "id": "wj2hiPxUd0PY",
    "tags": []
   },
   "outputs": [],
   "source": [
    "data = go.Bar(\n",
    "    y=df.groupby(\"Sub-Category\")[\"Profit\"].sum().index,\n",
    "    x=df.groupby(\"Sub-Category\")[\"Profit\"].sum().values,\n",
    "    orientation=\"h\",\n",
    ")\n",
    "\n",
    "layout = go.Layout(\n",
    "    title={\"text\": \"Total Profit by Sub-Category\", \"x\": 0.5, \"xanchor\": \"center\"}, legend=dict(orientation=\"h\")\n",
    ")\n",
    "fig9 = go.Figure(data=data, layout=layout)\n",
    "fig9"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.609542Z",
     "start_time": "2020-10-28T19:01:40.580944Z"
    },
    "id": "wEJ1FbIAQSxw",
    "tags": []
   },
   "outputs": [],
   "source": [
    "top_cities = df.groupby(\"City\")[\"Quantity\"].sum().sort_values(ascending=False)[:10].index\n",
    "\n",
    "fv = df[df.City.isin(top_cities)].pivot_table(index=\"City\", columns=\"dow\", values=\"Sales\", aggfunc=lambda x: x.mean())\n",
    "\n",
    "trace = go.Heatmap(\n",
    "    z=fv.values,\n",
    "    x=[\"Monday\", \"Tuesday\", \"Wednesday\", \"Thursday\", \"Friday\", \"Saturday\", \"Sunday\"],\n",
    "    y=fv.index.values,\n",
    ")\n",
    "\n",
    "data = [trace]\n",
    "layout = go.Layout(title={\"text\": \"Total Sales by City\", \"x\": 0.5, \"xanchor\": \"center\"})\n",
    "\n",
    "fig10 = go.Figure(data=data, layout=layout)\n",
    "fig10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:40.638445Z",
     "start_time": "2020-10-28T19:01:40.610963Z"
    },
    "id": "dXFZJLfAQSxy",
    "tags": []
   },
   "outputs": [],
   "source": [
    "top_cities = df.groupby(\"City\")[\"Quantity\"].sum().sort_values(ascending=False)[:10].index\n",
    "\n",
    "fv = df[df.City.isin(top_cities)].pivot_table(index=\"City\", columns=\"dow\", values=\"Profit\", aggfunc=lambda x: x.mean())\n",
    "\n",
    "trace = go.Heatmap(\n",
    "    z=fv.values,\n",
    "    x=[\"Monday\", \"Tuesday\", \"Wednesday\", \"Thursday\", \"Friday\", \"Saturday\", \"Sunday\"],\n",
    "    y=fv.index.values,\n",
    ")\n",
    "\n",
    "data = [trace]\n",
    "layout = go.Layout(title={\"text\": \"Total Profit by City\", \"x\": 0.5, \"xanchor\": \"center\"})\n",
    "\n",
    "fig11 = go.Figure(data=data, layout=layout)\n",
    "\n",
    "fig11"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "r1fIY9rj5sje"
   },
   "source": [
    "# Building a Datapane report\n",
    "\n",
    "Now that we have a series of plots created using Plotly, we can create a report using Datapane. \n",
    "\n",
    "In addition to the visualizations, this report includes Datapane's `HTML` component to display some custom styling.\n",
    "\n",
    "We are also creating a dropdown using Datpane's `Select` block (see [layout components](https://docs.datapane.com/features/layout_blocks/)). This allows the user to choose their desired visualization from a list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-10-28T19:01:47.291425Z",
     "start_time": "2020-10-28T19:01:43.918990Z"
    },
    "id": "UQyVpYFBvkw0"
   },
   "outputs": [],
   "source": [
    "banner_html = \"\"\"<div style=\"padding: 10px;display: flex;align-items: center;font-size: 40px;color: #312E81;background: #EEF2FF;\">\n",
    "<h1>Superstore Analysis</h1>\n",
    "</div>\n",
    "\"\"\"\n",
    "\n",
    "header = dp.Group(dp.Media(\"./cart.jpg\"), dp.HTML(banner_html), columns=2, widths=[1, 5])\n",
    "\n",
    "v = dp.View(\n",
    "    dp.Page(\n",
    "        header,\n",
    "        dp.Select(\n",
    "            dp.Plot(fig0, label=\"Sales by Day\", caption=\"This plot shows total sales by day from 2014 to 2017.\"),\n",
    "            dp.Plot(\n",
    "                fig4,\n",
    "                label=\"Sales by Region\",\n",
    "                caption=\"This plot shows total sales by west, east, central, south regions.\",\n",
    "            ),\n",
    "            dp.Plot(fig10, label=\"Sales by City\", caption=\"This plot shows total sales by city.\"),\n",
    "            dp.Plot(\n",
    "                fig2,\n",
    "                label=\"Sales by Segment\",\n",
    "                caption=\"This plot shows total sales by consumer, corporate, home-office segments.\",\n",
    "            ),\n",
    "            dp.Plot(fig6, label=\"Sales by Category\", caption=\"This plot shows total sales by category.\"),\n",
    "            dp.Plot(fig8, label=\"Sales by Subcategory\", caption=\"This plot shows total sales by subcategory.\"),\n",
    "        ),\n",
    "        title=\"Sales Analysis\",\n",
    "    ),\n",
    "    dp.Page(\n",
    "        header,\n",
    "        dp.Select(\n",
    "            dp.Plot(\n",
    "                fig1,\n",
    "                label=\"Profit by Day\",\n",
    "                caption=\"This plot shows total Profit by day from 2014 to 2017.\",\n",
    "            ),\n",
    "            dp.Plot(\n",
    "                fig5,\n",
    "                label=\"Profit by Region\",\n",
    "                caption=\"This plot shows total Profit by west, east, central, south regions.\",\n",
    "            ),\n",
    "            dp.Plot(fig11, label=\"Profit by City\", caption=\"This plot shows total Profit by city.\"),\n",
    "            dp.Plot(\n",
    "                fig3,\n",
    "                label=\"Profit by Segment\",\n",
    "                caption=\"This plot shows total Profit by consumer, corporate, home-office segments.\",\n",
    "            ),\n",
    "            dp.Plot(fig7, label=\"Profit by Category\", caption=\"This plot shows total Profit by category.\"),\n",
    "            dp.Plot(fig9, label=\"Profit by Subcategory\", caption=\"This plot shows total Profit by subcategory.\"),\n",
    "        ),\n",
    "        title=\"Profit Analysis\",\n",
    "    ),\n",
    ")\n",
    "\n",
    "dp.save_report(v, \"report.html\", open=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run this cell to upload this report to Datapane Cloud\n",
    "dp.upload_report(v, name=\"E-Commerce Report\", publicly_visible=True)"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "collapsed_sections": [],
   "name": "Datapane - E-Commerce Store Analysis",
   "provenance": []
  },
  "hide_input": false,
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}