# -*- coding: utf-8 -*- 案例1学生成绩统计分析 知识点 1. pandas 读取 Excelpd.read_excel() 2. DataFrame 基本分析求和、求平均、describe() 描述统计 3. 新增列、排序、排名 4. 将多个 DataFrame 写入同一个 Excel 的不同 SheetExcelWriter 运行后 在本文件所在目录生成 data 文件夹里面有 成绩_源数据.xlsx 和 成绩_分析结果.xlsx import os import pandas as pd # ---------- 0. 路径准备 ---------- BASE_DIR os.path.dirname(os.path.abspath(__file__)) # 本脚本所在目录 DATA_DIR os.path.join(BASE_DIR, data) # 数据文件夹 os.makedirs(DATA_DIR, exist_okTrue) # 不存在则创建 SRC_FILE os.path.join(DATA_DIR, 成绩_源数据.xlsx) OUT_FILE os.path.join(DATA_DIR, 成绩_分析结果.xlsx) # ---------- 1. 生成示例 Excel实际项目中这一步换成你已有的 Excel 文件 ---------- def make_sample(): df pd.DataFrame({ 学号: [202401, 202402, 202403, 202404, 202405, 202406], 姓名: [张三, 李四, 王五, 赵六, 钱七, 孙八], 语文: [85, 76, 92, 60, 78, 88], 数学: [92, 88, 75, 71, 83, 95], 英语: [78, 82, 90, 55, 69, 84], }) df.to_excel(SRC_FILE, sheet_name成绩表, indexFalse) print(已生成示例文件, SRC_FILE) # ---------- 2. 读取 Excel 到 DataFrame ---------- def analyze(): df pd.read_excel(SRC_FILE, sheet_name成绩表) print( 读取到的原始数据 ) print(df) # ---------- 3. 数据分析 ---------- # 3.1 每位学生总分、平均分只对语文/数学/英语三列计算 subjects [语文, 数学, 英语] df[总分] df[subjects].sum(axis1) # axis1 表示按行求和 df[平均分] df[subjects].mean(axis1).round(1) # 3.2 按总分降序排名methodmin 表示同分同名次 df[名次] df[总分].rank(ascendingFalse, methodmin).astype(int) df df.sort_values(名次) # 按名次排序 # 3.3 全班各科描述统计平均分、最高分、最低分、标准差等 subject_stat df[subjects].describe().round(1) # 追加“总分/平均分”两列的统计 subject_stat[总分] df[总分].describe().round(1) subject_stat[平均分] df[平均分].describe().round(1) # 3.4 及格情况平均分 60 标记为“及格” df[是否及格] df[平均分].apply(lambda x: 及格 if x 60 else 不及格) pass_rate pd.DataFrame({及格人数: [(df[是否及格] 及格).sum()], 不及格人数: [(df[是否及格] 不及格).sum()]}) # ---------- 4. 写回 Excel多个 Sheet 必须在同一个 ExcelWriter 中写入 ---------- with pd.ExcelWriter(OUT_FILE, engineopenpyxl) as writer: df.to_excel(writer, sheet_name学生排名, indexFalse) subject_stat.to_excel(writer, sheet_name各科统计) pass_rate.to_excel(writer, sheet_name及格情况, indexFalse) print(\n 分析结果按名次 ) print(df.to_string(indexFalse)) print(\n 各科描述统计 ) print(subject_stat) print(\n分析结果已写入, OUT_FILE) if __name__ __main__: make_sample() analyze()# -*- coding: utf-8 -*- 案例2销售数据分组汇总 知识点 1. pd.read_excel() 读取销售流水 2. groupby() 分组 agg() 多种聚合销售额合计、订单数、平均单价 3. 分组后排序、占比计算 4. to_excel() 写回 Excel 运行后 生成 data/销售_源数据.xlsx 和 data/销售_汇总结果.xlsx import os import pandas as pd BASE_DIR os.path.dirname(os.path.abspath(__file__)) DATA_DIR os.path.join(BASE_DIR, data) os.makedirs(DATA_DIR, exist_okTrue) SRC_FILE os.path.join(DATA_DIR, 销售_源数据.xlsx) OUT_FILE os.path.join(DATA_DIR, 销售_汇总结果.xlsx) # ---------- 1. 生成示例销售流水 ---------- def make_sample(): df pd.DataFrame({ 订单日期: [2024-03-01, 2024-03-01, 2024-03-02, 2024-03-02, 2024-03-03, 2024-03-03, 2024-03-04, 2024-03-04], 地区: [华北, 华东, 华北, 华南, 华东, 华南, 华北, 华东], 产品: [笔记本, 鼠标, 鼠标, 笔记本, 键盘, 鼠标, 键盘, 笔记本], 数量: [2, 10, 8, 1, 5, 6, 3, 4], 单价: [5000, 50, 50, 5200, 120, 50, 120, 4900], }) df.to_excel(SRC_FILE, sheet_name销售流水, indexFalse) print(已生成示例文件, SRC_FILE) def analyze(): # ---------- 2. 读取数据 ---------- df pd.read_excel(SRC_FILE, sheet_name销售流水) # ---------- 3. 派生“销售额”列 ---------- df[销售额] df[数量] * df[单价] print( 带销售额的流水 ) print(df) # ---------- 4. 按地区分组汇总 ---------- # agg 可同时对不同列做不同聚合命名格式为 新列名(原列名, 聚合函数) region_sum (df.groupby(地区) .agg(销售总额(销售额, sum), 订单数量(订单日期, count), 销售件数(数量, sum)) .reset_index()) region_sum region_sum.sort_values(销售总额, ascendingFalse) # 计算各地区销售额占比%% 在字符串里表示一个百分号 region_sum[占比] (region_sum[销售总额] / region_sum[销售总额].sum() * 100).round(1) # ---------- 5. 按产品分组汇总平均单价、最高单笔销售额 ---------- product_sum (df.groupby(产品) .agg(销售总额(销售额, sum), 平均单价(单价, mean), 最高单笔(销售额, max)) .reset_index() .round({平均单价: 1}) .sort_values(销售总额, ascendingFalse)) # ---------- 6. 按“地区 产品”两级分组 ---------- cross_sum (df.groupby([地区, 产品]) .agg(销售额(销售额, sum), 数量(数量, sum)) .reset_index()) # ---------- 7. 写回 Excel多个 Sheet 用同一个 ExcelWriter ---------- with pd.ExcelWriter(OUT_FILE, engineopenpyxl) as writer: region_sum.to_excel(writer, sheet_name按地区汇总, indexFalse) product_sum.to_excel(writer, sheet_name按产品汇总, indexFalse) cross_sum.to_excel(writer, sheet_name地区产品交叉, indexFalse) print(\n 按地区汇总 ) print(region_sum.to_string(indexFalse)) print(\n 按产品汇总 ) print(product_sum.to_string(indexFalse)) print(\n汇总结果已写入, OUT_FILE) if __name__ __main__: make_sample() analyze()# -*- coding: utf-8 -*- 案例3数据清洗 知识点 1. 查看数据质量info()、isnull()、duplicated() 2. 缺失值处理fillna() 填充、dropna() 删除 3. 重复值处理drop_duplicates() 4. 列值清洗strip() 去空格、replace() 统一写法、astype() 类型转换 5. 清洗前后对比写回不同 Sheet 运行后 生成 data/员工_脏数据.xlsx 和 data/员工_清洗结果.xlsx import os import numpy as np import pandas as pd BASE_DIR os.path.dirname(os.path.abspath(__file__)) DATA_DIR os.path.join(BASE_DIR, data) os.makedirs(DATA_DIR, exist_okTrue) SRC_FILE os.path.join(DATA_DIR, 员工_脏数据.xlsx) OUT_FILE os.path.join(DATA_DIR, 员工_清洗结果.xlsx) # ---------- 1. 生成一份“脏数据” ---------- def make_sample(): df pd.DataFrame({ 工号: [A001, A002, A003, A004, A005, A005, A006], 姓名: [张三, 李四, 王五 , 赵六, 钱七, 钱七, 孙八], 部门: [技术部, 技术部, 市场部, 市场部, 人事部, 人事部, None], 年龄: [25, 30, np.nan, 28, 35, 35, 40], 工资: [8000, 9000, 8500, abc, 12000, 12000, 7600], }) df.to_excel(SRC_FILE, sheet_name原始数据, indexFalse) print(已生成示例文件, SRC_FILE) def analyze(): # ---------- 2. 读取并体检 ---------- raw pd.read_excel(SRC_FILE, sheet_name原始数据) print( 原始数据 ) print(raw) print(\n各列缺失值数量) print(raw.isnull().sum()) print(重复行数量, raw.duplicated().sum()) df raw.copy() # 保留原始数据用于结果对比 # ---------- 3. 删除完全重复的行 ---------- df df.drop_duplicates() # ---------- 4. 文本列去空格、统一部门写法 ---------- df[姓名] df[姓名].astype(str).str.strip() df[部门] df[部门].astype(str).str.strip() df[部门] df[部门].replace({nan: 未分配}) # None 转成字符串后统一为“未分配” # ---------- 5. 缺失值处理 ---------- # 数值列用平均年龄填充fillna 也可填 0 或固定值 df[年龄] pd.to_numeric(df[年龄], errorscoerce) # 先确保是数值 df[年龄] df[年龄].fillna(df[年龄].mean()).round(0).astype(int) # ---------- 6. 异常值处理工资列含字母转不成数字的置为缺失再用中位数填充 ---------- df[工资] pd.to_numeric(df[工资], errorscoerce) df[工资] df[工资].fillna(df[工资].median()).astype(int) # ---------- 7. 再次体检 ---------- print(\n 清洗后数据 ) print(df) print(清洗后缺失值数量, int(df.isnull().sum().sum())) print(清洗后重复行数量, int(df.duplicated().sum())) # ---------- 8. 写回原始数据与清洗结果各占一个 Sheet 便于对比 ---------- with pd.ExcelWriter(OUT_FILE, engineopenpyxl) as writer: raw.to_excel(writer, sheet_name清洗前, indexFalse) df.to_excel(writer, sheet_name清洗后, indexFalse) print(\n清洗结果已写入, OUT_FILE) if __name__ __main__: make_sample() analyze()# -*- coding: utf-8 -*- 案例4条件筛选与新增计算列 知识点 1. 布尔索引筛选、query() 条件筛选、isin() 多值筛选 2. apply() / np.where() 按条件新增列 3. 简单分段计算个税按级距教学简化版 4. 将“全量结果”和“筛选结果”分别写入不同 Sheet 运行后 生成 data/工资_源数据.xlsx 和 data/工资_计算结果.xlsx import os import numpy as np import pandas as pd BASE_DIR os.path.dirname(os.path.abspath(__file__)) DATA_DIR os.path.join(BASE_DIR, data) os.makedirs(DATA_DIR, exist_okTrue) SRC_FILE os.path.join(DATA_DIR, 工资_源数据.xlsx) OUT_FILE os.path.join(DATA_DIR, 工资_计算结果.xlsx) # 个税起征点教学简化不考虑专项附加扣除按简化级距计算 TAX_THRESHOLD 5000 # ---------- 1. 生成示例工资数据 ---------- def make_sample(): df pd.DataFrame({ 工号: [A001, A002, A003, A004, A005, A006, A007], 姓名: [张三, 李四, 王五, 赵六, 钱七, 孙八, 周九], 部门: [技术部, 技术部, 市场部, 市场部, 人事部, 财务部, 技术部], 基本工资: [8000, 15000, 7000, 22000, 9500, 11000, 6000], 绩效奖金: [2000, 5000, 1500, 8000, 2500, 3000, 1000], }) df.to_excel(SRC_FILE, sheet_name工资表, indexFalse) print(已生成示例文件, SRC_FILE) def calc_tax(taxable): 教学简化版个税按应纳税所得额分三段计税 if taxable 0: return 0 elif taxable 3000: return taxable * 0.03 elif taxable 12000: return taxable * 0.10 - 210 # 速算扣除数 210 else: return taxable * 0.20 - 1410 # 速算扣除数 1410 def analyze(): # ---------- 2. 读取数据 ---------- df pd.read_excel(SRC_FILE, sheet_name工资表) # ---------- 3. 新增计算列 ---------- df[应发工资] df[基本工资] df[绩效奖金] df[应纳税所得额] (df[应发工资] - TAX_THRESHOLD).clip(lower0) # 低于起征点按 0 df[个税] df[应纳税所得额].apply(calc_tax).round(2) df[实发工资] df[应发工资] - df[个税] # np.where(条件, 满足时的值, 不满足时的值)打工资等级标签 df[工资等级] np.where(df[实发工资] 20000, 高薪, np.where(df[实发工资] 10000, 中等, 普通)) # ---------- 4. 条件筛选 ---------- # 方式一布尔索引——技术部且实发工资大于 10000 tech_high df[(df[部门] 技术部) (df[实发工资] 10000)] # 方式二query 字符串查询写法更接近自然语言 mid df.query(实发工资 8000 and 实发工资 20000) # 方式三isin 多值筛选——人事部或财务部 hr_fin df[df[部门].isin([人事部, 财务部])] print( 工资计算全表 ) print(df.to_string(indexFalse)) print(\n 技术部实发过万 ) print(tech_high[[姓名, 部门, 实发工资]].to_string(indexFalse)) # ---------- 5. 写回 Excel ---------- with pd.ExcelWriter(OUT_FILE, engineopenpyxl) as writer: df.to_excel(writer, sheet_name工资全表, indexFalse) tech_high.to_excel(writer, sheet_name技术部高薪, indexFalse) mid.to_excel(writer, sheet_name中等收入, indexFalse) hr_fin.to_excel(writer, sheet_name人事财务, indexFalse) print(\n计算结果已写入, OUT_FILE) if __name__ __main__: make_sample() analyze()# -*- coding: utf-8 -*- 案例5多表合并与数据透视表 知识点 1. 同一 Excel 中读取多个 Sheet员工信息表 销售流水表 2. pd.merge() 按公共列合并两张表类似 Excel 的 VLOOKUP 3. pd.pivot_table() 生成数据透视表行、列、值、汇总方式、合计行 4. 多结果写回 Excel 运行后 生成 data/业务_源数据.xlsx 和 data/业务_分析结果.xlsx import os import pandas as pd BASE_DIR os.path.dirname(os.path.abspath(__file__)) DATA_DIR os.path.join(BASE_DIR, data) os.makedirs(DATA_DIR, exist_okTrue) SRC_FILE os.path.join(DATA_DIR, 业务_源数据.xlsx) OUT_FILE os.path.join(DATA_DIR, 业务_分析结果.xlsx) # ---------- 1. 生成包含两个 Sheet 的示例工作簿 ---------- def make_sample(): staff pd.DataFrame({ 工号: [S01, S02, S03, S04], 姓名: [张三, 李四, 王五, 赵六], 所属部门: [一部, 一部, 二部, 二部], }) sales pd.DataFrame({ 工号: [S01, S01, S02, S03, S03, S04, S04], 季度: [Q1, Q2, Q1, Q1, Q2, Q1, Q2], 产品: [A, A, B, A, B, A, B], 销售额: [12000, 15000, 9000, 20000, 18000, 8000, 11000], }) with pd.ExcelWriter(SRC_FILE, engineopenpyxl) as writer: staff.to_excel(writer, sheet_name员工信息, indexFalse) sales.to_excel(writer, sheet_name销售流水, indexFalse) print(已生成示例文件, SRC_FILE) def analyze(): # ---------- 2. 分别读取两个 Sheet ---------- staff pd.read_excel(SRC_FILE, sheet_name员工信息) sales pd.read_excel(SRC_FILE, sheet_name销售流水) # ---------- 3. merge 合并把姓名、部门补到销售流水里 ---------- # on 公共列howleft 以销售流水为主表保留全部流水 merged pd.merge(sales, staff, on工号, howleft) print( 合并后的明细 ) print(merged.to_string(indexFalse)) # ---------- 4. 数据透视表行姓名列季度值销售额合计 ---------- pivot_q pd.pivot_table( merged, index姓名, # 行 columns季度, # 列 values销售额, # 值 aggfuncsum, # 汇总方式 fill_value0, # 空值补 0 marginsTrue, # 添加合计行/列 margins_name合计, ) # ---------- 5. 数据透视表行部门列产品值销售额均值 ---------- pivot_dp pd.pivot_table( merged, index所属部门, columns产品, values销售额, aggfuncmean, fill_value0, marginsTrue, margins_name平均, ).round(0) # ---------- 6. 员工业绩排名表 ---------- rank (merged.groupby([姓名, 所属部门]) .agg(总销售额(销售额, sum), 订单数(销售额, count)) .reset_index() .sort_values(总销售额, ascendingFalse)) # ---------- 7. 写回 Excel ---------- with pd.ExcelWriter(OUT_FILE, engineopenpyxl) as writer: merged.to_excel(writer, sheet_name合并明细, indexFalse) pivot_q.to_excel(writer, sheet_name季度透视表) pivot_dp.to_excel(writer, sheet_name部门产品透视表) rank.to_excel(writer, sheet_name业绩排名, indexFalse) print(\n 季度销售额透视表 ) print(pivot_q) print(\n 员工业绩排名 ) print(rank.to_string(indexFalse)) print(\n分析结果已写入, OUT_FILE) if __name__ __main__: make_sample() analyze()
