DOCSEARCHbyAlgolia
Submit Docs

qsv

repository·master·Indexed 25 days ago

https://github.com/dathere/qsv

A high-performance data-wrangling toolkit for tabular data (CSV, Excel, etc.) providing composable commands for querying, transforming, and analyzing datasets. Key capabilities include Polars SQL queries via `sqlp`, Luau scripting via `luau`, AI-powered data dictionary inference with `describegpt`, and synthetic data generation via `synthesize`. It supports a wide range of operations including data cleaning, validation using JSON Schema, and geospatial enrichment.

Tokens
159.3K
Snippets
222
Records
915
Agent score
81%

What's inside qsv

  1. Overview of qsv commands and capabilities

    master

    qsv is a high-performance data-wrangling toolkit for CSV and other tabular formats. It provides a wide array of commands for data transformation, analysis, and visualization. Key capabilities include:

    • Transformation: apply (string, date, math, NLP), replace (regex), rename (columns), explode/implode (row expansion/contraction), and py (Python expressions).
    • Analysis: stats (summary statistics), frequency (distribution tables), pragmastat (advanced statistics), and count (row counting).
    • Data Cleaning: dedup (remove duplicates), denull (detect null sentinels), clean (remove cache files), and validate (JSON Schema validation).
    • Querying & Joining: sqlp (Polars SQL), join/joinp (various join types), search (regex search), and luau (embedded scripting DSL).
    • I/O & Conversion: to (convert to Parquet, SQLite, Excel, etc.), jsonl/tojsonl (JSONL conversion), cat (concatenate), and get (fetch from URLs/CKAN).
    • Advanced Features: describegpt (LLM-powered data dictionary), viz (interactive charts/maps), synthesize (generate statistically faithful synthetic data), and geocode (geospatial enrichment).

    Many commands are optimized using an index (📇) for near-instantaneous random access and multithreaded execution.

  2. Overview of qsv data-wrangling toolkit

    master
    qsv is a high-performance toolkit designed for querying, slicing, sorting, analyzing, filtering, enriching, transforming, validating, joining, formatting, converting, chatting, FAIRifying, and documenting tabular data (such as CSV and Excel). Its commands are designed to be simple, composable, and optimized for speed.

  3. Geocode locations with qsv

    master

    The geocode command allows you to convert locations (addresses, cities, etc.) into geographic coordinates (latitude/longitude) or vice versa. It supports two primary modes:

    1. Offline Mode: Uses local copies of the Geonames cities and Maxmind GeoLite2 databases. This mode is extremely fast (up to 360,000 records/sec) and supports multi-threading and caching.
    2. Online Mode: Uses the OpenCage geocoder for forward and reverse geocoding via web requests.

    Key features include:

    • Caching: Expensive operations are memoized using Redis or disk caching.
    • Multi-threading: High performance even without a local index.
    • Geospatial capabilities: Supports various geocoding tasks including IP lookups and country information.

  4. Understand `qsv stats` output categories

    master

    The qsv stats command produces two types of statistics:

    1. Streaming Statistics: Computed in constant memory and always emitted. These include metadata (is_ascii), descriptive stats (sum, min, max, range, sort_order, sortiness), string lengths, central tendency (mean, stddev, etc.), and quality metrics (nullcount, sparsity).
    2. Non-Streaming Statistics: These are opt-in via specific flags or --everything. They typically require loading column data into memory. Examples include median, quartiles, cardinality, mode, and percentiles.

    Note: Unlike sniff, stats provides guaranteed data type inferences because it scans the entire file.

  5. Understand Geoconnex SHACL shapes in qsv

    master

    The geoconnex.ttl file contains canonical SHACL shapes provided by the Internet of Water's nabu project. These shapes define constraints for organizations, datasets, variables, distributions, and hydrologic places.

    In qsv, these shapes are embedded directly into the binary. This allows the geoconnex profile's validation.external block to perform validation using pyshacl against rendered JSON-LD without requiring the user to manage the .ttl file manually.

    Phase 1 Coverage: The current geoconnex profile (resources/profiles/geoconnex.yaml) only projects dataset-level metadata. The following shapes are covered:

    • DatasetShape
    • ProviderShape
    • PublisherShape
    • DistributionShape

    Note that LocationOrientedShape, VariableShape, and MeasurementMethodShape are either deferred or only trigger under specific schema conditions not present in the Phase 1 profile.

  6. Understand the `qsv stats` command functionality

    master

    The stats command provides comprehensive statistical summaries and data type inference for CSV columns. Unlike sampling-based tools, it scans the entire file to guarantee accurate inference.

    Key Capabilities:

    • Type Inference: Detects NULL, Integer, String, Float, Date, DateTime, and optionally Boolean (via --infer-boolean).
    • Streaming Statistics: Computes mean, sum, min/max, standard deviation, and variance using constant memory.
    • Non-Streaming Statistics: Computes cardinality, modes, medians, and quartiles (requires loading all data into memory).
    • Date Handling: Infers date formats with configurable patterns.
    • Caching: Automatically stores computed statistics to avoid recalculation based on file modification times.
    • Foundation: Provides the underlying data used by other commands like schema, validate, joinp, pivotp, and sqlp.

  7. Choose the correct qsv binary variant

    master

    Depending on your environment and feature requirements, choose one of the three available qsv binary variants:

    • qsv: The full feature-capable version. Includes prebuilt binaries for all applicable features (except Python support).
    • qsvlite: A lightweight version with all features disabled. This is approximately 40% the size of the standard qsv binary.
    • qsvdp: Optimized specifically for use with Datapusher+. This version includes only the commands relevant to Datapusher+ and is approximately 40% the size of the standard qsv binary.

    Note: qsvdp is only relevant for CKAN installations that use the Datastore.

  8. Use Luau and Python for complex data-wrangling

    master

    For complex tasks, qsv supports script interpreters:

    • Luau: Recommended for complex data-wrangling. It is faster, more memory-efficient, has no external dependencies, and includes a Domain Specific Language (DSL) with several data-wrangling helper functions.
    • Python: Supported for scripting tasks.
    • MiniJinja: Used internally by the template and fetchpost commands.

  9. Understand DCAT-AP v3 SHACL shape embedding in qsv

    master

    The dcat-ap-v3 profile in qsv uses an embedded version of the dcat-ap-SHACL.ttl file. This file is the canonical SHACL shapes file from the SEMIC.eu DCAT-AP v3.0.0 release.

    By embedding this file, the validation.external block in the dcat-ap-v3 profile can automatically spawn pyshacl against rendered JSON-LD without requiring the user to manually download or provide a path to the shapes file.

  10. Understand the NYC 311 SR 2010-2020 Dataset

    master

    The NYC_311_SR_2010-2020-sample-1M dataset contains one million NYC 311 complaint records from January 2010 to December 2020.

    Key Characteristics:

    • Grain: One row equals one NYC 311 complaint record.
    • Primary Key: Unique Key (surrogate integer).
    • Temporal Coverage: Created Date column, spanning 2010-01-01 to 2020-12-23.
    • Spatial Data: Includes Latitude/Longitude (EPSG:4326) and address components, though ~25% of records lack geospatial info.
    • Data Quality Notes:
      • Due Date is missing for ~65% of records.
      • Closed Date and Resolution Action Updated Date may contain placeholder/erroneous dates ranging from 1900 to 2100.
      • Many categorical fields use an "Other" bucket for non-standardized values.

  11. Understand Statistics Computation Modes

    master

    The qsv stats command distinguishes between two types of statistics based on memory requirements:

    Streaming Statistics (O(1) memory)

    These can be computed in a single pass without storing all values in memory:

    • Sum, min, max, range
    • Mean, standard deviation, variance
    • Sort order detection
    • String length statistics (min, max, avg)
    • Cardinality (excluding unique value counts)
    • Coefficient of variation

    Non-Streaming Statistics (O(n) memory)

    These require storing or sorting all values, consuming memory proportional to the number of records:

    • Median and Quartiles (requires sorting)
    • Percentiles (requires sorting)
    • Modes and antimodes (requires frequency counting)
    • Median Absolute Deviation (MAD)

  12. Understand the qsv stats command capabilities

    master

    The stats command is a high-performance CSV statistics engine. It performs the following tasks:

    • Type Inference: Automatically detects data types for each column (NULL, Integer, Float, String, Date, DateTime, and optionally Boolean via --infer-boolean).
    • Statistical Computation: Computes up to 48 summary statistics per column, including:
      • Streaming (Constant Memory): sum, min, max, range, mean, standard deviation, variance, coefficient of variation, string length statistics, sort order detection, skewness, and kurtosis.
      • Non-Streaming (Requires loading values): exact median, quartiles (Q1, Q2, Q3, IQR), percentiles, modes, antimodes, cardinality (unique count), and Median Absolute Deviation (MAD).
    • Execution Modes: Processes files sequentially (single-threaded) or in parallel (multi-threaded) if an index is available.
    • Caching: Automatically caches results to avoid recomputation.