Mastering Data Cleaning: Handling Missing Values, Outliers, and Inconsistencies with Pandas

1. Missing Values

Missing data frequently appears in high‑frequency financial series; it can be "normal" (e.g., a trading halt) or "abnormal" (data simply not recorded). Effective cleaning requires two programs: one to detect missing entries and another to fill them.

The typical workflow is:

Detect missing values and analyze their patterns.

Select an appropriate imputation method (e.g., forward fill, backward fill, or algorithmic estimation).

Apply the chosen method.

Re‑check the dataset and repeat until missingness is within acceptable limits.

Two practical checks are recommended before filling:

Verify that the source data itself is complete; sometimes extraction scripts introduce gaps.

If the source is incomplete, seek alternative data providers.

If no external source is available, use algorithmic filling such as forward or backward propagation.

Common Pandas functions for imputation:

df = pd.DataFrame([
    [np.nan, 2, np.nan, 0],
    [3, 4, np.nan, 1],
    [np.nan, np.nan, np.nan, 5],
    [np.nan, 3, np.nan, 4]
], columns=list('ABCD'))

Forward fill example:

df_filled = df.fillna(method='ffill')

Backward fill is also supported, but may require a specific fill value when the data pattern demands it.

2. Noise or Outliers

Outliers arise either from data entry errors (e.g., a price of 10000 instead of 10) or from genuine extreme events (e.g., market crashes). The cleaning process typically follows three steps:

Detect outliers using statistical rules, such as values beyond three standard deviations from the mean.

Manually verify whether each outlier is an error or a legitimate extreme observation.

Correct or remove erroneous points; retain genuine outliers for specialized modeling.

3. Data Inconsistency

When multiple data providers are combined, inconsistencies often emerge. For example, comparing post‑adjusted price series from Wind, Bloomberg, Tonghuashun, and CSMAR may reveal divergent values for the same timestamp. Cross‑validation helps identify the unreliable source, after which the trustworthy dataset is selected for further analysis.

In practice, inconsistency issues are far more varied than the three categories above, requiring systematic and meticulous handling.

Conclusion

Data cleaning can consume up to 80 % of a data‑science project’s effort, especially with large, noisy, or multi‑source datasets. By following the outlined detection, diagnosis, and remediation steps—and by leveraging Pandas utilities—practitioners can significantly reduce the impact of dirty data on downstream modeling.