{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Analysing Strava activities using Colab, Pandas and Matplotlib (Part 2)",
      "version": "0.3.2",
      "views": {},
      "default_view": {},
      "provenance": [
        {
          "file_id": "1eR2xoY-eOFodCIQuRROGytZrkioHLddt",
          "timestamp": 1527888313301
        }
      ],
      "collapsed_sections": []
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    }
  },
  "cells": [
    {
      "metadata": {
        "id": "EHDKAkTztUbi",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Install gspread\n",
        "!pip install --upgrade -q gspread"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "E51hj0o2tUcN",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Import Strava activitities from Google Sheets\n",
        "\n",
        "from google.colab import auth\n",
        "auth.authenticate_user()\n",
        "\n",
        "import gspread\n",
        "from oauth2client.client import GoogleCredentials\n",
        "\n",
        "gc = gspread.authorize(GoogleCredentials.get_application_default())\n",
        "\n",
        "worksheet = gc.open('Strava').sheet1\n",
        "\n",
        "# get_all_values gives a list of rows.\n",
        "rows = worksheet.get_all_values()\n",
        "\n",
        "# Convert to a DataFrame and render.\n",
        "import pandas as pd\n",
        "activities_raw = pd.DataFrame.from_records(rows[1:], columns=rows[0]);"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "CXFqnmhmt9wM",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Index activities, convert data types, convert units\n",
        "activities = activities_raw.copy()\n",
        "\n",
        "# Project\n",
        "activities = activities[['date', 'type', 'distance', 'elapsed_time', 'commute', 'name']]\n",
        "\n",
        "# Set data types\n",
        "activities['distance'] = activities['distance'].astype(float)\n",
        "activities['elapsed_time'] = activities['elapsed_time'].astype(float)\n",
        "activities['commute'] = activities['commute'].apply(lambda x: 1 if x == 'TRUE' else 0)\n",
        "\n",
        "# Convert units\n",
        "activities['distance'] = activities['distance'] / 1000\n",
        "activities['elapsed_time'] = activities['elapsed_time'] / 60\n",
        "\n",
        "# Number of activities (at this aggregation level, trivially 1)\n",
        "activities['count'] = 1\n",
        "\n",
        "# Index\n",
        "activities['date'] = pd.to_datetime(activities_raw['date'])\n",
        "activities.set_index(['date', 'type'], inplace=True)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "JZ9PgCHMHqK0",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Define formatters for readability\n",
        "formatters = {\n",
        "    'distance': '{:.0f}km'.format,\n",
        "    'elapsed_time':' {:.0f}min'.format,\n",
        "}\n",
        "\n",
        "detailed_formatters = {\n",
        "    'distance': '{:.1f}km'.format,\n",
        "    'elapsed_time':' {:.1f}min'.format,\n",
        "}"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "KmrQVbsdawmR",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Print a few random activities\n",
        "print(\n",
        "    activities[['distance', 'elapsed_time', 'commute', 'count']]\n",
        "      .sample(5)\n",
        "      .sort_index()\n",
        "      .to_string(formatters=formatters))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "_71DHKJuH8z-",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Print totals\n",
        "print(\n",
        "    activities[['distance', 'elapsed_time', 'count']]\n",
        "      .groupby(level='type')\n",
        "      .sum()\n",
        "      .sort_values(by='count', ascending=False)\n",
        "      .to_string(formatters=formatters))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "2jiWJhR0hJv8",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Show date range\n",
        "\n",
        "first_date = activities.index.levels[0].min()\n",
        "last_date = activities.index.levels[0].max()\n",
        "print('Activities recorded from %s to %s, total of %d days.' % (\n",
        "    first_date.strftime('%Y-%m-%d'),\n",
        "    last_date.strftime('%Y-%m-%d'),\n",
        "    (last_date - first_date).days))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "3t-tHRDxwa68",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Activities with the longest distance\n",
        "print(\n",
        "    activities['distance']\n",
        "        .groupby('type').nlargest(3)\n",
        "        .to_string(float_format='%.1fkm'))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "n6rUBZJ40v0-",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Activities with the longest duration\n",
        "print(\n",
        "    (activities['elapsed_time']\n",
        "        .groupby('type').nlargest(4) / 60)\n",
        "        .to_string(float_format='%.1fh'))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "ijelhc4oPasQ",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Summarize by day and activity type\n",
        "import numpy as np\n",
        "\n",
        "activities_by_day_type = (\n",
        "    activities\n",
        "        .groupby([pd.Grouper(freq='D', level=0), 'type'])\n",
        "        .sum())\n",
        "\n",
        "activities_by_day_type = (\n",
        "    activities_by_day_type\n",
        "        .reset_index(level='type')\n",
        "        .to_period('D')\n",
        "        .reset_index()\n",
        "        .set_index(['date', 'type']))\n",
        "\n",
        "first_day = activities_by_day_type.index.levels[0].min()\n",
        "last_day = activities_by_day_type.index.levels[0].max()\n",
        "days = pd.period_range(first_day, last_day, freq='D')\n",
        "types = activities_by_day_type.index.levels[1].unique()\n",
        "index = pd.MultiIndex.from_product([days, types], names=['day', 'type'])\n",
        "activities_by_day_type = activities_by_day_type.reindex(index, fill_value=0)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "Nx8UebDLjCL-",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Print a few days\n",
        "print(activities_by_day_type.tail(12).to_string(formatters=formatters))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "zNOVoBwJRr3m",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Activities by day\n",
        "activities_by_day = activities_by_day_type.groupby('day').sum()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "Q_T5AAfURtmu",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Activities by type\n",
        "activities_by_type = activities_by_day_type.groupby('type').sum()"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "nn6KeWl6RB2e",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Daily average\n",
        "print(\n",
        "    activities_by_day[['distance', 'elapsed_time']]\n",
        "        .mean()\n",
        "        .to_frame('mean')\n",
        "        .T\n",
        "        .to_string(formatters=detailed_formatters))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "iy9HdX6zueNF",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Quick sanity check\n",
        "\n",
        "print(\"average distance per day: %.1fkm\" % (\n",
        "    activities['distance'].sum() /\n",
        "    (last_day - first_day)))\n",
        "print(\"average time per day: %.1fmin\" % (\n",
        "    activities['elapsed_time'].sum() /\n",
        "    (last_day - first_day)))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "V6BReJIW4Qmj",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Find days, months, quarters, years with extremes\n",
        "\n",
        "frequencies = ['D', 'M', 'Q', 'Y']\n",
        "\n",
        "for f in frequencies:\n",
        "  extremes_by_type = (\n",
        "    activities_by_day_type[['distance', 'count']]\n",
        "      .unstack('type')\n",
        "      .groupby(pd.Grouper(freq=f)).sum()\n",
        "      .agg(['idxmax', 'max']))\n",
        "  extremes_by_type.index = [f, 'max']\n",
        "\n",
        "  print(\n",
        "    extremes_by_type.T\n",
        "      .unstack(level=0)\n",
        "      .swaplevel(axis=1)\n",
        "      .sort_index(axis=1))\n",
        "  print('freq=%s\\n' % f)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "92bmpDsVtlzq",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Weekdays with largest means\n",
        "weekday_abbr = 'Mon Tue Wed Thu Fri Sat Sun'.split()\n",
        "print(\n",
        "    activities_by_day_type[['distance', 'elapsed_time', 'count']]\n",
        "        .unstack('type')\n",
        "        .groupby(lambda d: weekday_abbr[d.weekday]).mean()\n",
        "        .agg(['idxmax', 'max'])\n",
        "        .T.unstack(level=0)\n",
        "        .swaplevel(axis=1).sort_index(axis=1))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "Pf2M5xKB3yPS",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Months of the year with largest means\n",
        "months = 'Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec'.split()\n",
        "print(\n",
        "    activities_by_day_type[['distance', 'elapsed_time', 'count']]\n",
        "        .unstack('type')\n",
        "        .groupby(pd.Grouper(freq='M')).sum()\n",
        "        .groupby(lambda m: months[m.month-1]).mean()\n",
        "        .agg(['idxmax', 'max'])\n",
        "        .T.unstack(level=0)\n",
        "        .swaplevel(axis=1).sort_index(axis=1))"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "metadata": {
        "id": "E83KyhMcxit0",
        "colab_type": "code",
        "colab": {
          "autoexec": {
            "startup": false,
            "wait_interval": 0
          }
        },
        "cellView": "form"
      },
      "cell_type": "code",
      "source": [
        "#@title Quarters of the year with largest means\n",
        "print(\n",
        "    activities_by_day_type[['distance', 'elapsed_time', 'count']]\n",
        "        .unstack('type')\n",
        "        .groupby(pd.Grouper(freq='Q')).sum()\n",
        "        .groupby(lambda d: \"Q%d\" % d.quarter).mean()\n",
        "        .agg(['idxmax', 'max'])\n",
        "        .T.unstack(level=0)\n",
        "        .swaplevel(axis=1).sort_index(axis=1))"
      ],
      "execution_count": 0,
      "outputs": []
    }
  ]
}