How to Clean Messy Excel Data with Pandas: Keep Nulls, "X" Values, and Positives

Introduction

Hello, I am PiPi. In a Python community a user asked how to process an Excel file with pandas. The question included screenshots of the original data.

The data showed two columns, but column X contained hidden issues.

Implementation Process

Two simple filters were suggested to keep non‑negative values:

df = df[df["X"] >= 0]

df = df[~df["X"] < 0]

Another answer provided a more complete script:

import pandas as pd
df = pd.read_excel('U.xlsx')
# print(df.head())
print(df["X"].value_counts())
print(df[(df.X == "X")].index.tolist())
data = df.drop(index=df[(df.X == "X")].index.tolist())
print(data.info())
data = data.reset_index(drop=True)
print(data["X"].value_counts())
df1 = data[data["X"] >= 0]
print(df1)

The requester wanted to retain null values, the literal "X" entries, and positive numbers. A custom solution was eventually posted (see image) and successfully solved the problem.

One line of the solution required further explanation (see image).

Conclusion

The article demonstrates a practical pandas data‑cleaning technique, offering clear code examples and explanations to help readers handle similar Excel processing challenges.