Thomas Eapen · Selected work

Clean durations, then pivot

Convert readable duration strings into numbers before summarising by group and activity.

Synthetic example · independently invented dataOne-page print edition · A3 landscape
01 Inspect the inputs02 Apply the method03 Check the result

INVENTED INPUTS

Six records, one deliberate fault

Six independently invented duration records
GroupActivityHH:MM
AReview00:35
AReview00:25
ABuild01:10
BReview00:40
BBuildinvalid
BBuild00:50

The text “invalid” is deliberate. It is excluded and counted before the pivot, rather than treated as a zero duration.

SELECTED PYTHON

The working calculation

Pandas is pd. rows contains the input records; every duration is expressed as hours:minutes.

data = pd.DataFrame(rows)
valid = data['duration'].str.fullmatch(r'\d+:[0-5]\d', na=False)
clean = data.loc[valid].copy()
clean['minutes'] = pd.to_timedelta(
    clean['duration'] + ':00'
).dt.total_seconds() / 60
summary = clean.pivot_table(
    index='group', columns='activity', values='minutes',
    aggfunc='sum', fill_value=0, margins=True,
    margins_name='Total'
)

The code shown here is executed to calculate this example’s results.

COMPUTED RESULT

A reconciled summary

220minutes in accepted records5 accepted · 1 excluded
Summed minutes, including row and column totals
GroupBuildReviewTotal
Group A7060130
Group B504090
Total120100220

Group A contributes 130 minutes and Group B contributes 90. The summary totals 220 minutes.

What this demonstratesCleaning establishes a trustworthy numeric measure. A pivot then exposes its distribution across two dimensions, with totals that can be checked.

Check the boundaryOnly non-negative HH:MM values with minutes below 60 are accepted. Rejected rows remain a separate quality signal.