12.3. Data Profiling Demonstration

This is a simple data profiling application that can be applied to workbooks to examine the data prior to creating builders.

This produces simple RST-format output on stdout.

A common use case is the following:

python3 demo/profile.py sample/\*.csv >profile_csv.rst
rst2html.py profile_csv.rst profile_csv.html

This gives us an HTML-formatted report.

12.3.1. Overheads

We depend on a number of Python libraries. Plus, of course, we’re creating workbooks, working with sheets and schema.

import logging
import sys
import argparse
import pprint
from collections import defaultdict

import stingray.workbook
import stingray.sheet
import stingray.schema

logger= logging.getLogger( __name__ )

12.3.2. Processing Context

This class handles the accumulation of global statistics on the source data. This a context manager which assures that we’ll successfully process the statistics in spite of any exception which might occur.

class Gather_Statistics:
    stop_on_exception= False
    def __init__( self ):
        self.stats= defaultdict( lambda: defaultdict(int) )
    def count( self, column, value ):
        self.stats[column][value] += 1
    def __enter__( self ):
        return self
    def __exit__( self, exc_type, exc_val, exc_tb ):
        if exc_type is not None: return False

See Application Level Data Validation Technique for a more complete example of this kind of use of a context manager.

12.3.3. Processing

See The Stingray Developer’s Guide for background. We’re going to need a “sheet process function.” This transforms the source sheet into the target collection, usually an output file.

The process_sheet() function the heart of the application. This handles all the rows present in a given sheet.

We use source_row[attr] to accumulate the cell.Cell instance information. This can help identify the source data format as well as the value.

We can use source_row[attr].value to accumulate the “raw” Python values present in the spreadsheet.

An alternative is to use a cell.Cell conversion to a desired type. We might, for example, use cell.Cell.to_str() to convert a raw value to a string. This would better parallel the way that an application will use the data.

def process_sheet( sheet, persistence ):
    counts= defaultdict( int )
    for source_row in sheet.schema.rows_as_dict_iter(sheet):
            counts['read'] += 1
            for attr in source_row:
                persistence.count( attr, source_row[attr] )
        except Exception as e:
            counts['invalid'] += 1
            if persistence.stop_on_exception: raise
            summary= "{0} '{1}'".format( e.__class__.__name__, e.message )
            logger.error( summary )
            counts['error '+summary] += 1

    title= "{0} :: {1}".format( sheet.workbook.name, sheet.name )
    print( title )
    print( "="*len(title) )
    for attr in sheet.schema:
        name= attr.name
        print( name )
        print( "-"*len(name) )
        print( "..  csv-table::" )
        for k in persistence.stats[name]:
            print( '    "{0}","{1}"'.format( k, persistence.stats[name][k] ) )
    return counts

Some applications will have variant processing for workbooks that contain different types of sheets. This leads to different process_this_sheet and process_that_sheet functions. Each will follow the above template to process all rows of the sheet.

12.3.4. High-Level Interfaces

This version of validate() doesn’t really do very much. We don’t have any persistence, so there’s no sensible alternative do this for simple data gathering. In more complex applications, we might have a process() function which does some more complex processing.

However, it’s often helpful to follow the template design for other, more sophisticated, applications. For that reason, we provide the processing context as a kind of Strategy object to the process_sheet() function.

def validate( sheet ):
    with Gather_Statistics() as mode:
        counts= process_sheet( sheet, mode )
    return counts

Note that the following process_workbook() function makes some specific claims about the given file. In particular:

If these assumptions are not universally true, then different application programs or different process_workbook() functions must be written to handle other kinds of workbooks.

def process_workbook( input ):
    for name in source.sheets():
        logger.info( "{0} :: {1}".format( input, name ) )
        sheet= source.sheet( name,
            loader_class=stingray.schema.loader.HeadingRowSchemaLoader )
        counts= validate( sheet )
        logger.info( pprint.pformat(dict(counts)) )

12.3.5. Command-Line Interface

We have an optional argument for verbosity and a positional argument that provides all the files to profile.

def parse_args():
    parser= argparse.ArgumentParser()
    parser.add_argument( 'file', nargs='+' )
    parser.add_argument( '-v', '--verbose', dest='verbosity',
        default=logging.INFO, action='store_const', const=logging.DEBUG )
    return parser.parse_args()

The main-import switch allows us to import this module and reuse the components or run it as a command-line application. To run from the command line, we have several issues to address.

  1. Logging.
  2. Parameter Parsing. This includes interpreting options.
  3. Argument Processing. This means looping over the positional arguments.
  4. Opening Workbooks. Some applications can’t use the default workbook.Opener. A subclass of Opener, or more complex logic, may be required.
  5. Gracefully catching and logging exceptions.
  6. Exit Status to the OS.
if __name__ == "__main__":
    logging.basicConfig( stream=sys.stderr )
    args= parse_args()
    logging.getLogger().setLevel( args.verbosity )
        for input in args.file:
            with stingray.workbook.open_workbook( input ) as source:
                process_workbook( source )
        status= 0
    except Exception as e:
        logging.exception( e )
        status= 3
    sys.exit( status ) Running the Demo

We can run this program like this:

python3 demo/profile.py sample/\*.csv >profile_csv.rst
rst2html.py profile_csv.rst profile_csv.html

The RST output file looks like this:

sample/csv_workbook.csv :: csv_workbook

Col 1 - int

..  csv-table::


Col 2.0 - float

..  csv-table::


Column "3" - string

..  csv-table::


Column '4' - date

..  csv-table::


Column 5 - boolean

..  csv-table::


Column 6 - empty

..  csv-table::


Column 7 - Error

..  csv-table::


sample/simple.csv :: simple


..  csv-table::

        "TextCell('Column 6 – empty')","1"
        "TextCell('Column “3” - string')","1"
        "TextCell('Col 2.0 – float')","1"
        "TextCell("Column '4' – date")","1"
        "TextCell('Column 7 – Error')","1"
        "TextCell('Column 5 – boolean')","1"
        "TextCell('Col 1 - int')","1"


..  csv-table::



..  csv-table::



..  csv-table::