Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Parse Records That Span Multiple Rows in Excel with pandas

Bank statements wrap long descriptions onto extra rows, and contact exports stack one field per row. Number each record from the row that starts it, join the continuation lines, pivot stacked fields into columns, and catch orphan lines and repeated fields instead of silently mixing records.
Messy spreadsheetsData cleaningpandas

Some exports put one record on several rows. A bank statement wraps a long description onto the next two lines, leaving the date and amount blank there. A contact list from an old system writes one field per row — Name, Email, Phone — with a blank line between people. Read either with pd.read_excel and every line becomes a row, so descriptions are cut into fragments and contacts are scattered across rows. This guide, part of Extracting Data from Messy Excel Sheets, rebuilds one row per record for both layouts and checks that nothing was attached to the wrong record.

Continuation lines folded back into their records The statement has six lines. The card payment's description continues on two more lines with no date or amount, and the transfer's description continues on one. After grouping, there are three transactions, each with its full description, date and amount. A B C 1 Date Description Amount 2 03-02 CARD PAYMENT TO -42.10 3 GREEN GROCER LTD 4 REF 88213 5 03-03 SALARY MARCH 2500.00 6 03-04 TRANSFER TO -300.00 7 SAVINGS ACCOUNT A B C 1 Date Description Amount 2 03-02 CARD PAYMENT… -42.10 3 03-03 SALARY MARCH 2500.00 4 03-04 TRANSFER… -300.00 before after group by record

Prerequisites

Bash
pip install pandas openpyxl

A statement export with wrapped descriptions:

Python
import datetime as dt

import pandas as pd
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
for row in [["Date", "Description", "Amount"],
            [dt.datetime(2026, 3, 2), "CARD PAYMENT TO", -42.10],
            [None, "GREEN GROCER LTD", None],
            [None, "REF 88213", None],
            [dt.datetime(2026, 3, 3), "SALARY MARCH", 2500],
            [dt.datetime(2026, 3, 4), "TRANSFER TO", -300],
            [None, "SAVINGS ACCOUNT", None]]:
    ws.append(row)
wb.save("statement.xlsx")
lines = pd.read_excel("statement.xlsx")
print(len(lines))            # 6 lines for 3 transactions

Step 1 — Find the rows that start a record

Every layout has some signal that a new record begins. In the statement it is a date: continuation lines never have one. Turn that signal into a boolean, then take its cumulative sum. The sum increases by one at each start row and stays the same on the continuation lines below it, so it numbers the records:

Python
starts = lines["Date"].notna()
record = starts.cumsum()
print(record.tolist())       # [1, 1, 1, 2, 3, 3]

Other exports use other signals: an invoice number, a value in the first column, a row that is not indented, or a line whose text matches a pattern such as a transaction code. Whatever it is, reduce it to one boolean Series; the rest of the method is the same. When the whole record sits in a single text column — a pasted log or a PDF converted to Excel — a regular expression usually provides the signal:

Python
text = pd.Series(["02/03 CARD PAYMENT TO", "GREEN GROCER LTD", "03/03 SALARY MARCH", "04/03 TRANSFER TO"])
text_starts = text.str.match(r"^\d{2}/\d{2}\s")
print(text_starts.cumsum().tolist())     # [1, 1, 2, 3]

Anchor the pattern to the start of the line and make it specific enough that a continuation line cannot match by accident — a description that happens to begin with a number would otherwise start a record of its own.

The cumulative sum gives every line its record number The start marker is true on the lines with a date and false on the continuation lines. Its running total is 1, 1, 1 for the card payment, 2 for the salary and 3, 3 for the transfer, so each continuation line carries the number of the record above it. A B C D 1 Date Description starts record 2 03-02 CARD PAYMENT TO True 1 3 GREEN GROCER LTD False 1 4 REF 88213 False 1 5 03-03 SALARY MARCH True 2 6 03-04 TRANSFER TO True 3 7 SAVINGS ACCOUNT False 3 start rows in blue; the teal column is starts.cumsum()

Step 2 — Aggregate each record

Group by the record number and give each column its own rule: join the text, keep the first date and amount. Named aggregation says this directly:

Python
transactions = lines.groupby(record).agg(
    date=("Date", "first"),
    description=("Description", lambda s: " ".join(s.dropna().astype(str).str.strip())),
    amount=("Amount", "first"),
    lines=("Description", "size"),
).reset_index(drop=True)
print(transactions)

first skips missing values, so it returns the date and amount from the start row even though the continuation lines are blank there. Dropping missing values before joining keeps stray nan text out of the description. The lines column records how many rows each transaction came from, which is useful when checking the result against the source. Aggregating different columns with different rules is covered in Use Different Aggregations per Column in a pandas Pivot.

Step 3 — Check that nothing was misattached

The method assumes the file follows the pattern. Three checks catch the cases where it does not:

Python
problems = []
if not starts.iloc[0]:
    problems.append("the first line is a continuation with no record above it")
stray_amounts = lines.loc[~starts & lines["Amount"].notna()]
if not stray_amounts.empty:
    problems.append(f"continuation lines with amounts: {stray_amounts.index.tolist()}")
if abs(transactions["amount"].sum() - lines["Amount"].sum()) > 1e-9:
    problems.append("total changed during regrouping")
print(problems or "ok")      # ok

An orphan line at the top usually means the file was cut from a longer statement, and its text belongs to a record on a previous page. A continuation line with an amount means the signal is wrong — perhaps a fee printed on its own line without a date — and first would silently drop that amount. The total check catches both. Page breaks that repeat the column headers in the middle of the data are a related problem; remove repeated header rows before numbering records.

Step 4 — Blocks separated by blank lines

Some exports put one field per row and separate records with a blank line — contact cards, or a form exported as "field, value" pairs. Here the record signal is the blank row:

Python
wb = Workbook()
ws = wb.active
for row in [["Name", "Ann Lee"], ["Email", "ann@example.com"], ["Phone", "0123 456"], [None, None],
            ["Name", "Bob Ray"], ["Email", "bob@example.com"], [None, None],
            ["Name", "Cai Wu"], ["Phone", "0456 789"], ["Email", "cai@example.com"]]:
    ws.append(row)
wb.save("contacts.xlsx")

kv = pd.read_excel("contacts.xlsx", header=None, names=["field", "value"])
blank = kv.isna().all(axis=1)
kv = kv.assign(record=blank.cumsum())[~blank]
print(kv["record"].tolist())     # [0, 0, 0, 1, 1, 2, 2, 2]

Reading with header=None matters: otherwise pandas would take "Name, Ann Lee" as the column headers. Fields do not have to appear in the same order in every block — Cai Wu has Phone before Email — because the next step matches them by name, not position.

Step 5 — Pivot stacked fields into columns

With a record number on every line, pivot the field names into columns. Check first that no field repeats within a record, because pivot raises an error on duplicates and a silent "keep one" would lose data:

Python
repeats = kv[kv.duplicated(["record", "field"], keep=False)]
if not repeats.empty:
    raise ValueError(f"repeated fields:\n{repeats}")
contacts = kv.pivot(index="record", columns="field", values="value").reset_index(drop=True)
contacts = contacts.reindex(columns=["Name", "Email", "Phone"])
contacts.columns.name = None
print(contacts)

Bob Ray has no phone, so his cell is empty rather than borrowing a value from a neighbour. reindex fixes the column order and guarantees every expected column exists even if no record in this file had it. If a field can legitimately repeat — several phone numbers — join the values with pivot_table(aggfunc="; ".join) instead of raising.

The same four steps for any multi-row layout Decide which rows start a record or which rows separate records, turn that into a boolean, number the records with a cumulative sum, aggregate or pivot each record into one row, then check for orphan lines, stray values and changed totals. Find the signal start row or blank Boolean marker one per line Number records cumsum() Aggregate or pivot one row each Check the result orphans, totals

Step 6 — Write the rebuilt records

Both results are ordinary one-row-per-record tables and can be written straight to Excel, with the source line count kept for auditing:

Python
with pd.ExcelWriter("rebuilt.xlsx") as writer:
    transactions.to_excel(writer, sheet_name="Transactions", index=False)
    contacts.to_excel(writer, sheet_name="Contacts", index=False)
print(pd.read_excel("rebuilt.xlsx", sheet_name="Transactions")["description"].iloc[0])
# CARD PAYMENT TO GREEN GROCER LTD REF 88213

Long joined descriptions are easier to read with wrapped text and a wider column; see Wrap Text and Align Cells with openpyxl.

Common pitfalls

  • Forward-filling instead of grouping. Filling the date down gives every continuation line a date and an amount of its own, so the amount is counted three times. Group, do not fill.
  • Sorting before numbering. The method depends on the original line order. Number the records first, then sort the result.
  • Trusting a single file. A layout that holds for this month's export may not hold for the next. Keep the checks from Step 3 in the pipeline permanently, so a changed layout fails the run instead of producing plausible nonsense.
  • Joining with no separator. "".join glues "TO" and "GREEN" into "TOGREEN". Join with a space, or with a line break if the text will be shown wrapped.

Conclusion

Multi-row records all yield to the same idea: turn "this line starts a record" or "this line separates records" into a boolean, number the records with its cumulative sum, then aggregate each record with per-column rules or pivot its stacked fields into columns. Check for orphan lines, values on continuation rows, repeated fields and changed totals before trusting the result.

Frequently asked questions

How do I merge continuation rows into the row above in pandas? Mark the rows that start a record — for example, rows with a date — and take the cumulative sum of that marker. Every continuation row gets the same number as its record, so groupby on that number with " ".join for text and "first" for other columns rebuilds one row per record.

What if a record is identified by a blank line instead of a start row? Take the cumulative sum of the blank-row marker instead. Each blank row increments the number, and the rows up to the next blank belong to one record. Drop the blank rows before grouping.

How do I turn one-field-per-row blocks into columns? Give each block a record number, then pivot with the field names as columns and the values as cell contents. Check for repeated fields first, because pivot raises an error on duplicates.

How do I know the parse went right? Check that no continuation line appears before the first record, that continuation lines carry no amounts, and that totals match the source — the sum of amounts should be unchanged by the regrouping.

More on messy spreadsheets

More on messy spreadsheets →