Image by Editor | Midjourney

Understanding data through descriptive statistics is a necessary first step in any data analysis process. It helps us summarize key characteristics of a dataset, identify patterns, and detect anomalies.

Unlike traditional databases, DuckDB is optimized for analytical workloads, making it ideal for exploring large datasets quickly.

In this article, we’ll use DuckDB to compute descriptive statistics on a cab ride dataset by learning how to:

• Load and query CSV data efficiently using DuckDB
• Compute essential descriptive statistics such as mean, median, standard deviation, and percentiles
• Interpret these statistics to understand fare distributions, trip durations, and tipping behavior using sample car rides data

Let’s begin.

🔗 Link to the Google Colab notebook

Setting Up DuckDB

First, install DuckDB if you haven’t already. You can install it using pip:

pip install duckdb

Next, import DuckDB into your Python script or your preferred Jupyter Notebook environment:

import duckdb

▶️ Now get the cab rides dataset (CSV file)

DuckDB is designed to efficiently process structured data, particularly from files like CSV or Parquet. You can query it like so:

duckdb.query("SELECT * FROM read_csv_auto('/content/cab_ride_data.csv') LIMIT 5").df()

Computing Descriptive Statistics

Now that we have our data loaded, let’s start calculating key statistics and interpreting what they mean.

Mean and Median

Why are these useful?

• The mean (average) gives an overall measure of central tendency but is sensitive to outliers.
• The median is the middle value, which is more robust against extreme values.

To compute these for fares, tips, trip distances, and durations, let’s run the following query:

duckdb.query("""
    SELECT
        AVG(fare) AS avg_fare,
        MEDIAN(fare) AS median_fare,
        AVG(tip) AS avg_tip,
        MEDIAN(tip) AS median_tip,
        AVG(total_distance_km) AS avg_distance,
        MEDIAN(total_distance_km) AS median_distance,
        AVG(duration_min) AS avg_duration,
        MEDIAN(duration_min) AS median_duration
    FROM read_csv('/content/cab_ride_data.csv')
""").df()

This helps us understand typical fare prices, tipping behaviors, and trip lengths.

 	avg_fare 	median_fare 	avg_tip 	median_tip 	avg_distance 	median_distance  avg_duration 	median_duration
0 	27.12023 	27.045 	        4.19085 	3.46 	        15.10572 	 14.9            52.44264 	46.79

Standard Deviation and Variance

Why are these useful?

• Standard deviation tells us how much the values deviate from the mean. A high standard deviation for fares might indicate that some rides are significantly more expensive.
• Variance is another measure of spread, though it is less interpretable because it’s in squared units.

To compute standard deviation and variance, run:

duckdb.query("""
    SELECT
        STDDEV(fare) AS std_fare,
        VARIANCE(fare) AS var_fare,
        STDDEV(tip) AS std_tip,
        VARIANCE(tip) AS var_tip,
        STDDEV(total_distance_km) AS std_distance,
        VARIANCE(total_distance_km) AS var_distance,
        STDDEV(duration_min) AS std_duration,
        VARIANCE(duration_min) AS var_duration
    FROM read_csv('/content/cab_ride_data.csv')
""").df()

If the standard deviation of fares is high, it suggests there are both cheap and expensive rides, making the dataset more diverse.

 	std_fare 	var_fare 	std_tip 	var_tip 	std_distance 	var_distance 	std_duration 	var_duration
0 	13.036581 	169.952437 	3.273766 	10.717541 	8.375241 	70.144664 	33.013551 	1089.894537

Minimum, Maximum, and Range

Why are these useful?

• The minimum and maximum values show the boundaries of our data.
• The range (max – min) gives a quick sense of spread.

duckdb.query("""
    SELECT
        MIN(fare) AS min_fare, MAX(fare) AS max_fare, MAX(fare) - MIN(fare) AS range_fare,
        MIN(tip) AS min_tip, MAX(tip) AS max_tip, MAX(tip) - MIN(tip) AS range_tip,
        MIN(total_distance_km) AS min_distance, MAX(total_distance_km) AS max_distance, MAX(total_distance_km) - MIN(total_distance_km) AS range_distance,
        MIN(duration_min) AS min_duration, MAX(duration_min) AS max_duration, MAX(duration_min) - MIN(duration_min) AS range_duration
    FROM read_csv('/content/cab_ride_data.csv')
""").df()

This helps us detect any extreme values, such as unusually high fares.

 	min_fare 	max_fare 	range_fare 	min_tip 	max_tip 	range_tip 	min_distance 	max_distance 	range_distance 	min_duration 	max_duration 	range_duration
0 	5.01 	        49.99 	        44.98 	        0.0 	        14.46 	    14.46 	1.01 	29.93 	28.92 	2.21

Percentiles (Quartiles)

Why are these useful?

• Quartiles split the dataset into four equal parts.
• The 25th percentile (Q1) represents the lower quartile, below which 25% of values fall.
• The 50th percentile (Q2, median) represents the middle value.
• The 75th percentile (Q3) represents the upper quartile.

Let’s calculate quartiles for a few fields:

duckdb.query("""
    SELECT
        QUANTILE_CONT(fare, 0.25) AS q1_fare,
        QUANTILE_CONT(fare, 0.50) AS median_fare,
        QUANTILE_CONT(fare, 0.75) AS q3_fare,
        QUANTILE_CONT(total_distance_km, 0.25) AS q1_distance,
        QUANTILE_CONT(total_distance_km, 0.50) AS median_distance,
        QUANTILE_CONT(total_distance_km, 0.75) AS q3_distance
    FROM read_csv('/content/cab_ride_data.csv')
""").df()

Output:

 	q1_fare 	median_fare 	q3_fare 	q1_distance 	median_distance q3_distance
0 	15.47 	        27.045 	        38.5625 	    7.925 	14.9 	    22.2975

Wrapping Up

Descriptive statistics provide a solid foundation for understanding a dataset before deeper analysis. With DuckDB, we can efficiently compute key metrics directly from CSV files, making it a great choice for data exploration. Try running these queries on your own and modifying them to understand the dataset better.