{
  "nbformat": 4,
  "nbformat_minor": 0,
  "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.7.3"
    },
    "colab": {
      "name": "Programming Languages (start).ipynb",
      "provenance": []
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "MAAKxOwsGxuj",
        "colab_type": "text"
      },
      "source": [
        "## Get the Data\n",
        "\n",
        "Either use the provided .csv file or (optionally) get fresh (the freshest?) data from running an SQL query on StackExchange: \n",
        "\n",
        "Follow this link to run the query from [StackExchange](https://data.stackexchange.com/stackoverflow/query/675441/popular-programming-languages-per-over-time-eversql-com) to get your own .csv file\n",
        "\n",
        "<code>\n",
        "select dateadd(month, datediff(month, 0, q.CreationDate), 0) m, TagName, count(*)\n",
        "from PostTags pt\n",
        "join Posts q on q.Id=pt.PostId\n",
        "join Tags t on t.Id=pt.TagId\n",
        "where TagName in ('java','c','c++','python','c#','javascript','assembly','php','perl','ruby','visual basic','swift','r','object-c','scratch','go','swift','delphi')\n",
        "and q.CreationDate < dateadd(month, datediff(month, 0, getdate()), 0)\n",
        "group by dateadd(month, datediff(month, 0, q.CreationDate), 0), TagName\n",
        "order by dateadd(month, datediff(month, 0, q.CreationDate), 0)\n",
        "</code>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "u5KcSXt1Gxuk",
        "colab_type": "text"
      },
      "source": [
        "## Import Statements"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Ru4Wq-pXGxuk",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "xEP6beuEGxun",
        "colab_type": "text"
      },
      "source": [
        "## Data Exploration"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "w3Q75B4CGxun",
        "colab_type": "text"
      },
      "source": [
        "**Challenge**: Read the .csv file and store it in a Pandas dataframe"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Bm7hQtEGIiri",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "x2WnDM75Gxup",
        "colab_type": "text"
      },
      "source": [
        "**Challenge**: Examine the first 5 rows and the last 5 rows of the of the dataframe"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "50oqpUxVIiJf",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "0o9hvVgyGxus",
        "colab_type": "text"
      },
      "source": [
        "**Challenge:** Check how many rows and how many columns there are. \n",
        "What are the dimensions of the dataframe?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZUidjCPFIho8",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ybZkNLmxGxuu",
        "colab_type": "text"
      },
      "source": [
        "**Challenge**: Count the number of entries in each column of the dataframe"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Sc1dmmOoIg2g",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hlnfFsscGxuw",
        "colab_type": "text"
      },
      "source": [
        "**Challenge**: Calculate the total number of post per language.\n",
        "Which Programming language has had the highest total number of posts of all time?"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "9-NYFONcIc1X",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 4,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "iVCesB49Gxuz",
        "colab_type": "text"
      },
      "source": [
        "Some languages are older (e.g., C) and other languages are newer (e.g., Swift). The dataset starts in September 2008.\n",
        "\n",
        "**Challenge**: How many months of data exist per language? Which language had the fewest months with an entry? \n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "hDT4JlJNJfgQ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "arguGp3ZGxu1",
        "colab_type": "text"
      },
      "source": [
        "## Data Cleaning\n",
        "\n",
        "Let's fix the date format to make it more readable. We need to use Pandas to change format from a string of \"2008-07-01 00:00:00\" to a datetime object with the format of \"2008-07-01\""
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "5nh5a4UtGxu1",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "016H-Fy4Gxu3",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "4EiSd7pdGxu5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "rWAV6tuzGxu6",
        "colab_type": "text"
      },
      "source": [
        "## Data Manipulation\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aHhbulJaGxu7",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "RWKcVIyFKwHM",
        "colab_type": "text"
      },
      "source": [
        "**Challenge**: What are the dimensions of our new dataframe? How many rows and columns does it have? Print out the column names and print out the first 5 rows of the dataframe."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "v-u4FcLXGxu9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "NUyBcaMMGxu-",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LnUIOL3LGxvA",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BoDCuRU0GxvC",
        "colab_type": "text"
      },
      "source": [
        "**Challenge**: Count the number of entries per programming language. Why might the number of entries be different? "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-peEFgaMGxvE",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "01f2BCF8GxvG",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KooRRxAdGxvI",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8xU7l_f4GxvK",
        "colab_type": "text"
      },
      "source": [
        "## Data Visualisaton with with Matplotlib\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "njnNXTlhGxvK",
        "colab_type": "text"
      },
      "source": [
        "**Challenge**: Use the [matplotlib documentation](https://matplotlib.org/3.2.1/api/_as_gen/matplotlib.pyplot.plot.html#matplotlib.pyplot.plot) to plot a single programming language (e.g., java) on a chart."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "S0OS8T8iGxvL",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "EU6AV1l9GxvM",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "_Qzzg6b_GxvO",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Sm2DL5tZGxvQ",
        "colab_type": "text"
      },
      "source": [
        "**Challenge**: Show two line (e.g. for Java and Python) on the same chart."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "T-0vClQSGxvQ",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": 5,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "3jSjfPy7GxvY",
        "colab_type": "text"
      },
      "source": [
        "# Smoothing out Time Series Data\n",
        "\n",
        "Time series data can be quite noisy, with a lot of up and down spikes. To better see a trend we can plot an average of, say 6 or 12 observations. This is called the rolling mean. We calculate the average in a window of time and move it forward by one overservation. Pandas has two handy methods already built in to work this out: [rolling()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rolling.html) and [mean()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.core.window.rolling.Rolling.mean.html). "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "s3WYd3OgGxvc",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "WMJOX8Y2Gxvd",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "fAvvarA7Gxvf",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "Gm0Ww0S4Gxvg",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        ""
      ],
      "execution_count": null,
      "outputs": []
    }
  ]
}