Skip to content

Instantly share code, notes, and snippets.

@bskinn

bskinn/ccl-db-msg-freqs.ipynb

Created July 12, 2022 14:44
Show Gist options
  • Save bskinn/39c522e7664f69bc82374f0c4e565531 to your computer and use it in GitHub Desktop.
Save bskinn/39c522e7664f69bc82374f0c4e565531 to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
ccl-db-msg-freqs.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"name": "CCL-DB Message Frequencies.ipynb",
"provenance": [],
"toc_visible": true,
"collapsed_sections": []
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"language_info": {
"name": "python"
}
},
"cells": [
{
"cell_type": "markdown",
"source": [
"# CCL.NET: Dashboard for Analysis of Message Posting Frequency\n",
"\n",
"*(description pending)*\n",
"\n",
"Instructions:\n",
"\n",
"1. Click `Runtime` > `Run all` in the menu.\n",
"2. Click the `127.0.0.1` link that should appear in the last cell of the notebook."
],
"metadata": {
"id": "UlfTo12-NGkm"
}
},
{
"cell_type": "markdown",
"source": [
"# Install dependencies\n",
"\n",
"The Google Colab instances don't include Dash or `jupyter-dash` by default. Installing `jupyter-dash` is enough to bring in all of the needed dependencies."
],
"metadata": {
"id": "QqbnM1IdPx_a"
}
},
{
"cell_type": "code",
"source": [
"!pip install -U -q jupyter-dash"
],
"metadata": {
"id": "Cp_Pk6FsQATO",
"outputId": "37f0a7a0-c56e-41c6-c48d-4e42de7e4044",
"colab": {
"base_uri": "https://localhost:8080/"
}
},
"execution_count": 1,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"\u001b[K |████████████████████████████████| 9.8 MB 17.2 MB/s \n",
"\u001b[K |████████████████████████████████| 357 kB 38.0 MB/s \n",
"\u001b[?25h Building wheel for retrying (setup.py) ... \u001b[?25l\u001b[?25hdone\n"
]
}
]
},
{
"cell_type": "markdown",
"source": [
"# Import Tools\n",
"\n",
"We need `datetime`, `calendar`, `pandas`, `plotly`, `dash`, and `jupyter_dash`.\n",
"\n",
"- `datetime` is part of the Python standard library and helps with date representation\n",
"- `calendar` is also part of the Python standard library, and makes it easy to find the last day of any given month\n",
"- `pandas` provides the `DataFrame` class for holding the timeseries data\n",
"- `numpy` provides some low-level array operations\n",
"- `plotly` is the plotting library underlying `dash`\n",
"- `dash` is a tool for creating interactive data dashboards\n",
"- `jupyter_dash` provides the machinery to render a Dash dashboard in Jupyter"
],
"metadata": {
"id": "Tq7FL0qbNUWX"
}
},
{
"cell_type": "code",
"source": [
"import calendar\n",
"import datetime\n",
"\n",
"import numpy as np\n",
"import pandas as pd\n",
"import plotly.express as px\n",
"from dash import dcc, html as dhtml\n",
"from dash.dependencies import Input, Output, State\n",
"from jupyter_dash import JupyterDash"
],
"metadata": {
"id": "fYDZxksbqegA"
},
"execution_count": 2,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"# Retrieve Data\n",
"\n",
"The data is hosted on Google Drive. These links point directly to CSV files of the yearly, monthly and daily message frequencies for CCL."
],
"metadata": {
"id": "xMEDh5oFNYbh"
}
},
{
"cell_type": "code",
"source": [
"df_yearly = pd.read_csv(\"https://drive.google.com/uc?id=19jZ62cQBMvoqQMHyzglsH-_r3JbF8KBQ\")\n",
"df_monthly = pd.read_csv(\"https://drive.google.com/uc?id=1PHc1eGwOC6Bb1rPH1yL7tEuxVQH_DJ-m\")\n",
"df_daily = pd.read_csv(\"https://drive.google.com/uc?id=1NpsgvbUjI_ahOy7z_n7RWeT_UOiTKK2v\")\n",
"df_monthly.tail()"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 206
},
"id": "_M7hGntAvd-R",
"outputId": "9f1d765b-3607-4800-c69f-9de0462f7328"
},
"execution_count": 3,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
" year month count\n",
"371 2021 12 47\n",
"372 2022 1 25\n",
"373 2022 2 20\n",
"374 2022 3 24\n",
"375 2022 4 24"
],
"text/html": [
"\n",
" <div id=\"df-826742a8-dc37-445e-a1f8-7642e447e720\">\n",
" <div class=\"colab-df-container\">\n",
" <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>year</th>\n",
" <th>month</th>\n",
" <th>count</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>371</th>\n",
" <td>2021</td>\n",
" <td>12</td>\n",
" <td>47</td>\n",
" </tr>\n",
" <tr>\n",
" <th>372</th>\n",
" <td>2022</td>\n",
" <td>1</td>\n",
" <td>25</td>\n",
" </tr>\n",
" <tr>\n",
" <th>373</th>\n",
" <td>2022</td>\n",
" <td>2</td>\n",
" <td>20</td>\n",
" </tr>\n",
" <tr>\n",
" <th>374</th>\n",
" <td>2022</td>\n",
" <td>3</td>\n",
" <td>24</td>\n",
" </tr>\n",
" <tr>\n",
" <th>375</th>\n",
" <td>2022</td>\n",
" <td>4</td>\n",
" <td>24</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>\n",
" <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-826742a8-dc37-445e-a1f8-7642e447e720')\"\n",
" title=\"Convert this dataframe to an interactive table.\"\n",
" style=\"display:none;\">\n",
" \n",
" <svg xmlns=\"http://www.w3.org/2000/svg\" height=\"24px\"viewBox=\"0 0 24 24\"\n",
" width=\"24px\">\n",
" <path d=\"M0 0h24v24H0V0z\" fill=\"none\"/>\n",
" <path d=\"M18.56 5.44l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94zm-11 1L8.5 8.5l.94-2.06 2.06-.94-2.06-.94L8.5 2.5l-.94 2.06-2.06.94zm10 10l.94 2.06.94-2.06 2.06-.94-2.06-.94-.94-2.06-.94 2.06-2.06.94z\"/><path d=\"M17.41 7.96l-1.37-1.37c-.4-.4-.92-.59-1.43-.59-.52 0-1.04.2-1.43.59L10.3 9.45l-7.72 7.72c-.78.78-.78 2.05 0 2.83L4 21.41c.39.39.9.59 1.41.59.51 0 1.02-.2 1.41-.59l7.78-7.78 2.81-2.81c.8-.78.8-2.07 0-2.86zM5.41 20L4 18.59l7.72-7.72 1.47 1.35L5.41 20z\"/>\n",
" </svg>\n",
" </button>\n",
" \n",
" <style>\n",
" .colab-df-container {\n",
" display:flex;\n",
" flex-wrap:wrap;\n",
" gap: 12px;\n",
" }\n",
"\n",
" .colab-df-convert {\n",
" background-color: #E8F0FE;\n",
" border: none;\n",
" border-radius: 50%;\n",
" cursor: pointer;\n",
" display: none;\n",
" fill: #1967D2;\n",
" height: 32px;\n",
" padding: 0 0 0 0;\n",
" width: 32px;\n",
" }\n",
"\n",
" .colab-df-convert:hover {\n",
" background-color: #E2EBFA;\n",
" box-shadow: 0px 1px 2px rgba(60, 64, 67, 0.3), 0px 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
" fill: #174EA6;\n",
" }\n",
"\n",
" [theme=dark] .colab-df-convert {\n",
" background-color: #3B4455;\n",
" fill: #D2E3FC;\n",
" }\n",
"\n",
" [theme=dark] .colab-df-convert:hover {\n",
" background-color: #434B5C;\n",
" box-shadow: 0px 1px 3px 1px rgba(0, 0, 0, 0.15);\n",
" filter: drop-shadow(0px 1px 2px rgba(0, 0, 0, 0.3));\n",
" fill: #FFFFFF;\n",
" }\n",
" </style>\n",
"\n",
" <script>\n",
" const buttonEl =\n",
" document.querySelector('#df-826742a8-dc37-445e-a1f8-7642e447e720 button.colab-df-convert');\n",
" buttonEl.style.display =\n",
" google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
"\n",
" async function convertToInteractive(key) {\n",
" const element = document.querySelector('#df-826742a8-dc37-445e-a1f8-7642e447e720');\n",
" const dataTable =\n",
" await google.colab.kernel.invokeFunction('convertToInteractive',\n",
" [key], {});\n",
" if (!dataTable) return;\n",
"\n",
" const docLinkHtml = 'Like what you see? Visit the ' +\n",
" '<a target=\"_blank\" href=https://colab.research.google.com/notebooks/data_table.ipynb>data table notebook</a>'\n",
" + ' to learn more about interactive tables.';\n",
" element.innerHTML = '';\n",
" dataTable['output_type'] = 'display_data';\n",
" await google.colab.output.renderOutput(dataTable, element);\n",
" const docLink = document.createElement('div');\n",
" docLink.innerHTML = docLinkHtml;\n",
" element.appendChild(docLink);\n",
" }\n",
" </script>\n",
" </div>\n",
" </div>\n",
" "
]
},
"metadata": {},
"execution_count": 3
}
]
},
{
"cell_type": "markdown",
"source": [
"# Add 'Date' Columns to Monthly and Daily Data\n",
"\n",
"In order to plot the monthly and daily data, we need to have a single column with a suitable `datetime` value for each year/month and year/month/day combination."
],
"metadata": {
"id": "p4EjHParTe-G"
}
},
{
"cell_type": "markdown",
"source": [
"## Define helper constants\n",
"\n",
"These will simplify access to the 'date' and 'count' columns."
],
"metadata": {
"id": "YG09WxtcT6kR"
}
},
{
"cell_type": "code",
"source": [
"COL_COUNT = \"count\"\n",
"COL_DATE = \"date\""
],
"metadata": {
"id": "mIUWThQIT_QD"
},
"execution_count": 4,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"## Add the 'date' columns"
],
"metadata": {
"id": "OGbKQqOFUG2W"
}
},
{
"cell_type": "code",
"source": [
"df_monthly[COL_DATE] = pd.to_datetime(pd.concat([df_monthly[[\"year\", \"month\"]], pd.DataFrame(np.ones(len(df_monthly.index), dtype=int), columns=[\"day\"])], axis=\"columns\"))\n",
"df_daily[COL_DATE] = pd.to_datetime(df_daily[[\"year\", \"month\", \"day\"]])"
],
"metadata": {
"id": "I_QPQKglTvUS"
},
"execution_count": 5,
"outputs": []
},
{
"cell_type": "code",
"source": [
"d = df_daily.iloc[0]['date']\n",
"pd.Timestamp(year=d.year, month=d.month, day=calendar.monthlen(year=d.year, month=d.month))"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "ZMD6sRfXI-zz",
"outputId": "d72a2da5-e9d6-41e2-d9b6-1277df116311"
},
"execution_count": 6,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"Timestamp('1991-01-31 00:00:00')"
]
},
"metadata": {},
"execution_count": 6
}
]
},
{
"cell_type": "markdown",
"source": [
"# Create the Dash app\n",
"\n",
"Dash lets you create visualization dashboards with interconnected controls and plots. Here, because of the size of the dataset, it's not practical to plot all of the daily data. So, this Dash app shows you all of the data per-month, and lets you choose a range of data to show per-day."
],
"metadata": {
"id": "IV2cBFFJQJut"
}
},
{
"cell_type": "markdown",
"source": [
"## Define helper constants\n",
"\n",
"Instead of using explicit strings to name and refer to all of the pieces of the app, we'll define string constants for these names. That way, if we want to change the name of a component for some reason, we only have to change it in once place."
],
"metadata": {
"id": "uDeQNwjBQ9OI"
}
},
{
"cell_type": "code",
"source": [
"APP_NAME = \"ccl-db-message-frequencies\"\n",
"HIGH_LEVEL_GRAPH = \"high-level-graph\"\n",
"DETAILED_GRAPH = \"detailed-graph\"\n",
"# OPEN_DAY_BUTTON = \"open-day-button\"\n",
"OPEN_DAY_ANCHOR = \"open-day-anchor\"\n",
"\n",
"# OPEN_DAY_TEMPLATE = \"Open {} on CCL\""
],
"metadata": {
"id": "2gHx_CyBRI95"
},
"execution_count": 7,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"## Define functions that create the figures"
],
"metadata": {
"id": "vbdl_rR9TPHL"
}
},
{
"cell_type": "code",
"source": [
"def create_empty_graph():\n",
" return px.scatter()"
],
"metadata": {
"id": "1gpKP132X0f1"
},
"execution_count": 8,
"outputs": []
},
{
"cell_type": "code",
"source": [
"def create_high_level_graph():\n",
" return px.bar(df_monthly, x=\"date\", y=\"count\")"
],
"metadata": {
"id": "DbUNliheTRwK"
},
"execution_count": 9,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"## Define helper functions for the app callbacks"
],
"metadata": {
"id": "akgiV01JC6QQ"
}
},
{
"cell_type": "code",
"source": [
"def get_lowest_date_info(selected_data):\n",
" \"\"\"Supply year, month, day for the earliest date in selected_data.\n",
" \n",
" () --> tuple[Optional[int], Optional[int], Optional[int]]\n",
" \n",
" (year, month, day)\n",
"\n",
" selected_data is of the type returned by an `Input(..., \"selectedData\")`\n",
" callback.\n",
" \n",
" \"\"\"\n",
" try:\n",
" pts = selected_data[\"points\"]\n",
" except:\n",
" return None, None, None\n",
"\n",
" if len(pts):\n",
" d = min(p[\"x\"] for p in pts)\n",
"\n",
" # Running the data points through `min` converts to yyyy-mm-dd strings\n",
" return d.split(\"-\")\n",
" else:\n",
" return None, None, None\n"
],
"metadata": {
"id": "mtMuTyIwC9zD"
},
"execution_count": 10,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"## Define the app"
],
"metadata": {
"id": "kcIVYADyRm7v"
}
},
{
"cell_type": "code",
"source": [
"# Create app and app layout\n",
"app = JupyterDash(\"ccl-db-message-frequencies\")\n",
"app.layout = dhtml.Div([\n",
" dhtml.H1(\"CCL.NET Message Posting Frequency\"),\n",
" dhtml.Div([\n",
" dhtml.P(\"Hover over a chart to show controls.\"),\n",
" dhtml.P(\n",
" \"Zoom (magnifying glass) and pan (up/down/left/right arrow) \"\n",
" \"to the area of interest, then select a range of bars \"\n",
" \"(dotted rectangle) to populate the posts-per-day chart below.\"\n",
" )]),\n",
" dhtml.H2(\"Posts Per Month (Full Archive)\"),\n",
" dcc.Graph(id=HIGH_LEVEL_GRAPH, figure=create_high_level_graph()),\n",
" dhtml.H2(\"Posts Per Day (Selected Range)\"),\n",
" dhtml.Div([\n",
" dhtml.P(\"Use the same zoom/pan/select controls as above.\"),\n",
" dhtml.P(\n",
" \"After selecting some data, clicking the link below \"\n",
" \"will take you to the day page on CCL for the first day \"\n",
" \"in that range.\"\n",
" ),\n",
" ]),\n",
" dhtml.Div([\n",
" dhtml.A(\"App loading...\", id=OPEN_DAY_ANCHOR, target=\"_blank\", href=\"\"),\n",
" ]),\n",
" dcc.Graph(id=DETAILED_GRAPH, figure=create_empty_graph()),\n",
"])\n",
"\n",
"# Callback for populating the detailed plot\n",
"@app.callback(\n",
" Output(DETAILED_GRAPH, \"figure\"),\n",
" Input(HIGH_LEVEL_GRAPH, \"selectedData\"),\n",
")\n",
"def set_detail_figure(hi_data):\n",
" \"\"\"Populate the detailed figure from the high-level selection.\"\"\"\n",
" try:\n",
" pts = hi_data[\"points\"]\n",
" except:\n",
" return create_empty_graph()\n",
"\n",
" if len(pts):\n",
" min_date = min(p[\"x\"] for p in hi_data[\"points\"])\n",
" max_date = max(p[\"x\"] for p in hi_data[\"points\"])\n",
"\n",
" # All months' dates are for the first day of the month.\n",
" # We have to shift max_date to the last day of the month.\n",
" md = pd.Timestamp(max_date)\n",
" md = pd.Timestamp(\n",
" year=md.year,\n",
" month=md.month,\n",
" day=calendar.monthrange(year=md.year, month=md.month)[1]\n",
" )\n",
" max_date = f\"{md.year}-{md.month:0>2}-{md.day:0>2}\"\n",
"\n",
" else:\n",
" # Filters that return *no* data, so the chart will be blank\n",
" # whenever no selection has been made\n",
" min_date = 0\n",
" max_date = 1\n",
" \n",
" df = df_daily[df_daily[\"date\"] >= min_date]\n",
" df = df[df[\"date\"] <= max_date]\n",
"\n",
" return px.bar(df, x=\"date\", y=\"count\")\n",
"\n",
"\n",
"# Callback for updating the outbound day-page anchor href\n",
"@app.callback(\n",
" Output(OPEN_DAY_ANCHOR, \"href\"),\n",
" Input(DETAILED_GRAPH, \"selectedData\"),\n",
")\n",
"def update_day_page_anchor_href(detail_data):\n",
" year, month, day = get_lowest_date_info(detail_data)\n",
"\n",
" if year:\n",
" return f\"http://ccl.net/cgi-bin/ccl/day-index.cgi?{year}+{month:0>2}+{day:0>2}\"\n",
" else:\n",
" return \"http://ccl.net/chemistry/resources/messages/index.shtml\"\n",
"\n",
"\n",
"# Callback for updating the outbound day-page anchor text\n",
"@app.callback(\n",
" Output(OPEN_DAY_ANCHOR, \"children\"),\n",
" Input(DETAILED_GRAPH, \"selectedData\"),\n",
")\n",
"def update_day_page_anchor_text(detail_data):\n",
" year, month, day = get_lowest_date_info(detail_data)\n",
"\n",
" if year:\n",
" return f\"Open {year}-{month:0>2}-{day:0>2} on CCL\"\n",
" else:\n",
" return dhtml.Em(\"(Nothing selected)\")\n"
],
"metadata": {
"id": "sZwHBkkrxbV1"
},
"execution_count": 11,
"outputs": []
},
{
"cell_type": "markdown",
"source": [
"## Display the app"
],
"metadata": {
"id": "YJFUqR9TRrVP"
}
},
{
"cell_type": "code",
"source": [
"print(\"Click the below '127.0.0.1' link to launch the app.\\n\")\n",
"app.run_server()"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/",
"height": 86
},
"id": "-wzV9da5RtEl",
"outputId": "218d9ae4-3400-418e-8296-2761584d44ee"
},
"execution_count": 12,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"Click the below '127.0.0.1' link to launch the app.\n",
"\n",
"Dash app running on:\n"
]
},
{
"output_type": "display_data",
"data": {
"text/plain": [
"<IPython.core.display.Javascript object>"
],
"application/javascript": [
"(async (port, path, text, element) => {\n",
" if (!google.colab.kernel.accessAllowed) {\n",
" return;\n",
" }\n",
" element.appendChild(document.createTextNode(''));\n",
" const url = await google.colab.kernel.proxyPort(port);\n",
" const anchor = document.createElement('a');\n",
" anchor.href = new URL(path, url).toString();\n",
" anchor.target = '_blank';\n",
" anchor.setAttribute('data-href', url + path);\n",
" anchor.textContent = text;\n",
" element.appendChild(anchor);\n",
" })(8050, \"/\", \"http://127.0.0.1:8050/\", window.element)"
]
},
"metadata": {}
}
]
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment