编程►python

python数据处理

csv数据处理

csv表示”Comma-Separated Values(逗号分隔的值)”，csv文件时简化的电子表格，保存为纯文本文件。
python中解析csv文件可以使用csv模块。
注意：csv是文本文件，通过打开文件的操作，将其内容读入一个字符串，然后通过字符串的split()方法处理每行文本取得值。需要注意csv文件中也有自己的转义字符，允许逗号或者其他字符作为值的一部分，split()方法无法处理这部分转义字符。所以处理csv文件时为避免这些潜在的问题，建议使用csv模块处理csv文件。

Reader

使用csv模块从csv文件中读取数据，需要创建一个Reader对象，可以通过Reader对象迭代遍历csv文件中的每一行。

1
2
3
4
5
6
import csv

with open("example.csv", encoding='utf-8') as dataObject:
    dataReader = csv.reader(dataObject)
    for row in dataReader:
        print("第{0}行:{1}".format(dataReader.line_num, row))

Reader对象的line_num变量是当前的行号。
输出结果

1
2
3
第1行:['2018/06/09', 'Apples', '73']
第2行:['2018/06/10', 'Cherries', '85']
第3行:['2018/06/11', 'Pears', '14']

Writer

Write对象可以将数据写入到csv文件中。
首先调用open()并传入w，以写模式打开一个文件。这时会创建一个对象。
然后将创建的对象传递给csv.writer()，创建一个Writer对象。
接下来调用Writer对象的writerow()方法写入文件。该方法接受参数为列表，返回值为写入文件这一行中的字符数(包括换行符)。

1
2
3
4
5
6
7
import csv

with open("test.csv", 'w', encoding='utf-8', newline='') as outputFile:
    dataWriter = csv.writer(outputFile)
    dataWriter.writerow(['a', 'b', 'c'])
    dataWriter.writerow(['啊', '吧', '次'])
    dataWriter.writerow(['A', 'Hello,World!', 'C'])

最后输出结果

1
2
3
a,b,c
啊,吧,次
A,"Hello,World!",C

此处的Hello,Wrold!中的逗号自动转义，使用双引号处理了。
在windows中open()需加上newline=''参数，否则将出现2倍行距的情况。
写入的文件必须已经存在。

  • delimiter 指定分隔符，默认为逗号
  • lineterminator 行距，默认为单倍行距
    1
    dataWriter = csv.writer(outputFile, delimiter='\t', lineterminator='\n\n')

上面表示使用制表符作为分隔符，两倍行距(也就是行之间字符变为2个换行符)。

excel数据处理

excel的操作都是通过第三方库来进行。常用的有xlrd、xlwt、xluntils、pyExcelerator、openpyxl。
安装类似与其它第三方模块，使用pip install module_name即可。

  • xlrd只能进行读取excel文件，没法进行写入文件。
  • xlwt可以写入文件，但是不能在已有的excel的文件上进行修改。
  • xluntils可以对文件进行复制和修改，该模块功能实现依赖于xlrd和xlwt。
  • pyExcelerator模块与xlwt类似，也可以用来生成excel文件，同时支持单元格合并、冻结等操作。
  • openpyxl也支持文件的读取、写入、创建和删除工作表、设置字体格式、单元格合并、冻结等操作。

xlrd

xlrd主要用于excel文件读取。
excel名称为test.xlsx，内容如下
python_data_xlrd

  • 打开excel文件并获取所有的sheet。

    1
    2
    3
    4
    5
    import xlrd

    workbook = xlrd.open_workbook("test.xlsx")
    worksheets = workbook.sheet_names()
    print(worksheets)    #输出结果：['物理机', '虚拟机']

  • 根据下标获取sheet名称

    1
    2
    3
    4
    worksheet1 = workbook.sheet_names()[0]
    print(worksheet1)    #输出结果：物理机
    worksheet2 = workbook.sheet_names()[1]
    print(worksheet2)    #输出结果：虚拟机

  • 根据sheet索引或名称获取sheet内容，同时获取sheet名称、行数、列数 

    1
    2
    3
    4
    sheet1 = workbook.sheet_by_index(0)
    print(sheet1.name, sheet1.nrows, sheet1.ncols)    #输出结果：物理机 16 6
    sheet2 = workbook.sheet_by_name("虚拟机")
    print(sheet2.name, sheet2.nrows, sheet2.ncols)    #输出结果：虚拟机 13 6

索引从0开始；nrows表示所有行；ncols表示所有列。

  • 根据sheet名称获取整行和整列的内容

    1
    2
    3
    4
    5
    sheet1 = workbook.sheet_by_name("物理机")
    rows = sheet1.row_values(0)    #第一行内容
    print(rows)    #输出结果：['IP地址', '设备用途', '型号', '主要配置', '操作系统', '区域']
    cols = sheet1.col_values(0)    #第一列的内容
    print(cols)    #输出结果：['IP地址', '192.168.0.1', '192.168.0.2',... '192.168.0.15']

  • 获取指定单元格的内容

    1
    2
    3
    4
    sheet1 = workbook.sheet_by_name("物理机")
    print(sheet1.cell(1, 2).value)     #输出结果：Lenovo R680g7
    print(sheet1.cell_value(1, 2))    #输出结果：Lenovo R680g7
    print(sheet1.row(1)[2].value)    #输出结果：Lenovo R680g7

  • 获取单元格内容的数据类型

    1
    2
    3
    sheet1 = workbook.sheet_by_name("物理机")
    print(sheet1.cell(1, 2).ctype)    #输出结果：1
    print(sheet1.cell(1, 6).ctype)    #输出结果：3

ctype共有5种，0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error。

  • 日期类型的特殊处理
    1
    2
    sheet1 = workbook.sheet_by_name("物理机")
    print(sheet1.cell(1, 6).value)    #输出结果：43252.0

日期类型输出结果为number，可以使用xldate_as_tuple或xldate_as_datetime进行处理。

  • xldate_as_tuple

    1
    2
    3
    4
    5
    6
    7
    8
    from datetime import date

    sheet1 = workbook.sheet_by_name("物理机")
    date_value = xlrd.xldate_as_tuple(sheet1.cell(1, 6).value, workbook.datemode)
    print(date_value)    #输出结果：(2018, 6, 1, 0, 0, 0)
    print(date_value[:3])    #输出结果：(2018, 6, 1)
    print(*date_value[:3])    #输出结果：2018 6 1
    print(date(*date_value[:3]))    #输出结果：2018-06-01

  • xldate_as_datetime

    1
    2
    3
    sheet1 = workbook.sheet_by_name("物理机")
    date_time = xlrd.xldate_as_datetime(sheet1.cell(1, 6).value, workbook.datemode)
    print(date_time.strftime('%Y-%m-%d'))    #输出结果：2018-06-01

在脚本中需要获取并显示单元格中的日期类型时，可以先判断数据类型是否为日期类型，如果是则进行处理。

1
2
3
if (sheet1.cell(1, 6).ctype == 3):
    date_time = xlrd.xldate_as_datetime(sheet1.cell(1, 6).value, workbook.datemode)
    print(date_time.strftime('%Y-%m-%d'))

关于xlrd模块更多介绍，可以点击xlrd documentation查看官方文档介绍。

xlwt

  • 创建workbook和sheet对象

    1
    2
    3
    4
    5
    6
    import xlwt

    workbook = xlwt.Workbook()
    sheet1 = workbook.add_sheet("sheet1", cell_overwrite_ok=True)
    sheet2 = workbook.add_sheet("sheet2", cell_overwrite_ok=True)
    sheet3 = workbook.add_sheet("表3", cell_overwrite_ok=True)

  • sheet页中写入数据

    1
    2
    sheet1.write(0, 0, "姓名")
    sheet2.write(0, 0, "HOW")

  • write方法参数列表
    write(r, c, label="", style=Style.default_style)
    r表示行，c表示列，label表示写入的内容，style表示单元格格式。

  • 使用样式

    1
    2
    3
    4
    5
    style = xlwt.XFStyle()
    font = style.font
    font.name = "Times New Roman"
    font.bold = True
    sheet3.write(0, 0, "WHAT", style)

  • 保存文件

    1
    workbook.save("test1.xls")    #此处保存为xls格式，xlsx格式无法打开

关于xlwt模块更多介绍，可以点击xlwt documentation查看官方文档介绍。

openpyxl

继续以上面的excel为例进行示例操作。

worksheet操作
  • 加载工作表并打印当前所有sheet
    1
    2
    3
    4
    5
    from openpyxl import Workbook, load_workbook

    wb1 = load_workbook('test.xlsx')
    print(wb1.sheetnames) #输出结果：['物理机', '虚拟机']
    print(wb1.active)    #输出结果：<Worksheet "物理机">

active调用的是_active_sheet_index属性，默认情况为0.所以得到的都是第一个worksheet。

  • 创建sheet页
    1
    2
    3
    4
    ws1 = wb1.create_sheet("sheet1")
    ws2 = wb1.create_sheet("sheet2", 0)
    print(wb1.sheetnames) #输出结果：['sheet2', '物理机', '虚拟机', 'sheet1']
    print(wb1.active) #输出结果：<Worksheet "sheet2">

create_sheet()默认情况下插入在最后，当传入index参数时，插入至指定位置，例如0表示插入至最前面。

  • sheet页属性修改
    1
    2
    3
    ws1.title = "new sheet" #修改标题
    ws1.sheet_properties.tabColor = "1072BA" #修改工作表标签颜色
    print(wb1.sheetnames) #输出结果：['sheet2', '物理机', '虚拟机', 'new sheet']

也可以通过循环的方式得到所有sheet页名称

1
2
for sheet in wb1:
    print(sheet.title, end=' ') #输出结果：sheet2 物理机 虚拟机 new sheet

  • sheet页复制
    1
    2
    3
    source = wb1.active
    target = wb1.copy_worksheet(source)
    print(wb1.sheetnames) #输出结果：['sheet2', '物理机', '虚拟机', 'new sheet', 'sheet2 Copy']
cell操作
  • 获取及修改指定cell内容
    1
    2
    3
    4
    5
    ws4 = wb1["物理机"]
    print(ws4["A4"].value) #输出结果：192.168.0.3
    print(ws4["G4"].value) #输出结果：2018-06-03 00:00:00
    ws4['A4'] = 4 #可直接修改指定单元格内容
    print(ws4["A4"].value) #输出结果：4

对于日期格式可直接正确输出。
修改指定单元格的内容也可以使用cell()方法传入行、列和值的方式

1
2
d = ws4.cell(column=1, row=4, value=10)
print(ws4["A4"].value) #输出结果：10

当一个sheet页被创建时无单元格。单元格在首次被访问的时候创建，无论是否有值。
对于下面的操作,将会在内存中创建100*100的单元格，即使未分配值。

1
2
3
>>> for i in range(1,101):
...        for j in range(1,101):
...            ws.cell(row=i, column=j)

  • 访问多个单元格或行或列
    1
    2
    cell_range = ws4['A1':'B2'] #多个单元格
    print(cell_range)

输出结果：

1
((<Cell '物理机'.A1>, <Cell '物理机'.B1>), (<Cell '物理机'.A2>, <Cell '物理机'.B2>))

输出结果为包含2个元组的元组

1
2
colC = ws4['C'] #指定单行
print(colC)

输出结果：

1
(<Cell '物理机'.C1>... <Cell '物理机'.C15>, <Cell '物理机'.C16>)

输出结果为元组

1
2
col_range = ws4['C:D'] #指定多行
print(col_range)

输出结果：

1
((<Cell '物理机'.C1>, ... <Cell '物理机'.C16>), (<Cell '物理机'.D1>,... <Cell '物理机'.D16>))

输出结果为包含2个元组的元组

1
2
row10 = ws4[10] #指定单列
print(row10)

输出结果：

1
(<Cell '物理机'.A10>, <Cell '物理机'.B10>,...<Cell '物理机'.L10>)

输出结果为元组

1
2
row_range = ws4[5:6] #指定多列
print(row_range)

输出结果：

1
((<Cell '物理机'.A5>, <Cell '物理机'.B5>,... <Cell '物理机'.L5>), (<Cell '物理机'.A6>, <Cell '物理机'.B6>,... <Cell '物理机'.L6>))

输出结果为包含2个元组的元组
也可以通过iter_rows()、iter_cols()、rows()、columns()方法

1
2
3
4
5
6
7
8
9
10
11
12
# iter_rows()
for row in ws4.iter_rows(min_row=1, max_col=3, max_row=2):
    for cell in row:
        print(cell)
# iter_cols()
for col in ws4.iter_cols(min_row=1, max_col=3, max_row=2):
    for cell in col:
        print(cell)
# rows()
print(tuple(ws4.rows))
# columns()
print(tuple(ws4.columns))

  • 数据存储

直接赋值

1
2
3
4
5
6
7
8
9
from openpyxl import load_workbook
wb1 = load_workbook('test.xlsx')
ws = wb1["sheet2"]
c = ws["A1"]
c.value = "hello, world"
print(c.value) #输出结果：hello, world
d = ws["B1"]
d.value = 3.14
print(d.value) #输出结果：3.14

启用格式和类型推断

1
2
3
4
5
6
7
8
wb = load_workbook('test.xlsx', guess_types=True)
ws = wb.create_sheet("sheet2")
c = ws["B1"]
c.value = '12%'
print(c.value)    #输出结果：0.12
d = ws["B2"]
d.value = datetime.now()
print(d.value)    #输出结果：2018-06-12 17:19:10.700439

  • 文件保存
    直接调用save()方法即可。
    1
    wb.save("test.xlsx")

注意

  1. 当文件存在时会直接覆盖，不会给出提示或警告信息。
  2. 当在操作系统中打开excel文件时调用保存方法，会出现PermissionError的错误，关闭即可。
  3. save()提供另一个文件名，相当于另存为操作，不会影响原文件的内容。
    总结
    使用openpyxl进行excel的操作，主要是Workbook(excel文件)、sheet(excel中的sheet页)、cell(一个单元格)进行操作。
    读写操作主要步骤：打开Workbook，定位sheet，操作cell。
    关于openpyxl模块的更多介绍，可以点击openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files查看官方文档。

Recommended Posts