当前位置：首页 > web >正文

数据清理的例子

web 2025/7/18 14:25:08

文章目录

- - df.duplicated().sum()
  - - 删除相同项
  - df['Name'] = df['Name'].str.lstrip()
  - - 变体
  - df['Name'] = df['Name'].ffill()

本文介绍了使用Pandas库进行数据处理的基础操作，包括数据读取、保存、索引操作和缺失值处理等。主要内容涵盖：

数据读写：使用read_csv()读取CSV文件，to_csv()保存数据
数据查看：head()/tail()查看首尾行，dtypes检查列类型，describe()获取统计信息
索引操作：loc基于标签选择，iloc基于位置选择
数据过滤：布尔条件筛选、多条件组合查询
列操作：添加/删除/修改列
缺失值处理：isna()检测缺失值

文中通过示例代码展示了Pandas的基本语法和应用场景，适合Python数据分析初学者参考。

import pandas as pd
import numpy as np
import os# Ensure the output directory exists
output_dir = r'D:\my_app\python\python project\data'
os.makedirs(output_dir, exist_ok=True)# Set random seed for reproducibility
np.random.seed(42)# Create synthetic dataset
data = {'Name': ['  John Doe  ', 'Alice Smith', 'Bob Johnson  ', '  Mary Brown', 'John Doe','Tom Wilson ', np.nan, 'Sarah Davis', '  Emma Clark ', 'Michael Lee','John Doe', 'Alice Smith', 'David Kim  ', 'Lisa White', np.nan,'Chris Evans', 'Anna Taylor  ', 'Mark Chen', '  Jane Doe', 'Tom Wilson','Emily Green', '  Paul Adams ', 'Laura King', 'James Lee', '  Amy Chen  '],'Age': [25, 30, np.nan, 45, 25, 35, 28, np.nan, 50, 32,25, 30, 40, np.nan, 27, 33, 29, 60, 45, 35,np.nan, 41, 26, 38, 31],'City': [' New York ', 'Paris  ', '  Tokyo', 'London ', 'New York','  Sydney', 'Berlin  ', np.nan, '  Tokyo ', 'Paris',' New York', 'Paris', '  Berlin ', 'London', 'Chicago ',np.nan, 'Sydney ', 'Tokyo', '  London', 'Sydney',' Chicago', 'Berlin', '  Paris ', 'Tokyo  ', np.nan],'Purchase_Amount': [100.50, 200.75, 150.00, np.nan, 100.50, 300.25, 175.00, 250.00, 400.00, np.nan,100.50, 200.75, 180.00, 220.00, np.nan, 190.00, 310.00, np.nan, 260.00, 300.25,270.00, 230.00, 210.00, np.nan, 320.00]
}# Create original DataFrame
df_original = pd.DataFrame(data)# Debug: Check potential duplicates after simulating text cleaning
df_temp = df_original.copy()
df_temp['Name'] = df_temp['Name'].str.strip()
df_temp['City'] = df_temp['City'].str.strip()
print("\nPotential duplicate rows in original (after simulating text cleaning):")
print(df_temp[df_temp.duplicated(keep=False)])# Create a copy for cleaning
df = df_original.copy()# Check duplicates before cleaning
print("Duplicates in original:", df.duplicated().sum())# Clean text to ensure duplicates are correctly identified
df['Name'] = df['Name'].str.strip()
df['City'] = df['City'].str.strip()# Check duplicates after text cleaning
print("Duplicates after text cleaning:", df.duplicated().sum())
#把删除每个字符串的前导和尾随空格（空格、制表符、换行符）# Remove duplicates
df = df.drop_duplicates()
print("Duplicates after drop:", df.duplicated().sum())# Handle missing values
df['Name'] = df['Name'].ffill()
df['City'] = df['City'].ffill()
df['Age'] = df['Age'].fillna(df['Age'].mean())
df['Purchase_Amount'] = df['Purchase_Amount'].fillna(df['Purchase_Amount'].median())# Convert 'Age' to integer
df['Age'] = df['Age'].astype(int)# Save both datasets to the same Excel file in different sheets
output_path = os.path.join(output_dir, 'datasets.xlsx')
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:df_original.to_excel(writer, sheet_name='Original', index=False)df.to_excel(writer, sheet_name='Cleaned', index=False)# Compare datasets
print("\nOriginal Dataset:")
print(df_original)
print("\nCleaned Dataset:")
print(df)
print("\nSummary of Changes:")
print(f"Original shape: {df_original.shape}")
print(f"Cleaned shape: {df.shape}")
print(f"Missing values in original:\n{df_original.isna().sum()}")
print(f"Missing values in cleaned:\n{df.isna().sum()}")

df.duplicated().sum()

示例用法：

import pandas as pd# Create a sample DataFrame with duplicates
df = pd.DataFrame({'Name': ['Alice', 'Bob', 'Alice', 'Charlie'],'Age': [25, 30, 25, 35]
})# Check for duplicates
duplicates = df.duplicated()
print(duplicates)

输出：
0 False
1 False
2 True
3 False
dtype: bool

要获取重复行的总数（如之前所做的那样），请使用 .sum() ：

print("Duplicates in original:", df.duplicated().sum())

输出：

Duplicates in original: 1

删除相同项

import pandas as pd# Create a sample DataFrame with duplicates
df = pd.DataFrame({'Name': ['Alice', 'Bob', 'Alice', 'Charlie'],'Age': [25, 30, 25, 35]
})#duplicates_all = df.duplicated(keep=False)
# print(duplicates_all)df_no_duplicates = df.drop_duplicates()
print(df_no_duplicates)

df.duplicated(subset=None, keep=‘first’)函数

keep ：控制要标记哪些重复项：

first：标记除第一次出现之外的所有重复项（默认）。

last：标记除最后一次出现之外的所有重复项。

False ：将所有重复行标记为 True 。

df.drop_duplicates(subset=None, keep='first', inplace=False, ignore_index=False)函数

first：保留第一个匹配项并删除其余匹配项（默认）。
last ：保留最后一次出现的内容并删除其余内容。
False ：删除所有重复的行（不保留任何重复项）。

df[‘Name’] = df[‘Name’].str.lstrip()

import pandas as pd# Load the DataFrame
df = pd.read_csv(r'D:\my_app\python\python project\data\sample_data.csv')# Add some whitespace to the 'Name' column for demonstration
df.loc[0, 'Name'] = '  Alice  '  # Leading and trailing spaces
df.loc[1, 'Name'] = 'Bob\n'     # Trailing newline
print("Before cleaning:")
print(df)df['Name'] = df['Name'].str.strip()
print("\nAfter cleaning with str.strip():")
print(df)