smart-read-file-separators.ipynb

{
  "cells": [
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "# Smart Read File\nCopyright (c) Microsoft Corporation. All rights reserved.<br>\nLicensed under the MIT License."
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "!pip install azureml",
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": "Requirement already satisfied: azureml in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (0.2.7)\nRequirement already satisfied: python-dateutil in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from azureml) (2.7.3)\nRequirement already satisfied: requests in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from azureml) (2.19.1)\nRequirement already satisfied: pandas in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from azureml) (0.22.0)\nRequirement already satisfied: six>=1.5 in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from python-dateutil->azureml) (1.11.0)\nRequirement already satisfied: urllib3<1.24,>=1.21.1 in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from requests->azureml) (1.23)\nRequirement already satisfied: idna<2.8,>=2.5 in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from requests->azureml) (2.7)\nRequirement already satisfied: certifi>=2017.4.17 in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from requests->azureml) (2018.8.24)\nRequirement already satisfied: chardet<3.1.0,>=3.0.2 in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from requests->azureml) (3.0.4)\nRequirement already satisfied: pytz>=2011k in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from pandas->azureml) (2018.5)\nRequirement already satisfied: numpy>=1.9.0 in /home/nbuser/anaconda3_501/lib/python3.6/site-packages (from pandas->azureml) (1.14.5)\n",
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "import azureml.dataprep as dprep",
      "execution_count": 2,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "DataPrep has the ability to load different kinds of text files. The `smart_read_file` entry point can take any text based file (including excel, json and parquet) and auto-detect how to parse the file. It will also attempt to auto-detect the types of each column and apply type transformations to the columns it detects.\n\nThe result will be a Dataflow object that has all the steps added that are required to read the given file(s) and convert their columns to the predicted types. No parameters are required beyond the file path or `FileDataSource` object."
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "smart_dataflow = dprep.smart_read_file('./data/multiple_separators.csv')\nsmart_dataflow.head(10)",
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 3,
          "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>CaseNumber</th>\n      <th>Column3</th>\n      <th>Completed</th>\n      <th>Column5</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>10140490.0</td>\n      <td>HY329907</td>\n      <td></td>\n      <td>Y</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10139776.0</td>\n      <td>HY329265</td>\n      <td></td>\n      <td>Y</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10140270.0</td>\n      <td>HY329253</td>\n      <td></td>\n      <td>N</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>10139885.0</td>\n      <td>HY329308</td>\n      <td></td>\n      <td>Y</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>10140379.0</td>\n      <td>HY329556</td>\n      <td></td>\n      <td>N</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>10140868.0</td>\n      <td>HY330421</td>\n      <td></td>\n      <td>N</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>10139762.0</td>\n      <td>HY329232</td>\n      <td></td>\n      <td>N</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>10139722.0</td>\n      <td>HY329228</td>\n      <td></td>\n      <td>Y</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>10139774.0</td>\n      <td>HY329209</td>\n      <td></td>\n      <td>N</td>\n      <td></td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>10139697.0</td>\n      <td>HY329177</td>\n      <td></td>\n      <td>N</td>\n      <td></td>\n    </tr>\n  </tbody>\n</table>\n</div>",
            "text/plain": "           ID CaseNumber Column3 Completed Column5\n0  10140490.0   HY329907                 Y        \n1  10139776.0   HY329265                 Y        \n2  10140270.0   HY329253                 N        \n3  10139885.0   HY329308                 Y        \n4  10140379.0   HY329556                 N        \n5  10140868.0   HY330421                 N        \n6  10139762.0   HY329232                 N        \n7  10139722.0   HY329228                 Y        \n8  10139774.0   HY329209                 N        \n9  10139697.0   HY329177                 N        "
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "Looking at the data, we can see that there are two empty columns either side of the 'Completed' column.\nIf we compare the dataframe to a few rows from the original file:\n```\nID |CaseNumber| |Completed|\n10140490 |HY329907| |Y|\n10139776 |HY329265| |Y|\n```\nWe can see that the `|`'s have disappeared in the dataframe. This is because `|` is a very common separator character in csv files, so `smart_read_file` guessed it was the column separator. For this data we actually want the `|`'s to remain and instead use space as the column separator.\n\nTo acheive this we can use `detect_file_format` which will take a file path or datasource obeject and give back a `FileFormatBuilder` which has learnt some information about the supplied data.\nThis is what `smart_file_read` is using behind the scenes to 'learn' the contents of the given file and determine how to parse it. With the `FileFormatBuilder` we can take advantage of the intelligent learning aspect of `smart_file_read` but have the chance to modify some of the learnt information."
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "ffb = dprep.detect_file_format('./data/multiple_separators.csv')\nffb_2 = dprep.detect_file_format('./data/excel.xlsx')\nffb_3 = dprep.detect_file_format('./data/fixed_width_file.txt')\nffb_4 = dprep.detect_file_format('./data/json.json')\n\nprint(ffb.file_format)\nprint(ffb_2.file_format)\nprint(ffb_3.file_format)\nprint(type(ffb_4.file_format))",
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "text": "ParseDelimitedProperties\n    separator: '|'\n    headers_mode: PromoteHeadersMode.CONSTANTGROUPED\n    encoding: FileEncoding.UTF8\n    quoting: False\n    skip_rows: 0\n    skip_mode: SkipMode.NONE\n    comment: None\n\nReadExcelProperties\n    sheet_name: None\n    use_headers: False\n    skip_rows: 0\n\nParseFixedWidthProperties\n    offsets: '[7, 13, 43, 46, 52, 58, 65, 73]'\n    headers_mode: PromoteHeadersMode.NONE\n    encoding: FileEncoding.UTF8\n    skip_rows: 0\n    skip_mode: SkipMode.NONE\n\n<class 'azureml.dataprep.api.parseproperties.ReadJsonProperties'>\n",
          "name": "stdout"
        }
      ]
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "After calling `detect_file_format` we get a `FileFormatBuilder` that has had `learn` called on it. This means the `file_format` attribute will be populated with a `<Parse|Read><type>Properties` object, it contains all the information that was learnt about the file. As we can see above different file types have corresponding file_formats detected. \nContinuing with our delimited example we can change any of these values and then call `ffb.to_dataflow()` to create a `Dataflow` that has the steps required to parse the datasource."
    },
    {
      "metadata": {
        "scrolled": true,
        "trusted": true
      },
      "cell_type": "code",
      "source": "ffb.file_format.separator = ' '\ndataflow = ffb.to_dataflow()\ndf = dataflow.to_pandas_dataframe()\ndf",
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 5,
          "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>|CaseNumber|</th>\n      <th>|Completed|</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>10140490</td>\n      <td>|HY329907|</td>\n      <td>|Y|</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10139776</td>\n      <td>|HY329265|</td>\n      <td>|Y|</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10140270</td>\n      <td>|HY329253|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>10139885</td>\n      <td>|HY329308|</td>\n      <td>|Y|</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>10140379</td>\n      <td>|HY329556|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>10140868</td>\n      <td>|HY330421|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>10139762</td>\n      <td>|HY329232|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>10139722</td>\n      <td>|HY329228|</td>\n      <td>|Y|</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>10139774</td>\n      <td>|HY329209|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>10139697</td>\n      <td>|HY329177|</td>\n      <td>|N|</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
            "text/plain": "         ID |CaseNumber| |Completed|\n0  10140490   |HY329907|         |Y|\n1  10139776   |HY329265|         |Y|\n2  10140270   |HY329253|         |N|\n3  10139885   |HY329308|         |Y|\n4  10140379   |HY329556|         |N|\n5  10140868   |HY330421|         |N|\n6  10139762   |HY329232|         |N|\n7  10139722   |HY329228|         |Y|\n8  10139774   |HY329209|         |N|\n9  10139697   |HY329177|         |N|"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "The result is our desired dataframe with `|`'s included.\n\nIf we refer back to the original data output by `smart_read_file` the 'ID' column was also detected as numeric and converted to a number data type, instead of remaining a string like in the data above.\nWe can perform type inference on our new dataflow using the `dataflow.builders` property. This property exposes different builders that can `learn` from a dataflow and `apply` the learning to produce a new dataflow, very similar to the pattern we used above for the `FileFormatBuilder`."
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "ctb = dataflow.builders.set_column_types()\nctb.learn()\nctb.inference_info",
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 6,
          "data": {
            "text/plain": "{'|CaseNumber|': [FieldType.STRING],\n '|Completed|': [FieldType.STRING],\n 'ID': [FieldType.DECIMAL]}"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "After learning `ctb.inference_info` has been populated with information about the inferred types for each column, it is possible for there to be multiple candidate types per column, in this example there is only one type for each column.\n\nThe candidates look correct, we only want to convert `ID` to be a number column (also known as `DECIMAL`), so applying this `ColumnTypesBuilder` should result in a Dataflow with our columns converted to their respective types."
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "converted_dataflow = ctb.to_dataflow()\nconverted_df = converted_dataflow.to_pandas_dataframe()\nconverted_df",
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 7,
          "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>|CaseNumber|</th>\n      <th>|Completed|</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>10140490.0</td>\n      <td>|HY329907|</td>\n      <td>|Y|</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>10139776.0</td>\n      <td>|HY329265|</td>\n      <td>|Y|</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>10140270.0</td>\n      <td>|HY329253|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>10139885.0</td>\n      <td>|HY329308|</td>\n      <td>|Y|</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>10140379.0</td>\n      <td>|HY329556|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>5</th>\n      <td>10140868.0</td>\n      <td>|HY330421|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>6</th>\n      <td>10139762.0</td>\n      <td>|HY329232|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>7</th>\n      <td>10139722.0</td>\n      <td>|HY329228|</td>\n      <td>|Y|</td>\n    </tr>\n    <tr>\n      <th>8</th>\n      <td>10139774.0</td>\n      <td>|HY329209|</td>\n      <td>|N|</td>\n    </tr>\n    <tr>\n      <th>9</th>\n      <td>10139697.0</td>\n      <td>|HY329177|</td>\n      <td>|N|</td>\n    </tr>\n  </tbody>\n</table>\n</div>",
            "text/plain": "           ID |CaseNumber| |Completed|\n0  10140490.0   |HY329907|         |Y|\n1  10139776.0   |HY329265|         |Y|\n2  10140270.0   |HY329253|         |N|\n3  10139885.0   |HY329308|         |Y|\n4  10140379.0   |HY329556|         |N|\n5  10140868.0   |HY330421|         |N|\n6  10139762.0   |HY329232|         |N|\n7  10139722.0   |HY329228|         |Y|\n8  10139774.0   |HY329209|         |N|\n9  10139697.0   |HY329177|         |N|"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python36",
      "display_name": "Python 3.6",
      "language": "python"
    },
    "language_info": {
      "mimetype": "text/x-python",
      "nbconvert_exporter": "python",
      "name": "python",
      "pygments_lexer": "ipython3",
      "version": "3.6.6",
      "file_extension": ".py",
      "codemirror_mode": {
        "version": 3,
        "name": "ipython"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2
}