data_preproc.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from sqlalchemy import create_engine\n",
    "from conf import *\n",
    "\n",
    "# set pandas shown columns to 50\n",
    "pd.set_option('display.max_columns', 50)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Push subjects data to db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>user_id</th>\n",
       "      <th>subj_id</th>\n",
       "      <th>device_id</th>\n",
       "      <th>cohort_id</th>\n",
       "      <th>dropped</th>\n",
       "      <th>drop_type</th>\n",
       "      <th>rank</th>\n",
       "      <th>rank_date</th>\n",
       "      <th>pref</th>\n",
       "      <th>rtc_checkin_date</th>\n",
       "      <th>brpc_attempts</th>\n",
       "      <th>pop</th>\n",
       "      <th>mos</th>\n",
       "      <th>pebd</th>\n",
       "      <th>eas</th>\n",
       "      <th>dob</th>\n",
       "      <th>height</th>\n",
       "      <th>weight</th>\n",
       "      <th>area_code</th>\n",
       "      <th>married</th>\n",
       "      <th>city</th>\n",
       "      <th>state</th>\n",
       "      <th>hs_name</th>\n",
       "      <th>hs_city</th>\n",
       "      <th>hs_state</th>\n",
       "      <th>hs_grad_year</th>\n",
       "      <th>hs_sports</th>\n",
       "      <th>hs_clubs</th>\n",
       "      <th>college_name</th>\n",
       "      <th>college_city</th>\n",
       "      <th>college_state</th>\n",
       "      <th>college_grad_year</th>\n",
       "      <th>college_sports</th>\n",
       "      <th>college_clubs</th>\n",
       "      <th>hobbies</th>\n",
       "      <th>swimming_exp</th>\n",
       "      <th>workout_exp</th>\n",
       "      <th>DOR_Time</th>\n",
       "      <th>DOR_Activity</th>\n",
       "      <th>drop_date</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>88e86f98-15c0-4c99-9460-ec80876bbba1</td>\n",
       "      <td>19</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>Pass</td>\n",
       "      <td>SGT</td>\n",
       "      <td>2017-11-01</td>\n",
       "      <td>ALAT</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>ME</td>\n",
       "      <td>612</td>\n",
       "      <td>2014-06-09</td>\n",
       "      <td>2024-06-08</td>\n",
       "      <td>1994-09-29</td>\n",
       "      <td>73</td>\n",
       "      <td>192</td>\n",
       "      <td>81.0</td>\n",
       "      <td>N</td>\n",
       "      <td>SACRAMENTO</td>\n",
       "      <td>CA</td>\n",
       "      <td>PAUL M. DORMAN HS</td>\n",
       "      <td>MOORE</td>\n",
       "      <td>SC</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>GYM, EXPLORING</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>5704d0d5-44d7-4e75-b598-792e9debb6f0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>Pass</td>\n",
       "      <td>HN</td>\n",
       "      <td>2015-03-24</td>\n",
       "      <td>NAV</td>\n",
       "      <td>20180318.0</td>\n",
       "      <td>2</td>\n",
       "      <td>NE</td>\n",
       "      <td>8404</td>\n",
       "      <td>2015-03-24</td>\n",
       "      <td>2021-03-24</td>\n",
       "      <td>1989-09-14</td>\n",
       "      <td>69</td>\n",
       "      <td>160</td>\n",
       "      <td>512.0</td>\n",
       "      <td>N</td>\n",
       "      <td>AUSTIN</td>\n",
       "      <td>TX</td>\n",
       "      <td>WESTLAKE HS</td>\n",
       "      <td>AUSTIN</td>\n",
       "      <td>TX</td>\n",
       "      <td>2008.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>TEXAS TECH UNIVERSTITY</td>\n",
       "      <td>LUBBOCK</td>\n",
       "      <td>TX</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>693034d5-df06-463f-9146-9c26d3fee770</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>DOR</td>\n",
       "      <td>PFC</td>\n",
       "      <td>2017-11-27</td>\n",
       "      <td>MCT/HZ</td>\n",
       "      <td>20180403.0</td>\n",
       "      <td>1</td>\n",
       "      <td>ME</td>\n",
       "      <td>03XX</td>\n",
       "      <td>2017-11-27</td>\n",
       "      <td>2022-11-27</td>\n",
       "      <td>1994-06-17</td>\n",
       "      <td>69</td>\n",
       "      <td>155</td>\n",
       "      <td>714.0</td>\n",
       "      <td>N</td>\n",
       "      <td>ANAHEIM</td>\n",
       "      <td>CA</td>\n",
       "      <td>SAVANNA HS</td>\n",
       "      <td>ANAHEIM</td>\n",
       "      <td>CA</td>\n",
       "      <td>2013.0</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>CALIFORNIA STATE UNIVERSITY FULLERTON</td>\n",
       "      <td>FULLERTON</td>\n",
       "      <td>CA</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>EXERCISING, COMIC BOOKS</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>15:00:00</td>\n",
       "      <td>WSA</td>\n",
       "      <td>2018-05-03</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2aeff147-0b19-48bd-9f45-f9ffc3266c50</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "      <td>Pass</td>\n",
       "      <td>PFC</td>\n",
       "      <td>2017-11-06</td>\n",
       "      <td>UH</td>\n",
       "      <td>20180417.0</td>\n",
       "      <td>1</td>\n",
       "      <td>ME</td>\n",
       "      <td>313</td>\n",
       "      <td>2017-11-06</td>\n",
       "      <td>2022-11-06</td>\n",
       "      <td>1999-09-09</td>\n",
       "      <td>67</td>\n",
       "      <td>130</td>\n",
       "      <td>747.0</td>\n",
       "      <td>N</td>\n",
       "      <td>HOLLY RIDGE</td>\n",
       "      <td>NC</td>\n",
       "      <td>BONNEVILLE HS</td>\n",
       "      <td>IDAHO FALLS</td>\n",
       "      <td>ID</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>1, 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SURFING, FISHING</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>e662d2d7-9110-4df4-8b27-734bebcd09b2</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>True</td>\n",
       "      <td>DOR</td>\n",
       "      <td>PFC</td>\n",
       "      <td>2017-12-01</td>\n",
       "      <td>UH</td>\n",
       "      <td>20180327.0</td>\n",
       "      <td>1</td>\n",
       "      <td>ME</td>\n",
       "      <td>311</td>\n",
       "      <td>2017-09-05</td>\n",
       "      <td>2022-09-05</td>\n",
       "      <td>1999-05-24</td>\n",
       "      <td>65</td>\n",
       "      <td>145</td>\n",
       "      <td>559.0</td>\n",
       "      <td>N</td>\n",
       "      <td>SANGER</td>\n",
       "      <td>CA</td>\n",
       "      <td>SANGER HS</td>\n",
       "      <td>SANGER</td>\n",
       "      <td>CA</td>\n",
       "      <td>2017.0</td>\n",
       "      <td>10, 13</td>\n",
       "      <td>6, 10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SWIMMING</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>13:30:00</td>\n",
       "      <td>Lunch</td>\n",
       "      <td>2018-05-02</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id                               subj_id  device_id  cohort_id  \\\n",
       "0        1  88e86f98-15c0-4c99-9460-ec80876bbba1         19          1   \n",
       "1        2  5704d0d5-44d7-4e75-b598-792e9debb6f0          1          1   \n",
       "2        3  693034d5-df06-463f-9146-9c26d3fee770          2          1   \n",
       "3        4  2aeff147-0b19-48bd-9f45-f9ffc3266c50          4          1   \n",
       "4        5  e662d2d7-9110-4df4-8b27-734bebcd09b2          5          1   \n",
       "\n",
       "   dropped drop_type rank   rank_date    pref  rtc_checkin_date brpc_attempts  \\\n",
       "0    False      Pass  SGT  2017-11-01    ALAT               NaN             1   \n",
       "1    False      Pass   HN  2015-03-24     NAV        20180318.0             2   \n",
       "2     True       DOR  PFC  2017-11-27  MCT/HZ        20180403.0             1   \n",
       "3    False      Pass  PFC  2017-11-06      UH        20180417.0             1   \n",
       "4     True       DOR  PFC  2017-12-01      UH        20180327.0             1   \n",
       "\n",
       "  pop   mos        pebd         eas        dob  height  weight  area_code  \\\n",
       "0  ME   612  2014-06-09  2024-06-08 1994-09-29      73     192       81.0   \n",
       "1  NE  8404  2015-03-24  2021-03-24 1989-09-14      69     160      512.0   \n",
       "2  ME  03XX  2017-11-27  2022-11-27 1994-06-17      69     155      714.0   \n",
       "3  ME   313  2017-11-06  2022-11-06 1999-09-09      67     130      747.0   \n",
       "4  ME   311  2017-09-05  2022-09-05 1999-05-24      65     145      559.0   \n",
       "\n",
       "  married         city state            hs_name      hs_city hs_state  \\\n",
       "0       N   SACRAMENTO    CA  PAUL M. DORMAN HS        MOORE       SC   \n",
       "1       N       AUSTIN    TX        WESTLAKE HS       AUSTIN       TX   \n",
       "2       N      ANAHEIM    CA         SAVANNA HS      ANAHEIM       CA   \n",
       "3       N  HOLLY RIDGE    NC      BONNEVILLE HS  IDAHO FALLS       ID   \n",
       "4       N       SANGER    CA          SANGER HS       SANGER       CA   \n",
       "\n",
       "   hs_grad_year hs_sports hs_clubs                           college_name  \\\n",
       "0        2013.0         0        0                                    NaN   \n",
       "1        2008.0         0        0                 TEXAS TECH UNIVERSTITY   \n",
       "2        2013.0         4        0  CALIFORNIA STATE UNIVERSITY FULLERTON   \n",
       "3        2017.0      1, 2      NaN                                    NaN   \n",
       "4        2017.0    10, 13    6, 10                                    NaN   \n",
       "\n",
       "  college_city college_state  college_grad_year college_sports college_clubs  \\\n",
       "0          NaN           NaN                NaN            NaN           NaN   \n",
       "1      LUBBOCK            TX             2013.0              0             0   \n",
       "2    FULLERTON            CA                0.0              0             0   \n",
       "3          NaN           NaN                NaN            NaN           NaN   \n",
       "4          NaN           NaN                NaN            NaN           NaN   \n",
       "\n",
       "                   hobbies  swimming_exp  workout_exp  DOR_Time DOR_Activity  \\\n",
       "0           GYM, EXPLORING           1.0          1.0       NaN          NaN   \n",
       "1                      NaN           1.0          1.0       NaN          NaN   \n",
       "2  EXERCISING, COMIC BOOKS           1.0          1.0  15:00:00          WSA   \n",
       "3         SURFING, FISHING           1.0          1.0       NaN          NaN   \n",
       "4                 SWIMMING           2.0          1.0  13:30:00        Lunch   \n",
       "\n",
       "    drop_date  age  \n",
       "0         NaN   28  \n",
       "1         NaN   33  \n",
       "2  2018-05-03   29  \n",
       "3         NaN   23  \n",
       "4  2018-05-02   24  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "subjects_df = pd.read_csv('./dataset/SUBJECTS.csv')\n",
    "subjects_df.rename(columns={'id': 'user_id', 'ht': 'height', 'wt': 'weight'}, inplace=True)\n",
    "# calculate age from dob\n",
    "subjects_df['dob'] = pd.to_datetime(subjects_df['dob'])\n",
    "subjects_df['age'] = (pd.to_datetime('today') - subjects_df['dob']).astype('<m8[Y]').astype(int)\n",
    "subjects_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "115"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# write df to database\n",
    "db_connection_str = f'postgresql://{DB_USER}:{DB_PASS}@{DB_HOST}:{DB_PORT}/{DB_NAME}'\n",
    "db_engine = create_engine(db_connection_str)\n",
    "subjects_df.to_sql('subjects', con=db_engine, if_exists='replace', index=False)\n"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Process band heart-rate data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>value</th>\n",
       "      <th>unit</th>\n",
       "      <th>response_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>107</td>\n",
       "      <td>bpm</td>\n",
       "      <td>2018-04-27</td>\n",
       "      <td>2018-04-26 18:26:04 +0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>106</td>\n",
       "      <td>bpm</td>\n",
       "      <td>2018-04-27</td>\n",
       "      <td>2018-04-26 18:26:06 +0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>77</td>\n",
       "      <td>bpm</td>\n",
       "      <td>2018-04-27</td>\n",
       "      <td>2018-04-26 18:54:59 +0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>75</td>\n",
       "      <td>bpm</td>\n",
       "      <td>2018-04-27</td>\n",
       "      <td>2018-04-26 18:56:29 +0000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>73</td>\n",
       "      <td>bpm</td>\n",
       "      <td>2018-04-27</td>\n",
       "      <td>2018-04-26 19:02:51 +0000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  value unit response_date                   end_date\n",
       "0   1    107  bpm    2018-04-27  2018-04-26 18:26:04 +0000\n",
       "1   1    106  bpm    2018-04-27  2018-04-26 18:26:06 +0000\n",
       "2   1     77  bpm    2018-04-27  2018-04-26 18:54:59 +0000\n",
       "3   1     75  bpm    2018-04-27  2018-04-26 18:56:29 +0000\n",
       "4   1     73  bpm    2018-04-27  2018-04-26 19:02:51 +0000"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hrates_raw_df = pd.read_csv('./dataset/BAND_Heart-Rate.csv')\n",
    "hrates_raw_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>user_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-26 18:26:04+00:00</td>\n",
       "      <td>107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-26 18:26:06+00:00</td>\n",
       "      <td>106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-26 18:54:59+00:00</td>\n",
       "      <td>77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-26 18:56:29+00:00</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-26 19:02:51+00:00</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   user_id                 timestamp  value\n",
       "0        1 2018-04-26 18:26:04+00:00    107\n",
       "1        1 2018-04-26 18:26:06+00:00    106\n",
       "2        1 2018-04-26 18:54:59+00:00     77\n",
       "3        1 2018-04-26 18:56:29+00:00     75\n",
       "4        1 2018-04-26 19:02:51+00:00     73"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hrates_df = hrates_raw_df[['id', 'end_date', 'value']].copy().rename(columns={'id': 'user_id', 'end_date': 'timestamp'})\n",
    "hrates_df = hrates_df[hrates_df['value']>0]\n",
    "hrates_df['timestamp'] = pd.to_datetime(hrates_df['timestamp'])\n",
    "# sort by user_id and timestamp\n",
    "hrates_df.sort_values(by=['user_id', 'timestamp'], inplace=True)\n",
    "hrates_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "656"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# resample values by 1 minute\n",
    "hrates_dt_df = hrates_df.set_index('timestamp')\n",
    "# hrates_resampled = hrates_dt_df.groupby('user_id')['value'].resample('10T').mean().interpolate(method='linear').to_frame().reset_index()\n",
    "hrates_resampled = hrates_dt_df.groupby('user_id')['value'].resample('1T').mean().dropna().to_frame().reset_index()\n",
    "hrates_resampled.to_sql('heart_rates', con=db_engine, if_exists='replace', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "36"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# what is the most frequent timestamp in hrates_resampled?\n",
    "hrates_resampled.timestamp.value_counts()['2018-04-30 8:20:00']"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Process band active energy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>value</th>\n",
       "      <th>unit</th>\n",
       "      <th>response_date</th>\n",
       "      <th>end_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>176.578000</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-04-27</td>\n",
       "      <td>2018-04-27T17:17:34.649-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>152.298000</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-05-23</td>\n",
       "      <td>2018-05-23T17:23:43.039-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>1892.518000</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-05-25</td>\n",
       "      <td>2018-05-25T15:50:30.620-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>904.485061</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>2018-05-31T12:12:32.216-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>1552.391939</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-05-31</td>\n",
       "      <td>2018-05-31T12:12:32.216-07:00</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>2582</th>\n",
       "      <td>113</td>\n",
       "      <td>1730.838906</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>2018-07-31T17:42:28.605-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2583</th>\n",
       "      <td>113</td>\n",
       "      <td>1616.340000</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-07-31</td>\n",
       "      <td>2018-07-31T17:42:28.605-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2584</th>\n",
       "      <td>111</td>\n",
       "      <td>214.923000</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-07-20</td>\n",
       "      <td>2018-07-20T16:39:31.255-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2585</th>\n",
       "      <td>82</td>\n",
       "      <td>1807.089000</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-07-19</td>\n",
       "      <td>2018-07-19T17:20:37.543-07:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2586</th>\n",
       "      <td>83</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>kcal</td>\n",
       "      <td>2018-07-19</td>\n",
       "      <td>2018-07-19T18:28:27.579-07:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2587 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       id        value  unit response_date                       end_date\n",
       "0       1   176.578000  kcal    2018-04-27  2018-04-27T17:17:34.649-07:00\n",
       "1       1   152.298000  kcal    2018-05-23  2018-05-23T17:23:43.039-07:00\n",
       "2       1  1892.518000  kcal    2018-05-25  2018-05-25T15:50:30.620-07:00\n",
       "3       1   904.485061  kcal    2018-05-31  2018-05-31T12:12:32.216-07:00\n",
       "4       1  1552.391939  kcal    2018-05-31  2018-05-31T12:12:32.216-07:00\n",
       "...   ...          ...   ...           ...                            ...\n",
       "2582  113  1730.838906  kcal    2018-07-31  2018-07-31T17:42:28.605-07:00\n",
       "2583  113  1616.340000  kcal    2018-07-31  2018-07-31T17:42:28.605-07:00\n",
       "2584  111   214.923000  kcal    2018-07-20  2018-07-20T16:39:31.255-07:00\n",
       "2585   82  1807.089000  kcal    2018-07-19  2018-07-19T17:20:37.543-07:00\n",
       "2586   83     0.000000  kcal    2018-07-19  2018-07-19T18:28:27.579-07:00\n",
       "\n",
       "[2587 rows x 5 columns]"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aenergy_raw_df = pd.read_csv('./dataset/BAND_Active-Energy.csv')\n",
    "aenergy_raw_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>user_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-27 17:17:34.649000-07:00</td>\n",
       "      <td>176.578000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-30 10:59:49.696000-07:00</td>\n",
       "      <td>611.059203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-01 17:52:48.615000-07:00</td>\n",
       "      <td>2180.596000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-02 17:52:48.609000-07:00</td>\n",
       "      <td>2180.596000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-05-03 17:52:48.600000-07:00</td>\n",
       "      <td>2180.596000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    user_id                        timestamp        value\n",
       "0         1 2018-04-27 17:17:34.649000-07:00   176.578000\n",
       "5         1 2018-04-30 10:59:49.696000-07:00   611.059203\n",
       "22        1 2018-05-01 17:52:48.615000-07:00  2180.596000\n",
       "41        1 2018-05-02 17:52:48.609000-07:00  2180.596000\n",
       "19        1 2018-05-03 17:52:48.600000-07:00  2180.596000"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aenergy_df = aenergy_raw_df[['id', 'end_date', 'value']].copy().rename(columns={'id': 'user_id', 'end_date': 'timestamp'})\n",
    "aenergy_df = aenergy_df[aenergy_df['value']>0]\n",
    "aenergy_df['timestamp'] = pd.to_datetime(aenergy_df['timestamp'])\n",
    "# sort by user_id and timestamp\n",
    "aenergy_df.sort_values(by=['user_id', 'timestamp'], inplace=True)\n",
    "aenergy_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<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>user_id</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-27 17:17:00-07:00</td>\n",
       "      <td>176.578</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-27 17:18:00-07:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-27 17:19:00-07:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-27 17:20:00-07:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2018-04-27 17:21:00-07:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3216303</th>\n",
       "      <td>115</td>\n",
       "      <td>2018-08-17 08:40:00-07:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3216304</th>\n",
       "      <td>115</td>\n",
       "      <td>2018-08-17 08:41:00-07:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3216305</th>\n",
       "      <td>115</td>\n",
       "      <td>2018-08-17 08:42:00-07:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3216306</th>\n",
       "      <td>115</td>\n",
       "      <td>2018-08-17 08:43:00-07:00</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3216307</th>\n",
       "      <td>115</td>\n",
       "      <td>2018-08-17 08:44:00-07:00</td>\n",
       "      <td>153.250</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3216308 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         user_id                 timestamp    value\n",
       "0              1 2018-04-27 17:17:00-07:00  176.578\n",
       "1              1 2018-04-27 17:18:00-07:00      NaN\n",
       "2              1 2018-04-27 17:19:00-07:00      NaN\n",
       "3              1 2018-04-27 17:20:00-07:00      NaN\n",
       "4              1 2018-04-27 17:21:00-07:00      NaN\n",
       "...          ...                       ...      ...\n",
       "3216303      115 2018-08-17 08:40:00-07:00      NaN\n",
       "3216304      115 2018-08-17 08:41:00-07:00      NaN\n",
       "3216305      115 2018-08-17 08:42:00-07:00      NaN\n",
       "3216306      115 2018-08-17 08:43:00-07:00      NaN\n",
       "3216307      115 2018-08-17 08:44:00-07:00  153.250\n",
       "\n",
       "[3216308 rows x 3 columns]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "aenergy_df.set_index('timestamp').groupby('user_id')['value'].resample('1T').mean().to_frame().reset_index()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.12"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}