How to Use Python Pandas to Locate First and Last Non‑Zero Values in Excel

1. Introduction

A fan in a Python community asked how to find the first and last non‑zero numbers in each row of an Excel sheet and determine their column indices. The following guide shares a solution using Python.

2. Implementation

The method uses Pandas to read the Excel file and iterate over rows with df.itertuples(). For each row, zero values are filtered out, and the first and last non‑zero values together with their column positions are recorded.

# code by: 小小明大佬
import pandas as pd
df = pd.read_excel('Data.xlsx', header=1, usecols="B:K")
result = []
for row in df.itertuples():
    code = row.Index
    row = pd.Series(row[1:])
    row.index += 1  # make index start from 1
    row = row[row != 0]
    s_num, s_i, e_num, e_i = [pd.NA] * 4
    if row.shape[0] > 0:
        s_num, s_i, e_num, e_i = row.iat[0], row.index[0], row.iat[-1], row.index[-1]
    result.append([code, s_num, s_i, e_num, e_i])
result = pd.DataFrame(result, columns=["产品编码", "起始数", "起始位置", "结束数", "结束位置"])
print(result)

Running the script produces the expected table:

Note that df.itertuples() returns namedtuple rows, which is faster than df.iterrows() and thus preferred for large datasets.

Additional solutions include an Excel formula approach and a WPS macro implementation, both illustrated with screenshots.

3. Summary

This article presents three ways to extract the first and last non‑zero values from Excel using Python, pure Excel formulas, and macro code, providing practical code snippets and performance advice to help readers solve similar data‑processing tasks.