How to Clean Messy CSV Data with Python Pandas: A Step-by-Step Guide

Hello, I'm Pipi.

Introduction

Recently I asked a practical Pandas question in a Python community group; the AI-generated answer was unsatisfactory, so I consulted a senior teacher and will share the provided code.

Implementation Process

The original fan data contains irregularities such as spaces, Chinese characters, asterisks, and missing brackets, which must be cleaned before further analysis.

The teacher's specific code is shown below:

import re
import pandas as pd

result = []
# Open original txt file
with open('data.txt', 'r', encoding='utf-8') as f:
    # Read line by line
    for line in f:
        if '[' in line and ']' in line:
            result.append(eval(line))
        elif '数据' in line:
            if "备注" not in line:
                line = re.sub(r"[\u4e00-\u9fa5【】！\]", "", line).strip()
                result.append(eval(line))
        elif "*" not in line:
            line = line.replace("]", "")
            result.append(eval(line))

res = [result[i:i+3] for i in range(0, len(result), 3)]
df = pd.DataFrame(res)
df = df.explode([1, 2], ignore_index=True)
df.columns = ["日期", "股票代码", "data"]
print(df)
df.to_excel("data.xlsx")

Running the script produces the expected DataFrame, as shown:

Summary

This article presented a practical Pandas problem, detailed the data cleaning steps, and provided a complete code solution to help the fan successfully resolve the issue.