Example_Combined_Personal_Data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# EXAMPLE: Combined QS and Personal Tracking Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This Notebook provides an example on how to combine multiple data sources into a combined dataframe. In this example, we combine both manual exports and some data downloaded from specific services' APIs, including RescueTime, Apple Health, Last.fm, and others. \n",
    "\n",
    "NOTE: This is still a work-in-progress. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dependencies and Libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date, datetime as dt, timedelta as td\n",
    "import pytz\n",
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# functions to convert UTC to Shanghai time zone and extract date/time elements\n",
    "convert_tz = lambda x: x.to_pydatetime().replace(tzinfo=pytz.utc).astimezone(pytz.timezone('Asia/Shanghai'))\n",
    "get_year = lambda x: convert_tz(x).year\n",
    "get_month = lambda x: '{}-{:02}'.format(convert_tz(x).year, convert_tz(x).month) #inefficient\n",
    "get_date = lambda x: '{}-{:02}-{:02}'.format(convert_tz(x).year, convert_tz(x).month, convert_tz(x).day) #inefficient\n",
    "get_day = lambda x: convert_tz(x).day\n",
    "get_hour = lambda x: convert_tz(x).hour\n",
    "get_day_of_week = lambda x: convert_tz(x).weekday()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Steps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fitbit\n",
    "# TODO\n",
    "\n",
    "# apple health\n",
    "# steps = pd.read_csv(\"apple_health/data/steps_per_day.csv\", names=['Date', 'Steps'])\n",
    "\n",
    "# Gyroscope Steps Import\n",
    "steps = pd.read_csv(\"/Users/markkoester/Dropbox/tracking/gyroscope/gyroscope-Mark-steps-export.csv\", names=[\"Date\", \"Steps\", \"Service\"])\n",
    "steps = steps.drop(['Service'], axis=1)\n",
    "\n",
    "# steps.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Tasks"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# todoist\n",
    "tasks = pd.read_csv(\"todoist/data/todoist-daily-completed.csv\", names=[\"Date\", \"Tasks\"])\n",
    "\n",
    "# tasks.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# toggl\n",
    "toggl = pd.read_csv('toggl/data/daily_project_time.csv', names=[\"Date\", \"ProjectSeconds\"])\n",
    "\n",
    "toggl['ProjectTime'] = toggl['ProjectSeconds'] / 60 # in minutes\n",
    "# toggl['ProjectTimeHours'] = toggl['ProjectSeconds'] / 60 / 60\n",
    "# toggl['ProjectTimePer'] = round((toggl['ProjectSeconds'] / 60 / 60 / 24), 3)\n",
    "toggl = toggl.drop(['ProjectSeconds'], axis=1)\n",
    "\n",
    "\n",
    "# toggl.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# RescueTime\n",
    "rescuetime = pd.read_csv(\"rescuetime/data/days_productive_time.csv\", \n",
    "                        names=['Date', 'NeutralTime', 'ProductiveTime', 'DistractingTime'])\n",
    "\n",
    "# rescuetime.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### HRV and Other Morning Data Points"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# HRV4Training\n",
    "hrv_data = pd.read_csv(\"/Users/markkoester/Dropbox/tracking/hrv4training/hr4training.csv\")\n",
    "hrv_data = hrv_data.drop(['test_duration',\n",
    "       'training', 'training_performance', 'training_type', 'training_phase',\n",
    "       'physical_condition', 'trainingRPE', ' trainingTSS', ' suffer_score',\n",
    "       ' trainingMotivation', ' trainingDistance', 'mental_energy',\n",
    "       'muscle_soreness', 'fatigue', 'advice', 'note', 'signal_quality',\n",
    "       ' supplements', ' diet', ' custom_tag_1_name', ' custom_tag_2_name',\n",
    "       ' custom_tag_3_name', ' custom_tag_1_value', ' custom_tag_2_value',\n",
    "       ' custom_tag_3_value', ' menstrual_cycle', ' trainingTime',\n",
    "       ' current_lifestyle', ' run_distance', ' run_time', ' run_pace',\n",
    "       ' run_hr', ' run_elevation', ' bike_distance', ' bike_time',\n",
    "       ' bike_speed', ' bike_elevation', ' bike_hr', ' bike_power',\n",
    "       ' swim_distance', ' swim_time', ' swim_speed', ' swim_hr',\n",
    "       ' daily_message  '], axis=1)\n",
    "hrv_data['date'] = pd.to_datetime(hrv_data['date'])\n",
    "hrv_data['date'] = hrv_data['date'].apply(lambda x: x.strftime('%Y-%m-%d')) # note: not very efficient\n",
    "hrv_data.columns = ['Date', 'timestamp_measurement', 'HR', 'AVNN', 'SDNN', 'rMSSD', 'pNN50',\n",
    "       'LF', 'HF', 'LFHF', 'HRV4T_Recovery_Points', 'sleep_quality',\n",
    "       'sleep_time', 'sleep_tobed', 'sleep_awake', 'traveling', 'sickness',\n",
    "       'alcohol', 'baseline', 'location', 'vo2max', 'latitude',\n",
    "       'longitude', 'altitude', 'temperature', 'humidity']\n",
    "\n",
    "# hrv_data.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# TODO: convert sleep duration text to minutes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "hrv_data.to_csv(\"data/hrv_data.csv\", index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Running"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# strava\n",
    "# strava = pd.read_csv('strava/data/strava-activities-raw.csv')\n",
    "# TODO: Process\n",
    "\n",
    "# Gyroscope \n",
    "running = pd.read_csv(\"/Users/markkoester/Dropbox/tracking/gyroscope/gyroscope-Mark-running-export.csv\")\n",
    "running = running.drop(['service', 'id'], axis=1)\n",
    "\n",
    "running['start_time'] = pd.to_datetime(running['start_time'])\n",
    "running['end_time'] = pd.to_datetime(running['end_time'])\n",
    "\n",
    "running['seconds'] = (running['end_time'] - running['start_time']).dt.total_seconds()\n",
    "running['minutes'] = running['seconds'] / 60\n",
    "\n",
    "# parse out date and time elements as Shanghai time\n",
    "running['year'] = running['start_time'].map(get_year)\n",
    "running['month'] = running['start_time'].map(get_month)\n",
    "running['Date'] = running['start_time'].map(get_date)\n",
    "running['day'] = running['start_time'].map(get_day)\n",
    "running['hour'] = running['start_time'].map(get_hour)\n",
    "running['dow'] = running['start_time'].map(get_day_of_week)\n",
    "\n",
    "# running.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "463 total days of running\n"
     ]
    }
   ],
   "source": [
    "# combine to daily number\n",
    "# how many days did I run?\n",
    "daily_running = running.groupby(['Date'])['minutes', 'meters', 'climb'].sum()\n",
    "daily_running.columns = ['RunningMinutes', 'RunningMeters', 'RunningClimb']\n",
    "print('{:,} total days of running'.format(len(daily_running)))\n",
    "daily_running.to_csv('data/daily_running.csv')\n",
    "\n",
    "# daily_running.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "daily_running = pd.read_csv('data/daily_running.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Reading"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Books: Good Reads\n",
    "\n",
    "# books = pd.read_csv(\"goodreads/data/books.csv\")\n",
    "# books_read = pd.read_csv(\"goodreads/data/books_read.csv\")\n",
    "# books_read.tail()\n",
    "# len(books_read)\n",
    "\n",
    "books_read_with_date = pd.read_csv(\"goodreads/data/books-read.csv\")\n",
    "\n",
    "#books_read_with_date.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# books_read_with_date.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# combine to daily number\n",
    "# how many days did I finish a book?\n",
    "# daily_books = books_read_with_date.groupby(['date'])['num_page', 'meters', 'climb'].sum()\n",
    "#daily_books.columns = ['RunningSeconds', 'RunningMeters', 'RunningClimb']\n",
    "#print('{:,} total days of running'.format(len(daily_running)))\n",
    "#daily_running.to_csv('data/daily_running.csv')\n",
    "#daily_running.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# kindle\n",
    "highlights_count = pd.read_csv(\"kindle/data/daily_count_kindle_clippings.csv\", names=[\"Date\", \"KindleHighlights\"])\n",
    "\n",
    "#highlights_count.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Articles Read\n",
    "# TODO: Pocket Articles\n",
    "# TODO: Instapaper Articles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Photos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Instagram Photo Taking from Gyroscope Export\n",
    "instagram = pd.read_csv(\"/Users/markkoester/Dropbox/tracking/gyroscope/gyroscope-Mark-photos-export.csv\")\n",
    "instagram['Time'] = pd.to_datetime(instagram['Time'])\n",
    "instagram['year'] = instagram['Time'].map(get_year)\n",
    "instagram['month'] = instagram['Time'].map(get_month)\n",
    "instagram['date'] = instagram['Time'].map(get_date)\n",
    "instagram['day'] = instagram['Time'].map(get_day)\n",
    "instagram['hour'] = instagram['Time'].map(get_hour)\n",
    "instagram['dow'] = instagram['Time'].map(get_day_of_week)\n",
    "\n",
    "# instagram.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "536 total days when I posted a photo on social media\n"
     ]
    }
   ],
   "source": [
    "# how many days did I post a photo on social media?\n",
    "daily_social_photos = instagram['date'].value_counts().sort_index()\n",
    "print('{:,} total days when I posted a photo on social media'.format(len(daily_social_photos)))\n",
    "daily_social_photos.to_csv('data/social_photos_count.csv')\n",
    "\n",
    "# daily_social_photos.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "daily_social_photos = pd.read_csv('data/social_photos_count.csv', names=['Date', 'Photos'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Device Photo Taking from PhotoStats\n",
    "# TODO"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Podcast Listening\n",
    "# TODO"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Music Listening"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Last.fm\n",
    "music = pd.read_csv(\"last_fm/data/lastfm_daily_listens.csv\", names=[\"Date\", \"Songs\"])\n",
    "\n",
    "# music.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Stats"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2,171 total computer days\n",
      "1,883 total toggl project time days\n",
      "826 total days with steps\n",
      "630 total days with tasks completed\n",
      "597 total days with HRV data\n",
      "536 total days when I posted a photo on social media\n",
      "540 total days with songs listened to\n",
      "463 total days when I did a run\n"
     ]
    }
   ],
   "source": [
    "print('{:,} total computer days'.format(len(rescuetime)))\n",
    "print('{:,} total toggl project time days'.format(len(toggl)))\n",
    "print('{:,} total days with steps'.format(len(steps)))\n",
    "print('{:,} total days with tasks completed'.format(len(tasks)))\n",
    "print('{:,} total days with HRV data'.format(len(hrv_data)))\n",
    "print('{:,} total days when I posted a photo on social media'.format(len(daily_social_photos)))\n",
    "print('{:,} total days with songs listened to'.format(len(music)))\n",
    "print('{:,} total days when I did a run'.format(len(daily_running)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "----"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Combine and Merge into Unified Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "from functools import reduce"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs = [rescuetime, toggl, steps, tasks, highlights_count, hrv_data, daily_social_photos, music, daily_running]\n",
    "data = reduce(lambda left,right: pd.merge(left,right,on='Date', how='left'), dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2,171 total number of data days\n"
     ]
    }
   ],
   "source": [
    "print('{:,} total number of data days'.format(len(data)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "38 total number of columns\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Index(['Date', 'NeutralTime', 'ProductiveTime', 'DistractingTime',\n",
       "       'ProjectTime', 'Steps', 'Tasks', 'KindleHighlights',\n",
       "       'timestamp_measurement', 'HR', 'AVNN', 'SDNN', 'rMSSD', 'pNN50', 'LF',\n",
       "       'HF', 'LFHF', 'HRV4T_Recovery_Points', 'sleep_quality', 'sleep_time',\n",
       "       'sleep_tobed', 'sleep_awake', 'traveling', 'sickness', 'alcohol',\n",
       "       'baseline', 'location', 'vo2max', 'latitude', 'longitude', 'altitude',\n",
       "       'temperature', 'humidity', 'Photos', 'Songs', 'RunningMinutes',\n",
       "       'RunningMeters', 'RunningClimb'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('{:,} total number of columns'.format(len(data.columns)))\n",
    "data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv(\"data/combined_personal_data.csv\")"
   ]
  }
 ],
 "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.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}