Intro to Data Readers¶
Within the Data Profiler, there are 5 data reader classes:
CSVData (delimited data: CSV, TSV, etc.)
JSONData
ParquetData
AVROData
GraphData
TextData
Each of these classes can be used to read data individually, however the Data Profiler provides the unique capability of auto detecting what data you have and reading it automatically by using the Data
class.
import dataprofiler as dp
data = dp.Data('/path/to/mydata.abc') # auto detects and reads your data
Automatically reading and detecting data¶
Below is a demonstration of utilizing the Data
class which automatically detects the type of data for a given file and reads it automatically.
[ ]:
import os
import sys
try:
sys.path.insert(0, '..')
import dataprofiler as dp
except ImportError:
import dataprofiler as dp
[ ]:
# use data reader to read input data with different file types
data_folder = "../dataprofiler/tests/data"
csv_files = [
"csv/aws_honeypot_marx_geo.csv",
"csv/all-strings-skip-header-author.csv", # csv files with the author/description on the first line
"csv/sparse-first-and-last-column-empty-first-row.txt", # csv file with the .txt extension
]
json_files = [
"json/complex_nested.json",
"json/honeypot_intentially_mislabeled_file.csv", # json file with the .csv extension
]
parquet_files = [
"parquet/nation.dict.parquet",
"parquet/nation.plain.intentionally_mislabled_file.csv", # parquet file with the .csv extension
]
avro_files = [
"avro/userdata1.avro",
"avro/userdata1_intentionally_mislabled_file.json", # avro file with the .json extension
]
graph_files = [
"csv/graph_data_csv_identify.csv", # csv file with graph column names
]
text_files = [
"txt/discussion_reddit.txt",
]
all_files = csv_files + json_files + parquet_files + avro_files + graph_files + text_files
print('filepath' + ' ' * 58 + 'data type')
print('='*80)
for file in all_files:
filepath = os.path.join(data_folder, file)
data = dp.Data(filepath)
print("{:<65} {:<15}".format(file, data.data_type))
print("\n")
[ ]:
# importing from a url
data = dp.Data('https://raw.githubusercontent.com/capitalone/DataProfiler/main/dataprofiler/tests/data/csv/diamonds.csv')
data.head()
Specifying detection options of Data
and loading pandas.DataFrame
¶
The Data
class also gives the ability to set options or if the user wants to load their data with specific requirements. Options for each data reader are specified in the docs: https://capitalone.github.io/DataProfiler/docs/0.4.4/html/dataprofiler.data_readers.html
import dataprofiler as dp
options = {...} # allowed options are specified for each data reader.
data = dp.Data(data, options=options)
Later in this tutorial, the options for the CSVData class will be discussed.
Additionally, a user can directly load a pandas.DataFrame
as any data reader they choose.
[ ]:
import pandas as pd
from dataprofiler.data_readers.csv_data import CSVData
df = pd.DataFrame(['my', 'random', 'data'])
# specify via the `Data` class
data = dp.Data(data=df, data_type='csv')
print('Data Type: ', data.data_type)
# specifically use the CSVData class
data = CSVData(data=df)
print('Data Type: ', data.data_type)
Accessing data and attributes¶
Once loaded, the data can be accessed via the data
property of the object. Additional information about the data loaded may differ between data readers.
For this example we will focus on CSVData
.
[ ]:
filepath = "../dataprofiler/tests/data/csv/aws_honeypot_marx_geo.csv"
data = dp.Data(filepath)
print('Data Type: ', data.data_type)
print('Data Filepath: ', data.input_file_path)
print('File Encoding: ', data.file_encoding)
print('Data Length (two techniques): ', len(data), data.length)
print("Data Access:")
data.data
Checking data file types with is_match
¶
Each data reader has a class method is_match
which determines whether or not a dataset is of a given data type.
CSVData.is_match
JSONData.is_match
ParquetData.is_match
AVROData.is_match
GraphData.is_match
TextData.is_match
[ ]:
# supplemental function
def add_true_false_color(value):
"""Converts True to green and False to red in printed text."""
if value:
return "\x1b[92m " + str(is_match) + "\x1b[0m"
return "\x1b[31m " + str(is_match) + "\x1b[0m"
[ ]:
from dataprofiler.data_readers.csv_data import CSVData
non_csv_files = [
'json/iris-utf-8.json',
'json/honeypot_intentially_mislabeled_file.csv',
'parquet/titanic.parq',
'parquet/nation.plain.intentionally_mislabled_file.csv',
'txt/code.txt',
'txt/sentence.txt',
'avro/users.avro',
'avro/snappy_compressed_intentionally_mislabeled_file.csv',
]
print("Is the file a CSV?")
print('=' * 80)
for file in csv_files:
filepath = os.path.join(data_folder, file)
is_match = CSVData.is_match(filepath)
print(add_true_false_color(is_match), ':', file)
print('=' * 80)
for file in non_csv_files:
filepath = os.path.join(data_folder, file)
is_match = CSVData.is_match(filepath)
print(add_true_false_color(is_match), ':', file)
print('=' * 80)
Reloading data after altering options with reload
¶
There are two cases for using the reload function, both of which require the data type to have been interpreted correctly:
1. The options were not correctly determined
2. The options were loaded correctly but a change is desired.
In the example below, the data_format
for reading the data is changed and the data is then reloaded.
[ ]:
filepath = "../dataprofiler/tests/data/csv/diamonds.csv"
data = dp.Data(filepath)
print('original data:')
print('=' * 80)
print(data.data[:5])
print()
data.reload(options={'data_format': 'records', 'record_samples_per_line': 1})
print('reloaded data:')
print('=' * 80)
data.data[:5]
A deeper dive into CSVData
¶
This next section will focus on how to use the data reader class: CSVData
. The CSVData
class is used for reading delimited data. Delimited data are datasets which have their columns specified by a specific character, commonly the ,
. E.g. from the diamonds.csv
dataset:
carat,cut,color,clarity,depth,table,price,x,y,z
0.23,Ideal,E,SI2,61.5,55,326,3.95,3.98,2.43
0.21,Premium,E,SI1,59.8,61,326,3.89,3.84,2.31
0.23,Good,E,VS1,56.9,65,327,4.05,4.07,2.31
0.29,Premium,I,VS2,62.4,58,334,4.2,4.23,2.63
0.31,Good,J,SI2,63.3,58,335,4.34,4.35,2.75
However, the delimiter can be any character. Additionally, a quotechar
, commonly "
, can be specified which allows a delimiter to be contained within a column value. E.g. from the blogposts.csv
dataset:
Blog Post,Date,Subject,Field
"Monty Hall, meet Game Theory",4/13/2014,Statistics,Mathematics
Gaussian Quadrature,4/13/2014,Algorithms,Mathematics
Notice how "Monty Hall, meet Game Theory"
is contained by the quotechar because it contains the delimiter value ,
.
These delimiter dataset parameters (and more) can be automatically determined by the CSVData
data reader, however they can also be set via the options as demonstrated later in this tutorial.
Intro to the CSVData
data reader¶
Previously, it was shown that CSVData
may automatically be detected using Data
or can be manually specified by the user:
import dataprofiler as dp
from dataprofiler.data_readers.csv_data import CSVData
data = dp.Data(filepath)
data = CSVData(filepath)
[ ]:
# use data reader to read delimited data
data_folder = "../dataprofiler/tests/data"
csv_files = [
"csv/diamonds.csv",
"csv/all-strings-skip-header-author.csv", # csv files with the author/description on the first line
"csv/sparse-first-and-last-column-empty-first-row.txt", # csv file with the .txt extension
]
for file in csv_files:
data = CSVData(os.path.join(data_folder, file))
print(data.data.head())
print('=' * 80)
CSVData Options¶
As mentioned preivously, CSVData
has options that can be set to finetune its detection or to ensure the data is being read in a specific manner. The options for CSVData
are detailed below:
delimiter - delimiter used to decipher the csv input file
quotechar - quote character used in the delimited file
header - location of the header in the file.
data_format - user selected format in which to return data can only be of specified types
selected_columns - columns being selected from the entire dataset
[ ]:
# options are set via a dictionary object in which the parameters are specified.
# these are the default values for each option
options = {
"delimiter": ",",
"quotechar": '"',
"header": 'auto',
"data_format": "dataframe", # type: str, choices: "dataframe", "records"
"selected_columns": list(),
}
Options: delimiter and quotechar¶
Below, both the auto detection and use of options will be illustrated for delimiter
and quotechar
.
[ ]:
# display the data we are reading
filepath = "../dataprofiler/tests/data/csv/daily-activity-sheet-@-singlequote.csv"
num_lines = 10
with open(filepath) as fp:
print(''.join(fp.readlines()[:num_lines]))
[ ]:
data = dp.Data(filepath) # or use CSVData
print('Auto detected')
print('=' * 80)
print('delimiter: ', data.delimiter)
print('quotechar: ', data.quotechar)
data.data.head()
[ ]:
options = {'delimiter': '@', 'quotechar': "'"}
data = dp.Data(filepath, options=options) # or use CSVData
print('manually set')
print('=' * 80)
print('delimiter: ', data.delimiter)
print('quotechar: ', data.quotechar)
data.data.head()
[ ]:
# intentional failure with incorrect options
options = {'delimiter': ',', 'quotechar': '"'}
# will be interepted as TextData because the delimtier and quotechar were incorrect
data = dp.Data(filepath, options=options)
print('intentional faliure set')
print('=' * 80)
try:
print('delimiter: ', data.delimiter) # attribute error raised here, bc TextData, not CSVData
print('quotechar: ', data.quotechar)
# should not reach this or something went wrong
raise Exception('Should have failed because this is detected as TextData.')
except AttributeError:
print('When data_type is not set or the CSVData is not set, it will fail over to the\n'
'next best reader. In this case it is "TextData"\n')
data.data
Options: header¶
Below, both the auto detection and use of options will be illustrated for header
.
Notice how in the manually set mechanism, we are intentionally setting the header incorrectly to illustrate what happens.
[ ]:
# display the data we are reading
filepath = "../dataprofiler/tests/data/csv/sparse-first-and-last-column-header-and-author-description.txt"
num_lines = 10
with open(filepath) as fp:
print(''.join(fp.readlines()[:num_lines]))
[ ]:
options = {'header': 'auto'} # auto detected (default value)
data = dp.Data(filepath, options=options) # or use CSVData
print('Data Header:', data.header)
print('=' * 80)
data.data.head()
[ ]:
options = {'header': 2} # intentionally set incorrectly at value 2
data = dp.Data(filepath, options=options) # or use CSVData
print('Data Header:', data.header)
print('=' * 80)
data.data.head()
Options: data_format¶
For CSVData, the data_format
option can have the following values:
dataframe - (default) loads the dataset as a pandas.DataFrame
records - loads the data as rows of text values, the extra parameter
record_samples_per_line
how many rows are combined into a single line
dataframe
is used for conducting structured profiling of the dataset while records
is for unstructured profiling.
Below, both the auto detection and use of options will be illustrated for data_format
.
[ ]:
# display the data we are reading
filepath = "../dataprofiler/tests/data/csv/diamonds.csv"
num_lines = 10
with open(filepath) as fp:
print(''.join(fp.readlines()[:num_lines]))
[ ]:
options = {'data_format': 'dataframe'} # default
data = dp.Data(filepath, options=options) # or use CSVData
data.data[:5]
[ ]:
options = {'data_format': 'records', 'record_samples_per_line': 1}
data = dp.Data(filepath, options=options)
data.data[:5]
Options: selected columns¶
By default, all columns of a dataset will be read and loaded into the data reader. However, selected_columns
can be set to only load columns which the user requests.
[ ]:
# display the data we are reading
filepath = "../dataprofiler/tests/data/csv/aws_honeypot_marx_geo.csv"
num_lines = 10
with open(filepath) as fp:
print(''.join(fp.readlines()[:num_lines]))
[ ]:
options = {'selected_columns': ['datetime', 'host', 'src', 'proto']}
data = dp.Data(filepath, options=options)
data.data.head()
Intro to GraphData
data reader¶
This tutorial will focus on how to use the data reader class: GraphData
. The GraphData
class is used for reading the delimited data from a CSV file into a NetworkX
Graph object. This is all in an effort to prepare the data automaticaly for GraphProfiler
class to then profile graph data.
The DataProiler keys off of common graph naming conventions in the column header row. E.G. from dataprofiler/tests/csv/graph_data_csv_identify.csv
node_id_dst, node_id_src, continuous_weight, categorical_status
108,289,7.4448069,9
81,180,3.65064207,0
458,83,5.9959787,10
55,116,4.63359209,79
454,177,5.76715529,11
429,225,4.79556889,3
Options for the GraphData
are exactly the same as CSVData
.
Example implementation of GraphData
:
import dataprofiler as dp
from dataprofiler.data_readers.graph_data import GraphData
data = dp.Data(graph_file)
data = GraphData(graph_file)
[ ]:
from dataprofiler.data_readers.graph_data import GraphData
# use data reader to read delimited data
data_folder = "../dataprofiler/tests/data"
graph_file = "csv/graph_data_csv_identify.csv"
data = GraphData(os.path.join(data_folder, graph_file))
print(data.data.edges)
print('=' * 80)