Computational Thinking── Decomposition, abstraction, pattern recognition, algorithm design and verification, step by step on a real task

The task — what do we want to know from the request ledger?

The example task: from a request ledger for document checks (Excel, 40 rows, fictional), find, per requesting department and document type and per month, the days from request to completion and the share of returned requests. The ledger columns are request ID, document name, type, department, request date, owner, status, number of comments and completion date.

The task is worked through with five ways of thinking in turn: decomposition, abstraction, pattern recognition, algorithm design and verification.

1. Decomposition — split into questions you can answer

1. Work out the days to completion for each request
2. Decide for each request whether it was returned
3. Group by month, department and type
4. Write the grouped numbers out as a table

Left as one big question, it is unclear where to start and whether the answer is right. Split down to questions that can be answered for a single request, and each row can be checked by hand.

2. Abstraction — drop what is not needed and define the terms

Definitions: days to completion is completion date minus request date, only for rows whose status is completed or returned. The return share is the number returned divided by completed plus returned. Rows that are received or in progress count towards neither. Writing definitions first means the same yardstick is used when asking Copilot and when checking the result.

3. Pattern recognition — spot calculations with the same shape

"Per department", "per type" and "per month" all have the same shape: split the rows into groups by some criteria, do the same calculation for each group, and combine the results into one table. The pandas documentation describes this as three steps: splitting the data into groups, applying a function to each group independently, and combining the results (groupby).

Monthly groups take the same shape once dates are turned into months. According to the pandas documentation, Series.dt.to_period casts dates to periods at a given frequency ("M" for months).

4. Algorithm design — choose the order and write it

1. Read the ledger (pandas.read_excel)
2. Keep only rows with status completed or returned
3. Add a days column (completion date minus request date)
4. Add a returned flag column
5. Add a request month column (request date as a monthly period)
6. Group by department, type and month; compute count, median days and return share
7. Write the table to a new workbook

Following the order keep, add columns, group, summarize means the intermediate table at each step can be inspected.

The tally (run on the sample ledger)

"""Tally a request ledger: days to finish and return rate by department, type and month."""
import pandas as pd

ledger = pd.read_excel("samples/request_ledger.xlsx", sheet_name="依頼台帳")
done = ledger[ledger["状態"].isin(["完了", "差戻し"])].copy()
done["日数"] = (pd.to_datetime(done["完了日"]) - pd.to_datetime(done["依頼日"])).dt.days
done["差戻し"] = done["状態"] == "差戻し"
done["依頼月"] = pd.to_datetime(done["依頼日"]).dt.to_period("M")

summary = (done.groupby(["依頼部署", "種別", "依頼月"])
               .agg(件数=("依頼ID", "count"), 日数の中央値=("日数", "median"), 差戻しの割合=("差戻し", "mean"))
               .reset_index())
summary["依頼月"] = summary["依頼月"].astype(str)
summary.to_excel("out_summary.xlsx", index=False)
print("rows in ledger:", len(ledger), "/ rows used:", len(done), "/ groups:", len(summary))
print(summary.head(8).to_string(index=False))

rows in ledger: 40 / rows used: 25 / groups: 15
依頼部署      種別     依頼月  件数  日数の中央値  差戻しの割合
 人事部  Web 記事 2026-07   1     6.0     0.0
 人事部 プレスリリース 2026-07   1    12.0     1.0
 人事部     提案書 2026-07   1     5.0     0.0
 人事部     提案書 2026-08   1     3.0     1.0
 人事部    社内通知 2026-07   1    12.0     0.0
 人事部    社内通知 2026-08   3    14.0     0.0
 営業部  Web 記事 2026-07   1    14.0     1.0
 営業部     契約書 2026-08   1     5.0     0.0

5. Verification — check that another route gives the same answer

1. Do the group counts add up to the number of rows kept?
2. Are there no negative day counts (no completion date before its request date)?
3. For the largest group, does counting by plain filtering, without groupby, give the same number?
4. Is the return share between 0 and 1?

The checks (any failed assert stops the run)

"""Check the ledger tally by a second route: invariants and one group counted by plain filtering."""
import pandas as pd

ledger = pd.read_excel("samples/request_ledger.xlsx", sheet_name="依頼台帳")
done = ledger[ledger["状態"].isin(["完了", "差戻し"])].copy()
done["日数"] = (pd.to_datetime(done["完了日"]) - pd.to_datetime(done["依頼日"])).dt.days
by_group = done.groupby(["依頼部署", "種別"]).size()

assert by_group.sum() == len(done), "group counts must add up to the rows used"
assert (done["日数"] >= 0).all(), "a finish date is earlier than its request date"

dept, kind = by_group.idxmax()  # the largest group
manual = ((ledger["依頼部署"] == dept) & (ledger["種別"] == kind) & ledger["状態"].isin(["完了", "差戻し"])).sum()
assert manual == by_group[(dept, kind)], "groupby and plain filtering disagree"
rate = (done["状態"] == "差戻し").mean()
assert 0 <= rate <= 1, "a rate must be between 0 and 1"
print("largest group checked by plain filtering:", dept, kind, int(manual), "rows")
print("all checks passed")

largest group checked by plain filtering: 広報部 プレスリリース 5 rows
all checks passed

Verification counts by a different route (plain filtering) rather than repeating the tally's route (groupby); repeating the same route can simply repeat the same mistake.

6. When asking Copilot — turn the decomposition and definitions into the instructions

"Return rate" alone leaves the denominator open, so code that counts received or in-progress rows in the denominator would not contradict the instruction. That is why the definition goes into the instruction.

Summary — where each way of thinking applies

For the same thinking applied to one task from start to finish, see the End-to-End Manual.