# -*- coding: utf-8 -*-
"""第 8 章《Pandas 数据处理与分析》示例代码 · 模块 5：数据预处理

配套页面：https://www.qixiangsun.com.cn/python-bigdata-2026-lesson8
运行环境：Python 3.14+ / pandas 3.0.5（pandas 2.x 亦可运行，个别输出格式略有差异）
说明：本模块：缺失值 isna / dropna / fillna · 异常值查找与处理 · 重复值 · astype / str / dt。
      页面中每段代码都独立可运行，本文件按页面顺序排列；
      页面上标注“故意报错”的示例未收录（运行会中断），请到页面查看。
用法：python 示例代码_模块5_数据预处理.py
"""
import pandas as pd

# ===== [k5-overview] 先体检：行列数、每列缺失数、重复行数、金额为 0 的行数 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('行列：', df.shape)
print('每列缺失数：')
print(df.isnull().sum())
print('完全重复的行：', df.duplicated().sum())
print('金额为 0 的行：', int((df['金额'] == 0).sum()))


# ===== [k5-isnull] isnull().any() 看哪些列有缺失，sum() 数每列缺几个（例 8-7） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('哪些列有空值（any）：')
print(df.isnull().any())
print('每列缺几个（sum）：')
print(df.isnull().sum())
print('isna 与 isnull 同义：', df.isna().any().equals(df.isnull().any()))


# ===== [k5-missing-rows] 把含缺失值的行挑出来：df[df['商品名称'].isnull()] =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print(df[df['商品名称'].isnull()])
print('单价也空的订单编号：', df[df['单价'].isnull()]['订单编号'].tolist())
print('这一行有几格是空的：', int(df.loc[8].isnull().sum()))


# ===== [k5-dropna-default] 默认 dropna() 删掉含空值的整行：12 行变 11 行，删的是 302009 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('删除前：', df.shape)
clean = df.dropna()
print('删除后：', clean.shape)
dropped = df.loc[df.index.difference(clean.index), '订单编号'].tolist()
print('被删掉的订单编号：', dropped)
print('删完还有空值吗？', clean.isnull().any().any())


# ===== [k5-dropna-how] how='all' 一行不删、subset 只盯一列、axis=1 整列删掉：四种写法四种形状 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print("默认 any：", df.dropna().shape)
print("how='all'：", df.dropna(how='all').shape)
print("subset 只看商品名称：", df.dropna(subset=['商品名称']).shape)
print('axis=1 删列：', df.dropna(axis=1).shape)
print('保留的列：', list(df.dropna(axis=1).columns))


# ===== [k5-fill-fixed] 按列填固定值：fillna 传字典 {'列名': 值}，缺失格一次补上 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

fixed = df.fillna({'商品名称': '未知商品', '单价': 0, '数量': 0})
print(fixed.loc[[8], ['商品名称', '单价', '数量']])
print('还剩下的缺失格：', int(fixed.isnull().sum().sum()))


# ===== [k5-fill-mean] 用均值填数量：非空值的平均值 15.55，填完这一列不再有 NaN =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

mean = round(df['数量'].mean(), 2)
print('数量列均值：', mean)
filled = df.copy()
filled['数量'] = filled['数量'].fillna(mean)
print(filled.loc[[8], ['数量']])
print('数量列还有空值吗？', filled['数量'].isnull().any())


# ===== [k5-fill-ffill] 前向填充 ffill：空格抄上一行的值，商品名称和数量都补齐 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

cols = ['商品名称', '数量']
print('填充前（第 7—9 行）：')
print(df[cols].loc[[7, 8, 9]])
print('ffill 之后：')
print(df.ffill()[cols].loc[[7, 8, 9]])


# ===== [k5-outlier-find] 查异常值：单价 > 150 且数量 > 25（例 8-10）、金额为 0（例 8-9） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('单价 > 150 且数量 > 25：')
print(df[(df['单价'] > 150) & (df['数量'] > 25)][['订单编号', '单价', '数量']])
print('金额为 0：')
print(df[df['金额'] == 0][['订单编号', '数量', '金额']])


# ===== [k5-outlier-drop] 删除异常值：先取异常行的 index，再 drop(index=...) 一次性删掉 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

err = df[(df['单价'] > 150) & (df['数量'] > 25)]
print('异常行订单编号：', err['订单编号'].tolist())
print('删除前：', df.shape)
clean = df.drop(index=err.index)
print('删除后：', clean.shape)
print('还查得到 302010 吗？', 302010 in clean['订单编号'].values)


# ===== [k5-outlier-replace] 替换异常值：loc 精确改 + clip 批量裁剪，把超限值压回合理区间 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

fix = df.copy()
fix.loc[fix['数量'] == 0, '数量'] = 1
fix.loc[fix['单价'] > 150, '单价'] = 150
print(fix[fix['订单编号'].isin([302010, 302011])][['订单编号', '单价', '数量']])
print('clip 把数量压到 1—25：')
print(df['数量'].clip(lower=1, upper=25).tolist())


# ===== [k5-duplicated] duplicated(keep=False) 两行都标 True，方便把重复记录一起显示出来 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print("keep='first' 标出：", df.duplicated().sum(), '行')
print("keep='last'  标出：", df.duplicated(keep='last').sum(), '行')
print("keep=False   标出：", df.duplicated(keep=False).sum(), '行')
print(df[df.duplicated(keep=False)][['订单编号', '订单日期', '商品名称', '金额']])
print('按子集（去掉单价数量）判重：',
      df.duplicated(subset=['订单日期', '用户ID', '商品名称']).sum(), '行')


# ===== [k5-dropdup] drop_duplicates 去重：默认留一条变 11 行，keep=False 两条全删变 10 行 =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

print('去重前：', df.shape)
print("keep='first'：", df.drop_duplicates().shape)
print('keep=False：', df.drop_duplicates(keep=False).shape)
kept = df.drop_duplicates()
print('302006 还剩几条：', int((kept['订单编号'] == 302006).sum()))


# ===== [k5-astype-region] astype('str') 转类型 + .str[0:2] 抽地区编码（例 8-12、8-13） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

df['用户ID'] = df['用户ID'].astype('str')
df['地区编码'] = df['用户ID'].str[0:2]
print(df[['用户ID', '地区编码']].head(4))
print('用户ID 的 dtype：', df['用户ID'].dtype)
print('一共有几个地区：', df['地区编码'].nunique())


# ===== [k5-dt-week] dt.isocalendar().week 提周次、dt.year / dt.month 提年月（例 8-14） =====
import pandas as pd
from io import StringIO

csv = """订单编号,订单日期,用户ID,商品名称,单价,数量,金额
302001,2021-02-01,137401,围巾,21.6,12,259.2
302002,2021-02-01,177562,运动服,210.0,7,1470.0
302003,2021-02-01,133422,T恤,55.93,24,1342.32
302004,2021-02-05,138462,运动服,178.5,24,4284.0
302005,2021-02-05,158949,T恤,65.8,14,921.2
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302006,2021-02-08,120957,卫衣,126.5,15,1897.5
302008,2021-02-12,186059,T恤,65.8,5,329.0
302009,2021-02-15,199357,,,,540.0
302010,2021-02-20,149590,休闲鞋,162.0,30,4860.0
302011,2021-02-25,149590,运动服,178.5,0,0.0
302012,2021-02-28,137401,围巾,21.6,25,540.0
"""
df = pd.read_csv(StringIO(csv), parse_dates=['订单日期'])

df['年'] = df['订单日期'].dt.year
df['月'] = df['订单日期'].dt.month
df['周次'] = df['订单日期'].dt.isocalendar().week.astype(int)
print(df[['订单日期', '年', '月', '周次']].head(4))
print('每个周次有几条订单：')
print(df['周次'].value_counts().sort_index())

