Lesson 4 · Intermediate · 25 min

Build a simple RCD summary with pandas

Group collections by collector and fund, list the receipts used, and match collections against deposits.

✓ Last checked: November 2026 · code run, output real, facts checked

The Report of Collections and Deposits brings three questions together: which receipts were used, how much was collected for each fund, and whether all of it reached the bank. The pandas library answers each question in a few lines. This lesson builds a simple summary of those three parts; it is a learning exercise, not the official report form.

Note: pandas is not part of standard Python. If you skipped step 5 of Lesson 0, install it once with: py -m pip install pandas (Windows) or python3 -m pip install pandas (Mac).

The data

Save the collections of two collectors as collections.csv:

collector,or_number,fund,amount
Collector A,7650101,General Fund,4500.00
Collector A,7650102,General Fund,1250.75
Collector A,7650103,Special Education Fund,1250.75
Collector A,7650104,General Fund,105.00
Collector A,7650105,Trust Fund,2350.50
Collector A,7650106,General Fund,1800.00
Collector B,7650201,General Fund,600.00
Collector B,7650202,General Fund,980.40
Collector B,7650203,Special Education Fund,980.40
Collector B,7650204,General Fund,12750.00
Collector B,7650205,General Fund,250.00

and the deposits made per fund as deposits.csv:

fund,deposited
General Fund,22236.15
Special Education Fund,2231.15
Trust Fund,2000.00

The code

"""A simple Report of Collections and Deposits (RCD) summary with pandas."""

import pandas as pd

pd.set_option("display.float_format", "{:,.2f}".format)

collections = pd.read_csv("collections.csv")
deposits = pd.read_csv("deposits.csv")

# A. Receipts used by each collector
receipts = collections.groupby("collector").agg(
    first_or=("or_number", "min"),
    last_or=("or_number", "max"),
    receipts=("or_number", "count"),
    amount=("amount", "sum"),
)
print("A. Official receipts used")
print(receipts, end="\n\n")

# B. Collections per fund, one column per collector
by_fund = collections.pivot_table(
    index="fund", columns="collector", values="amount",
    aggfunc="sum", fill_value=0, margins=True, margins_name="Total",
)
print("B. Collections by fund")
print(by_fund, end="\n\n")

# C. Deposits against collections
summary = collections.groupby("fund")["amount"].sum().to_frame("collected")
summary = summary.join(deposits.set_index("fund"))
summary["undeposited"] = (summary["collected"] - summary["deposited"]).round(2)
print("C. Collections and deposits")
print(summary)

Output

A. Official receipts used
             first_or  last_or  receipts    amount
collector                                         
Collector A   7650101  7650106         6 11,257.00
Collector B   7650201  7650205         5 15,560.80

B. Collections by fund
collector               Collector A  Collector B     Total
fund                                                      
General Fund               7,655.75    14,580.40 22,236.15
Special Education Fund     1,250.75       980.40  2,231.15
Trust Fund                 2,350.50         0.00  2,350.50
Total                     11,257.00    15,560.80 26,817.80

C. Collections and deposits
                        collected  deposited  undeposited
fund                                                     
General Fund            22,236.15  22,236.15         0.00
Special Education Fund   2,231.15   2,231.15         0.00
Trust Fund               2,350.50   2,000.00       350.50

How it works

groupby("collector").agg(...) splits the receipts by collector and computes several results at once: the first and last receipt number, how many were used, and the total amount.

pivot_table(...) spreads the totals into a grid, one row per fund and one column per collector. margins=True adds the Total row and column, and fill_value=0 fills funds a collector did not collect.

join(...) lines up deposits with collections by fund name, so the two amounts sit side by side and can be subtracted.

.round(2) pandas works in floats, so the difference is rounded to the centavo before it is shown or compared.

Section C shows 350.50 of Trust Fund collections not yet deposited. That is exactly the kind of line that should be explained, or deposited, before the report is signed.

Try it yourself

"""Answer: save the summary tables to one Excel file, one sheet each.

Needs:  pip install pandas openpyxl
"""

import pandas as pd

collections = pd.read_csv("collections.csv")
deposits = pd.read_csv("deposits.csv")

receipts = collections.groupby("collector").agg(
    first_or=("or_number", "min"), last_or=("or_number", "max"),
    receipts=("or_number", "count"), amount=("amount", "sum"),
)
by_fund = collections.pivot_table(index="fund", columns="collector", values="amount",
                                  aggfunc="sum", fill_value=0, margins=True, margins_name="Total")
summary = collections.groupby("fund")["amount"].sum().to_frame("collected")
summary = summary.join(deposits.set_index("fund"))
summary["undeposited"] = (summary["collected"] - summary["deposited"]).round(2)

with pd.ExcelWriter("rcd_summary.xlsx") as excel:
    receipts.to_excel(excel, sheet_name="Receipts used")
    by_fund.to_excel(excel, sheet_name="By fund")
    summary.to_excel(excel, sheet_name="Deposits")

# Open the file again to confirm what was saved
print("Saved rcd_summary.xlsx with sheets:", pd.ExcelFile("rcd_summary.xlsx").sheet_names)

Saved rcd_summary.xlsx with sheets: ['Receipts used', 'By fund', 'Deposits']