資料處理基礎

文章目錄

• CSV
• • 寫入csv
  • 讀取csv
• Excel
• 資料庫
• • sqlite3
• 圖
• • 垂直條形圖

CSV

csv是純文字檔案

寫入csv

with open('a.csv','w') as f:
    f.write("af,b\n")
    f.write("q,,w\n")
    f.write(",,b")
    # f.write("af,b\nq,,w\n,,b")  #等價于上面三行
	# f.write('af,b,"10,2"\n')    

	f.close()
           

插入逗号

讀取csv

讀取整個表格，以清單形式傳回，一行為一進制素

with open('a.csv','r') as f:
    print(f.readlines()) # ['af,b\n', 'q,,w\n', ',,b']
    f.close()
           

讀取一行

with open('a.csv','r') as f:
    print(f.readline()) # af,b
    f.close()
           

讀取整個表格

with open('a.csv','r') as f:
    print(f.read())
    f.close()
           

行中正規表達式比對某值

import re
pattern = re.compile(r'(?P<my_pattern_group>^001-.*)',re.I)
with open('a.csv','r') as f:
    a = f.readlines() # ['af,b\n', 'q,,w\n', ',,b']
    if pattern.search(a):
        print(a)
    
    f.close()
           

Excel

from xlrd import open_workbook
a = open_workbook('a.xls')
print(a.nsheets) # 表格數量
for b in a.sheets():
    print(b.name,b.nrows,b.ncols)# 表格名稱，表格高度，表格寬度
           

sheet1為4*5， sheet2,sheet都為空

将a.xls中Sheet1工作表資料寫入b.xls的新添加的jan_2013_output工作表中

from xlrd import open_workbook
from xlwt import Workbook
a = Workbook()
b = a.add_sheet('jan_2013_output')  # 添加一個工作表
with open_workbook('a.xls') as workbook:
    worksheet = workbook.sheet_by_name('Sheet1')  # 調用名為這個的工作表
    for row_index in range(worksheet.nrows):
        for column_index in range(worksheet.ncols):
            b.write(row_index,column_index,worksheet.cell_value(row_index,column_index))  # 寫入資料
a.save('b.xls')  # 儲存為b.xls
           

如果上面程式日期資料顯示為一個數值，則進行以下處理得到2020/1/1類型

from xlrd import open_workbook
from xlwt import Workbook
from xlrd import open_workbook,xldate_as_tuple
a = Workbook()
b = a.add_sheet('jan_2013_output')  # 添加一個工作表
with open_workbook('a.xls') as workbook:
    worksheet = workbook.sheet_by_name('Sheet1')  # 調用名為這個的工作表
    for row_index in range(worksheet.nrows):
        for column_index in range(worksheet.ncols):
            if worksheet.cell_type(row_index,column_index) == 3:  # 為3則為日期資料
                date_cell = xldate_as_tuple(worksheet.cell_value(row_index,column_index),workbook.datemode)
                date_cell = date(*date_cell[0:3]).strftime('%m/%d/%Y')
                b.write(row_index,column_index,date_cell)
            else:
                non_date_cell = worksheet.cell_value(row_index,column_index)
                b.write(row_index,column_index,non_date_cell)
a.save('b.xls')
           

資料庫

sqlite3

建立檔案名為a.db資料庫名為sales的資料庫，并插入資料，讀取資料，修改資料

import sqlite3
# 建立資料SQLLite記憶體資料庫
con = sqlite3.connect('a.db') # 檔案名  :memory:為臨時的不儲存的
# 建立帶有4個屬性的sales表
# 表名：sales，屬性： customer（變長字元型字段最大字元數20），product（變長字元型字段最大字元數20），amount（浮點型字段），data（日期型字段）
query = """CREATE TABLE IF NOT EXISTS sales(customer VARCHAR(20),product VARCHAR(40),amount FLOAT,date DATA);"""
con.execute(query) # 執行query中的SQL指令
con.commit()  # 修改送出，也是儲存，沒有則儲存不到資料中

# 在表中插入幾行資料
data = [('Richard Lucas', 'Notepad', 2.50, '2014-01-02'),('Jenny Kim', 'Binder', 4.15, '2014-01-15'),('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),('Stephen Randolph', 'Computer',679.40, '2014-02-20')]
statement = "INSERT INTO sales VALUES(?,?,?,?)"
con.executemany(statement, data)
con.commit()

# 查詢sales表
cursor = con.execute("SELECT * FROM sales")
rows = cursor.fetchall() # 傳回所有行
print(rows)# [('Richard Lucas', 'Notepad', 2.5, '2014-01-02'),('Jenny Kim', 'Binder', 4.15, '2014-01-15'),('Svetlana Crow', 'Printer', 155.75, '2014-02-03'),('Stephen Randolph', 'Computer', 679.4, '2014-02-20')]

# 更新資料
data = (100, '2014-01-02','Richard Lucas')
con.execute("UPDATE sales SET amount=?, date=? WHERE customer=?;", data) # 給特定的customer修改amount和date
con.commit()

# 查詢sales表
cursor = con.execute("SELECT * FROM sales")
rows = cursor.fetchall() # 傳回所有行
print(rows)  # [('Richard Lucas', 'Notepad', 100.0, '2014-01-02'), ('Jenny Kim', 'Binder', 4.15, '2014-01-15'), ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'), ('Stephen Randolph', 'Computer', 679.4, '2014-02-20'), ('Richard Lucas', 'Notepad', 100.0, '2014-01-02'), ('Jenny Kim', 'Binder', 4.15, '2014-01-15'), ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'), ('Stephen Randolph', 'Computer', 679.4, '2014-02-20'), ('Richard Lucas', 'Notepad', 100.0, '2014-01-02'), ('Jenny Kim', 'Binder', 4.15, '2014-01-15'), ('Svetlana Crow', 'Printer', 155.75, '2014-02-03'), ('Stephen Randolph', 'Computer', 679.4, '2014-02-20')]

           

圖

垂直條形圖

import matplotlib.pyplot as plt
plt.style.use('ggplot')
customers = ['a','b','c','d','e']
customers_index = range(len(customers))
sale_amounts = [120,52,200,165,34]
fig = plt.figure()  # 建立基礎圖
ax1 = fig.add_subplot(1,1,1)  # 添加一個子圖
ax1.bar(customers_index,sale_amounts,align='center',color='darkblue') # 建立條形圖
ax1.xaxis.set_ticks_position('bottom')  # 刻度線在下方
ax1.yaxis.set_ticks_position('left')   # 刻度線在左邊
plt.xticks(customers_index,customers,rotation=0,fontsize='small')  # x軸上customers(a,b,c,d,e)替代customers_index(0,1,2,3,4)，rotation标簽角度，fontsize字型大小
plt.xlabel('Customer Amount')  # x軸标簽
plt.ylabel('Sale Amount')   # y标簽
plt.title('Sale Amount per Customer')  # 标題
plt.savefig('a.png',dpi=400,bbox_inches='tight')  # 儲存圖檔，dpi分辨率每英寸點數，tight空白部分去除
plt.show()
           

ax1 = fig.add_subplot(1,2,1)  # 添加一個子圖
ax1.bar(customers_index,sale_amounts,align='center',color='darkblue') # 建立條形圖
ax1.xaxis.set_ticks_position('bottom')  # 刻度線在下方
ax1.yaxis.set_ticks_position('left')   # 刻度線在左邊

ax2 = fig.add_subplot(2,2,4)  # 添加一個子圖
ax2.bar(customers_index,sale_amounts,align='center',color='darkblue') # 建立條形圖
ax2.xaxis.set_ticks_position('bottom')  # 刻度線在下方
ax2.yaxis.set_ticks_position('left')   # 刻度線在左邊