{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Column Type Transforms\n",
        "Copyright (c) Microsoft Corporation. All rights reserved.<br>\n",
        "Licensed under the MIT License."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "When consuming a data set, it is highly useful to know as much as possible about the data. Column types can help you understand more about each column, and enable type-specific transformations later. This provides much more insight than treating all data as strings.\n",
        "\n",
        "In this notebook, you will learn about:\n",
        "- [Built-in column types](#types)\n",
        "- How to:\n",
        " - [Convert to long (integer)](#long)\n",
        " - [Convert to double (floating point or decimal number)](#double)\n",
        " - [Convert to boolean](#boolean)\n",
        " - [Convert to datetime](#datetime)\n",
        "- [How to use `ColumnTypesBuilder` to get suggested column types and convert them](#builder)\n",
        "- [How to convert column type for multiple columns if types are known](#multiple-columns)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Set up"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "import azureml.dataprep as dprep"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow = dprep.read_csv('../data/crime-winter.csv')\n",
        "dflow = dflow.keep_columns(['Case Number', 'Date', 'IUCR', 'Arrest', 'Longitude', 'Latitude'])"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<a id=\"types\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Built-in column types"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Currently, Data Prep supports the following column types: string, long (integer), double (floating point or decimal number), boolean, and datetime.\n",
        "\n",
        "In the previous step, a data set was read in as a Dataflow, with only a few interesting columns kept. We will use this Dataflow to explore column types throughout the notebook."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow.head(5)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "From the first few rows of the Dataflow, you can see that the columns contain different types of data. However, by looking at `dtypes`, you can see that `read_csv()` treats all columns as string columns.\n",
        "\n",
        "Note that `auto_read_file()` is a data ingestion function that infers column types. Learn more about it [here](./auto-read-file.ipynb)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow.dtypes"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<a id=\"long\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Converting to long (integer)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Suppose the \"IUCR\" column should only contain integers. You can call `to_long` to convert the column type of \"IUCR\" to `FieldType.INTEGER`. If you look at the data profile ([learn more about data profiles](./data-profile.ipynb)), you will see numeric metrics populated for that column such as mean, variance, quantiles, etc. This is helpful for understanding the shape and distribution of numeric data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow_conversion = dflow.to_long('IUCR')\n",
        "profile = dflow_conversion.get_profile()\n",
        "profile"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<a id=\"double\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Converting to double (floating point or decimal number)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Suppose the \"Latitude\" and \"Longitude\" columns should only contain decimal numbers. You can call `to_double` to convert the column type of \"Latitude\" and \"Longitude\" to `FieldType.DECIMAL`. In the data profile, you will see numeric metrics populated for these columns as well. Note that after converting the column types, you can see that there are missing values in these columns. Metrics like this can be helpful for noticing issues with the data set."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow_conversion = dflow_conversion.to_number(['Latitude', 'Longitude'])\n",
        "profile = dflow_conversion.get_profile()\n",
        "profile"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<a id=\"boolean\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "### Converting to boolean"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Suppose the \"Arrest\" column should only contain boolean values. You can call `to_bool` to convert the column type of \"Arrest\" to `FieldType.BOOLEAN`.\n",
        "\n",
        "The `to_bool` function allows you to specify which values should map to `True` and which values should map to `False`. To do so, you can provide those values in an array as parameters `true_values` and `false_values`. Additionally, you can specify whether all other values should become `True`, `False` or Error by using the `mismatch_as` parameter."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow_conversion.to_bool('Arrest', \n",
        "                         true_values=[1],\n",
        "                         false_values=[0],\n",
        "                         mismatch_as=dprep.MismatchAsOption.ASERROR).head(5)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "In the previous conversion, all the values in the \"Arrest\" column became `DataPrepError`, because 'FALSE' didn't match any of the `false_values` nor any of the `true_values`, and all the unmatched values were set to become errors. Let's try the conversion again with different `false_values`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow_conversion = dflow_conversion.to_bool('Arrest',\n",
        "                                            true_values=['1', 'TRUE'],\n",
        "                                            false_values=['0', 'FALSE'],\n",
        "                                            mismatch_as=dprep.MismatchAsOption.ASERROR)\n",
        "dflow_conversion.head(5)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "This time, all the string values 'FALSE' have been successfully converted to the boolean value `False`. Take another look at the data profile."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "profile = dflow_conversion.get_profile()\n",
        "profile"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<a id=\"datetime\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Suppose the \"Date\" column should only contain datetime values. You can convert its column type to `FieldType.DateTime` using the `to_datetime` function. Typically, datetime formats can be confusing or inconsistent. Next, we will show you all the tools that can help correctly converting the column to `DateTime`."
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "In the first example, directly call `to_datetime` with only the column name. Data Prep will inspect the data in this column and learn what format should be used for the conversion.\n",
        "\n",
        "Note that if there is data in the column that cannot be converted to datetime, an Error value will be created in that cell."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow_conversion_date = dflow_conversion.to_datetime('Date')\n",
        "dflow_conversion_date.head(5)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "In this case, we can see that '1/10/2016 11:00' was converted using the format `%m/%d/%Y %H:%M`.\n",
        "\n",
        "The data in this column is actually somewhat ambiguous. Should the dates be 'October 1' or 'January 10'? The function `to_datetime` determines that both are possible, but defaults to month-first (US format).\n",
        "\n",
        "If the data was supposed to be day-first, you can customize the conversion."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow_alternate_conversion = dflow_conversion.to_datetime('Date', date_time_formats=['%d/%m/%Y %H:%M'])\n",
        "dflow_alternate_conversion.head(5)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<a id=\"builder\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Using `ColumnTypesBuilder`"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Data Prep can help you automatically detect what are the likely column types.\n",
        "\n",
        "You can call `dflow.builders.set_column_types()` to get a `ColumnTypesBuilder`. Then, calling `learn()` on it will trigger Data Prep to inspect the data in each column. As a result, you can see the suggested column types for each column (conversion candidates)."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "builder = dflow.builders.set_column_types()\n",
        "builder.learn()\n",
        "builder"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "In this case, Data Prep suggested the correct column types for \"Arrest\", \"Case Number\", \"Latitude\", and \"Longitude\".\n",
        "\n",
        "However, for \"Date\", it has suggested two possible date formats: month-first, or day-first. The ambiguity must be resolved before you complete the conversion. To use the month-first format, you can call `builder.ambiguous_date_conversions_keep_month_day()`. Otherwise, call `builder.ambiguous_date_conversions_keep_day_month()`. Note that if there were multiple datetime columns with ambiguous date conversions, calling one of these functions will apply the resolution to all of them.\n",
        "\n",
        "If you want to skip all the ambiguous date column conversions instead, you can call: `builder.ambiguous_date_conversions_drop()`"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "builder.ambiguous_date_conversions_keep_month_day()\n",
        "builder.conversion_candidates"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "The conversion candidate for \"IUCR\" is currently `FieldType.INTEGER`. If you know that \"IUCR\" should be floating point (called `FieldType.DECIMAL`), you can tweak the builder to change the conversion candidate for that specific column. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "builder.conversion_candidates['IUCR'] = dprep.FieldType.DECIMAL\n",
        "builder"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "In this case we are happy with \"IUCR\" as `FieldType.INTEGER`. So we set it back. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "builder.conversion_candidates['IUCR'] = dprep.FieldType.INTEGER\n",
        "builder"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "Once you are happy with the conversion candidates, you can complete the conversion by calling `builder.to_dataflow()`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow_converion_using_builder = builder.to_dataflow()\n",
        "dflow_converion_using_builder.head(5)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "<a id=\"multiple-columns\"></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Convert column types for multiple columns"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "If you already know the column types, you can simply call `dflow.set_column_types()`. This function allows you to specify multiple columns, and the desired column type for each one. Here's how you can convert all five columns at once.\n",
        "\n",
        "Note that `set_column_types` only supports a subset of column type conversions. For example, we cannot specify the true/false values for a boolean conversion, so the results of this operation is incorrect for the \"Arrest\" column."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {},
      "outputs": [],
      "source": [
        "dflow_conversion_using_set = dflow.set_column_types({\n",
        "    'IUCR': dprep.FieldType.INTEGER,\n",
        "    'Latitude': dprep.FieldType.DECIMAL,\n",
        "    'Longitude': dprep.FieldType.DECIMAL,\n",
        "    'Arrest': dprep.FieldType.BOOLEAN,\n",
        "    'Date': (dprep.FieldType.DATE, ['%m/%d/%Y %H:%M']),\n",
        "})\n",
        "dflow_conversion_using_set.head(5)"
      ]
    }
  ],
  "metadata": {
    "authors": [
      {
        "name": "sihhu"
      }
    ],
    "kernelspec": {
      "display_name": "Python 3.6",
      "language": "python",
      "name": "python36"
    },
    "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.4"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2
}