Read an Excel Sheet Where Records Run Across Columns

Most sheets put one record per row. Some turn it sideways: the field names run down column A — Supplier, Quote date, Unit price, Minimum order — and each record sits in its own column, so three quotes can be compared side by side. It is a perfectly readable layout for people and an awkward one for read_excel, which assumes the first row holds column names. Read it naively and you get a DataFrame whose "columns" are supplier names and whose "rows" are fields, with every column a jumble of types. This page, part of Extracting Data from Messy Excel Sheets, reads such sheets properly, including two blocks of quotes on one sheet.

Prerequisites

pip install pandas openpyxl

quotes.xlsx holds five supplier quotes in two blocks separated by a blank row: three suppliers in the first block, two in the second. Each block lists supplier, quote date, unit price, minimum order, lead time and certification down column A.

Step 1 — See the problem with a normal read

import pandas as pd

naive = pd.read_excel("quotes.xlsx")
print(naive.head(4))
print(naive.dtypes.to_dict())

The supplier names have become column headers, "Quote date" and "Unit price" are values in a column called Supplier, and every data column is object because it mixes dates, prices, counts and yes/no text. None of the usual operations — filter by price, sort by lead time — can work on this shape.

Step 2 — Read with the field names as the index, then transpose

Tell read_excel there is no header row and that column A holds the row labels. Transposing then swaps the axes so each supplier becomes a row:

raw = pd.read_excel("quotes.xlsx", header=None, index_col=0, nrows=6)
quotes = raw.T.reset_index(drop=True)
quotes.columns.name = None
print(quotes)

nrows=6 limits the read to the first block for now. After .T, the field names are the columns and each supplier is one row — the tidy layout. reset_index(drop=True) replaces the leftover column positions with a clean 0, 1, 2 index, and clearing columns.name removes the stray label that transposing leaves behind.

Step 3 — Fix the column types

The transposed frame still has the problem from Step 1, turned sideways: every column is object. Before the transpose, each sheet column mixed a date, a price, an integer and a string, so pandas stored them as Python objects, and those objects are what moved into the new columns:

print(quotes.dtypes.to_dict())

quotes = quotes.infer_objects()
quotes["Quote date"] = pd.to_datetime(quotes["Quote date"])
for col in ["Unit price", "Minimum order", "Lead time (days)"]:
    quotes[col] = pd.to_numeric(quotes[col])
quotes["Certified"] = quotes["Certified"].map({"Yes": True, "No": False})
print(quotes.dtypes.to_dict())

infer_objects() recovers the obvious types; explicit to_numeric and to_datetime calls make the intended type of each field part of the code rather than a guess, and fail loudly if a quote contains something unexpected. The yes/no field is mapped to real booleans. Read Yes/No and TRUE/FALSE Columns as Booleans with pandas covers messier flag columns.

Step 4 — Read several blocks from one sheet

The sheet has a second block of quotes below a blank row. Read the whole sheet once without a header, split it at the blank rows, and apply the same transpose to each block:

full = pd.read_excel("quotes.xlsx", header=None)
blank = full.isna().all(axis=1)
block_id = blank.cumsum()

blocks = []
for _, block in full[~blank].groupby(block_id[~blank]):
    t = block.set_index(0).T.dropna(how="all").reset_index(drop=True)
    t.columns.name = None
    blocks.append(t)

all_quotes = pd.concat(blocks, ignore_index=True).infer_objects()
print(all_quotes[["Supplier", "Unit price", "Lead time (days)"]])

cumsum over the blank-row flags numbers the blocks: every blank row starts a new block. dropna(how="all") after transposing removes the empty columns the second block leaves behind — it only has two suppliers, so its third column is blank. When blocks are not separated by blank rows, split at each row whose first cell repeats the first field name (full[0] == "Supplier") instead. Read Multiple Tables from One Excel Sheet covers block detection in more depth.

Step 5 — Check the field names are consistent

Transposing relies on every block using the same field names in column A. A block with "Unit Price" instead of "Unit price", or an extra field, produces a new column full of blanks rather than an error. Compare the fields of each block before concatenating:

fields = [tuple(b.columns) for b in blocks]
if len(set(fields)) != 1:
    raise ValueError(f"blocks use different fields: {set(fields)}")
print("all blocks share", len(fields[0]), "fields")

For sheets maintained by several people, normalising the field names first — stripping spaces and fixing case, as in Standardise Excel Column Names with pandas — removes most of these mismatches before the check.

Step 6 — Compare, and write the tidy table

With one row per quote, the comparison the sheet was built for becomes a sort and a filter — cheapest certified supplier that can deliver within two weeks:

all_quotes["Unit price"] = pd.to_numeric(all_quotes["Unit price"])
all_quotes["Lead time (days)"] = pd.to_numeric(all_quotes["Lead time (days)"])
eligible = all_quotes[(all_quotes["Certified"] == "Yes") & (all_quotes["Lead time (days)"] <= 14)]
print(eligible.sort_values("Unit price")[["Supplier", "Unit price", "Lead time (days)"]].to_string(index=False))

all_quotes.to_excel("quotes_tidy.xlsx", index=False)
print(pd.read_excel("quotes_tidy.xlsx").shape)

Acme and Hooli qualify, with Acme the cheaper at 2.45. The tidy file has one row per supplier and one column per field, ready for pivots, merges or a database load; if people still prefer the side-by-side view, it is one .T away when writing a presentation sheet.

Write the side-by-side view back for readers

The people who built the sideways layout usually want to keep seeing it. Keep the tidy table as the data, and produce the comparison view from it when writing a presentation sheet — transposing back, with the supplier names as the header row:

from openpyxl import load_workbook
from openpyxl.styles import Font

view = all_quotes.set_index("Supplier").T
view.index.name = "Field"
with pd.ExcelWriter("quotes_tidy.xlsx", mode="a", engine="openpyxl", if_sheet_exists="replace") as writer:
    view.to_excel(writer, sheet_name="Comparison")
wb = load_workbook("quotes_tidy.xlsx")
ws = wb["Comparison"]
for cell in ws[1]:
    cell.font = Font(bold=True)
ws.column_dimensions["A"].width = 18
wb.save("quotes_tidy.xlsx")
print(wb.sheetnames, [c.value for c in ws[1]])

One source of truth — the tidy sheet — and a view derived from it, so the two can never disagree. Adding a sixth supplier means adding one row to the data; the comparison gains a column automatically on the next run.

Common pitfalls

• Reading with the default header. The first record's values become column names; use header=None.
• Trusting types after .T. Every column is object; convert explicitly.
• Blank columns in short blocks. Drop all-empty rows after transposing.
• Inconsistent field labels. Check that blocks share fields before stacking.
• Confusing transpose with melt. Transpose swaps records and fields; melt turns repeated measures into rows.
• Merged cells in column A. A field label merged across two rows leaves the second row without a label, and the transpose produces an unnamed column; unmerge and fill the labels first.
• Units in the labels. Keep "Lead time (days)" as the column name, or move the unit into documentation — dropping it leaves a number with no stated unit.

Performance notes

Transposing is cheap for sheets of this shape, which are small by nature — dozens of fields, hundreds of records at most. For a very wide sheet with thousands of record columns, read it once with header=None and transpose the whole frame before any type conversion, since converting columns is faster than converting rows.

Conclusion

A sheet with fields down column A and one record per column reads correctly with header=None, index_col=0 followed by .T. Convert every column to its intended type afterwards, because transposing leaves them all as object; split multi-block sheets at blank rows and transpose each block; check that all blocks use the same fields; and write the tidy one-row-per-record table for everything downstream.

Frequently asked questions

How do I read a sheet where each column is a record? Read it with header=None and index_col=0 so the first column becomes the row labels, then transpose with .T. The field names become columns and each original column becomes a row.

Why are all my columns object dtype after transposing? Before transposing, each original column mixed values of different fields — a date, a price, a count. pandas stores such a column as object, and the objects survive the transpose. Call infer_objects() and convert specific columns with to_numeric and to_datetime.

What if the sheet has several blocks of records? Split the sheet at the blank rows between blocks, or at each repetition of the first field name, read each block the same way, and concatenate the results.

Is this the same as unpivoting with melt? No. melt turns wide columns of the same measure (months, say) into rows. A transposed sheet has records in columns and fields in rows; transposing swaps the two axes so each record becomes one row.

Related

• Up one level: Extracting Data from Messy Excel Sheets — reading sheets built for people.
• Unpivot a Wide Excel Sheet with pandas melt — the other kind of "wide" sheet.
• Extract Report Header Fields from an Excel Sheet with Python — label-and-value pairs at the top of a sheet.
• Check Excel Data Types with pandas — verifying types after conversion.