Toolchain | For: Data analysts tired of manual monthly reporting
The Pain: From 2 Hours to 5 Minutes Per Report
Many data analysts doing freelance work spend the most time not on analysis, but on formatting results into polished PDFs for clients. Manual reports are costly, error-prone, and leave clients unsatisfied.
With DuckDB + Python + ReportLab, this entire workflow compresses from 2 hours down to 5 minutes, supporting batch generation across multiple clients with near-zero marginal cost.
Step 1: Core Analysis with DuckDB
import duckdb
import pandas as pd
con = duckdb.connect()
# read_csv_auto infers column types automatically—no schema needed
analysis_sql = """
SELECT
DATE_TRUNC('month', order_date) AS month,
product_category,
COUNT(*) AS order_count,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value,
COUNT(DISTINCT customer_id) AS unique_customers
FROM read_csv_auto('orders.csv')
GROUP BY month, product_category
ORDER BY month DESC, total_revenue DESC
"""
result = con.sql(analysis_sql).df()
print(result.head(10))
Why DuckDB beats traditional approaches:
| Approach | CSV Reading | Type Inference | Aggregation Speed |
|---|---|---|---|
| Pandas + manual | Specify columns | Manual astype | Loop-based |
| SQLite | Import first | Table schema required | Average |
| DuckDB | read_csv_auto one-liner | Fully automatic | Columnar, millisecond |
read_csv_auto is DuckDB’s killer feature—read any CSV directly, auto-detect delimiters, column types, and date formats. For multi-file scenarios, glob patterns merge everything: read_csv_auto('sales_data/*.csv').
Step 2: Generate Chart Visualizations
import matplotlib.pyplot as plt
import matplotlib
matplotlib.use('Agg') # headless mode for server deployment
fig, axes = plt.subplots(2, 2, figsize=(14, 10))
fig.suptitle('Monthly Sales Analysis Report', fontsize=16, fontweight='bold')
# 1. Revenue trend by category
pivot_data = result.pivot(index='month', columns='product_category',
values='total_revenue')
pivot_data.plot(kind='line', ax=axes[0, 0], marker='o')
axes[0, 0].set_title('Revenue Trend by Category')
axes[0, 0].tick_params(axis='x', rotation=45)
# 2. Pie chart for latest month
latest_month = result['month'].max()
monthly_data = result[result['month'] == latest_month]
monthly_data.plot.pie(y='total_revenue', ax=axes[0, 1],
labels=monthly_data['product_category'],
autopct='%1.1f%%')
axes[0, 1].set_title(f'{latest_month} Revenue Share')
# 3. Order value distribution
axes[1, 0].hist(result['avg_order_value'], bins=20,
color='steelblue', alpha=0.7)
axes[1, 0].set_title('Average Order Value Distribution')
axes[1, 0].set_xlabel('Amount (CNY)')
# 4. Customer count by category
pivot_customers = result.pivot(index='month',
columns='product_category',
values='unique_customers')
pivot_customers.plot.bar(stacked=True, ax=axes[1, 1])
axes[1, 1].set_title('Monthly Customers by Category')
axes[1, 1].tick_params(axis='x', rotation=45)
plt.tight_layout()
plt.savefig('report_charts.png', dpi=150, bbox_inches='tight')
plt.close()
print('Charts generated')
Step 3: Assemble Professional PDF Report
from reportlab.lib import colors
from reportlab.lib.pagesizes import A4
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, \
Image, Table, TableStyle
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.units import cm
from reportlab.pdfbase import pdfmetrics
from reportlab.pdfbase.ttfonts import TTFont
# Register Chinese font
try:
pdfmetrics.registerFont(TTFont('Chinese',
'/usr/share/fonts/truetype/wqy/wqy-zenhei.ttc'))
font_name = 'Chinese'
except:
font_name = 'Helvetica'
doc = SimpleDocTemplate('sales_report.pdf', pagesize=A4,
rightMargin=2*cm, leftMargin=2*cm,
topMargin=2*cm, bottomMargin=2*cm)
styles = getSampleStyleSheet()
title_style = ParagraphStyle('Title', parent=styles['Title'],
fontName=font_name, fontSize=18,
spaceAfter=20)
heading_style = ParagraphStyle('Heading', parent=styles['Heading1'],
fontName=font_name, fontSize=14,
spaceAfter=10)
body_style = ParagraphStyle('Body', parent=styles['Normal'],
fontName=font_name, fontSize=10,
leading=16)
story = []
# Title
story.append(Paragraph('Monthly Sales Analysis Report', title_style))
story.append(Paragraph(
f'Generated: {pd.Timestamp.now().strftime("%Y-%m-%d")}', body_style))
story.append(Spacer(1, 0.5*cm))
# Executive summary
total_revenue = result['total_revenue'].sum()
total_orders = result['order_count'].sum()
avg_value = result['avg_order_value'].mean()
story.append(Paragraph('I. Executive Summary', heading_style))
summary = (f'Analysis covers {len(result)} records across '
f'{result["product_category"].nunique()} categories. '
f'Total revenue: {total_revenue:,.0f} CNY, '
f'{total_orders:,} orders, average order value: '
f'{avg_value:.2f} CNY.')
story.append(Paragraph(summary, body_style))
story.append(Spacer(1, 0.5*cm))
# Charts
story.append(Paragraph('II. Data Visualization', heading_style))
story.append(Image('report_charts.png', width=450, height=320))
story.append(Spacer(1, 0.5*cm))
# Top 10 table
story.append(Paragraph('III. Top 10 Categories', heading_style))
top10 = result.nlargest(10, 'total_revenue')
table_data = [['Month', 'Category', 'Orders', 'Revenue', 'Avg Value']]
for _, row in top10.iterrows():
table_data.append([str(row['month'])[:10], row['product_category'],
str(int(row['order_count'])),
f"{row['total_revenue']:,.0f}",
f"{row['avg_order_value']:.2f}"])
table = Table(table_data, colWidths=[2.5*cm, 3*cm, 2*cm, 3*cm, 3*cm])
table.setStyle(TableStyle([
('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#2C3E50')),
('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
('ALIGN', (0, 0), (-1, -1), 'CENTER'),
('FONTNAME', (0, 0), (-1, 0), font_name),
('FONTSIZE', (0, 0), (-1, 0), 10),
('BOTTOMPADDING', (0, 0), (-1, 0), 12),
('GRID', (0, 0), (-1, -1), 1, colors.grey),
('BACKGROUND', (0, 1), (-1, -1), colors.HexColor('#ECF0F1')),
]))
story.append(table)
doc.build(story)
print('PDF report generated: sales_report.pdf')
Advanced: Configuration-Driven Pipeline
Wrap the above into a config-driven pipeline. Switch clients by changing a YAML file—no code modification needed:
import yaml
from datetime import datetime
def run_report(client_config_path='clients/default.yaml'):
with open(client_config_path) as f:
config = yaml.safe_load(f)
con = duckdb.connect()
sql = config['sql_template'].format(
date_range=config.get('date_range', 'last_30_days')
)
result = con.sql(sql).df()
output_name = (f"reports/{config['name']}_"
f"{datetime.now().strftime('%Y%m%d')}.pdf")
render_pdf(result, config, output_path=output_name)
print(f"✅ {config['name']} report generated")
# clients/ecommerce_a.yaml:
# name: Ecommerce Client A
# sql_template: "SELECT * FROM read_csv_auto('orders_a.csv') WHERE ..."
# date_range: last_30_days
# One configuration change, batch-generate 10+ reports per day
Comparison with Traditional Tools
| Tool | CSV Reading | SQL Aggregation | PDF Generation | Chinese Fonts | Config-Driven |
|---|---|---|---|---|---|
| Excel + VBA | Manual | Pivot tables | Print/export | Complex | Hard |
| Pandas + matplotlib | Column definition needed | Code-heavy | Extra library | Requires handling | Medium |
| Power BI | GUI import | DAX | Export PDF | Built-in | Template-driven |
| DuckDB + Python | Auto-detect | One SQL line | ReportLab | TTFont register | YAML config |
Action Plan
- Get started now: Prepare a CSV file, copy the three code blocks above, run the full pipeline
- Iterate: Customize ReportLab styles with your company logo and headers
- Scale up: Use YAML config for multiple clients—one development, batch output
- : Start with one real client, replace manual reporting, validate willingness to pay
💡 More DuckDB automation tutorials → duckdblab.org