Image by Editor | Midjourney
DuckDB is a powerful in-memory database designed for analytical workloads. You can use it to efficiently query CSV files.
In this article, we’ll walk through a structured approach to analyzing a CSV file using DuckDB from the command line. We’ll use a sample shopping dataset that mimics real-world transactions and cover key analysis steps.
Let’s get started!
Installing DuckDB and Getting Started
First, install DuckDB if you haven’t already:
$ curl https://install.duckdb.org | sh
Now, open your terminal and launch DuckDB. Be sure to use the right path on your machine replacing ‘user’ with the right user:
$ /home/user/.duckdb/cli/latest/duckdb
If you need help, check the installation guide.
▶️ To follow along, get the CSV file: shopping_data.csv
To preview the first 5 rows of shopping_data.csv, run:
SELECT * FROM read_csv('shopping_data.csv') LIMIT 5;
This should return the first few rows:
┌─────────────┬───────────────┬───────┬───┬───────────────┬────────────────┐ │ customer_id │ customer_name │ age │ … │ purchase_date │ payment_method │ │ int64 │ varchar │ int64 │ │ date │ varchar │ ├─────────────┼───────────────┼───────┼───┼───────────────┼────────────────┤ │ 1 │ Customer 1 │ 56 │ … │ 2024-01-01 │ PayPal │ │ 2 │ Customer 2 │ 46 │ … │ 2024-01-02 │ Credit Card │ │ 3 │ Customer 3 │ 32 │ … │ 2024-01-03 │ Cash │ │ 4 │ Customer 4 │ 60 │ … │ 2024-01-04 │ Cash │ │ 5 │ Customer 5 │ 25 │ … │ 2024-01-05 │ Debit Card │ ├─────────────┴───────────────┴───────┴───┴───────────────┴────────────────┤ │ 5 rows 8 columns (5 shown) │ └──────────────────────────────────────────────────────────────────────────┘
As seen, you can use SELECT columns FROM read_csv(‘your_data.csv’); to query CSV files.
Exploring the Dataset: Column Information and Summary Statistics
Now let’s take the first steps to analyzing the CSV file.
Check Column Names and Data Types
To inspect the structure of the dataset, run:
DESCRIBE SELECT * FROM read_csv('shopping_data.csv') LIMIT 5;
Output:
┌─────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐ │ column_name │ column_type │ null │ key │ default │ extra │ │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ ├─────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤ │ customer_id │ BIGINT │ YES │ NULL │ NULL │ NULL │ │ customer_name │ VARCHAR │ YES │ NULL │ NULL │ NULL │ │ age │ BIGINT │ YES │ NULL │ NULL │ NULL │ │ gender │ VARCHAR │ YES │ NULL │ NULL │ NULL │ │ purchase_amount │ DOUBLE │ YES │ NULL │ NULL │ NULL │ │ category │ VARCHAR │ YES │ NULL │ NULL │ NULL │ │ purchase_date │ DATE │ YES │ NULL │ NULL │ NULL │ │ payment_method │ VARCHAR │ YES │ NULL │ NULL │ NULL │ └─────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘
This displays column names, their data types, and any constraints.
Generate Summary Statistics
To get an overview of numerical columns like age and purchase_amount, use:
SELECT
MIN(age) AS min_age, MAX(age) AS max_age, AVG(age) AS avg_age,
MIN(purchase_amount) AS min_purchase, MAX(purchase_amount) AS max_purchase, AVG(purchase_amount) AS avg_purchase
FROM read_csv('shopping_data.csv');
Output:
┌─────────┬─────────┬───┬──────────────┬────────────────────┐ │ min_age │ max_age │ … │ max_purchase │ avg_purchase │ │ int64 │ int64 │ │ double │ double │ ├─────────┼─────────┼───┼──────────────┼────────────────────┤ │ 19 │ 61 │ … │ 485.1 │ 242.92866666666666 │ ├─────────┴─────────┴───┴──────────────┴────────────────────┤ │ 1 rows 6 columns (4 shown) │ └───────────────────────────────────────────────────────────┘
This provides key statistics such as minimum, maximum, and average values for age and purchase amounts.
Filtering and Sorting Transactions
To find all customers who spent more than $200, sorted by purchase amount in descending order:
SELECT customer_name, age, purchase_amount, category
FROM read_csv('shopping_data.csv')
WHERE purchase_amount > 200
ORDER BY purchase_amount DESC;
Output:
┌───────────────┬───────┬─────────────────┬─────────────┐ │ customer_name │ age │ purchase_amount │ category │ │ varchar │ int64 │ double │ varchar │ ├───────────────┼───────┼─────────────────┼─────────────┤ │ Customer 16 │ 20 │ 485.1 │ Groceries │ │ Customer 18 │ 19 │ 470.35 │ Groceries │ │ Customer 21 │ 47 │ 461.72 │ Groceries │ │ Customer 9 │ 40 │ 455.57 │ Groceries │ │ Customer 19 │ 41 │ 448.47 │ Electronics │ │ Customer 28 │ 61 │ 416.08 │ Clothing │ │ Customer 1 │ 56 │ 406.11 │ Books │ │ Customer 17 │ 39 │ 389.82 │ Groceries │ │ Customer 4 │ 60 │ 345.27 │ Electronics │ │ Customer 11 │ 28 │ 334.64 │ Books │ │ Customer 20 │ 61 │ 302.97 │ Books │ │ Customer 14 │ 57 │ 277.89 │ Books │ │ Customer 13 │ 53 │ 264.83 │ Books │ │ Customer 7 │ 56 │ 252.64 │ Electronics │ │ Customer 5 │ 25 │ 225.67 │ Groceries │ │ Customer 26 │ 29 │ 200.45 │ Clothing │ ├───────────────┴───────┴─────────────────┴─────────────┤ │ 16 rows 4 columns │ └───────────────────────────────────────────────────────┘
This query helps identify high-spending customers and their purchases.
Grouping and Aggregating Data for Insights
Let’s now calculate total sales per product category:
SELECT category, COUNT(*) AS total_purchases, SUM(purchase_amount) AS total_sales, AVG(purchase_amount) AS avg_spent
FROM read_csv('shopping_data.csv')
GROUP BY category
ORDER BY total_sales DESC;
Output:
┌─────────────┬─────────────────┬────────────────────┬────────────────────┐ │ category │ total_purchases │ total_sales │ avg_spent │ │ varchar │ int64 │ double │ double │ ├─────────────┼─────────────────┼────────────────────┼────────────────────┤ │ Groceries │ 9 │ 2716.7100000000005 │ 301.85666666666674 │ │ Electronics │ 12 │ 2119.94 │ 176.66166666666666 │ │ Books │ 7 │ 1834.6799999999998 │ 262.09714285714284 │ │ Clothing │ 2 │ 616.53 │ 308.265 │ └─────────────┴─────────────────┴────────────────────┴────────────────────┘
This will show:
- How many transactions occurred in each category
- Total revenue per category
- Average spending per transaction
Wrapping Up
DuckDB makes it easy to analyze large CSV files efficiently using the CLI, without complex setups. In this article, we covered:
- Loading and previewing CSV files in DuckDB CLI
- Exploring dataset structure and summary statistics
- Filtering and sorting transactions
- Grouping data to extract insights
These techniques form the foundation for more advanced data analysis workflows. Want to dive deeper? Try experimenting with joins, window functions, and more.
