目录
一、处理excel表常见操作
1）读取excel表格
#读取整个表格
df = pd.read_excel("文件名.xlsx")
#读取指定工作表
df = pd.read_excel("文件名.xlsx", sheet_name="Sheet2")
#读取指定单元格范围
#usecoles读取的列范围，skiprows跳过前一行，nrows=5读前5行
df = pd.read_excel("文件名.xlsx", usecols="H:J", skiprows=1, nrows=5)
#读取无表头的数据
#header=None：不将任何行作为表头，列名默认设为 0,1,2...
df = pd.read_excel("文件名.xlsx", header=None)
#读取指定列（按列名 / 索引）
#usecols 传列表，可指定列名（需有表头）或列索引（如 [0,2] 代表第 1、3 列）
df = pd.read_excel("灰色关联分析.xlsx", usecols=["单品名称", "销量(千克)"])
2）数据查看
了解数据结构，常见操作如下：
#查看数据默认前5行/后5行，也可以指定
print(df.head())
print(df.head(3))
print(df.tail())
print(df.tail(4))
#查看数据维度（行*列）
print(df.shape) # 输出格式：(行数, 列数)
# 查看列名
print(df.columns.tolist()) # 转为列表，方便后续调用
#查看数据类型,检查是否有数值列被识别为字符串（需转换）
print(df.dtypes)
#查看数据基本统计信息,输出均值、标准差、最值等
print(df.describe())
#检查缺失值，统计每列缺失值数量
print(df.isnull().sum())
3）数据清洗
数学建模对数据质量要求高，需处理缺失值、异常值、格式错误等问题
1.缺失值处理
# 删除含缺失值
df_clean = df.dropna(axis=0) # axis=0 删行，axis=1 删列
# 用均值/中位数填充
df["销量(千克)"] = df["销量(千克)"].fillna(df["销量(千克)"].mean())
#用众数填充， 分类数据（如商品名称、类别）
df["单品名称"] = df["单品名称"].fillna(df["单品名称"].mode()[0])
#时间序列数据（如按日期排序的数据，缺失值可继承相邻值，用前/后值填充
# ffill=前向，bfill=后向
df["日期"] = df["日期"].fillna(method="ffill")
2. 异常值处理
常用“3σ 原则”（正态分布数据）或“四分位距（IQR）”识别异常值：
# 示例：用IQR处理“销量(千克)”列的异常值（替换为上下限）
Q1 = df["销量(千克)"].quantile(0.25) # 下四分位
Q3 = df["销量(千克)"].quantile(0.75) # 上四分位
IQR = Q3 - Q1
lower_bound = Q1 - 1.5 * IQR # 下限
upper_bound = Q3 + 1.5 * IQR # 上限
# 异常值替换为上下限
#检查df["销量(千克)"]列中的每个值，如果该值小于lower_bound，就用lower_bound替换它；否则保持原来的值不变
df["销量(千克)"] = np.where(df["销量(千克)"] < lower_bound, lower_bound, df["销量(千克)"])
df["销量(千克)"] = np.where(df["销量(千克)"] > upper_bound, upper_bound, df["销量(千克)"])
#也可删除，进行行筛选，保留满足条件的行
df = df[(df["销量(千克)"]>=lower_bound) & (df["销量(千克)"]<=upper_bound)]
4） 数据类型转换
例如将字符串格式的“日期”“数值”转为正确类型：
# 日期列转换（建模中时间序列分析必备）
df["销售日期"] = pd.to_datetime(df["销售日期"]) # 自动识别常见日期格式
# 数值列转换（如“销量”被识别为字符串，需转为float）
# errors=coerce：无法转换的设为NaN
df["销量(千克)"] = pd.to_numeric(df["销量(千克)"], errors="coerce")
5）数据预处理
数学建模中常需对数据进行筛选、分组、归一化/标准化、特征计算等操作：
1. 数据筛选（按条件提取目标数据）
# 筛选“单品名称=西兰花”且“销售日期=2021-08-07”的数据
df_broccoli = df[(df["单品名称"] == "西兰花") & (df["销售日期"] == "2021-08-07")]
#筛选“销量(千克) > 100”且“季度=Q3”的数据
df_high_sales = df[(df["销量(千克)"] > 100) & (df["季度"] == "Q3")]
2. 数据分组与聚合（按类别计算统计量）
数学建模中常需按“类别”“时间”分组计算均值、总和等（如按季度算总销量）：
# 按“单品名称”分组，计算每组的“销量(千克)”均值、总和、最大值
grouped = df.groupby("单品名称")["销量(千克)"].agg(["mean", "sum", "max"]).reset_index()
# 重命名列（方便后续使用）
grouped.columns = ["单品名称", "平均销量", "总销量", "最大销量"]
3. 数据归一化/标准化（消除量纲影响）
建模中若特征量纲差异大（如“销量（千克）”和“价格（元）”），需标准化（如 Z-Score）或归一化（如 Min-Max）：
# 示例1：Min-Max归一化（将数据缩放到[0,1]）
from sklearn.preprocessing import MinMaxScaler
scaler = MinMaxScaler()
df["销量_归一化"] = scaler.fit_transform(df[["销量(千克)"]]) # 需传二维数组
# 示例2：Z-Score标准化（均值=0，标准差=1）
from sklearn.preprocessing import StandardScaler
scaler = StandardScaler()
df["销量_标准化"] = scaler.fit_transform(df[["销量(千克)"]])
4. 特征计算
例如从“日期”提取“季度”“月份”，或计算“销量增长率”：
# 从“销售日期”提取月份、季度（时间序列建模常用）
df["月份"] = df["销售日期"].dt.month
df["季度"] = df["销售日期"].dt.quarter # 1-4代表Q1-Q4
# 计算“销量增长率”（前后行差值/前一行值，需先按日期排序）
df = df.sort_values("销售日期") # 按日期排序
df["销量增长率"] = df["销量(千克)"].pct_change() # pct_change()：计算环比增长率
6）结果导出：将建模结果写入 Excel
建模后需将处理后的数据、计算结果导出为 Excel，方便展示或后续使用：
#导出到单个工作表
#index=False:不导出行索引，避免多余列
#sheet_name:指定工作表名
df.to_excel("建模结果.xlsx",sheet_name="销量分析",index=False)
#导出多个DataFrame到不同工作表，使用ExcelWriter可批量导出，避免多次打开文件
with pd.ExcelWriter("建模结果.xlsx") as writer:
df_clean.to_excel(writer, sheet_name="清洗后数据", index=False)
grouped.to_excel(writer, sheet_name="分组统计", index=False)
result.to_excel(writer, sheet_name="灰色关联度结果", index=False)
#导出指定列
#columns：指定需导出的列（按顺序）
df.to_excel("建模结果.xlsx", columns=["单品名称", "总销量", "季度"], index=False)
二、数学建模高频场景示例
以“灰色关联分析”（数学建模常见方法）为例，综合上述操作：
# 1. 读取Excel数据（指定范围J2:H13）
df = pd.read_excel(
"灰色关联分析.xlsx",
usecols="H:J", skiprows=1, nrows=12, header=None
)
# 2. 数据预处理，替换列名
df.columns = ["母序列", "子序列1", "子序列2"]
# 3. 计算灰色关联度
Mean = df.mean()
df_norm = df / Mean # 利用均值归一化
Y = df_norm["母序列"]
X = df_norm[["子序列1", "子序列2"]]
abs_diff = np.abs(X - Y.values.reshape(-1, 1)) # 计算绝对差
a = abs_diff.min().min() # 两级最小差
b = abs_diff.max().max() # 两级最大差
rho = 0.5 # 分辨系数
gamma = (a + rho*b) / (abs_diff + rho*b) # 关联系数
corr_degree = gamma.mean() # 关联度
# 4. 结果整理与导出
result = pd.DataFrame({
"子序列": ["子序列1", "子序列2"],
"灰色关联度": corr_degree.values
})
result.to_excel("灰色关联分析结果.xlsx", index=False)
print("灰色关联分析结果：")
print(result)
三、注意事项
- 数据规模：若 Excel 数据超过 10 万行，建议用
df = pd.read_excel(..., chunksize=10000)分块读取，避免内存溢出
# 分块读取大文件
df_chunks = pd.read_excel("large_file.xlsx", chunksize=10000)
# 迭代处理每个块
for chunk in df_chunks:
# 对当前块进行处理（如清洗、分析等）
process_chunk(chunk) # 自定义处理函数
- 如果 Excel 没有表头（第一行就是数据），读取时需指定 header=None，此时 pandas 会用 0,1,2… 作为默认列名：
df = pd.read_excel("数据.xlsx", header=None) # 无表头，列名默认0,1,2...
print("列名：", df.columns.tolist()) # 输出 [0,1,2,...]
示例一：excel文件合并处理
#合并附件1、附件4
import pandas as pd
# 读取两个Excel表
table1 = pd.read_excel('附件1.xlsx')
table4 = pd.read_excel('附件4.xlsx', sheet_name='Sheet1')
# 按公共列 '客户' 合并
merged = pd.merge(table1, table4, on='单品编码', how='left')
# print(table1.columns) #查看table1的所有列的名称
# print(table4.columns)
# print(table1.head()) #查看table1的前五行
# print(table4.head())
# 保存结果到指定excel表格中，不将索引（行号）写入 Excel 文件中
merged.to_excel('merged_result.xlsx', index=False)
示例二：画图
使用python画图需要提前配置的代码
#画图显示中文配置信息
import pandas as pd
import matplotlib
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
from matplotlib.font_manager import FontProperties
# 1. 后端设置需放在绘图相关操作前
matplotlib.use('tkAgg')
# 2. 先设置图表样式，再覆盖字体配置（避免样式重置字体）
plt.style.use('default')
# 3. 优化中文字体配置（优先用SimHei，适配多数系统，确保不被样式覆盖）
config = {
"font.family": "sans-serif", # 匹配无衬线字体（SimHei属于此类）
"font.sans-serif": ["SimHei", "WenQuanYi Micro Hei", "Heiti TC"], # 优先列表
"axes.unicode_minus": False, # 确保负号正常显示
"axes.labelsize": 12, # 坐标轴标签字体大小
"axes.titlesize": 14, # 标题字体大小
"legend.fontsize": 10, # 图例字体大小
"xtick.labelsize": 10, # x轴刻度字体大小
"ytick.labelsize": 10 # y轴刻度字体大小
}
matplotlib.rcParams.update(config)
示例三：相关性分析系数热力图
#热力图函数
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
def plot_heatmap(matrix, # matrix: 二维列表或numpy数组，热力图的数据矩阵
row_labels=None, # row_labels: 列表，行标签（默认为None，显示索引）
col_labels=None, # col_labels: 列表，列标签（默认为None，显示索引）
title="spearman相关系数热力图",
cmap="coolwarm", #cmap: 字符串，颜色映射方案（如"coolwarm", "viridis", "YlGnBu"等）
annot=True, #annot: 布尔值，是否在热力图上显示数值
fmt=".4f", #fmt: 字符串，数值显示格式（如".2f"表示保留两位小数）
figsize=(7,3),
show_colorbar=True):
# 设置中文显示
plt.rcParams["font.family"] = ["SimSun"]
plt.rcParams["axes.unicode_minus"] = False # 解决负号显示问题
# 将输入转换为numpy数组便于处理
matrix = np.array(matrix)
# 检查矩阵是否为二维
if len(matrix.shape) != 2:
raise ValueError("输入必须是二维矩阵")
# 创建画布
plt.figure(figsize=figsize)
# 绘制热力图
ax = sns.heatmap(
matrix,
annot=annot,
fmt=fmt,
cmap=cmap,
cbar=show_colorbar,
xticklabels=col_labels,
yticklabels=row_labels
)
# 设置标题
plt.title(title, pad=20)
# 调整布局
plt.tight_layout()
# 显示热力图
plt.show()
# 示例用法------------------------------
if __name__ == "__main__":
print("\n示例2：蔬菜品类相关性矩阵")
veg_corr = [
[-0.1703, -0.2596, -0.462, -0.4671, -0.311, -0.2979]
]
plot_heatmap(
matrix=veg_corr,
row_labels=["spearman相关系数"],
col_labels=["花叶类" ,"花菜类" ,"水生根茎类","食用菌类","辣椒类","茄类"],
title="蔬菜品类相关性系数热力图",
cmap="YlGnBu"
)
效果图如下：
扫一扫
17
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
