invalid-data-cleaning

Invalid_Data_Cleaning

This sub-skill covers one capability of the Excel workflow. For reading/counting/Parquet optimization, see the parent workflow SKILL.md.

Skill Steps

Step1 Determine based on the total number of rows whether the data volume is too large. If the condition is met, convert the Excel file to Parquet format to improve read/write efficiency, then read the data for subsequent analysis.

import pandas as pd

file_path = "input_data.xlsx"
parquet_path = "temp_data.parquet"

# 读取 Excel 文件并转换为 Parquet 格式
xls = pd.ExcelFile(file_path)
dfs = []
for sheet in xls.sheet_names:
    df_sheet = pd.read_excel(xls, sheet_name=sheet)
    dfs.append(df_sheet)

Merge all sheet data and write it to a Parquet file

if dfs: df_all = pd.concat(dfs, ignore_index=True) df_all.to_parquet(parquet_path, engine='pyarrow', index=False)

Read the Parquet file for subsequent processing

df = pd.read_parquet(parquet_path)


Step2 对目标文本字段中的特殊字符（如 #、-、数字）进行清洗，使用正则表达式仅保留中文字符。
```python
import pandas as pd
import re

target_col = 'target_column' # 替换为实际需要清洗的列名

# 定义清洗函数
def clean_chinese_text(text):
    if pd.isna(text):
        return text
    s = str(text)
    # 提取所有中文字符（Unicode 范围：[一-鿿]）
    chinese_chars = re.findall(r'[一-鿿]', s)
    cleaned = ''.join(chinese_chars)
    return cleaned if cleaned else ''

# 应用清洗函数
if target_col in df.columns:
    df[target_col] = df[target_col].apply(clean_chinese_text)

Step3 Save the cleaned data as a spreadsheet file (.xlsx), and provide a local download link in the report.

import pandas as pd

# 保存清洗后的数据为 .xlsx 文件
output_path = "cleaned_data.xlsx"
df.to_excel(output_path, index=False)

print("清洗后的数据已保存至:", output_path)
# 生成本地文件下载链接
print("下载链接:", f"file://{output_path}")

About this skill

Skill Steps