A toolbox and library of ETL, data quality, and data analysis tools

# Introduction

Datagristle is a toolbox of tough and flexible command line tools for working with data. It’s kind of an interactive mix between ETL and data analysis optimized for rapid analysis and manipulation of a wide variety of data at the command line.

More info is on the DataGristle wiki here: wiki <https://github.com/kenfar/DataGristle/wiki>__

And examples of all csv utilities can be found here: examples <https://github.com/kenfar/DataGristle/tree/master/examples>__

# Installation

• Using pip <http://www.pip-installer.org/en/latest/>__:

::

$pip install datagristle # Dependencies • Python 3.8 • or Python 3.9 • or Python 3.10 • or Python 3.11 # CSV Utilities provided in this release: • gristle_differ • Allows two identically-structured files to be compared by key columns and split into same, inserts, deletes, chgold and chgnew files. • The user can configure which columns are included in the comparison. • Post delta transformations can include assign sequence numbers, copying field values, etc. • gristle_converter (was: gristle_file_converter) • Converts an input file with one csv dialect into an output file with another. • gristle_freaker • Produces a frequency distribution of multiple columns from input file. • gristle_profiler (was: gristle_determinator) • Identifies file formats, generates metadata, prints file analysis report • This is the most mature - and also used by the other utilities so that you generally do not need to enter file structure info. • gristle_slicer • Used to extract a subset of columns and/or rows out of an input file. • Uses python slicing notation to specific items or ranges of items to extract. • gristle_sorter • CSV-aware sort utility that handles data that breaks unix sorts. • gristle_validator • Validates csv files by confirming that all records have the right number of fields, and by applying a json schema to each record. • gristle_viewer • Shows one record from a file at a time - formatted based on metadata. # File and Directory Utilities provided in this release: • gristle_dir_merger • Used to consolidate large directories with options to control matching criteria as well as matching actions. # gristle_slicer :: Extracts subsets of input files based on user-specified columns and rows. The input csv file can be piped into the program through stdin or identified via a command line option. The output will default to stdout, or redirected to a filename via a command line option. The columns and rows are specified using python list slicing syntax - so individual columns or rows can be listed as can ranges. Inclusion or exclusion logic can be used - and even combined. Examples:$ gristle_slicer -i sample.csv Prints all rows and columns $gristle_slicer -i sample.csv -c":5, 10:15, dept" -C 13 Prints columns 0-4 and 10,11,12,14, and the col associated with the header field 'dept' for all records$ gristle_slicer -i sample.csv -C:-1 Prints all columns except for the last for all records $gristle_slicer -i sample.csv -c:5 -r 100:1:-1 Prints records 1 to 100 in reverse order$ gristle_slicer -i sample.csv -c:5 -r :100:3 Prints every third record from 0 to 99 $gristle_slicer -i sample.csv -c:5 -r :100:0.25 Prints a random 25% of the records from 0 to 99$ gristle_slicer -i sample.csv -c:5 -r-100 -d'|' --quoting=quote_all Prints columns 0-4 for the last 100 records, csv dialect info (delimiter, quoting) provided manually) $cat sample.csv | gristle_slicer -c:5 -r-100 -d'|' --quoting=quote_all Prints columns 0-4 for the last 100 records, csv dialect info (delimiter, quoting) provided manually) Many more examples can be found here: https://github.com/kenfar/DataGristle/tree/master/examples/gristle_slicer # gristle_freaker :: Creates a frequency distribution of values from columns of the input file and prints it out in columns - the first being the unique key and the last being the count of occurances. Examples:$ gristle_freaker -i sample.csv -c 0 Creates two columns from the input - the first with unique keys from column 0, the second with a count of how many times each exists. $gristle_freaker -i sample.csv -c home_state This is the same as the previous example - but in this case the column reference uses the name of the field from the file header.$ gristle_freaker -i sample.csv -d '|' -c 0 --sortcol 1 --sortorder forward --writelimit 25 In addition to what was described in the first example, this example adds sorting of the output by count ascending and just prints the first 25 entries. $gristle_freaker -i sample.csv -d '|' -c 0,1 Creates three columns from the input - the first two with unique key combinations from columns 0 & 1, the third with the number of times each combination exists. Many more examples can be found here: https://github.com/kenfar/DataGristle/tree/master/examples/gristle_freaker # gristle_sorter :: Provides a csv dialect-aware sort that can safely handle delimiters, quotes, and newlines within fields. Examples:$ gristle_sorter -i sample.csv -k 0sf -D Sort file by the 0-position string column in forward (ascending) direction, dedupes the results and writes them to stdout. The csv dialect is auto- detected. $gristle_sorter -i sample.csv -k 0sf dept-s-r -D This example uses the optional tildes to separate the parts of the key, and uses a fieldname reference from the file header (dept) rather than a numeric field position.$ gristle_sorter -i sample.csv --keys 0sf 3ir --outfile sample_out.csv Sorts file by the 0-position column string in forward direction followed by the position 3 column integer in reverse direction. The output is not deduped, but is written to a file. The csv dialect is auto-detected. $gristle_sorter -i sample.csv -k 0sf -d '|' -q quote_all --doublequote --has-header Sort file by the 0-position string column in forward (ascending) direction, specifies the csv dialect explicitly, including that the file has a header that will be written to the top of the output file. Many more examples can be found here: https://github.com/kenfar/DataGristle/tree/master/examples/gristle_sorter # gristle_profiler :: Analyzes the structures and contents of csv files in the end producing a report of its findings. It is intended to speed analysis of csv files by automating the most common and frequently-performed analysis tasks. It's useful in both understanding the format and data and quickly spotting issues. Examples:$ gristle_profiler --infiles japan_station_radiation.csv This command will analyze a file with radiation measurements from various Japanese radiation stations.

   File Structure:
format type:       csv
field cnt:         4
record cnt:        100
delimiter:
csv quoting:       False
skipinitialspace:  False
quoting:           QUOTE_NONE
doublequote:       False
quotechar:         "
lineterminator:    '\n'
escapechar:        None

Field Analysis Progress:
Analyzing field: 0
Analyzing field: 1
Analyzing field: 2
Analyzing field: 3

Fields Analysis Results:

------------------------------------------------------
Name:             station_id
Field Number:     0
Wrong Field Cnt:  0
Type:             timestamp
Min:              1010000001
Max:              1140000006
Unique Values:    99
Known Values:     99
Top Values not shown - all values are unique

------------------------------------------------------
Name:             datetime_utc
Field Number:     1
Wrong Field Cnt:  0
Type:             timestamp
Min:              2011-02-28 15:00:00
Max:              2011-02-28 15:00:00
Unique Values:    1
Known Values:     1
Top Values:
2011-02-28 15:00:00                      x 99 occurrences

------------------------------------------------------
Name:             sa
Field Number:     2
Wrong Field Cnt:  0
Type:             integer
Min:              -999
Max:              52
Unique Values:    35
Known Values:     35
Mean:             2.45454545455
Median:           38.0
Variance:         31470.2681359
Std Dev:          177.398613681
Top Values:
41                                       x 7 occurrences
42                                       x 7 occurrences
39                                       x 6 occurrences
37                                       x 5 occurrences
46                                       x 5 occurrences
17                                       x 4 occurrences
38                                       x 4 occurrences
40                                       x 4 occurrences
45                                       x 4 occurrences
44                                       x 4 occurrences

------------------------------------------------------
Name:             ra
Field Number:     3
Wrong Field Cnt:  0
Type:             integer
Min:              -888
Max:              0
Unique Values:    2
Known Values:     2
Mean:             -556.121212121
Median:           -888.0
Variance:         184564.833792
Std Dev:          429.610095077
Top Values:
-888                                     x 62 occurrences
0                                        x 37 occurrences


Many more examples can be found here: https://github.com/kenfar/DataGristle/tree/master/examples/gristle_profiler

# gristle_converter

::

Converts a file from one csv dialect to another

# gristle_viewer

::

Displays a single record of a file, one field per line, with field names displayed as labels to the left of the field values. Also allows simple navigation between records.

Examples: $gristle_viewer -i sample.csv -r 3 Presents the third record in the file with one field per line and field names from the header record as labels in the left column.$ gristle_viewer -i sample.csv -r 3 -d '|' -q quote_none In addition to what was described in the first example this adds explicit csv dialect overrides.

Many more examples can be found here: https://github.com/kenfar/DataGristle/tree/master/examples/gristle_viewer

# gristle_differ

::

gristle_differ compares two files, typically an old and a new file, based on explicit keys in a way that is far more accurate than diff. It can also compare just subsets of columns, and perform post-delta transforms to populate fields with static values, values from other fields, variables from the command line, or incrementing sequence numbers.

Examples:

  $gristle_differ --infiles file0.dat file1.dat --key-cols 0 2 --ignore_cols 19 22 33 - Sorts both files on columns 0 & 2 - Dedupes both files on column 0 - Compares all fields except fields 19,22, and 23 - Automatically determines the csv dialect - Produces the following files: - file1.dat.insert - file1.dat.delete - file1.dat.same - file1.dat.chgnew - file1.dat.chgold$ gristle_differ --infiles file0.dat file1.dat --key-cols 0 --compare-cols 1 2 3 4 5 6 7  -d '|'

- Sorts both files on columns 0
- Dedupes both files on column 0
- Compares fields 1,2,3,4,5,6,7
- Uses '|' as the field delimiter
- Produces the same output file names as example 1.

# gristle_dir_merger

::

Gristle_dir_merger consolidates directory structures of files. Is both fast and flexible with a variety of options for choosing which file to use based on full (name and md5) and partial matches (name only) .

Examples $gristle_dir_merger --source-dir /tmp/foo --dest-dir /data/foo - Compares source of /tmp/foo to dest of /data/foo. - Files will be consolidated into /data/foo, and deleted from /tmp/foo. - Comparison will be: match-on-name-and-md5 (default) - Full matches will use: keep_dest (default) - Partial matches will use: keep_newest (default) - Bottom line: this is what you normally want.$ gristle_dir_merger --source-dir /tmp/foo --dest-dir /data/foo --dry-run - Same as the first example - except it only prints what it would do without actually doing it. - Bottom line: this is a good step to take prior to running it for real. $gristle_dir_merger --source-dir /tmp/foo --dest-dir /data/foo -r - Same as the first example - except it runs recursively through the directories.$ gristle_dir_merger --source-dir /tmp/foo --dest-dir /data/foo --on-partial-match keep-biggest - Comparison will be: match-on-name-and-md5 (default) - Full matches will use: keep_dest (default) - Partial matches will use: keep_biggest (override) - Bottom line: this is a good combo if you know that some files have been modified on both source & dest, and newest isn't the best. \$ gristle_dir_merger --source-dir /tmp/foo --dest-dir /data/foo --match-on name_only --on-full-match keep-source - Comparison will be: match-on-name-only (override) - Full matches will use: keep_source (override) - Bottom line: this is a good way to go if you have files that have changed in both directories, but always want to use the source files.

# Licensing

• Gristle uses the BSD license - see the separate LICENSE file for further information

# V0.2.3 - 2022-11

• Improvement: gristle_slicer - add support for writing data out in the config order rather than the data order, as well as negative, positive and random stepping.

• BREAKING CHANGE: refactored gristle_validator:

• improved error reporting
• replaced obsolete customized jsonschema version with support for the current version (Draft 7)
• BREAKING CHANGE: Removed gristle_processor.

• BREAKING CHANGE: envvar and config file boolean args with values other than True, true, t, or 1 will be rejected. This is because they can be very ambiguous and confusing, in particular with with a pair of args like has-header and has-no-header.

• Improvement: crash reporting is now more consistent, complete and informative

• Improvement: is now enforcing the minimum version of 3.8. It was previously almost completely working on 3.7 but would occasionally crash on a 3.8 feature.

• Improvement: replaced pyyaml with ruamel.yaml - to better support config generation.

• Bug Fix: fixed pathing on two bash aliases: gristle_determinator & gristle_file_converter

• Bug Fix: fixed bug in handling of obsolete options

# V0.2.2 - 2021-07

• Improvement: the field-names from headers can now be used instead of column offsets for gristle_sorter, gristle_freaker, gristle_profiler, and gristle_slicer.

• Improvement: The use of the header now follows four simple rules:

• It can be referred to as row 0 when it makes sense - like with gristle_slicer & gristle_viewer.
• It will be passed through when it makes sense - like with gristle_sorter.
• It will be used to translate field names to offsets for configuration.
• But will otherwise be ignored.
• Bug Fix: gristle_freaker was failing with 0-length files when using col-type=each

• Bug Fix: gristle_sorter was failing with some multi-directional sorts

# V0.2.1 - 2021-04

• Improvement: added gristle_sorter as a script to install in the system so that it is available to users.

# V0.2.0 - 2021-04

• Improvement: Now supports python versions 3.8 and 3.9.

• Improvement: All csv programs now support envvars and config files for input and can generate config files.

• Improvement: Programs always autodetect file csv dialect before applying user overrides - except for piped-in data.

• BREAKING CHANGE: dropped support for python version 3.7

• BREAKING CHANGES to all csv programs:

• Various changes to names of options for consistency, with older versions caught with an error msg to provides new name.
• Various changes to csv dialect handling for consistency and correct handling of escapechar, doublequoting, skipinitialspace.

# v0.1.7 - 2020-07

• Improvement: now supports python versions 3.7 and 3.8

• BREAKING CHANGE: dropped support for python version 3.6

• Bumped versions on dependent modules to eliminate vulnerabilities

• gristle_differ

• BREAKING CHANGE: col_names renamed to col-names for consistency
• Fixes –already-unix option bug with file parsing
• Fixes –stats bug with empty files
• Improvement: if a key-col is in the ignore-cols - it will simply be ignored, and the program will continue processing.
• Improvement: if a key-col is in the compare-cols - it will simply be ignored, and the program will continue processing.
• Improvement: if neither compare or ignore cols are provided it will use all cols as compare-cols and continue processing.
• Improvement: CLI help is updated to provide more details and accurate examples of these options.

# v0.1.5 - 2018-05

• fixed setup.py bug in which pip10 no longer includes req module

# v0.1.4 - 2017-12

• fixed gristle_validator bug in which checks on dg_maximum were not being run

# v0.1.3 - 2017-08

• additional improvements to code quality, but with some breaking changes

• changed argument handling for multiple utilities to simplify code and get more consistency.

• affects: gristle_freaker, gristle_slicer, and gristle_viewer
• This means words are separated by hyphens, not underscores. –sortorder is –sort-order.
• changed file handling for multiple utilities to simplify code and get more consistency.

• affects: gristle_freaker, gristle_slicer, gristle_validator, and gristle_viewer
• This means that behavior in handling multiple files, piped input, and other edge cases is more consistent between utilities.

# v0.1.1 - 2017-05

• changed versioning format, which has broken pypy for history

# v0.59 - 2016-11

• gristle_differ

• totally rewritten. Can now handle very large files, perform post-transform transformations, handle more complex comparisons, and use column names rather than just positions.
• gristle_determinator

• added read-limit argument. This allows the tool to be easily run against a subset of a very large input file.
• gristle_scalar

• removed from toolkit. There are better tools in other solutions can be used instead. This tool may come back again later, but only if enormously rewritten.
• gristle_filter

• removed from toolkit. There are better tools in other solutions can be used instead. This tool may come back again later, but only if enormously rewritten.
• minor:

• gristle_md_reporter - slight formatting change: text descriptions of fields are now included, and column widths were tweaked.
• all utilities - a substantial performance improvement for large files when quoting information is not provided.

# v0.58 - 2014-08

• gristle_dir_merger

• initial addition to toolkit. Merges directories of files using a variety of matching criteria and matching actions.

# v0.57 - 2014-07

• gristle_processor

• initial addition to toolkit. Provides ability to scan through directory structure recursively, and delete files that match config criteria.

# v0.56 - 2014-03

• gristle_determinator

• fixed problem printing top_values on empty file with header
• gristle_validator

• gristle_freaker

# v0.55 - 2014-02

• gristle_determinator - fixed a few problems:

• the ‘Top Values not shown - all unique’ message being truncated
• floats not handled correctly for stddev & variance
• quoted ints & floats not handled

# v0.54 - 2014-02

• gristle_validator - major updates to allow validation of csv files based on the json schema standard, with help from the Validictory module.

# v0.53 - 2014-01

• gristle_freaker - major updates to enable distributes on all columns to be automatically gathered through either (all or each) args. ‘All’ combines all columns into a single tuple prior to producing distribution. ‘Each’ creates a separate distribution for every column within the csv file.
• travisci - added support and started using this testing service.

## Project details

Uploaded source