{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "![Impressions](https://PixelServer20190423114238.azurewebsites.net/api/impressions/MachineLearningNotebooks/work-with-data/dataprep/how-to-guides/auto-read-file.png)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "# Auto Read File\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import azureml.dataprep as dprep" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Data Prep has the ability to load different kinds of text files. The `auto_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", "\n", "The 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." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dflow_auto = dprep.auto_read_file('../data/crime_multiple_separators.csv')\n", "dflow_auto.head(5)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dflow_auto1 = dprep.auto_read_file('../data/crime.xlsx')\n", "dflow_auto1.head(5)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dflow_auto2 = dprep.auto_read_file('../data/crime.parquet')\n", "dflow_auto2.head(5)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Looking at the data, we can see that there are two empty columns either side of the 'Completed' column.\n", "If we compare the dataframe to a few rows from the original file:\n", "```\n", "ID |CaseNumber| |Completed|\n", "10140490 |HY329907| |Y|\n", "10139776 |HY329265| |Y|\n", "```\n", "We can see that the `|`'s have disappeared in the dataframe. This is because `|` is a very common separator character in csv files, so `auto_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", "\n", "To achieve this we can use `detect_file_format`. It takes a file path or datasource object and gives back a `FileFormatBuilder` which has learnt some information about the supplied data.\n", "This is what `auto_read_file` 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 `auto_read_file` but have the chance to modify some of the learnt information." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "ffb = dprep.detect_file_format('../data/crime_multiple_separators.csv')\n", "ffb_2 = dprep.detect_file_format('../data/crime.xlsx')\n", "ffb_3 = dprep.detect_file_format('../data/crime_fixed_width_file.txt')\n", "ffb_4 = dprep.detect_file_format('../data/json.json')\n", "\n", "print(ffb.file_format)\n", "print(ffb_2.file_format)\n", "print(ffb_3.file_format)\n", "print(type(ffb_4.file_format))" ] }, { "cell_type": "markdown", "metadata": {}, "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 `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. \n", "Continuing 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." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "ffb.file_format.separator = ' '\n", "dflow = ffb.to_dataflow()\n", "df = dflow.to_pandas_dataframe()\n", "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "The result is our desired dataframe with `|`'s included.\n", "\n", "If we refer back to the original data output by `auto_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.\n", "We 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`." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "ctb = dflow.builders.set_column_types()\n", "ctb.learn()\n", "ctb.conversion_candidates" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "After learning `ctb.conversion_candidates` 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", "\n", "The candidates look correct, we only want to convert `ID` to be an integer column, so applying this `ColumnTypesBuilder` should result in a Dataflow with our columns converted to their respective types." ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dflow_converted = ctb.to_dataflow()\n", "\n", "df_converted = dflow_converted.to_pandas_dataframe()\n", "df_converted" ] } ], "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" }, "notice": "Copyright (c) Microsoft Corporation. All rights reserved. Licensed under the MIT License." }, "nbformat": 4, "nbformat_minor": 2 }