excel数据清洗的小案例--转快学python公众号
需求如下：原始数据如下，想要达到上图的效果
from openpyxl import load_workbook
from openpyxl import Workbook
# 1、读取数据
workbook = load_workbook(filename = "D:\\1.xlsx")
workbook.sheetnames
sheet = workbook["Sheet1"]
# sheet.dimensions查看表格的维度
cell = sheet["A2:G27"]
# 2、提取表格中的数据
y = []
for i in cell:
x = []
for j in i:
x.append(j.value)
xx = x[:1]+x#利用切片把中间三个空白切掉
y.append(xx)
# 3、清洗数据，将清洗好的数据写入表格
# 新建一个空白的excel表格
workbook = Workbook()
sheet1 = workbook.active
sheet1.title = "表格1"
sheet1.append(["学号","姓名","检测","讨论","成绩"])
# 数据清洗
for xx in y:
# 提取学号
xuehao = xx
# 提取姓名
name = xx
# 提取检测
test = float(xx)
# 提取讨论
taolun = xx
if taolun == "-":
taolun = 0
else:
taolun = float(xx)
# 提取成绩
score = float(xx)
final =
# 将最终的数据一行行的写入到excel中
sheet1.append(final)
# 将数据写入到excel后，必须保存，否则前面的操作前功尽弃
workbook.save(filename = "d:\作业.xlsx")
页:
[1]