TabularDatasetFactory Class

Contains methods to create a tabular dataset for Azure Machine Learning.

A TabularDataset is created using the from_* methods in this class, for example, the method azureml.data.dataset_factory.TabularDatasetFactory.from_delimited_files.

For more information on working with tabular datasets, see the notebook https://aka.ms/tabulardataset-samplenotebook.

Inheritance
TabularDatasetFactory

Methods

from_delimited_files

Create a TabularDataset to represent tabular data in delimited files (e.g. CSV and TSV).

from_json_lines_files

Create a TabularDataset to represent tabular data in JSON Lines files (http://jsonlines.org/).

from_parquet_files

Create a TabularDataset to represent tabular data in Parquet files.

from_sql_query

Create a TabularDataset to represent tabular data in SQL databases.

register_pandas_dataframe

Note

This is an experimental method, and may change at any time.
For more information, see https://aka.ms/azuremlexperimental.

Create a dataset from pandas dataframe.

register_spark_dataframe

Note

This is an experimental method, and may change at any time.
For more information, see https://aka.ms/azuremlexperimental.

Create a dataset from spark dataframe.

from_delimited_files

Create a TabularDataset to represent tabular data in delimited files (e.g. CSV and TSV).

from_delimited_files(path, validate=True, include_path=False, infer_column_types=True, set_column_types=None, separator=',', header=True, partition_format=None, support_multi_line=False, empty_as_string=False, encoding='utf8')

Parameters

path
str, list[str]<xref:,azureml.data.datapath.DataPath>, list[DataPath]<xref:,>(Datastore, str)<xref:,> or list[(Datastore, str)]

The path to the source files, which can be single value or list of http url string, DataPath object, or tuple of Datastore and relative path.

validate
bool

Boolean to validate if data can be loaded from the returned dataset. Defaults to True. Validation requires that the data source is accessible from the current compute.

include_path
bool

Boolean to keep path information as column in the dataset. Defaults to False. This is useful when reading multiple files, and want to know which file a particular record originated from, or to keep useful information in file path.

infer_column_types
bool

Boolean to infer column data types. Defaults to True. Type inference requires that the data source is accessible from current compute.

set_column_types
dict[(str, DataType)]

A dictionary to set column data type, where key is column name and value is DataType.

separator
str

The separator used to split columns.

header
bool or PromoteHeadersBehavior

Controls how column headers are promoted when reading from files. Defaults to True for all files having the same header. Files will read as having no header When header=False. More options can be specified using enum value of PromoteHeadersBehavior.

partition_format
str

Specify the partition format of path. Defaults to None. The partition information of each path will be extracted into columns based on the specified format. Format part '{column_name}' creates string column, and '{column_name:yyyy/MM/dd/HH/mm/ss}' creates datetime column, where 'yyyy', 'MM', 'dd', 'HH', 'mm' and 'ss' are used to extract year, month, day, hour, minute and second for the datetime type. The format should start from the position of first partition key until the end of file path. For example, given the path '../Accounts/2019/01/01/data.csv' where the partition is by department name and time, partition_format='/{Department}/{PartitionDate:yyyy/MM/dd}/data.csv' creates a string column 'Department' with the value 'Accounts' and a datetime column 'PartitionDate' with the value '2019-01-01'.

support_multi_line
bool

By default (support_multi_line=False), all line breaks, including those in quoted field values, will be interpreted as a record break. Reading data this way is faster and more optimized for parallel execution on multiple CPU cores. However, it may result in silently producing more records with misaligned field values. This should be set to True when the delimited files are known to contain quoted line breaks.

Given this csv file as example, the data will be read differently based on support_multi_line.

A,B,C A1,B1,C1 A2,"B 2",C2


   from azureml.core import Dataset, Datastore
   from azureml.data.datapath import DataPath

   # default behavior: support_multi_line=False
   dataset = Dataset.Tabular.from_delimited_files(path=datastore_path)
   print(dataset.to_pandas_dataframe())
   #      A   B     C
   #  0  A1  B1    C1
   #  1  A2   B  None
   #  2  2"  C2  None

   # to handle quoted line breaks
   dataset = Dataset.Tabular.from_delimited_files(path=datastore_path,
                                                  support_multi_line=True)
   print(dataset.to_pandas_dataframe())
   #      A       B   C
   #  0  A1      B1  C1
   #  1  A2  B\r\n2  C2
empty_as_string
bool, <xref:optional>

Specify if empty field values should be loaded as empty strings. The default (False) will read empty field values as nulls. Passing this as True will read empty field values as empty strings. If the values are converted to numeric or datetime then this has no effect, as empty values will be converted to nulls.

encoding
str

Specify the file encoding. Supported encodings are 'utf8', 'iso88591', 'latin1', 'ascii', 'utf16', 'utf32', 'utf8bom' and 'windows1252'

Returns

Returns a TabularDataset object.

Return type

Remarks

from_delimited_files creates an object of TabularDataset class, which defines the operations to load data from delimited files into tabular representation.

For the data to be accessible by Azure Machine Learning, the delimited files specified by path must be located in Datastore or behind public web urls.

Column data types are by default inferred from data in the delimited files. Providing set_column_types will override the data type for the specified columns in the returned TabularDataset.


   from azureml.core import Dataset, Datastore
   from azureml.data.datapath import DataPath

   # create tabular dataset from delimited files in datastore
   datastore = Datastore.get(workspace, 'workspaceblobstore')
   datastore_path = [
       DataPath(datastore, 'weather/2018/11.csv'),
       DataPath(datastore, 'weather/2018/12.csv'),
       DataPath(datastore, 'weather/2019/*.csv')
   ]
   tabular = Dataset.Tabular.from_delimited_files(path=datastore_path)

   # create tabular dataset from delimited files behind public web urls.
   web_path = [
       'https://url/datafile1.tsv',
       'https://url/datafile2.tsv'
   ]
   tabular = Dataset.Tabular.from_delimited_files(path=web_path, separator='\t')

   # use `set_column_types` to set column data types
   from azureml.data import DataType
   data_types = {
       'ID': DataType.to_string(),
       'Date': DataType.to_datetime('%d/%m/%Y %I:%M:%S %p'),
       'Count': DataType.to_long(),
       'Latitude': DataType.to_float(),
       'Found': DataType.to_bool()
   }
   tabular = Dataset.Tabular.from_delimited_files(path=web_path, set_column_types=data_types)

from_json_lines_files

Create a TabularDataset to represent tabular data in JSON Lines files (http://jsonlines.org/).

from_json_lines_files(path, validate=True, include_path=False, set_column_types=None, partition_format=None, invalid_lines='error', encoding='utf8')

Parameters

path
str, list[str]<xref:,azureml.data.datapath.DataPath>, list[DataPath]<xref:,>(Datastore, str)<xref:,> or list[(Datastore, str)]

The path to the source files, which can be single value or list of http url string, DataPath object, or tuple of Datastore and relative path.

validate
bool

Boolean to validate if data can be loaded from the returned dataset. Defaults to True. Validation requires that the data source is accessible from the current compute.

include_path
bool

Boolean to keep path information as column in the dataset. Defaults to False. This is useful when reading multiple files, and want to know which file a particular record originated from, or to keep useful information in file path.

set_column_types
dict[(str, DataType)]

A dictionary to set column data type, where key is column name and value is DataType

partition_format
str

Specify the partition format of path. Defaults to None. The partition information of each path will be extracted into columns based on the specified format. Format part '{column_name}' creates string column, and '{column_name:yyyy/MM/dd/HH/mm/ss}' creates datetime column, where 'yyyy', 'MM', 'dd', 'HH', 'mm' and 'ss' are used to extract year, month, day, hour, minute and second for the datetime type. The format should start from the position of first partition key until the end of file path. For example, given the path '../Accounts/2019/01/01/data.jsonl' where the partition is by department name and time, partition_format='/{Department}/{PartitionDate:yyyy/MM/dd}/data.jsonl' creates a string column 'Department' with the value 'Accounts' and a datetime column 'PartitionDate' with the value '2019-01-01'.

invalid_lines
str

How to handle lines that are invalid JSON. Supported values are 'error' and 'drop'.

encoding
str

Specify the file encoding. Supported encodings are 'utf8', 'iso88591', 'latin1', 'ascii', 'utf16', 'utf32', 'utf8bom' and 'windows1252'

Returns

Returns a TabularDataset object.

Return type

Remarks

from_json_lines_files creates an object of TabularDataset class, which defines the operations to load data from JSON Lines files into tabular representation.

For the data to be accessible by Azure Machine Learning, the JSON Lines files specified by path must be located in Datastore or behind public web urls.

Column data types are read from data types saved in the JSON Lines files. Providing set_column_types will override the data type for the specified columns in the returned TabularDataset.


   from azureml.core import Dataset, Datastore
   from azureml.data.datapath import DataPath

   # create tabular dataset from JSON Lines files in datastore
   datastore = Datastore.get(workspace, 'workspaceblobstore')
   datastore_path = [
       DataPath(datastore, 'weather/2018/11.jsonl'),
       DataPath(datastore, 'weather/2018/12.jsonl'),
       DataPath(datastore, 'weather/2019/*.jsonl')
   ]
   tabular = Dataset.Tabular.from_json_lines_files(path=datastore_path)

   # create tabular dataset from JSON Lines files behind public web urls.
   web_path = [
       'https://url/datafile1.jsonl',
       'https://url/datafile2.jsonl'
   ]
   tabular = Dataset.Tabular.from_json_lines_files(path=web_path)

   # use `set_column_types` to set column data types
   from azureml.data import DataType
   data_types = {
       'ID': DataType.to_string(),
       'Date': DataType.to_datetime('%d/%m/%Y %I:%M:%S %p'),
       'Count': DataType.to_long(),
       'Latitude': DataType.to_float(),
       'Found': DataType.to_bool()
   }
   tabular = Dataset.Tabular.from_json_lines_files(path=web_path, set_column_types=data_types)

from_parquet_files

Create a TabularDataset to represent tabular data in Parquet files.

from_parquet_files(path, validate=True, include_path=False, set_column_types=None, partition_format=None)

Parameters

path
str, list[str]<xref:,azureml.data.datapath.DataPath>, list[DataPath]<xref:,>(Datastore, str)<xref:,> or list[(Datastore, str)]

The path to the source files, which can be single value or list of http url string, DataPath object, or tuple of Datastore and relative path.

validate
bool

Boolean to validate if data can be loaded from the returned dataset. Defaults to True. Validation requires that the data source is accessible from the current compute.

include_path
bool

Boolean to keep path information as column in the dataset. Defaults to False. This is useful when reading multiple files, and want to know which file a particular record originated from, or to keep useful information in file path.

set_column_types
dict[(str, DataType)]

A dictionary to set column data type, where key is column name and value is DataType.

partition_format
str

Specify the partition format of path. Defaults to None. The partition information of each path will be extracted into columns based on the specified format. Format part '{column_name}' creates string column, and '{column_name:yyyy/MM/dd/HH/mm/ss}' creates datetime column, where 'yyyy', 'MM', 'dd', 'HH', 'mm' and 'ss' are used to extract year, month, day, hour, minute and second for the datetime type. The format should start from the position of first partition key until the end of file path. For example, given the path '../Accounts/2019/01/01/data.parquet' where the partition is by department name and time, partition_format='/{Department}/{PartitionDate:yyyy/MM/dd}/data.parquet' creates a string column 'Department' with the value 'Accounts' and a datetime column 'PartitionDate' with the value '2019-01-01'.

Returns

Returns a TabularDataset object.

Return type

Remarks

from_parquet_files creates an object of TabularDataset class, which defines the operations to load data from Parquet files into tabular representation.

For the data to be accessible by Azure Machine Learning, the Parquet files specified by path must be located in Datastore or behind public web urls.

Column data types are read from data types saved in the Parquet files. Providing set_column_types will override the data type for the specified columns in the returned TabularDataset.


   from azureml.core import Dataset, Datastore
   from azureml.data.datapath import DataPath

   # create tabular dataset from Parquet files in datastore
   datastore = Datastore.get(workspace, 'workspaceblobstore')
   datastore_path = [
       DataPath(datastore, 'weather/2018/11.parquet'),
       DataPath(datastore, 'weather/2018/12.parquet'),
       DataPath(datastore, 'weather/2019/*.parquet')
   ]
   tabular = Dataset.Tabular.from_parquet_files(path=datastore_path)

   # create tabular dataset from Parquet files behind public web urls.
   web_path = [
       'https://url/datafile1.parquet',
       'https://url/datafile2.parquet'
   ]
   tabular = Dataset.Tabular.from_parquet_files(path=web_path)

   # use `set_column_types` to set column data types
   from azureml.data import DataType
   data_types = {
       'ID': DataType.to_string(),
       'Date': DataType.to_datetime('%d/%m/%Y %I:%M:%S %p'),
       'Count': DataType.to_long(),
       'Latitude': DataType.to_float(),
       'Found': DataType.to_bool()
   }
   tabular = Dataset.Tabular.from_parquet_files(path=web_path, set_column_types=data_types)

from_sql_query

Create a TabularDataset to represent tabular data in SQL databases.

from_sql_query(query, validate=True, set_column_types=None, query_timeout=30)

Parameters

query
DataPath or (Datastore, str)

A SQL-kind datastore and a query.

validate
bool

Boolean to validate if data can be loaded from the returned dataset. Defaults to True. Validation requires that the data source is accessible from the current compute.

set_column_types
dict[(str, DataType)]

A dictionary to set column data type, where key is column name and value is DataType.

query_timeout

Sets the wait time (in seconds) before terminating the attempt to execute a command and generating an error. The default is 30 seconds.

Returns

Returns a TabularDataset object.

Return type

Remarks

from_sql_query creates an object of TabularDataset class, which defines the operations to load data from SQL databases into tabular representation. Currently, we only support MSSQLDataSource.

For the data to be accessible by Azure Machine Learning, the SQL database specified by query must be located in Datastore and the datastore type must be of a SQL kind.

Column data types are read from data types in SQL query result. Providing set_column_types will override the data type for the specified columns in the returned TabularDataset.


   from azureml.core import Dataset, Datastore
   from azureml.data.datapath import DataPath

   # create tabular dataset from a SQL database in datastore
   datastore = Datastore.get(workspace, 'mssql')
   query = DataPath(datastore, 'SELECT * FROM my_table')
   tabular = Dataset.Tabular.from_sql_query(query, query_timeout=10)
   df = tabular.to_pandas_dataframe()

   # use `set_column_types` to set column data types
   from azureml.data import DataType
   data_types = {
       'ID': DataType.to_string(),
       'Date': DataType.to_datetime('%d/%m/%Y %I:%M:%S %p'),
       'Count': DataType.to_long(),
       'Latitude': DataType.to_float(),
       'Found': DataType.to_bool()
   }
   tabular = Dataset.Tabular.from_sql_query(query, set_column_types=data_types)

register_pandas_dataframe

Note

This is an experimental method, and may change at any time.
For more information, see https://aka.ms/azuremlexperimental.

Create a dataset from pandas dataframe.

register_pandas_dataframe(dataframe, target, name, description=None, tags=None, show_progress=True)

Parameters

dataframe
DataFrame

Required, in memory dataframe to be uploaded.

target
DataPath, Datastore or tuple(Datastore, str)object

Required, the datastore path where the dataframe parquet data will be uploaded to. A guid folder will be generated under the target path to avoid conflict.

name
str

Required, the name of the registered dataset.

description
str

Optional. A text description of the dataset. Defaults to None.

tags
dict[str, str]

Optional. Dictionary of key value tags to give the dataset. Defaults to None.

show_progress
bool

Optional, indicates whether to show progress of the upload in the console. Defaults to be True.

Returns

The registered dataset.

Return type

register_spark_dataframe

Note

This is an experimental method, and may change at any time.
For more information, see https://aka.ms/azuremlexperimental.

Create a dataset from spark dataframe.

register_spark_dataframe(dataframe, target, name, description=None, tags=None, show_progress=True)

Parameters

dataframe
DataFrame

Required, in memory dataframe to be uploaded.

target
DataPath, Datastore or tuple(Datastore, str)object

Required, the datastore path where the dataframe parquet data will be uploaded to. A guid folder will be generated under the target path to avoid conflict.

name
str

Required, the name of the registered dataset.

description
str

Optional. A text description of the dataset. Defaults to None.

tags
dict[str, str]

Optional. Dictionary of key value tags to give the dataset. Defaults to None.

show_progress
bool

Optional, indicates whether to show progress of the upload in the console. Defaults to be True.

Returns

The registered dataset.

Return type