Excel Files: Sheets, Ranges, and Formats
Unlike CSV files with a single, flat structure, Excel files are multi-dimensional workbooks. They contain multiple sheets, complex formatting, merged cells, and empty report headers. Think of an Excel file as a layered notebook, whereas a CSV is just a plain sheet of text.
To handle these files accurately in pandas, pd.read_excel() provides a suite of parameters to target precise data regions:
sheet_name: Selects the specific tab by name or 0-based index.skiprows: Bypasses the top rows, which is crucial for skipping visual report titles and empty space.nrows: Limits the import to a specific number of data rows, stopping before unwanted summary footers.usecols: Accepts Excel-style column ranges (like"A:C") to exclude irrelevant side columns.
Additionally, Excel files frequently feature merged cells (for example, a single "Category" label spanning multiple rows). When pandas reads merged cells, it extracts the value into the top-left cell and fills the remaining cells with NaN. We can repair these structural blanks using forward filling (.ffill()). It is critical to use forward-fill only to repair structurally grouped layouts like merged cells, rather than to invent missing values in actual recorded data.
Below, we first build a sample workbook simulating a formatted report, and then cleanly extract exactly the data we need.
import pandas as pd
# 1. Build a sample workbook with openpyxl
# We simulate merged 'Category' cells by leaving grouped rows as None
data = {
"Category": ["Electronics", None, "Furniture", None],
"Item": ["Laptop", "Mouse", "Desk", "Chair"],
"Sales": [1200, 25, 450, 150],
"Notes": ["Top seller", "Accessory", "Wood", "Ergonomic"]
}
df_setup = pd.DataFrame(data)
# Save to Excel, shifting down 2 rows to simulate a report title space
with pd.ExcelWriter("sales_report.xlsx", engine="openpyxl") as writer:
df_setup.to_excel(writer, sheet_name="Q1_Sales", startrow=2, index=False)
# 2. Read the specific cell range and sheet precisely
# Skip the 2 blank header rows, read exactly 4 data rows, and grab only columns A to C
df = pd.read_excel(
"sales_report.xlsx",
sheet_name="Q1_Sales",
skiprows=2,
nrows=4,
usecols="A:C"
)
print("Before forward-fill:")
print(df)
# 3. Repair structurally blank cells
# We use ffill() to propagate the category labels down.
df["Category"] = df["Category"].ffill()
print("\nAfter forward-fill:")
print(df)
Key Takeaway: Use sheet_name, skiprows, nrows, and usecols to surgically extract data from complex Excel workbooks, and use df["Column"].ffill() specifically to repair structurally merged cells without fabricating actual missing data.