Data_Restructure.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "yrgHJ3yuuwjf"
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ZPVFpsUpu3VV"
   },
   "outputs": [],
   "source": [
    "path = Path('drive/MyDrive/erin_sheets')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "qUsSfMZMxlu6"
   },
   "outputs": [],
   "source": [
    "timeline = pd.read_excel(path/'c1c2_timeline_7.3.xlsx')\n",
    "trimmed = pd.read_excel(path/'c1c2_weekly_trimmed_7.3.xlsx')\n",
    "times = pd.read_excel(path/'Time.xlsx')\n",
    "ids = pd.read_excel(path/'all_ids.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9yqpQUOJzRSC"
   },
   "source": [
    "#Timeline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9v2HwWKVzUfa"
   },
   "source": [
    "Timeline (I think that was weekly B last time) -\n",
    "column for SID, column for day (e.g., B1W1D1), MJ use Y/N) and hours high. if MJ use is no, this should be 0\n",
    "for day column, want to combine burst, week and day column into something like this = B1W1D1. so we should have 63 rows for each person\n",
    "all missing -999"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "OxwHMgVz2d0B"
   },
   "outputs": [],
   "source": [
    "def condense_bwd(timeline):\n",
    "  return f\"B{timeline['burst']}W{timeline['week']}D{timeline['day']}\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "sJS-jV394i_9"
   },
   "outputs": [],
   "source": [
    "def used_cannabis(timeline):\n",
    "  return 1 if timeline['cannabis'] == 1 else timeline['cannabis']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "8S8Y1X-Ftu2C"
   },
   "outputs": [],
   "source": [
    "def used_ecig(timeline):\n",
    "  return 1 if timeline['ecig'] == 1 else timeline['ecig']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "A-tsTPQg2DX4"
   },
   "outputs": [],
   "source": [
    "timeline[\"bwd\"] = timeline.apply(condense_bwd, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Rig4ipX94JGZ"
   },
   "outputs": [],
   "source": [
    "d = {\"PID\":[], \"bwd\": []}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "ZFr8bi8Fz3Eo"
   },
   "outputs": [],
   "source": [
    "for id in timeline[\"PID\"].unique():\n",
    "  for bwd in timeline[\"bwd\"].unique():\n",
    "    d[\"PID\"].append(id)\n",
    "    d[\"bwd\"].append(bwd)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "A-GLD9eE4Snn"
   },
   "outputs": [],
   "source": [
    "timeline_complete = pd.DataFrame(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 677
    },
    "id": "QWjmTj1Zf_jG",
    "outputId": "fd020e9d-0d44-44fd-f078-d108d0a2317c"
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "    PID  burst  week  day       date  cannabis  num_hours_high  ecig      bwd\n",
       "0   101      1     1    1 2022-03-11       0.0             0.0   0.0   B1W1D1\n",
       "1   101      1     1    2 2022-03-12       0.0             0.0   0.0   B1W1D2\n",
       "2   101      1     1    3 2022-03-13       0.0             0.0   0.0   B1W1D3\n",
       "3   101      1     1    4 2022-03-14       0.0             0.0   0.0   B1W1D4\n",
       "4   101      1     1    5 2022-03-15       0.0             0.0   0.0   B1W1D5\n",
       "5   101      1     1    6 2022-03-16       0.0             0.0   0.0   B1W1D6\n",
       "6   101      1     1    7 2022-03-17       0.0             0.0   0.0   B1W1D7\n",
       "7   101      2     1    1 2022-07-01       0.0             0.0   0.0   B2W1D1\n",
       "8   101      2     1    2 2022-07-02       0.0             0.0   0.0   B2W1D2\n",
       "9   101      2     1    3 2022-07-03       0.0             0.0   0.0   B2W1D3\n",
       "10  101      2     1    4 2022-07-04       0.0             0.0   0.0   B2W1D4\n",
       "11  101      2     1    5 2022-07-05       0.0             0.0   0.0   B2W1D5\n",
       "12  101      2     1    6 2022-07-06       0.0             0.0   0.0   B2W1D6\n",
       "13  101      2     1    7 2022-07-07       0.0             0.0   0.0   B2W1D7\n",
       "14  101      2     2    8 2022-07-08       0.0             0.0   0.0   B2W2D8\n",
       "15  101      2     2    9 2022-07-09       0.0             0.0   0.0   B2W2D9\n",
       "16  101      2     2   10 2022-07-10       0.0             0.0   0.0  B2W2D10\n",
       "17  101      2     2   11 2022-07-11       0.0             0.0   0.0  B2W2D11\n",
       "18  101      2     2   12 2022-07-12       0.0             0.0   0.0  B2W2D12\n",
       "19  101      2     2   13 2022-07-13       0.0             0.0   0.0  B2W2D13"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-7790a763-43e1-4429-abed-9c3246cfbbc5\" 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>PID</th>\n",
       "      <th>burst</th>\n",
       "      <th>week</th>\n",
       "      <th>day</th>\n",
       "      <th>date</th>\n",
       "      <th>cannabis</th>\n",
       "      <th>num_hours_high</th>\n",
       "      <th>ecig</th>\n",
       "      <th>bwd</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-03-11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B1W1D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2022-03-12</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B1W1D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2022-03-13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B1W1D3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2022-03-14</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B1W1D4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2022-03-15</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B1W1D5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2022-03-16</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B1W1D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2022-03-17</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B1W1D7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2022-07-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W1D1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2022-07-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W1D2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2022-07-03</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W1D3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>2022-07-04</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W1D4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2022-07-05</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W1D5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2022-07-06</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W1D6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2022-07-07</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W1D7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>2022-07-08</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W2D8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>2022-07-09</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W2D9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>10</td>\n",
       "      <td>2022-07-10</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W2D10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>2022-07-11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W2D11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>12</td>\n",
       "      <td>2022-07-12</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W2D12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>101</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>13</td>\n",
       "      <td>2022-07-13</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>B2W2D13</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-7790a763-43e1-4429-abed-9c3246cfbbc5')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-7790a763-43e1-4429-abed-9c3246cfbbc5 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-7790a763-43e1-4429-abed-9c3246cfbbc5');\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",
       "\n",
       "\n",
       "<div id=\"df-a253306d-c101-45de-a2ee-c4c8988a8b57\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-a253306d-c101-45de-a2ee-c4c8988a8b57')\"\n",
       "            title=\"Suggest charts.\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-a253306d-c101-45de-a2ee-c4c8988a8b57 button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 12
    }
   ],
   "source": [
    "timeline.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "NHX1lS374WEW"
   },
   "outputs": [],
   "source": [
    "timeline_complete = timeline_complete.merge(timeline, on=[\"PID\", \"bwd\"], how=\"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "CVKB4zO5_WHC"
   },
   "outputs": [],
   "source": [
    "timeline_complete['cannabis_binary'] = timeline.apply(used_cannabis, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "Zqc_FXvJt2YA"
   },
   "outputs": [],
   "source": [
    "timeline_complete['ecig_binary'] = timeline.apply(used_ecig, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "yihVU6gUKsuK"
   },
   "outputs": [],
   "source": [
    "timeline_complete.fillna(-999, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "FpcIB6UYKuAN",
    "outputId": "f7b2186e-d1e3-492e-a355-c82fa19a23d1"
   },
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      PID      bwd  burst   week    day                 date  cannabis  \\\n",
       "0     101   B1W1D1    1.0    1.0    1.0  2022-03-11 00:00:00       0.0   \n",
       "1     101   B1W1D2    1.0    1.0    2.0  2022-03-12 00:00:00       0.0   \n",
       "2     101   B1W1D3    1.0    1.0    3.0  2022-03-13 00:00:00       0.0   \n",
       "3     101   B1W1D4    1.0    1.0    4.0  2022-03-14 00:00:00       0.0   \n",
       "4     101   B1W1D5    1.0    1.0    5.0  2022-03-15 00:00:00       0.0   \n",
       "...   ...      ...    ...    ...    ...                  ...       ...   \n",
       "9760  305  B3W3D17 -999.0 -999.0 -999.0                 -999    -999.0   \n",
       "9761  305  B3W3D18 -999.0 -999.0 -999.0                 -999    -999.0   \n",
       "9762  305  B3W3D19 -999.0 -999.0 -999.0                 -999    -999.0   \n",
       "9763  305  B3W3D20 -999.0 -999.0 -999.0                 -999    -999.0   \n",
       "9764  305  B3W3D21 -999.0 -999.0 -999.0                 -999    -999.0   \n",
       "\n",
       "      num_hours_high   ecig  cannabis_binary  ecig_binary  \n",
       "0                0.0    0.0              0.0          0.0  \n",
       "1                0.0    0.0              0.0          0.0  \n",
       "2                0.0    0.0              0.0          0.0  \n",
       "3                0.0    0.0              0.0          0.0  \n",
       "4                0.0    0.0              0.0          0.0  \n",
       "...              ...    ...              ...          ...  \n",
       "9760          -999.0 -999.0           -999.0       -999.0  \n",
       "9761          -999.0 -999.0           -999.0       -999.0  \n",
       "9762          -999.0 -999.0           -999.0       -999.0  \n",
       "9763          -999.0 -999.0           -999.0       -999.0  \n",
       "9764          -999.0 -999.0           -999.0       -999.0  \n",
       "\n",
       "[9765 rows x 11 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-86c6f947-ce66-4d00-9451-3205d49e79b8\" 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>PID</th>\n",
       "      <th>bwd</th>\n",
       "      <th>burst</th>\n",
       "      <th>week</th>\n",
       "      <th>day</th>\n",
       "      <th>date</th>\n",
       "      <th>cannabis</th>\n",
       "      <th>num_hours_high</th>\n",
       "      <th>ecig</th>\n",
       "      <th>cannabis_binary</th>\n",
       "      <th>ecig_binary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2022-03-11 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2022-03-12 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2022-03-13 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D4</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2022-03-14 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D5</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2022-03-15 00:00:00</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9760</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D17</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9761</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D18</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9762</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D19</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9763</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D20</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9764</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D21</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9765 rows × 11 columns</p>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-86c6f947-ce66-4d00-9451-3205d49e79b8')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-86c6f947-ce66-4d00-9451-3205d49e79b8 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-86c6f947-ce66-4d00-9451-3205d49e79b8');\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",
       "\n",
       "\n",
       "<div id=\"df-19303d3e-a1c0-4540-9383-f27999fcebb4\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-19303d3e-a1c0-4540-9383-f27999fcebb4')\"\n",
       "            title=\"Suggest charts.\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-19303d3e-a1c0-4540-9383-f27999fcebb4 button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 18
    }
   ],
   "source": [
    "timeline_complete"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "RTTnCEuOuNi7",
    "outputId": "bd83e7fb-6dcc-4283-e838-837341f389d5"
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "  <div id=\"df-50921318-4879-433c-a2ad-8bcec31aa126\">\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>PID</th>\n",
       "      <th>bwd</th>\n",
       "      <th>num_hours_high</th>\n",
       "      <th>cannabis_binary</th>\n",
       "      <th>ecig_binary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9760</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D17</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9761</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D18</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9762</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D19</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9763</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D20</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9764</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D21</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9765 rows × 5 columns</p>\n",
       "</div>\n",
       "      <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-50921318-4879-433c-a2ad-8bcec31aa126')\"\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-50921318-4879-433c-a2ad-8bcec31aa126 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-50921318-4879-433c-a2ad-8bcec31aa126');\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",
       "  "
      ],
      "text/plain": [
       "      PID      bwd  num_hours_high  cannabis_binary  ecig_binary\n",
       "0     101   B1W1D1             0.0              0.0          0.0\n",
       "1     101   B1W1D2             0.0              0.0          0.0\n",
       "2     101   B1W1D3             0.0              0.0          0.0\n",
       "3     101   B1W1D4             0.0              0.0          0.0\n",
       "4     101   B1W1D5             0.0              0.0          0.0\n",
       "...   ...      ...             ...              ...          ...\n",
       "9760  305  B3W3D17          -999.0           -999.0       -999.0\n",
       "9761  305  B3W3D18          -999.0           -999.0       -999.0\n",
       "9762  305  B3W3D19          -999.0           -999.0       -999.0\n",
       "9763  305  B3W3D20          -999.0           -999.0       -999.0\n",
       "9764  305  B3W3D21          -999.0           -999.0       -999.0\n",
       "\n",
       "[9765 rows x 5 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_sheet = timeline_complete[[\"PID\", \"bwd\", \"num_hours_high\", \"cannabis_binary\", \"ecig_binary\"]]\n",
    "final_sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "id": "PCit9FgWF3tx"
   },
   "outputs": [],
   "source": [
    "final_sheet.to_excel(\"timeline.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "# EMA_V1\n"
   ],
   "metadata": {
    "id": "9rAP9xTrh0DU"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "ema_raw = pd.read_excel(path/'TEAM300_EMA_7.3.23.xlsx')"
   ],
   "metadata": {
    "id": "2EFKkaQD-5ko"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "ema_raw.fillna(-999, inplace=True)"
   ],
   "metadata": {
    "id": "FXit8C4E_n4Z"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "import datetime\n",
    "\n",
    "def extract_b_for_sorting(row):\n",
    "  return int(row[\"day\"][1])\n",
    "def extract_d_for_sorting(row):\n",
    "  try:\n",
    "    return int(row[\"day\"][-2:])\n",
    "  except:\n",
    "    return int(row[\"day\"][-1])\n",
    "\n",
    "def move_sr_to_br(d):\n",
    "  if d[\"Time\"] >= datetime.time(21) and (d[\"Survey abbrev\"] in [\"SCMR\", \"SMRP\", \"SMRB\"]):\n",
    "    return \"BR\"\n",
    "  return d[\"Survey abbrev\"]\n",
    "\n",
    "def apply_letter_code(d):\n",
    "  if d[\"Survey abbrev\"] == \"MR\":\n",
    "    return \"A\"\n",
    "  if d[\"Survey abbrev\"] == \"BR\":\n",
    "    return \"C\"\n",
    "  return \"B\"\n",
    "\n",
    "def move_date(d):\n",
    "  if d[\"Date\"] == datetime.date(2022, 3, 10):\n",
    "    return datetime.date(2022, 3, 11)\n",
    "  elif d[\"Date\"] == datetime.date(2022, 11, 22):\n",
    "    return datetime.date(2022, 11, 21)\n",
    "  return d[\"Date\"]"
   ],
   "metadata": {
    "id": "w3eEUvxR_rAy"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "ema_raw[\"Survey abbrev\"] = ema_raw.apply(move_sr_to_br, axis=1)\n",
    "ema_raw[\"letter_code\"] = ema_raw.apply(apply_letter_code, axis=1)\n",
    "ema_raw[\"Date\"] = ema_raw.apply(move_date, axis=1)\n",
    "ema_raw = ema_raw.rename(columns={\"Date\": \"date\"})"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "An2RAwvVHhl1",
    "outputId": "ad4c2b71-a637-48e4-8926-8a458151d865"
   },
   "outputs": [],
   "execution_count": null
  },
  {
   "cell_type": "code",
   "source": [
    "ema_raw = ema_raw.merge(times[[\"date\", \"day\"]], on=\"date\")"
   ],
   "metadata": {
    "id": "-8xeYLieNO21"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "final = ema_raw[[\"SID\", \"day\", \"letter_code\", \"MJ\", \"Vaping\"]].groupby([\"SID\", \"day\", \"letter_code\"]).max().reset_index()"
   ],
   "metadata": {
    "id": "QsDQUFCmOvFR"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def generate_all_combos():\n",
    "  map = { \"SID\": [], \"day\": [], \"letter_code\": []}\n",
    "  for id in ids.PID.unique():\n",
    "    for day in ema_raw.day.unique():\n",
    "      for letter in ema_raw.letter_code.unique():\n",
    "        map[\"SID\"].append(id)\n",
    "        map[\"day\"].append(day)\n",
    "        map[\"letter_code\"].append(letter)\n",
    "  return map"
   ],
   "metadata": {
    "id": "xaZtm2KwQTP0"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "combos = pd.DataFrame(generate_all_combos())"
   ],
   "metadata": {
    "id": "Ksd9lsc-SHoN"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "last = combos.merge(final, on=[\"SID\", \"day\", \"letter_code\"], how=\"left\").fillna(-999)"
   ],
   "metadata": {
    "id": "pYmWMxHzTcR8"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def make_binary(d):\n",
    "  if d > 0:\n",
    "    return 1\n",
    "  return d"
   ],
   "metadata": {
    "id": "VnSvnciKUDFy"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "last[\"MJ\"] = last[\"MJ\"].apply(make_binary)\n",
    "last[\"Vaping\"] = last[\"Vaping\"].apply(make_binary)"
   ],
   "metadata": {
    "id": "-hl-MIqUUtYL"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def has_either(row):\n",
    "  if row[\"MJ\"] == -999:\n",
    "    return -999\n",
    "  if row[\"MJ\"] == 1 or row[\"Vaping\"] == 1:\n",
    "    return 1\n",
    "  return 0"
   ],
   "metadata": {
    "id": "mZPAAGm5C8bI"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "last[\"either\"] = last.apply(has_either, axis=1)"
   ],
   "metadata": {
    "id": "OLzFzUbCDdl0"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "last = last.rename(columns={\"letter_code\": \"Survey\"})\n",
    "last = last[[\"SID\", \"Survey\", \"day\", \"MJ\", \"Vaping\", \"either\"]]"
   ],
   "metadata": {
    "id": "BwyBTGmgU8kT"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "last[\"b\"] = last.apply(extract_b_for_sorting, axis=1)\n",
    "last[\"d\"] = last.apply(extract_d_for_sorting, axis=1)"
   ],
   "metadata": {
    "id": "TOnclrKJ1zuJ"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "last = last.sort_values(by=[\"SID\", \"b\", \"d\", \"Survey\"]).drop(columns=[\"b\", \"d\"])"
   ],
   "metadata": {
    "id": "4QKheRTx2MOO"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "last.to_excel(\"revised_ema.xlsx\", index=False)"
   ],
   "metadata": {
    "id": "z_4SPbzMVA0D"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Transpose weekly"
   ],
   "metadata": {
    "id": "JLUAv0joqCyR"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "weekly = pd.read_excel(path/'weekly.xlsx')"
   ],
   "metadata": {
    "id": "FbvSd3RxkaMS"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "weekly = weekly.rename(columns={\"MJ_Willingness\": \"MJ\", \"MJ_Friend_Approve\": \"Peer\"})"
   ],
   "metadata": {
    "id": "dScJmF4Wob-c"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "transpose_dict = {\"SID\": []}"
   ],
   "metadata": {
    "id": "Ls3yBbvvqBlE"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "for SID in weekly.SID.unique():\n",
    "  transpose_dict[\"SID\"].append(SID)\n",
    "  for BW in weekly.BW.unique():\n",
    "    row = weekly.loc[(weekly.SID == SID) & (weekly.BW == BW)]\n",
    "    if f\"MJ_{BW}\" not in transpose_dict:\n",
    "      transpose_dict[f\"MJ_{BW}\"] = []\n",
    "    transpose_dict[f\"MJ_{BW}\"].append(row.iloc[0][\"MJ\"])\n",
    "\n",
    "for SID in weekly.SID.unique():\n",
    "  for BW in weekly.BW.unique():\n",
    "    row = weekly.loc[(weekly.SID == SID) & (weekly.BW == BW)]\n",
    "    if f\"Peer_{BW}\" not in transpose_dict:\n",
    "      transpose_dict[f\"Peer_{BW}\"] = []\n",
    "    transpose_dict[f\"Peer_{BW}\"].append(row.iloc[0][\"Peer\"])"
   ],
   "metadata": {
    "id": "-s3xyRUBqPDN"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "weekly_transposed = pd.DataFrame(transpose_dict)\n",
    "weekly_transposed"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 444
    },
    "id": "pmOuGQ2focns",
    "outputId": "fbc7592b-cad1-42e6-d843-19e5d26530a9"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     SID  MJ_B1W1  MJ_B1W2  MJ_B1W3  MJ_B2W1  MJ_B2W2  MJ_B2W3  MJ_B3W1  \\\n",
       "0    101        4     -999     -999        4        4        4     -999   \n",
       "1    102        2        3        2        2        1        2     -999   \n",
       "2    103        4     -999     -999        4        4        3        4   \n",
       "3    104        3        3        3        3        3        3        3   \n",
       "4    106        2        2     -999        2        2        2        1   \n",
       "..   ...      ...      ...      ...      ...      ...      ...      ...   \n",
       "150  299        1     -999     -999     -999     -999     -999     -999   \n",
       "151  301     -999        2        2     -999     -999     -999        2   \n",
       "152  302        4        4        4        4        4        4        4   \n",
       "153  304        4        4        4        4        4        4        3   \n",
       "154  305        2     -999        2     -999        2        3        3   \n",
       "\n",
       "     MJ_B3W2  MJ_B3W3  Peer_B1W1  Peer_B1W2  Peer_B1W3  Peer_B2W1  Peer_B2W2  \\\n",
       "0       -999     -999          5       -999       -999          5          3   \n",
       "1       -999     -999          5          3          5          5          5   \n",
       "2          4        4          1       -999       -999          1          1   \n",
       "3          3        3          5          3          3          3          3   \n",
       "4          1        1          5          5       -999          3          3   \n",
       "..       ...      ...        ...        ...        ...        ...        ...   \n",
       "150     -999     -999          4       -999       -999       -999       -999   \n",
       "151        1     -999       -999          3          3       -999       -999   \n",
       "152        4        4          5          5          2          3          2   \n",
       "153        3        3          2          3          3          3          2   \n",
       "154        4     -999          4       -999          3       -999          5   \n",
       "\n",
       "     Peer_B2W3  Peer_B3W1  Peer_B3W2  Peer_B3W3  \n",
       "0            3       -999       -999       -999  \n",
       "1            5       -999       -999       -999  \n",
       "2            1          1          1          1  \n",
       "3            3          5          5          3  \n",
       "4            3          5          2          2  \n",
       "..         ...        ...        ...        ...  \n",
       "150       -999       -999       -999       -999  \n",
       "151       -999          5          4       -999  \n",
       "152          2          3          3          2  \n",
       "153          3          3          5          3  \n",
       "154          4          5          5       -999  \n",
       "\n",
       "[155 rows x 19 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-d40f2e23-84e0-41b0-a4d5-f7f389eadb43\" 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>SID</th>\n",
       "      <th>MJ_B1W1</th>\n",
       "      <th>MJ_B1W2</th>\n",
       "      <th>MJ_B1W3</th>\n",
       "      <th>MJ_B2W1</th>\n",
       "      <th>MJ_B2W2</th>\n",
       "      <th>MJ_B2W3</th>\n",
       "      <th>MJ_B3W1</th>\n",
       "      <th>MJ_B3W2</th>\n",
       "      <th>MJ_B3W3</th>\n",
       "      <th>Peer_B1W1</th>\n",
       "      <th>Peer_B1W2</th>\n",
       "      <th>Peer_B1W3</th>\n",
       "      <th>Peer_B2W1</th>\n",
       "      <th>Peer_B2W2</th>\n",
       "      <th>Peer_B2W3</th>\n",
       "      <th>Peer_B3W1</th>\n",
       "      <th>Peer_B3W2</th>\n",
       "      <th>Peer_B3W3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>4</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>5</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>4</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>106</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-999</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>-999</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>150</th>\n",
       "      <td>299</td>\n",
       "      <td>1</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>4</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>301</td>\n",
       "      <td>-999</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>302</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>304</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>305</td>\n",
       "      <td>2</td>\n",
       "      <td>-999</td>\n",
       "      <td>2</td>\n",
       "      <td>-999</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>-999</td>\n",
       "      <td>4</td>\n",
       "      <td>-999</td>\n",
       "      <td>3</td>\n",
       "      <td>-999</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>155 rows × 19 columns</p>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-d40f2e23-84e0-41b0-a4d5-f7f389eadb43')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-d40f2e23-84e0-41b0-a4d5-f7f389eadb43 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-d40f2e23-84e0-41b0-a4d5-f7f389eadb43');\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",
       "\n",
       "\n",
       "<div id=\"df-d2ac67d0-5e34-42f5-8d2f-5d659df60587\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-d2ac67d0-5e34-42f5-8d2f-5d659df60587')\"\n",
       "            title=\"Suggest charts.\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-d2ac67d0-5e34-42f5-8d2f-5d659df60587 button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 198
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "all_ids = pd.DataFrame({\"SID\": ids.PID.unique()})\n",
    "weekly_transposed = all_ids.merge(weekly_transposed, on=[\"SID\"], how=\"left\").fillna(-999)"
   ],
   "metadata": {
    "id": "-r-qZYHwoSkT"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "weekly_transposed.to_excel(\"weekly_transposed.xlsx\", index=False)"
   ],
   "metadata": {
    "id": "CAynSpFqscay"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Fix timeline.xlsx format"
   ],
   "metadata": {
    "id": "W6QG0mDk0RTm"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "timeline = pd.read_excel(path/\"timeline.xlsx\")"
   ],
   "metadata": {
    "id": "0Ya_xd2v0QDO"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def extract_b_for_sorting(row):\n",
    "  return int(row[\"bwd\"][1])\n",
    "def extract_d_for_sorting(row):\n",
    "  try:\n",
    "    return int(row[\"bwd\"][-2:])\n",
    "  except:\n",
    "    return int(row[\"bwd\"][-1])"
   ],
   "metadata": {
    "id": "vEjuE-sF0gOr"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "timeline[\"b_num\"] = timeline.apply(extract_b_for_sorting, axis=1)\n",
    "timeline[\"d_num\"] = timeline.apply(extract_d_for_sorting, axis=1)"
   ],
   "metadata": {
    "id": "Ijwq0iLn0hf5"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "timeline_sorted = timeline.sort_values(by=[\"PID\", \"b_num\", \"d_num\"])\n",
    "timeline_sorted = timeline_sorted.rename(columns={\"cannabis_binary\": \"mj_binary\", \"mj_binary\": \"either\"})\n",
    "timeline_sorted = timeline_sorted.drop(columns=[\"b_num\", \"d_num\"], axis=1)\n",
    "timeline_sorted"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "xTNgZFW732LZ",
    "outputId": "38609757-cbb3-4561-bf5a-846c86144645"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      PID      bwd  num_hours_high  mj_binary  ecig_binary  either\n",
       "0     101   B1W1D1               0          0            0       0\n",
       "1     101   B1W1D2               0          0            0       0\n",
       "2     101   B1W1D3               0          0            0       0\n",
       "3     101   B1W1D4               0          0            0       0\n",
       "4     101   B1W1D5               0          0            0       0\n",
       "...   ...      ...             ...        ...          ...     ...\n",
       "9760  305  B3W3D17            -999       -999         -999    -999\n",
       "9761  305  B3W3D18            -999       -999         -999    -999\n",
       "9762  305  B3W3D19            -999       -999         -999    -999\n",
       "9763  305  B3W3D20            -999       -999         -999    -999\n",
       "9764  305  B3W3D21            -999       -999         -999    -999\n",
       "\n",
       "[9765 rows x 6 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-0f031110-2b4f-4008-a196-bf5ac34af059\" 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>PID</th>\n",
       "      <th>bwd</th>\n",
       "      <th>num_hours_high</th>\n",
       "      <th>mj_binary</th>\n",
       "      <th>ecig_binary</th>\n",
       "      <th>either</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>B1W1D5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9760</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D17</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9761</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D18</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9762</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D19</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9763</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D20</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9764</th>\n",
       "      <td>305</td>\n",
       "      <td>B3W3D21</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9765 rows × 6 columns</p>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-0f031110-2b4f-4008-a196-bf5ac34af059')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-0f031110-2b4f-4008-a196-bf5ac34af059 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-0f031110-2b4f-4008-a196-bf5ac34af059');\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",
       "\n",
       "\n",
       "<div id=\"df-ac99225b-09e6-42fb-a5b8-93436bf78212\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-ac99225b-09e6-42fb-a5b8-93436bf78212')\"\n",
       "            title=\"Suggest charts.\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-ac99225b-09e6-42fb-a5b8-93436bf78212 button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 73
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "timeline_sorted.to_excel(\"TFLB_V1.xlsx\")"
   ],
   "metadata": {
    "id": "hDOrH_Ej33U5"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "id_col_name, bw_name = \"PID\", \"bwd\""
   ],
   "metadata": {
    "id": "YArYgZyNrCER"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "col_codes = {\n",
    "    \"mj_binary\": \"mj\",\n",
    "    \"num_hours_high\": \"hour\",\n",
    "    \"ecig_binary\": \"ecig\",\n",
    "    \"either\": \"either\"\n",
    "}"
   ],
   "metadata": {
    "id": "gL6MKfzArH8U"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def widen_timeline(df, id_col_name, bw_name, col_codes):\n",
    "  transpose_dict = {\"id\": df[id_col_name].unique().tolist()}\n",
    "\n",
    "  for code in col_codes:\n",
    "    for ID in df[id_col_name].unique():\n",
    "      for BW in df[bw_name].unique():\n",
    "\n",
    "        col_name = f\"{col_codes[code]}_{BW}\"\n",
    "        if col_name not in transpose_dict:\n",
    "          transpose_dict[col_name] = []\n",
    "\n",
    "        row = df.loc[(df[id_col_name] == ID) & (df[bw_name] == BW)]\n",
    "        transpose_dict[col_name].append(row.iloc[0][code])\n",
    "\n",
    "  return pd.DataFrame(transpose_dict)"
   ],
   "metadata": {
    "id": "LWZ0sBK2oDqL"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "timeline_wide = widen_timeline(timeline_sorted, id_col_name, bw_name, col_codes)"
   ],
   "metadata": {
    "id": "aBnL-NS_rg2C"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "timeline_wide.to_excel(\"TLFB_V1_WIDE.xlsx\")"
   ],
   "metadata": {
    "id": "llPHN5GHtJhN"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Timeline Parcels"
   ],
   "metadata": {
    "id": "F2PpbmaSeO2U"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "t1 = pd.read_excel(path/\"TLFB_V1.xlsx\")"
   ],
   "metadata": {
    "id": "0QHare-ieN7j"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "parcels = {\n",
    "    \"B1P1\": [\"B1D1\", \"B1D6\"],\n",
    "    \"B1P2\": [\"B1D2\", \"B1D5\"],\n",
    "    \"B1P3\": [\"B1D3\", \"B1D7\", \"B1D4\"],\n",
    "    \"B1P4\": [\"B1D8\", \"B1D12\"],\n",
    "    \"B1P5\": [\"B1D9\", \"B1D13\", \"B1D14\"],\n",
    "    \"B1P6\": [\"B1D10\", \"B1D11\"],\n",
    "    \"B1P7\": [\"B1D15\", \"B1D20\"],\n",
    "    \"B1P8\": [\"B1D16\", \"B1D19\"],\n",
    "    \"B1P9\": [\"B1D17\", \"B1D18\", \"B1D21\"]\n",
    "}\n",
    "\n",
    "codes = {\n",
    "    \"mj_binary\": \"M\",\n",
    "    \"num_hours_high\": \"H\",\n",
    "    \"ecig_binary\": \"V\",\n",
    "    \"either\": \"E\"\n",
    "}"
   ],
   "metadata": {
    "id": "_FIWv0EqeewH"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "t1"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "tumVd0jsfav6",
    "outputId": "2d7cf855-6e8d-4a23-ee0e-fa5ba85863e3"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      PID    bwd  num_hours_high  mj_binary  ecig_binary  either\n",
       "0     101   B1D1               0          0            0       0\n",
       "1     101   B1D2               0          0            0       0\n",
       "2     101   B1D3               0          0            0       0\n",
       "3     101   B1D4               0          0            0       0\n",
       "4     101   B1D5               0          0            0       0\n",
       "...   ...    ...             ...        ...          ...     ...\n",
       "9760  305  B3D17            -999       -999         -999    -999\n",
       "9761  305  B3D18            -999       -999         -999    -999\n",
       "9762  305  B3D19            -999       -999         -999    -999\n",
       "9763  305  B3D20            -999       -999         -999    -999\n",
       "9764  305  B3D21            -999       -999         -999    -999\n",
       "\n",
       "[9765 rows x 6 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-649e903d-8b4e-4370-8414-5c49ffec701b\" 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>PID</th>\n",
       "      <th>bwd</th>\n",
       "      <th>num_hours_high</th>\n",
       "      <th>mj_binary</th>\n",
       "      <th>ecig_binary</th>\n",
       "      <th>either</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9760</th>\n",
       "      <td>305</td>\n",
       "      <td>B3D17</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9761</th>\n",
       "      <td>305</td>\n",
       "      <td>B3D18</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9762</th>\n",
       "      <td>305</td>\n",
       "      <td>B3D19</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9763</th>\n",
       "      <td>305</td>\n",
       "      <td>B3D20</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9764</th>\n",
       "      <td>305</td>\n",
       "      <td>B3D21</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9765 rows × 6 columns</p>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-649e903d-8b4e-4370-8414-5c49ffec701b')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-649e903d-8b4e-4370-8414-5c49ffec701b 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-649e903d-8b4e-4370-8414-5c49ffec701b');\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",
       "\n",
       "\n",
       "<div id=\"df-2774be84-4b11-439d-9013-8361415f7354\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-2774be84-4b11-439d-9013-8361415f7354')\"\n",
       "            title=\"Suggest charts.\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-2774be84-4b11-439d-9013-8361415f7354 button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 32
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "parcel_calculations = {\"PID\": t1.PID.unique().tolist()}\n",
    "\n",
    "def calculate_parcels(PID, code):\n",
    "  for p in parcels:\n",
    "    days = parcels[p]\n",
    "    vals = []\n",
    "    for d in days:\n",
    "      val = t1.loc[(t1.PID == PID) & (t1.bwd == d)].iloc[0][code]\n",
    "      vals.append(val)\n",
    "\n",
    "    calculated_parcel = sum(vals)/len(vals)\n",
    "    col_name = f\"U_{p}_{codes[code]}\"\n",
    "    if col_name not in parcel_calculations:\n",
    "      parcel_calculations[col_name] = []\n",
    "\n",
    "    parcel_calculations[col_name].append(calculated_parcel)"
   ],
   "metadata": {
    "id": "Kq6pTFbOftXv"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "for PID in parcel_calculations[\"PID\"]:\n",
    "  for code in codes:\n",
    "    calculate_parcels(PID, code)"
   ],
   "metadata": {
    "id": "U38S3zaviruI"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "tlfb_v2 = pd.DataFrame(parcel_calculations)"
   ],
   "metadata": {
    "id": "6cBZw7MDi0YW"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "tlfb_v2.to_excel(\"TFLB_V2.xlsx\", index=False)"
   ],
   "metadata": {
    "id": "Zs4za-7rjEDt"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "t1.loc[t1.PID == 134].head(21)"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 708
    },
    "id": "3GKEeskgjtQH",
    "outputId": "95115437-3507-4e82-bed5-665f7a4e653f"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      PID    bwd  num_hours_high  mj_binary  ecig_binary  either\n",
       "1575  134   B1D1               0          0            1       1\n",
       "1576  134   B1D2               5          1            1       1\n",
       "1577  134   B1D3               5          1            1       1\n",
       "1578  134   B1D4               6          1            1       1\n",
       "1579  134   B1D5               6          1            1       1\n",
       "1580  134   B1D6               6          1            1       1\n",
       "1581  134   B1D7               4          1            1       1\n",
       "1582  134   B1D8               0          0            0       0\n",
       "1583  134   B1D9               0          0            0       0\n",
       "1584  134  B1D10               0          0            0       0\n",
       "1585  134  B1D11               4          1            1       1\n",
       "1586  134  B1D12            -999          1            1       1\n",
       "1587  134  B1D13               2          1            1       1\n",
       "1588  134  B1D14               4          1            1       1\n",
       "1589  134  B1D15               5          1            1       1\n",
       "1590  134  B1D16               0          0            0       0\n",
       "1591  134  B1D17               0          0            0       0\n",
       "1592  134  B1D18            -999          1            1       1\n",
       "1593  134  B1D19               3          1            1       1\n",
       "1594  134  B1D20            -999          1            1       1\n",
       "1595  134  B1D21               4          1            1       1"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-809d1857-df89-464b-b8f7-cf36069afda8\" 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>PID</th>\n",
       "      <th>bwd</th>\n",
       "      <th>num_hours_high</th>\n",
       "      <th>mj_binary</th>\n",
       "      <th>ecig_binary</th>\n",
       "      <th>either</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1575</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1576</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1577</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D3</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1578</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D4</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1579</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D5</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1580</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D6</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1581</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D7</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1582</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D8</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1583</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1584</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D10</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1585</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D11</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1586</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D12</td>\n",
       "      <td>-999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1587</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D13</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1588</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D14</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1589</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D15</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1590</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1591</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D17</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1592</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D18</td>\n",
       "      <td>-999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1593</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D19</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1594</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D20</td>\n",
       "      <td>-999</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1595</th>\n",
       "      <td>134</td>\n",
       "      <td>B1D21</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-809d1857-df89-464b-b8f7-cf36069afda8')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-809d1857-df89-464b-b8f7-cf36069afda8 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-809d1857-df89-464b-b8f7-cf36069afda8');\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",
       "\n",
       "\n",
       "<div id=\"df-4e4d6d34-d373-4533-aba7-df5c4a0fefd9\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-4e4d6d34-d373-4533-aba7-df5c4a0fefd9')\"\n",
       "            title=\"Suggest charts.\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-4e4d6d34-d373-4533-aba7-df5c4a0fefd9 button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 57
    }
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "# EMA_V2"
   ],
   "metadata": {
    "id": "H4Wmqm2Ym65R"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "ema1 = pd.read_excel(path/\"EMA_V1_Sorted.xlsx\")"
   ],
   "metadata": {
    "id": "ipDkuglolHJL"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def widen_ema(df, id_col_name, bw_name, col_codes):\n",
    "  transpose_dict = {\"id\": df[id_col_name].unique().tolist()}\n",
    "\n",
    "  for code in col_codes:\n",
    "    for ID in transpose_dict[\"id\"]:\n",
    "      for BW in df[bw_name].unique():\n",
    "        for letter in [\"A\", \"B\", \"C\"]:\n",
    "          col_name = f\"{BW}_{letter}_{col_codes[code]}\"\n",
    "          if col_name not in transpose_dict:\n",
    "            transpose_dict[col_name] = []\n",
    "\n",
    "          row = df.loc[(df[id_col_name] == ID) & (df[bw_name] == BW) & (df[\"Survey\"] == letter)]\n",
    "          transpose_dict[col_name].append(row.iloc[0][code])\n",
    "\n",
    "  return pd.DataFrame(transpose_dict)"
   ],
   "metadata": {
    "id": "U7nSA5bTtdtN"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "col_codes = {\n",
    "    \"MJ\": \"MJ\",\n",
    "    \"Vaping\": \"Vap\",\n",
    "    \"either\": \"Either\"\n",
    "}\n",
    "\n",
    "ema_wide = widen_ema(ema1, \"SID\", \"day\", col_codes)"
   ],
   "metadata": {
    "id": "F8AnxF56tuIg"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "ema_wide"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 444
    },
    "id": "m1sl_BgCGsiL",
    "outputId": "abd7f417-bf5e-4644-cb44-2303f9f8df7a"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      id  B1D1_A_MJ  B1D1_B_MJ  B1D1_C_MJ  B1D2_A_MJ  B1D2_B_MJ  B1D2_C_MJ  \\\n",
       "0    101          0          0       -999       -999          0          0   \n",
       "1    102          0          0       -999          0       -999          0   \n",
       "2    103       -999       -999       -999          0       -999       -999   \n",
       "3    104          0          0          0          0          0          0   \n",
       "4    106          0          0          0          0          0       -999   \n",
       "..   ...        ...        ...        ...        ...        ...        ...   \n",
       "152  299          1          1          0          0          0       -999   \n",
       "153  301          1       -999          0          0       -999       -999   \n",
       "154  302          0          0          0          0       -999          0   \n",
       "155  304          0          0          0          0       -999       -999   \n",
       "156  305          1          1       -999          1       -999       -999   \n",
       "\n",
       "     B1D3_A_MJ  B1D3_B_MJ  B1D3_C_MJ  ...  B3D18_C_Either  B3D19_A_Either  \\\n",
       "0         -999          0       -999  ...            -999            -999   \n",
       "1            0          0          1  ...            -999            -999   \n",
       "2            0          0          0  ...            -999               0   \n",
       "3            0          0          1  ...               0               0   \n",
       "4            0          0          0  ...            -999               0   \n",
       "..         ...        ...        ...  ...             ...             ...   \n",
       "152          0       -999          0  ...            -999            -999   \n",
       "153       -999          0          0  ...            -999            -999   \n",
       "154          0          0       -999  ...            -999               0   \n",
       "155          0       -999          0  ...               0               0   \n",
       "156          1       -999       -999  ...            -999               0   \n",
       "\n",
       "     B3D19_B_Either  B3D19_C_Either  B3D20_A_Either  B3D20_B_Either  \\\n",
       "0              -999            -999            -999            -999   \n",
       "1              -999            -999            -999            -999   \n",
       "2              -999            -999               0            -999   \n",
       "3                 0               0               0               0   \n",
       "4              -999            -999               0            -999   \n",
       "..              ...             ...             ...             ...   \n",
       "152            -999            -999            -999            -999   \n",
       "153            -999            -999               0            -999   \n",
       "154            -999            -999               0               0   \n",
       "155               0            -999               0               0   \n",
       "156               1            -999               0               0   \n",
       "\n",
       "     B3D20_C_Either  B3D21_A_Either  B3D21_B_Either  B3D21_C_Either  \n",
       "0              -999            -999            -999            -999  \n",
       "1              -999            -999            -999            -999  \n",
       "2              -999               0            -999            -999  \n",
       "3                 0               0               0            -999  \n",
       "4              -999               0            -999            -999  \n",
       "..              ...             ...             ...             ...  \n",
       "152            -999            -999            -999            -999  \n",
       "153            -999            -999               0            -999  \n",
       "154            -999               0               0               0  \n",
       "155               0               0               0            -999  \n",
       "156            -999            -999            -999               0  \n",
       "\n",
       "[157 rows x 568 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-8f01c7f1-0f68-4ba7-aede-768f3ac42633\" 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>id</th>\n",
       "      <th>B1D1_A_MJ</th>\n",
       "      <th>B1D1_B_MJ</th>\n",
       "      <th>B1D1_C_MJ</th>\n",
       "      <th>B1D2_A_MJ</th>\n",
       "      <th>B1D2_B_MJ</th>\n",
       "      <th>B1D2_C_MJ</th>\n",
       "      <th>B1D3_A_MJ</th>\n",
       "      <th>B1D3_B_MJ</th>\n",
       "      <th>B1D3_C_MJ</th>\n",
       "      <th>...</th>\n",
       "      <th>B3D18_C_Either</th>\n",
       "      <th>B3D19_A_Either</th>\n",
       "      <th>B3D19_B_Either</th>\n",
       "      <th>B3D19_C_Either</th>\n",
       "      <th>B3D20_A_Either</th>\n",
       "      <th>B3D20_B_Either</th>\n",
       "      <th>B3D20_C_Either</th>\n",
       "      <th>B3D21_A_Either</th>\n",
       "      <th>B3D21_B_Either</th>\n",
       "      <th>B3D21_C_Either</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>...</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>106</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>299</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>301</td>\n",
       "      <td>1</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>302</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>...</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>304</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>305</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>-999</td>\n",
       "      <td>1</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>1</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>...</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>157 rows × 568 columns</p>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-8f01c7f1-0f68-4ba7-aede-768f3ac42633')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-8f01c7f1-0f68-4ba7-aede-768f3ac42633 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-8f01c7f1-0f68-4ba7-aede-768f3ac42633');\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",
       "\n",
       "\n",
       "<div id=\"df-70767593-70c3-4510-ac25-bf02891a2595\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-70767593-70c3-4510-ac25-bf02891a2595')\"\n",
       "            title=\"Suggest charts.\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-70767593-70c3-4510-ac25-bf02891a2595 button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 34
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "ema_wide.to_excel(\"ema_v1_wide_all.xlsx\", index=False)"
   ],
   "metadata": {
    "id": "9Zkgg-TNtJyU"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "parcel_calculations = {\"PID\": t1.PID.unique().tolist()}\n",
    "\n",
    "codes = {\n",
    "    \"mj_binary\": \"M\",\n",
    "    \"num_hours_high\": \"H\",\n",
    "    \"ecig_binary\": \"V\",\n",
    "    \"either\": \"E\"\n",
    "}\n",
    "\n",
    "def calculate_parcels(PID, code):\n",
    "  for p in parcels:\n",
    "    days = parcels[p]\n",
    "    vals = []\n",
    "    for d in days:\n",
    "      val = t1.loc[(t1.PID == PID) & (t1.bwd == d)].iloc[0][code]\n",
    "      vals.append(val)\n",
    "\n",
    "    calculated_parcel = sum(vals)/len(vals)\n",
    "    col_name = f\"U_{p}_{codes[code]}\"\n",
    "    if col_name not in parcel_calculations:\n",
    "      parcel_calculations[col_name] = []\n",
    "\n",
    "    parcel_calculations[col_name].append(calculated_parcel)"
   ],
   "metadata": {
    "id": "64TzYjqanUMY"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "# EMA Condensed"
   ],
   "metadata": {
    "id": "MuM49DjPHZD3"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "ema_sorted = pd.read_excel(path/\"EMA_V1_Sorted.xlsx\")"
   ],
   "metadata": {
    "id": "ZtiwQlehHZ0Q"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "ema_sorted"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "dgmMAC3zHrM6",
    "outputId": "3bf4ed67-6203-4fa3-938e-cc564b9c4f65"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "       SID Survey    day   MJ  Vaping  either\n",
       "0      101      A   B1D1    0       0       0\n",
       "1      101      B   B1D1    0       0       0\n",
       "2      101      C   B1D1 -999    -999    -999\n",
       "3      101      A   B1D2 -999    -999    -999\n",
       "4      101      B   B1D2    0       0       0\n",
       "...    ...    ...    ...  ...     ...     ...\n",
       "29668  305      B  B3D20    0       0       0\n",
       "29669  305      C  B3D20 -999    -999    -999\n",
       "29670  305      A  B3D21 -999    -999    -999\n",
       "29671  305      B  B3D21 -999    -999    -999\n",
       "29672  305      C  B3D21    0       0       0\n",
       "\n",
       "[29673 rows x 6 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-11179781-dccc-4df5-b840-1df7a28b7f34\" 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>SID</th>\n",
       "      <th>Survey</th>\n",
       "      <th>day</th>\n",
       "      <th>MJ</th>\n",
       "      <th>Vaping</th>\n",
       "      <th>either</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>A</td>\n",
       "      <td>B1D1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>B</td>\n",
       "      <td>B1D1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>C</td>\n",
       "      <td>B1D1</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>A</td>\n",
       "      <td>B1D2</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>B</td>\n",
       "      <td>B1D2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29668</th>\n",
       "      <td>305</td>\n",
       "      <td>B</td>\n",
       "      <td>B3D20</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29669</th>\n",
       "      <td>305</td>\n",
       "      <td>C</td>\n",
       "      <td>B3D20</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29670</th>\n",
       "      <td>305</td>\n",
       "      <td>A</td>\n",
       "      <td>B3D21</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29671</th>\n",
       "      <td>305</td>\n",
       "      <td>B</td>\n",
       "      <td>B3D21</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29672</th>\n",
       "      <td>305</td>\n",
       "      <td>C</td>\n",
       "      <td>B3D21</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>29673 rows × 6 columns</p>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-11179781-dccc-4df5-b840-1df7a28b7f34')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-11179781-dccc-4df5-b840-1df7a28b7f34 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-11179781-dccc-4df5-b840-1df7a28b7f34');\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",
       "\n",
       "\n",
       "<div id=\"df-e572f1ed-9c48-4b5c-b47a-d3373b962c3f\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-e572f1ed-9c48-4b5c-b47a-d3373b962c3f')\"\n",
       "            title=\"Suggest charts.\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-e572f1ed-9c48-4b5c-b47a-d3373b962c3f button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 9
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "consolidated = ema_sorted.groupby([\"SID\", \"day\"]).max().drop(columns=\"Survey\")"
   ],
   "metadata": {
    "id": "K3UhxgxzHvG6"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "consolidated = consolidated.reset_index()"
   ],
   "metadata": {
    "id": "h3Nwvg0eIek0"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def extract_b_for_sorting(row):\n",
    "  return int(row[\"day\"][1])\n",
    "def extract_d_for_sorting(row):\n",
    "  try:\n",
    "    return int(row[\"day\"][-2:])\n",
    "  except:\n",
    "    return int(row[\"day\"][-1])\n",
    "\n",
    "consolidated[\"B\"] = consolidated.apply(extract_b_for_sorting, axis=1)\n",
    "consolidated[\"D\"] = consolidated.apply(extract_d_for_sorting, axis=1)"
   ],
   "metadata": {
    "id": "g9Q3r8iTN-Y5"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "consolidated = consolidated.sort_values(by=[\"SID\", \"B\", \"D\"]).drop(columns=[\"B\", \"D\"])"
   ],
   "metadata": {
    "id": "rbZe3-7XPU0K"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "consolidated.to_excel(\"ema_v1_consolidated.xlsx\", index=False)"
   ],
   "metadata": {
    "id": "QHtSKDjTJvI7"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "consolidated = pd.read_excel(path/\"ema_v1_consolidated.xlsx\")"
   ],
   "metadata": {
    "id": "YTcJkmlJKY7m"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "consolidated.loc[consolidated.SID == 101]"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 424
    },
    "id": "nFqLNE5rbHZ8",
    "outputId": "a8bdc66a-df2a-48fc-be46-dc8c96784a13"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "    SID    day   MJ  Vaping  either\n",
       "0   101   B1D1    0       0       0\n",
       "1   101   B1D2    0       1       1\n",
       "2   101   B1D3    0       0       0\n",
       "3   101   B1D4    0       0       0\n",
       "4   101   B1D5    0       0       0\n",
       "..  ...    ...  ...     ...     ...\n",
       "58  101  B3D17 -999    -999    -999\n",
       "59  101  B3D18 -999    -999    -999\n",
       "60  101  B3D19 -999    -999    -999\n",
       "61  101  B3D20 -999    -999    -999\n",
       "62  101  B3D21 -999    -999    -999\n",
       "\n",
       "[63 rows x 5 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-90024680-988d-4006-9108-5d2b2d182f3d\" 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>SID</th>\n",
       "      <th>day</th>\n",
       "      <th>MJ</th>\n",
       "      <th>Vaping</th>\n",
       "      <th>either</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>B1D5</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>101</td>\n",
       "      <td>B3D17</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>101</td>\n",
       "      <td>B3D18</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>101</td>\n",
       "      <td>B3D19</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>101</td>\n",
       "      <td>B3D20</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>101</td>\n",
       "      <td>B3D21</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>63 rows × 5 columns</p>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-90024680-988d-4006-9108-5d2b2d182f3d')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-90024680-988d-4006-9108-5d2b2d182f3d 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-90024680-988d-4006-9108-5d2b2d182f3d');\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",
       "\n",
       "\n",
       "<div id=\"df-0b59100d-d019-40f8-8e79-deaa4c90aca5\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-0b59100d-d019-40f8-8e79-deaa4c90aca5')\"\n",
       "            title=\"Suggest charts.\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-0b59100d-d019-40f8-8e79-deaa4c90aca5 button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 20
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "def widen_ema(df, id_col_name, bw_name, col_codes):\n",
    "  transpose_dict = {\"id\": df[id_col_name].unique().tolist()}\n",
    "\n",
    "  for code in col_codes:\n",
    "    for ID in transpose_dict[\"id\"]:\n",
    "      for BW in df[bw_name].unique():\n",
    "        col_name = f\"{BW}_{col_codes[code]}\"\n",
    "        if col_name not in transpose_dict:\n",
    "          transpose_dict[col_name] = []\n",
    "\n",
    "        row = df.loc[(df[id_col_name] == ID) & (df[bw_name] == BW)]\n",
    "        transpose_dict[col_name].append(row.iloc[0][code])\n",
    "\n",
    "  return pd.DataFrame(transpose_dict)"
   ],
   "metadata": {
    "id": "kDT7-O8tbIzb"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "col_codes = {\n",
    "    \"MJ\": \"MJ\",\n",
    "    \"Vaping\": \"Vap\",\n",
    "    \"either\": \"Either\"\n",
    "}\n",
    "\n",
    "ema_wide = widen_ema(consolidated, \"SID\", \"day\", col_codes)"
   ],
   "metadata": {
    "id": "bkKbJn1DeBOx"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "ema_wide.to_excel(\"ema_condensed_wide.xlsx\", index=False)"
   ],
   "metadata": {
    "id": "zSyHP4kpeFMC"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "# New Task"
   ],
   "metadata": {
    "id": "mljp4h1vS9ib"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "from os import listdir\n",
    "from os.path import isfile, join\n",
    "def get_excel_file():\n",
    "  mypath = \"/content\"\n",
    "  files = [f for f in listdir(mypath) if isfile(join(mypath, f))]\n",
    "  for f in files:\n",
    "    if f.find('.xlsx') != -1:\n",
    "      print(\"Found excel file:\", f)\n",
    "      return f"
   ],
   "metadata": {
    "id": "roluX4rDS6jJ"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "file = 'EC EMA_11.10.23.xlsx'"
   ],
   "metadata": {
    "id": "w6DZOibHwnQV"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "data = pd.read_excel(file, \"Data\")\n",
    "data = pd.concat([data, pd.read_excel('SID 305.xlsx')])"
   ],
   "metadata": {
    "id": "UQQb-iJpnuz4"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "timeline = pd.read_excel(file, \"Timeline\")\n",
    "timeline = timeline.rename(columns={'date': 'Survey_Submitted_Date'})"
   ],
   "metadata": {
    "id": "6m6wEWHkq4pp"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "all_combos = ids.merge(data[[\"PID\", \"cohort\"]], on=\"PID\", how=\"left\")\n",
    "all_combos = all_combos.groupby(\"PID\").max().reset_index().fillna(2)\n",
    "all_combos_c1 = all_combos.loc[all_combos.cohort == 1]\n",
    "all_combos_c2 = all_combos.loc[all_combos.cohort == 2]"
   ],
   "metadata": {
    "id": "UVetXfFSdXtf"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "timeline_c1 = timeline.loc[timeline.cohort_burst.str[1] == '1']\n",
    "timeline_c2 = timeline.loc[timeline.cohort_burst.str[1] == '2']"
   ],
   "metadata": {
    "id": "VM7QPtofeyHN"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "all_combos_c1 = all_combos_c1[[\"PID\"]].merge(timeline_c1[['Survey_Submitted_Date', 'burst_day']], how='cross')\n",
    "all_combos_c2 = all_combos_c2[[\"PID\"]].merge(timeline_c2[['Survey_Submitted_Date', 'burst_day']], how='cross')"
   ],
   "metadata": {
    "id": "pct3TXdtZt6F"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "all_combos = pd.concat([all_combos_c1, all_combos_c2])"
   ],
   "metadata": {
    "id": "cZTRGXbeZ6bn"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "data = all_combos.merge(data, how='left')\n",
    "data.fillna(-999, inplace=True)"
   ],
   "metadata": {
    "id": "zoa5zzdzt2mm"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def get_alcohol_bin(df): return 1 if df[\"Alc volume\"] > 0 else df[\"Alc volume\"]\n",
    "def get_alcohol_volume(df): return 2 if df[\"Alc volume\"] > 1 else df[\"Alc volume\"]"
   ],
   "metadata": {
    "id": "8-29rdgD_bEH"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "groupings = data.groupby([\"PID\", \"burst_day\"]).sum(numeric_only=True).reset_index()\n",
    "groupings[\"AlcBin\"] = groupings.apply(get_alcohol_bin, axis=1)\n",
    "groupings[\"AlcV\"] = groupings.apply(get_alcohol_volume, axis=1)"
   ],
   "metadata": {
    "id": "q2e3qE9V_aN2"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def extract_b_for_sorting(row):\n",
    "  return int(row[\"burst_day\"][1])\n",
    "def extract_d_for_sorting(row):\n",
    "  try:\n",
    "    return int(row[\"burst_day\"][-2:])\n",
    "  except:\n",
    "    return int(row[\"burst_day\"][-1])"
   ],
   "metadata": {
    "id": "-BstAuBiX-Jz"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "long_df = groupings[[\"PID\", \"AlcBin\", \"AlcV\", \"burst_day\"]]\n",
    "long_df[\"B\"] = long_df.apply(extract_b_for_sorting, axis=1)\n",
    "long_df[\"D\"] = long_df.apply(extract_d_for_sorting, axis=1)"
   ],
   "metadata": {
    "id": "FpzvZ0kkJZ_Y",
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "outputId": "415e5caf-9de3-4477-86b5-e67453214f78"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "<ipython-input-103-deb47c3d6857>:2: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  long_df[\"B\"] = long_df.apply(extract_b_for_sorting, axis=1)\n",
      "<ipython-input-103-deb47c3d6857>:3: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  long_df[\"D\"] = long_df.apply(extract_d_for_sorting, axis=1)\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "long_df = long_df.sort_values(by=[\"PID\", \"B\", \"D\"])[[\"PID\", \"AlcBin\", \"AlcV\", \"burst_day\"]]"
   ],
   "metadata": {
    "id": "C6Y_vB5vjab6"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "long_df.to_excel(\"EC_EMA_LONG.xlsx\", index=False)"
   ],
   "metadata": {
    "id": "PMeSptniMXZZ"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def reverse_and_join(col_name):\n",
    "  reversed = [col_name[1], col_name[0]]\n",
    "  return '_'.join(reversed)"
   ],
   "metadata": {
    "id": "i-w2Rqm2nmNi"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "wide_df = pd.pivot(long_df, index='PID', columns='burst_day', values=['AlcBin', 'AlcV']).reset_index()\n",
    "wide_df.columns = wide_df.columns.map(reverse_and_join).str.strip('_')"
   ],
   "metadata": {
    "id": "oZz7FISzlsT9"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "wide_df"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 444
    },
    "id": "HMLI9D2Kr-tN",
    "outputId": "cd8b6f3b-8fcf-4962-fc92-fee1b3f7f6cd"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "     PID  B1D1_AlcBin  B1D10_AlcBin  B1D11_AlcBin  B1D12_AlcBin  B1D13_AlcBin  \\\n",
       "0    101          1.0           0.0           0.0           0.0        -999.0   \n",
       "1    102          0.0           1.0           0.0           0.0           0.0   \n",
       "2    103          0.0           0.0           1.0           0.0           0.0   \n",
       "3    104          1.0           1.0           0.0           1.0           1.0   \n",
       "4    106          1.0           1.0           0.0           0.0           0.0   \n",
       "..   ...          ...           ...           ...           ...           ...   \n",
       "152  299          1.0           1.0           0.0           0.0        -999.0   \n",
       "153  301          1.0           0.0           0.0           1.0           0.0   \n",
       "154  302          0.0           1.0           0.0           1.0           0.0   \n",
       "155  304          1.0           1.0           1.0           0.0           1.0   \n",
       "156  305          1.0           1.0           0.0           0.0        -999.0   \n",
       "\n",
       "     B1D14_AlcBin  B1D15_AlcBin  B1D16_AlcBin  B1D17_AlcBin  ...  B3D2_AlcV  \\\n",
       "0          -999.0        -999.0        -999.0        -999.0  ...     -999.0   \n",
       "1             0.0           0.0           0.0           0.0  ...     -999.0   \n",
       "2             0.0           0.0           0.0           0.0  ...        1.0   \n",
       "3             1.0           1.0           1.0           1.0  ...        0.0   \n",
       "4             0.0           0.0           0.0           0.0  ...        0.0   \n",
       "..            ...           ...           ...           ...  ...        ...   \n",
       "152        -999.0        -999.0        -999.0        -999.0  ...        0.0   \n",
       "153           0.0           0.0           0.0           0.0  ...        0.0   \n",
       "154           1.0           1.0           1.0           1.0  ...        2.0   \n",
       "155           1.0           1.0           1.0           1.0  ...        1.0   \n",
       "156        -999.0        -999.0        -999.0        -999.0  ...        0.0   \n",
       "\n",
       "     B3D20_AlcV  B3D21_AlcV  B3D3_AlcV  B3D4_AlcV  B3D5_AlcV  B3D6_AlcV  \\\n",
       "0        -999.0      -999.0     -999.0     -999.0     -999.0     -999.0   \n",
       "1        -999.0      -999.0     -999.0     -999.0     -999.0     -999.0   \n",
       "2           0.0         0.0        0.0        0.0        0.0        0.0   \n",
       "3           0.0         0.0        0.0        0.0        0.0        0.0   \n",
       "4           0.0         0.0        0.0        0.0        0.0        0.0   \n",
       "..          ...         ...        ...        ...        ...        ...   \n",
       "152      -999.0      -999.0        0.0        1.0     -999.0        0.0   \n",
       "153         0.0         0.0        0.0        0.0        0.0        0.0   \n",
       "154         0.0         0.0        2.0        2.0        2.0        2.0   \n",
       "155         1.0         0.0        1.0        0.0        0.0        0.0   \n",
       "156         0.0         0.0        1.0        0.0        0.0        0.0   \n",
       "\n",
       "     B3D7_AlcV  B3D8_AlcV  B3D9_AlcV  \n",
       "0       -999.0     -999.0     -999.0  \n",
       "1       -999.0     -999.0     -999.0  \n",
       "2          0.0        0.0        0.0  \n",
       "3          0.0        0.0        0.0  \n",
       "4          0.0        0.0        0.0  \n",
       "..         ...        ...        ...  \n",
       "152     -999.0     -999.0     -999.0  \n",
       "153        0.0        0.0        0.0  \n",
       "154        0.0        0.0        0.0  \n",
       "155        1.0        0.0        1.0  \n",
       "156        0.0        0.0        0.0  \n",
       "\n",
       "[157 rows x 127 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-ecdd293b-5402-45b1-874f-2eaf948b3594\" 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>PID</th>\n",
       "      <th>B1D1_AlcBin</th>\n",
       "      <th>B1D10_AlcBin</th>\n",
       "      <th>B1D11_AlcBin</th>\n",
       "      <th>B1D12_AlcBin</th>\n",
       "      <th>B1D13_AlcBin</th>\n",
       "      <th>B1D14_AlcBin</th>\n",
       "      <th>B1D15_AlcBin</th>\n",
       "      <th>B1D16_AlcBin</th>\n",
       "      <th>B1D17_AlcBin</th>\n",
       "      <th>...</th>\n",
       "      <th>B3D2_AlcV</th>\n",
       "      <th>B3D20_AlcV</th>\n",
       "      <th>B3D21_AlcV</th>\n",
       "      <th>B3D3_AlcV</th>\n",
       "      <th>B3D4_AlcV</th>\n",
       "      <th>B3D5_AlcV</th>\n",
       "      <th>B3D6_AlcV</th>\n",
       "      <th>B3D7_AlcV</th>\n",
       "      <th>B3D8_AlcV</th>\n",
       "      <th>B3D9_AlcV</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>102</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>103</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>104</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>106</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>152</th>\n",
       "      <td>299</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>301</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154</th>\n",
       "      <td>302</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>155</th>\n",
       "      <td>304</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>156</th>\n",
       "      <td>305</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>-999.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>157 rows × 127 columns</p>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-ecdd293b-5402-45b1-874f-2eaf948b3594')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-ecdd293b-5402-45b1-874f-2eaf948b3594 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-ecdd293b-5402-45b1-874f-2eaf948b3594');\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",
       "\n",
       "\n",
       "<div id=\"df-308606b0-503a-48b1-aa00-2337a5104dab\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-308606b0-503a-48b1-aa00-2337a5104dab')\"\n",
       "            title=\"Suggest charts\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-308606b0-503a-48b1-aa00-2337a5104dab button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 108
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "burst_days_sorted = sorted(long_df.burst_day.unique().tolist(), key=lambda x: x[:-1])\n",
    "burst_days_alcbin = [s + \"_AlcBin\" for s in burst_days_sorted]\n",
    "burst_days_alcv = [s + \"_AlcV\" for s in burst_days_sorted]\n",
    "burst_days_sorted = burst_days_alcbin + burst_days_alcv"
   ],
   "metadata": {
    "id": "dbvCEsISq6p2"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "wide_df = wide_df[[\"PID\"] + burst_days_sorted]"
   ],
   "metadata": {
    "id": "fVoYjkEHohJ4"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "wide_df.to_excel(\"EC_EMA_WIDE.xlsx\", index=False)"
   ],
   "metadata": {
    "id": "YM36CfZQQaFp"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [],
   "metadata": {
    "id": "fmH8CUjARsCT"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "markdown",
   "source": [
    "# Task on December 1st"
   ],
   "metadata": {
    "id": "ZWkPMKgRSS0L"
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "would it be possible to convert this long dataset to a less long dataset, with a row for every week rather than day? and then any variable that i have daily values for we make a sum variable for that week? so that would be mm, mv,  me, um, and umh? i highlighted the variables in the file that will need to be recalculated, all the others will just be condensed"
   ],
   "metadata": {
    "id": "iBhxiMt-Su2R"
   }
  },
  {
   "cell_type": "code",
   "source": [
    "import numpy as np\n",
    "mlm = pd.read_csv(\"MLM 11.26.csv\")"
   ],
   "metadata": {
    "id": "PboLyoNNSZZB"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "mlm.replace(r'^\\s*$', np.nan, regex=True, inplace=True)\n",
    "mlm.fillna('-999', inplace=True)\n",
    "mlm.replace({'-999': '0'}, inplace=True)\n",
    "mlm.drop(mlm.tail(1).index, inplace=True)"
   ],
   "metadata": {
    "id": "yLAgQjeeXS1-"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "def calculate_week(df):\n",
    "  burst = df[\"burst\"]\n",
    "  week = int(df[\"day\"]) // 8 + 1\n",
    "  return f\"B{burst}W{week}\""
   ],
   "metadata": {
    "id": "2j_TEJ5cS1j4"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "mlm[\"week\"] = mlm.apply(calculate_week, axis=1)"
   ],
   "metadata": {
    "id": "pQqtRax4Toer"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "columns = mlm.columns.to_list()\n",
    "variables_to_condense = columns[:2] + columns[5:-7] + columns[-2:]\n",
    "variables_for_sum = columns[-7:-2]"
   ],
   "metadata": {
    "id": "B47DXnbGYL_F"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "for var in ([\"SID\"] + variables_for_sum):\n",
    "  mlm[var] = pd.to_numeric(mlm[var])"
   ],
   "metadata": {
    "id": "b_Pq6kc6WxDv"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "tallied = mlm.groupby([\"SID\", \"week\"]).sum().reset_index()"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "id": "RJtxcqJ1UZ7p",
    "outputId": "35ae4513-31be-4f34-fe7e-3ad743124119"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "stream",
     "name": "stderr",
     "text": [
      "<ipython-input-358-fc9c3ca26c80>:1: FutureWarning: The default value of numeric_only in DataFrameGroupBy.sum is deprecated. In a future version, numeric_only will default to False. Either specify numeric_only or select only columns which should be valid for the function.\n",
      "  tallied = mlm.groupby([\"SID\", \"week\"]).sum().reset_index()\n"
     ]
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "condensed = mlm.groupby([\"SID\", \"week\"]).agg(pd.Series.mode).reset_index()[variables_to_condense]"
   ],
   "metadata": {
    "id": "6K5D09N9U-tn"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "final = tallied.merge(condensed, on=[\"SID\", \"week\"], how=\"left\")"
   ],
   "metadata": {
    "id": "xWYTUNtlbYx_"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "final_cols = columns[:2] + columns[5:]"
   ],
   "metadata": {
    "id": "eefKGvEh3w0x"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "final = final[final_cols]"
   ],
   "metadata": {
    "id": "-shd9aWb3KmT"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "mjoffer = pd.read_excel(\"mjoffer_tomakelong.xlsx\")"
   ],
   "metadata": {
    "id": "MNlLx2Oo3LWy"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "renamed_columns = {\"Unnamed: 0\": \"SID\"}\n",
    "cols = mjoffer.columns.to_list()\n",
    "for i in range(1, len(cols)):\n",
    "  renamed_columns[cols[i]] = f\"{cols[i][1:]}\""
   ],
   "metadata": {
    "id": "XCQgJGoC5Za-"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "mjoffer = mjoffer.rename(columns=renamed_columns)"
   ],
   "metadata": {
    "id": "s1stRK1u4K74"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "mjoffer = pd.melt(mjoffer,\n",
    "                  id_vars='SID',\n",
    "                  value_vars=mjoffer.columns.to_list()[1:],\n",
    "                  var_name=\"week\",\n",
    "                  value_name='mj_offer')"
   ],
   "metadata": {
    "id": "JANJfsot7l76"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "final = final.merge(mjoffer, on=[\"SID\", \"week\"], how=\"left\")"
   ],
   "metadata": {
    "id": "Jp3p5QvS9A3k"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "final"
   ],
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/",
     "height": 444
    },
    "id": "-16EzrxeBHpT",
    "outputId": "55787449-ce11-469a-ae45-9ea177c1a3f8"
   },
   "execution_count": null,
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "      SID Cohort  week sex age whitebin ses upps everalcF mj  ... peermj  \\\n",
       "0     101      1  B1W1   2   5        0   1    2        1  2  ...     42   \n",
       "1     101      1  B1W2   2   5        0   1    2        1  2  ...     42   \n",
       "2     101      1  B1W3   2   5        0   1    2        1  2  ...     42   \n",
       "3     101      1  B2W1   2   5        0   1    2        1  2  ...     42   \n",
       "4     101      1  B2W2   2   5        0   1    2        1  2  ...     42   \n",
       "...   ...    ...   ...  ..  ..      ...  ..  ...      ... ..  ...    ...   \n",
       "1408  305      2  B2W2   2   4        0   1    1        2  2  ...     75   \n",
       "1409  305      2  B2W3   2   4        0   1    1        2  2  ...     75   \n",
       "1410  305      2  B3W1   2   4        0   1    1        2  2  ...     75   \n",
       "1411  305      2  B3W2   2   4        0   1    1        2  2  ...     75   \n",
       "1412  305      2  B3W3   2   4        0   1    1        2  2  ...     75   \n",
       "\n",
       "     ycasts mm  mv  me  um  umh      me_mean      mm_mean mj_offer  \n",
       "0         6  0   1   1   0    0  0.028571429            0        4  \n",
       "1         6  0   0   0   0    0  0.028571429            0     -999  \n",
       "2         6  0   0   0   0    0  0.028571429            0     -999  \n",
       "3         6  0   0   0   0    0  0.028571429            0        4  \n",
       "4         6  0   0   0   0    0  0.028571429            0        4  \n",
       "...     ... ..  ..  ..  ..  ...          ...          ...      ...  \n",
       "1408      6  3   3   3   0    0  0.461538462  0.423076923        2  \n",
       "1409      6  1   0   1   0    0  0.461538462  0.423076923        3  \n",
       "1410      6  2   1   2   0    0  0.461538462  0.423076923        3  \n",
       "1411      6  0   1   1   0    0  0.461538462  0.423076923        4  \n",
       "1412      6  1   1   1   0    0  0.461538462  0.423076923     -999  \n",
       "\n",
       "[1413 rows x 21 columns]"
      ],
      "text/html": [
       "\n",
       "  <div id=\"df-c001280e-43e5-45b7-b3eb-de0e1331d70f\" 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>SID</th>\n",
       "      <th>Cohort</th>\n",
       "      <th>week</th>\n",
       "      <th>sex</th>\n",
       "      <th>age</th>\n",
       "      <th>whitebin</th>\n",
       "      <th>ses</th>\n",
       "      <th>upps</th>\n",
       "      <th>everalcF</th>\n",
       "      <th>mj</th>\n",
       "      <th>...</th>\n",
       "      <th>peermj</th>\n",
       "      <th>ycasts</th>\n",
       "      <th>mm</th>\n",
       "      <th>mv</th>\n",
       "      <th>me</th>\n",
       "      <th>um</th>\n",
       "      <th>umh</th>\n",
       "      <th>me_mean</th>\n",
       "      <th>mm_mean</th>\n",
       "      <th>mj_offer</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>B1W1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>42</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.028571429</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>B1W2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>42</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.028571429</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>B1W3</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>42</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.028571429</td>\n",
       "      <td>0</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>B2W1</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>42</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.028571429</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>101</td>\n",
       "      <td>1</td>\n",
       "      <td>B2W2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>42</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.028571429</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1408</th>\n",
       "      <td>305</td>\n",
       "      <td>2</td>\n",
       "      <td>B2W2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>6</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.461538462</td>\n",
       "      <td>0.423076923</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1409</th>\n",
       "      <td>305</td>\n",
       "      <td>2</td>\n",
       "      <td>B2W3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.461538462</td>\n",
       "      <td>0.423076923</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1410</th>\n",
       "      <td>305</td>\n",
       "      <td>2</td>\n",
       "      <td>B3W1</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>6</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.461538462</td>\n",
       "      <td>0.423076923</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1411</th>\n",
       "      <td>305</td>\n",
       "      <td>2</td>\n",
       "      <td>B3W2</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>6</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.461538462</td>\n",
       "      <td>0.423076923</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1412</th>\n",
       "      <td>305</td>\n",
       "      <td>2</td>\n",
       "      <td>B3W3</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>75</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.461538462</td>\n",
       "      <td>0.423076923</td>\n",
       "      <td>-999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1413 rows × 21 columns</p>\n",
       "</div>\n",
       "    <div class=\"colab-df-buttons\">\n",
       "\n",
       "  <div class=\"colab-df-container\">\n",
       "    <button class=\"colab-df-convert\" onclick=\"convertToInteractive('df-c001280e-43e5-45b7-b3eb-de0e1331d70f')\"\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 -960 960 960\">\n",
       "    <path d=\"M120-120v-720h720v720H120Zm60-500h600v-160H180v160Zm220 220h160v-160H400v160Zm0 220h160v-160H400v160ZM180-400h160v-160H180v160Zm440 0h160v-160H620v160ZM180-180h160v-160H180v160Zm440 0h160v-160H620v160Z\"/>\n",
       "  </svg>\n",
       "    </button>\n",
       "\n",
       "  <style>\n",
       "    .colab-df-container {\n",
       "      display:flex;\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",
       "    .colab-df-buttons div {\n",
       "      margin-bottom: 4px;\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-c001280e-43e5-45b7-b3eb-de0e1331d70f 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-c001280e-43e5-45b7-b3eb-de0e1331d70f');\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",
       "\n",
       "\n",
       "<div id=\"df-3bf6b4db-1ffd-4549-b917-662a5ca1c84f\">\n",
       "  <button class=\"colab-df-quickchart\" onclick=\"quickchart('df-3bf6b4db-1ffd-4549-b917-662a5ca1c84f')\"\n",
       "            title=\"Suggest charts\"\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",
       "    <g>\n",
       "        <path d=\"M19 3H5c-1.1 0-2 .9-2 2v14c0 1.1.9 2 2 2h14c1.1 0 2-.9 2-2V5c0-1.1-.9-2-2-2zM9 17H7v-7h2v7zm4 0h-2V7h2v10zm4 0h-2v-4h2v4z\"/>\n",
       "    </g>\n",
       "</svg>\n",
       "  </button>\n",
       "\n",
       "<style>\n",
       "  .colab-df-quickchart {\n",
       "      --bg-color: #E8F0FE;\n",
       "      --fill-color: #1967D2;\n",
       "      --hover-bg-color: #E2EBFA;\n",
       "      --hover-fill-color: #174EA6;\n",
       "      --disabled-fill-color: #AAA;\n",
       "      --disabled-bg-color: #DDD;\n",
       "  }\n",
       "\n",
       "  [theme=dark] .colab-df-quickchart {\n",
       "      --bg-color: #3B4455;\n",
       "      --fill-color: #D2E3FC;\n",
       "      --hover-bg-color: #434B5C;\n",
       "      --hover-fill-color: #FFFFFF;\n",
       "      --disabled-bg-color: #3B4455;\n",
       "      --disabled-fill-color: #666;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart {\n",
       "    background-color: var(--bg-color);\n",
       "    border: none;\n",
       "    border-radius: 50%;\n",
       "    cursor: pointer;\n",
       "    display: none;\n",
       "    fill: var(--fill-color);\n",
       "    height: 32px;\n",
       "    padding: 0;\n",
       "    width: 32px;\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart:hover {\n",
       "    background-color: var(--hover-bg-color);\n",
       "    box-shadow: 0 1px 2px rgba(60, 64, 67, 0.3), 0 1px 3px 1px rgba(60, 64, 67, 0.15);\n",
       "    fill: var(--button-hover-fill-color);\n",
       "  }\n",
       "\n",
       "  .colab-df-quickchart-complete:disabled,\n",
       "  .colab-df-quickchart-complete:disabled:hover {\n",
       "    background-color: var(--disabled-bg-color);\n",
       "    fill: var(--disabled-fill-color);\n",
       "    box-shadow: none;\n",
       "  }\n",
       "\n",
       "  .colab-df-spinner {\n",
       "    border: 2px solid var(--fill-color);\n",
       "    border-color: transparent;\n",
       "    border-bottom-color: var(--fill-color);\n",
       "    animation:\n",
       "      spin 1s steps(1) infinite;\n",
       "  }\n",
       "\n",
       "  @keyframes spin {\n",
       "    0% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "      border-left-color: var(--fill-color);\n",
       "    }\n",
       "    20% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    30% {\n",
       "      border-color: transparent;\n",
       "      border-left-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    40% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-top-color: var(--fill-color);\n",
       "    }\n",
       "    60% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "    }\n",
       "    80% {\n",
       "      border-color: transparent;\n",
       "      border-right-color: var(--fill-color);\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "    90% {\n",
       "      border-color: transparent;\n",
       "      border-bottom-color: var(--fill-color);\n",
       "    }\n",
       "  }\n",
       "</style>\n",
       "\n",
       "  <script>\n",
       "    async function quickchart(key) {\n",
       "      const quickchartButtonEl =\n",
       "        document.querySelector('#' + key + ' button');\n",
       "      quickchartButtonEl.disabled = true;  // To prevent multiple clicks.\n",
       "      quickchartButtonEl.classList.add('colab-df-spinner');\n",
       "      try {\n",
       "        const charts = await google.colab.kernel.invokeFunction(\n",
       "            'suggestCharts', [key], {});\n",
       "      } catch (error) {\n",
       "        console.error('Error during call to suggestCharts:', error);\n",
       "      }\n",
       "      quickchartButtonEl.classList.remove('colab-df-spinner');\n",
       "      quickchartButtonEl.classList.add('colab-df-quickchart-complete');\n",
       "    }\n",
       "    (() => {\n",
       "      let quickchartButtonEl =\n",
       "        document.querySelector('#df-3bf6b4db-1ffd-4549-b917-662a5ca1c84f button');\n",
       "      quickchartButtonEl.style.display =\n",
       "        google.colab.kernel.accessAllowed ? 'block' : 'none';\n",
       "    })();\n",
       "  </script>\n",
       "</div>\n",
       "    </div>\n",
       "  </div>\n"
      ]
     },
     "metadata": {},
     "execution_count": 369
    }
   ]
  },
  {
   "cell_type": "code",
   "source": [
    "for col in final.columns.to_list()[:2] + final.columns.to_list()[3:]:\n",
    "  final[col] = pd.to_numeric(final[col])"
   ],
   "metadata": {
    "id": "EPw6taFsAKDl"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [
    "final.to_excel(\"MLM_condensed_with_mjoffer.xlsx\", index=False)"
   ],
   "metadata": {
    "id": "CsB0j7k5-_6A"
   },
   "execution_count": null,
   "outputs": []
  },
  {
   "cell_type": "code",
   "source": [],
   "metadata": {
    "id": "WiRbrwCT_78B"
   },
   "execution_count": null,
   "outputs": []
  }
 ],
 "metadata": {
  "colab": {
   "provenance": [],
   "collapsed_sections": [
    "9yqpQUOJzRSC",
    "9rAP9xTrh0DU",
    "JLUAv0joqCyR",
    "W6QG0mDk0RTm",
    "F2PpbmaSeO2U",
    "H4Wmqm2Ym65R",
    "MuM49DjPHZD3",
    "mljp4h1vS9ib"
   ]
  },
  "kernelspec": {
   "display_name": "Python 3",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}