python 44 lines · 7 steps

Summarizing an Excel sheet with openpyxl

Read a spreadsheet column by column and compute type-aware statistics for each one.

Explained by highlit
Intermediate excel-parsing aggregation type-dispatch streaming data-summary
1from collections import defaultdict
2from datetime import datetime
3from openpyxl import load_workbook
4 
5 
6def summarize_sheet(path, sheet=None):
7 wb = load_workbook(path, read_only=True, data_only=True)
8 ws = wb[sheet] if sheet else wb.active
9 
10 rows = ws.iter_rows(values_only=True)
11 try:
12 headers = [str(h).strip() if h is not None else f"col_{i}"
13 for i, h in enumerate(next(rows))]
14 except StopIteration:
15 return {}
16 
17 columns = defaultdict(list)
18 for record in rows:
19 for header, value in zip(headers, record):
20 if value is not None:
21 columns[header].append(value)
22 
23 summary = {}
24 for header, values in columns.items():
25 numeric = [v for v in values if isinstance(v, (int, float))]
26 col = {"count": len(values), "missing": ws.max_row - 1 - len(values)}
27 
28 if numeric:
29 col.update(
30 total=sum(numeric),
31 mean=round(sum(numeric) / len(numeric), 2),
32 minimum=min(numeric),
33 maximum=max(numeric),
34 )
35 elif any(isinstance(v, datetime) for v in values):
36 dates = [v for v in values if isinstance(v, datetime)]
37 col.update(earliest=min(dates), latest=max(dates))
38 else:
39 col.update(unique=len(set(values)))
40 
41 summary[header] = col
42 
43 wb.close()
44 return summary
01 / 01
STEP 01

Walkthrough

Space play ←→ step click any line
Three takeaways
  1. 1Streaming rows with read_only and iter_rows keeps memory flat on large spreadsheets.
  2. 2Grouping values by column first lets you compute per-column stats in one clean pass.
  3. 3Branching on runtime type lets one summarizer handle numbers, dates, and text alike.

Related explainers

python 
from flask import Blueprint, jsonify, request, abort
 
v1 = Blueprint("users_v1", __name__)
v2 = Blueprint("users_v2", __name__)

Versioning a Flask API with Blueprints

api versioning blueprints rest
Intermediate 7 steps
php 
<?php
 
namespace App\Http\Controllers;
 

Streaming a filtered CSV export in Laravel

streaming csv-export query-builder
Intermediate 9 steps
python 
import time
import threading
from enum import Enum
from functools import wraps

Building a circuit breaker in Python

circuit-breaker resilience decorators
Advanced 7 steps
python 
from django.contrib.postgres.search import SearchQuery, SearchRank, SearchVector
from django.core.cache import cache
from django.db.models import F
from django.http import JsonResponse

Ranked full-text search in Django

full-text-search debouncing caching
Advanced 9 steps
python 
import os
from datetime import timedelta
 
 

Class-based config in a Flask app factory

configuration app-factory inheritance
Intermediate 7 steps
python 
import time
 
from flask import Flask, g, request
 

Timing requests with Flask hooks

middleware request-lifecycle instrumentation
Intermediate 5 steps

Share this explainer

Here's the card — post it anywhere.

Summarizing an Excel sheet with openpyxl — share card
Made with highlit — turn any snippet into a walkthrough like this in about a minute.
Explain your code