# 案例01 批量升序排序一個工作簿中的所有工作表
import xlwings as xw # 導入xlwings子產品
import pandas as pd # 導入pandas子產品
app = xw.App(visible = False, add_book = False) # 啟動Excel程式
workbook = app.books.open('E:\\Python案例操作\\産品銷售統計表.xlsx') # 打開要升序排序的工作簿
worksheet = workbook.sheets # 列出工作簿中的所有工作表
for i in worksheet: # 周遊工作簿中的工作表
values = i.range('A1').expand('table').options(pd.DataFrame).value # 讀取目前工作表的資料并轉換為DataFrame格式
result = values.sort_values(by = '銷售利潤') # 對“銷售利潤”列進行升序排序
i.range('A1').value = result # 将排序結果寫入目前工作表，替換原有資料
workbook.save() # 儲存工作簿
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [2]:
# 案例01-1 批量降序排序一個工作簿中的所有工作表
import xlwings as xw # 導入xlwings子產品
import pandas as pd # 導入pandas子產品
app = xw.App(visible = False, add_book = False) # 啟動Excel程式
workbook = app.books.open('E:\\Python案例操作\\産品銷售統計表.xlsx') # 打開要升序排序的工作簿
worksheet = workbook.sheets # 列出工作簿中的所有工作表
for i in worksheet: # 周遊工作簿中的工作表
values = i.range('A1').expand('table').options(pd.DataFrame).value # 讀取目前工作表的資料并轉換為DataFrame格式
result = values.sort_values(by = '銷售利潤',ascending = False) # 對“銷售利潤”列進行降序排序
i.range('A1').value = result # 将排序結果寫入目前工作表，替換原有資料
workbook.save() # 儲存工作簿
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [3]:
# 案例01-2 批量排序多個工作簿中的資料
import os
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
file_path = 'E:\\Python案例操作\\産品銷售統計表'
file_list = os.listdir(file_path)
for i in file_list:
if os.path.splitext(i)[1] == '.xlsx':
workbook = app.books.open(file_path + '\\' + i)
worksheet = workbook.sheets
for j in worksheet:
values = j.range('A1').expand('table').options(pd.DataFrame).value
result = values.sort_values(by = '銷售利潤')
j.range('A1').value = result
workbook.save()
workbook.close()
app.quit()
In [4]:
# 案例02 篩選一個工作簿中的所有工作表資料
import xlwings as xw # 導入xlwings子產品
import pandas as pd # 導入pandas子產品
app = xw.App(visible = False, add_book = False) # 啟動Excel程式
workbook = app.books.open('E:\\Python案例操作\\采購表.xlsx') # 打開要處理的工作簿
worksheet = workbook.sheets # 列出工作簿中的所有工作表
table = pd.DataFrame() # 建立一個空DataFrame
for i, j in enumerate(worksheet): # 周遊工作簿中的工作表
values = j.range('A1').options(pd.DataFrame, header=1, index= False, expand='table').value # 讀取目前工作表的資料
data = values.reindex(columns=['采購物品', '采購日期', '采購數量', '采購金額']) # 調整列的順序，将“采購物品”移到第1列
table = table.append(data, ignore_index = True) # 将調整列順序後的資料合并到前面建立的DataFrame中
table = table.groupby('采購物品') # 根據“采購物品”列篩選資料
new_workbook = xw.books.add() # 建立一個工作簿
for idx, group in table: # 周遊篩選好的資料，其中idx對應物品名稱，group對應該物品的所有明細資料
new_worksheet = new_workbook.sheets.add(idx) # 在新工作簿中新增工作表，以物品名稱作為工作表名
new_worksheet['A1'].options(index = False).value = group # 在新工作表中寫入目前物品的所有明細資料
last_cell = new_worksheet['A1'].expand('table').last_cell # 擷取目前工作表資料區域右下角的單元格
last_row = last_cell.row # 擷取資料區域最後一行的行号
last_column = last_cell.column # 擷取資料區域最後一列的列号
last_column_letter = chr(64 + last_column) # 将資料區域最後一列的列号（數字）轉換為該列的列标（字母）
sum_cell_name = '{}{}'.format(last_column_letter, last_row+1) # 擷取資料區域右下角單元格下方的單元格的位置
sum_last_row_name = '{}{}'.format(last_column_letter, last_row) # 擷取資料區域右下角單元格的位置
formula = '=SUM({}2:{})'.format(last_column_letter, sum_last_row_name) # 根據前面擷取的單元格位置構造Excel公式，對采購金額進行求和
new_worksheet[sum_cell_name].formula = formula # 将求和公式寫入資料區域右下角單元格下方的單元格中
new_worksheet.autofit() # 根據單元格中的資料内容自動調整工作表的行高和列寬
new_workbook.save('E:\\Python案例操作\\采購分類表.xlsx') # 儲存建立的工作簿并命名為“采購分類表.xlsx”
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [7]:
# 案例02-1 篩選一個工作簿中的所有工作表資料
import xlwings as xw # 導入xlwings子產品
import pandas as pd # 導入pandas子產品
app = xw.App(visible = False, add_book = False) # 啟動Excel程式
workbook = app.books.open('E:\\Python案例操作\\采購表.xlsx') # 打開要處理的工作簿
worksheet = workbook.sheets # 列出工作簿中的所有工作表
table = pd.DataFrame() # 建立一個空DataFrame
for i, j in enumerate(worksheet): # 周遊工作簿中的工作表
values = j.range('A1').options(pd.DataFrame, header=1, index= False, expand='table').value # 讀取目前工作表的資料
data = values.reindex(columns=['采購物品', '采購日期', '采購數量', '采購金額']) # 調整列的順序，将“采購物品”移到第1列
table = table.append(data, ignore_index = True) # 将調整列順序後的資料合并到前面建立的DataFrame中
table = table.groupby('采購物品') # 根據“采購物品”列篩選資料
new_workbook = xw.books.add() # 建立一個工作簿
for idx, group in table: # 周遊篩選好的資料，其中idx對應物品名稱，group對應該物品的所有明細資料
new_worksheet = new_workbook.sheets.add(idx) # 在新工作簿中新增工作表，以物品名稱作為工作表名
new_worksheet['A1'].options(index = False).value = group # 在新工作表中寫入目前物品的所有明細資料
last_cell = new_worksheet['A1'].expand('table').last_cell # 擷取目前工作表資料區域右下角的單元格
last_row = last_cell.row # 擷取資料區域最後一行的行号
last_column = last_cell.column # 擷取資料區域最後一列的列号
last_column_letter = chr(64 + last_column) # 将資料區域最後一列的列号（數字）轉換為該列的列标（字母）
sum_cell_name = '{}{}'.format(last_column_letter, last_row+1) # 擷取資料區域右下角單元格下方的單元格的位置
sum_last_row_name = '{}{}'.format(last_column_letter, last_row) # 擷取資料區域右下角單元格的位置
formula = '=SUM({}2:{})'.format(last_column_letter, sum_last_row_name) # 根據前面擷取的單元格位置構造Excel公式，對采購金額進行求和
new_worksheet[sum_cell_name].formula = formula # 将求和公式寫入資料區域右下角單元格下方的單元格中
new_worksheet.autofit() # 根據單元格中的資料内容自動調整工作表的行高和列寬
new_worksheet['A1:D1'].api.Font.Name = '宋體' # 設定工作表标題行的字型為“宋體”
new_worksheet['A1:D1'].api.Font.Size = 10 # 設定工作表标題行的字号為“10”磅
new_worksheet['A1:D1'].api.Font.Bold = True # 加粗工作表标題行
new_worksheet['A1:D1'].api.Font.Color = xw.utils.rgb_to_int((255,255,255)) # 設定工作表标題行的字型顔色為“白色”
new_worksheet['A1:D1'].color = xw.utils.rgb_to_int((0,0,0)) # 設定工作表标題行的單元格填充顔色為“黑色”
new_worksheet['A1:D1'].api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter # 設定工作表标題行的水準對齊方式為“居中”
new_worksheet['A1:D1'].api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter # 設定工作表标題行的垂直對齊方式為“居中”
new_worksheet['B2:B{}'.format(last_row)].number_format = 'm/d'
new_worksheet['D2:D{}'.format(last_row)].number_format = '¥#,##0.00'
new_worksheet['A2'].expand('table').api.Font.Name = '宋體' # 設定工作表的正文字型為“宋體”
new_worksheet['A2'].expand('table').api.Font.Size = 10 # 設定工作表的正文字号為“10”磅
new_worksheet['A2'].expand('table').api.HorizontalAlignment = xw.constants.HAlign.xlHAlignLeft # 設定工作表正文的水準對齊方式為“靠左”
new_worksheet['A2'].expand('table').api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter # 設定工作表正文的垂直對齊方式為“居中”
for cell in new_worksheet['A1'].expand('table'): # 從單元格A1開始為工作表添加合适粗細的邊框
for b in range(7,12):
cell.api.Borders(b).LineStyle = 1 # 設定單元格的邊框線型
cell.api.Borders(b).Weight = 2 # 設定單元格的邊框粗細
new_workbook.save('E:\\Python案例操作\\采購分類表3.xlsx') # 儲存建立的工作簿并命名為“采購分類表.xlsx”
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [12]:
# 案例02-2 篩選一個工作簿中的所有工作表資料
import xlwings as xw # 導入xlwings子產品
import pandas as pd # 導入pandas子產品
app = xw.App(visible = False, add_book = False) # 啟動Excel程式
workbook = app.books.open('E:\\Python案例操作\\采購表.xlsx') # 打開要處理的工作簿
worksheet = workbook.sheets # 列出工作簿中的所有工作表
table = pd.DataFrame() # 建立一個空DataFrame
for i, j in enumerate(worksheet): # 周遊工作簿中的工作表
values = j.range('A1').options(pd.DataFrame, header=1, index= False, expand='table').value # 讀取目前工作表的資料
data = values.reindex(columns=['采購物品', '采購日期', '采購數量', '采購金額']) # 調整列的順序，将“采購物品”移到第1列
table = table.append(data, ignore_index = True) # 将調整列順序後的資料合并到前面建立的DataFrame中
table = table.groupby('采購物品') # 根據“采購物品”列篩選資料
new_workbook = xw.books.add() # 建立一個工作簿
for idx, group in table: # 周遊篩選好的資料，其中idx對應物品名稱，group對應該物品的所有明細資料
new_worksheet = new_workbook.sheets.add(idx) # 在新工作簿中新增工作表，以物品名稱作為工作表名
new_worksheet['A1'].options(index = False).value = group # 在新工作表中寫入目前物品的所有明細資料
last_cell = new_worksheet['A1'].expand('table').last_cell # 擷取目前工作表資料區域右下角的單元格
last_row = last_cell.row # 擷取資料區域最後一行的行号
last_column = last_cell.column # 擷取資料區域最後一列的列号
last_column_letter = chr(64 + last_column) # 将資料區域最後一列的列号（數字）轉換為該列的列标（字母）
sum_cell_name = '{}{}'.format(last_column_letter, last_row+1) # 擷取資料區域右下角單元格下方的單元格的位置
sum_last_row_name = '{}{}'.format(last_column_letter, last_row) # 擷取資料區域右下角單元格的位置
formula = '=SUM({}2:{})'.format(last_column_letter, sum_last_row_name) # 根據前面擷取的單元格位置構造Excel公式，對采購金額進行求和
new_worksheet[sum_cell_name].formula = formula # 将求和公式寫入資料區域右下角單元格下方的單元格中
new_worksheet['A1:{}1'.format(last_column_letter)].api.Font.Name = '宋體' # 設定工作表标題行的字型為“宋體”
new_worksheet['A1:{}1'.format(last_column_letter)].api.Font.Size = 20 # 設定工作表标題行的字号為“10”磅
new_worksheet['A1:{}1'.format(last_column_letter)].api.Font.Bold = True # 加粗工作表标題行
new_worksheet['A1:{}1'.format(last_column_letter)].api.Font.Color = xw.utils.rgb_to_int((255,255,255)) # 設定工作表标題行的字型顔色為“白色”
new_worksheet['A1:{}1'.format(last_column_letter)].color = xw.utils.rgb_to_int((0,0,0)) # 設定工作表标題行的單元格填充顔色為“黑色”
new_worksheet['A1:{}1'.format(last_column_letter)].api.HorizontalAlignment = xw.constants.HAlign.xlHAlignCenter # 設定工作表标題行的水準對齊方式為“居中”
new_worksheet['A1:{}1'.format(last_column_letter)].api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter # 設定工作表标題行的垂直對齊方式為“居中”
new_worksheet['B2:B{}'.format(last_row + 1)].number_format = 'm/d'
new_worksheet['D2:D{}'.format(last_row + 1)].number_format = '¥#,##0.00'
new_worksheet['A2'].expand('table').api.Font.Name = '宋體' # 設定工作表的正文字型為“宋體”
new_worksheet['A2'].expand('table').api.Font.Size = 40 # 設定工作表的正文字号為“10”磅
new_worksheet['A2'].expand('table').api.HorizontalAlignment = xw.constants.HAlign.xlHAlignLeft # 設定工作表正文的水準對齊方式為“靠左”
new_worksheet['A2'].expand('table').api.VerticalAlignment = xw.constants.VAlign.xlVAlignCenter # 設定工作表正文的垂直對齊方式為“居中”
new_worksheet.autofit() # 根據單元格中的資料内容自動調整工作表的行高和列寬
for cell in new_worksheet['A1'].expand('table'): # 從單元格A1開始為工作表添加合适粗細的邊框
for b in range(7,12):
cell.api.Borders(b).LineStyle = 1 # 設定單元格的邊框線型
cell.api.Borders(b).Weight = 2 # 設定單元格的邊框粗細
new_workbook.save('E:\\Python案例操作\\采購分類表5.xlsx') # 儲存建立的工作簿并命名為“采購分類表.xlsx”
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [14]:
# 案例02-3 在一個工作簿中篩選單一類别資料
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('E:\\Python案例操作\\采購表.xlsx')
worksheet = workbook.sheets
table = pd.DataFrame()
for i, j in enumerate(worksheet):
values = j.range('A1').options(pd.DataFrame, header = 1, index = False, expand = 'table').value
data = values.reindex(columns = ['采購物品', '采購日期', '采購數量', '采購金額'])
table = table.append(data, ignore_index = True) # 将多個工作表的資料合并到一個DataFrame中
product = table[table['采購物品'] == '檔案櫃'] # 篩選“采購物品”是“保險箱”的資料
new_workbook = xw.books.add()
new_worksheet = new_workbook.sheets.add('檔案櫃')
new_worksheet['A1'].options(index = False).value = product # 将篩選出的資料寫入工作表（index=False為删除索引列）
row_num = new_worksheet['A1'].current_region.last_cell.row # 擷取工作表中資料區域最後一行的行号
new_worksheet['B2:B{}'.format(row_num)].number_format = 'm/d'
new_worksheet.autofit()
new_workbook.save('E:\\Python案例操作\\檔案櫃.xlsx')
workbook.close()
app.quit()
In [1]:
# 案例03 對多個工作簿中的工作表分别進行分類彙總
import os # 導入os子產品
import xlwings as xw # 導入xlwings子產品
import pandas as pd # 導入pandas子產品
app = xw.App(visible = False, add_book = False) # 啟動Excel程式
file_path = 'E:\\Python案例操作2\\銷售表' # 給出要分類彙總的工作簿所在的檔案夾路徑
file_list = os.listdir(file_path) # 列出檔案夾下所有檔案和子檔案夾的名稱
for i in file_list: # 周遊檔案夾下的檔案
if os.path.splitext(i)[1] == '.xlsx': # 判斷檔案是否是工作簿
workbook = app.books.open(file_path + '\\' + i) # 打開檔案夾中的工作簿
worksheet = workbook.sheets # 列出工作簿中的所有工作表
for j in worksheet: # 周遊工作簿中的工作表
values = j.range('A1').expand('table').options(pd.DataFrame).value # 讀取目前工作表的資料
values['銷售利潤'] = values['銷售利潤'].astype('float') # 轉換“銷售利潤”列的資料類型
result = values.groupby('銷售區域').sum()# 根據“銷售區域”列對資料進行分類彙總，彙總運算方式為求和
j.range('J1').value = result['銷售利潤'] # 将各個銷售區域的銷售利潤彙總結果寫入目前工作表
workbook.save() # 儲存工作簿
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [2]:
# 案例03-1 批量分類彙總多個工作簿中的指定工作表
import os
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
file_path = 'E:\\Python案例操作2\\銷售表1'
file_list = os.listdir(file_path)
for i in file_list:
if os.path.splitext(i)[1] == '.xlsx':
workbook = app.books.open(file_path + '\\' + i)
worksheet = workbook.sheets['銷售記錄表']
values = worksheet.range('A1').expand('table').options(pd.DataFrame).value
values['銷售利潤'] = values['銷售利潤'].astype('float')
result = values.groupby('銷售區域').sum()
worksheet.range('J1').value = result['銷售利潤']
workbook.save()
workbook.close()
app.quit()
In [3]:
# 案例03-2 将多個工作簿資料分類彙總到一個工作簿
import os
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
file_path = 'E:\\Python案例操作2\\銷售表'
file_list = os.listdir(file_path)
collection = []
for i in file_list:
if os.path.splitext(i)[1] == '.xlsx':
workbook = app.books.open(file_path + '\\' + i)
worksheet = workbook.sheets['銷售記錄表']
values = worksheet.range('A1').expand('table').options(pd.DataFrame).value
filtered = values[['銷售區域', '銷售利潤']] # 隻保留'銷售區域', '銷售利潤'兩列資料
collection.append(filtered)
workbook.close()
new_values = pd.concat(collection, ignore_index = False).set_index('銷售區域')
new_values['銷售利潤'] = new_values['銷售利潤'].astype('float')
result = new_values.groupby('銷售區域').sum()
new_workbook = app.books.add()
sheet = new_workbook.sheets[0]
sheet.range('A1').value = result
new_workbook.save('E:\\Python案例操作2\\彙總.xlsx')
app.quit()
In [4]:
# 案例04 對一個工作簿中的所有工作表分别求和
import xlwings as xw # 導入xlwings子產品
import pandas as pd # 導入pandas子產品
app = xw.App(visible = False, add_book = False) # 啟動Excel程式
workbook = app.books.open('E:\\Python案例操作2\\采購表.xlsx') # 打開要求和的工作簿
worksheet = workbook.sheets # 列出工作簿中的所有工作表
for i in worksheet: # 周遊工作簿中的工作表
values = i.range('A1').expand('table') # 選中工作表中含有資料的單元格區域
data = values.options(pd.DataFrame).value # 使用選中的單元格區域中的資料建立一個DataFrame
sums = data['采購金額'].sum() # 在建立的DataFrame中對“采購金額”列進行求和
column = values.value[0].index('采購金額') + 1 # 擷取“采購金額”列的列号
row = values.shape[0] # 擷取資料區域最後一行的行号
i.range(row + 1, column).value = sums # 将求和結果寫入“采購金額”列最後一個單元格下方的單元格中
workbook.save() # 儲存工作簿
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [5]:
# 案例04-1 對一個工作簿中的所有工作表分别求和并将求和結果寫入固定單元格
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('E:\\Python案例操作2\\采購表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
values = i.range('A1').expand('table').options(pd.DataFrame).value
sums = values['采購金額'].sum()
i.range('F1').value = sums
workbook.save()
workbook.close()
app.quit()
In [7]:
# 案例05 批量統計工作簿的最大值和最小值
import os # 導入os子產品
import xlwings as xw # 導入xlwings子產品
import pandas as pd # 導入pandas子產品
app = xw.App(visible = False, add_book = False) # 啟動Excel程式
file_path = 'E:\\Python案例操作2\\産品銷售統計表' # 列出要統計最大值和最小值的工作簿所在的檔案夾路徑
file_list = os.listdir(file_path) # 列出檔案夾下所有檔案和子檔案夾的名稱
for j in file_list: # 周遊檔案夾下的檔案
if os.path.splitext(j)[1] == '.xlsx': # 判斷檔案是否是工作簿
workbook = app.books.open(file_path + '\\' + j) # 打開檔案夾中的工作簿
worksheet = workbook.sheets # 列出目前工作簿中的所有工作表
for i in worksheet: # 周遊工作簿中的工作表
values = i.range('A1').expand('table').options(pd.DataFrame).value # 讀取目前工作表的資料
max = values['銷售利潤'].max() # 統計“銷售利潤”列的最大值
min = values['銷售利潤'].min() # 統計“銷售利潤”列的最小值
i.range('I1').value = '最大銷售利潤' # 在目前工作表的單元格I1中寫入文本内容
i.range('J1').value = max # 在目前工作表的單元格J1中寫入統計出的最大值
i.range('I2').value = '最小銷售利潤' # 在目前工作表的單元格I2中寫入文本内容
i.range('J2').value = min # 在目前工作表的單元格J2中寫入統計出的最小值
workbook.save() # 儲存工作簿
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [9]:
# 案例05-1 批量統計一個工作簿中所有工作表的最大值和最小值
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = xw.Book('E:\\Python案例操作2\\産品銷售統計表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
values = i.range('A1').expand('table').options(pd.DataFrame).value
max = values['銷售利潤'].max()
min = values['銷售利潤'].min()
i.range('I1').value = '最大銷售利潤'
i.range('J1').value = max
i.range('I2').value = '最小銷售利潤'
i.range('J2').value = min
workbook.save()
workbook.close()
app.quit()
In [10]:
# 案例06 批量制作資料透視表
import os # 導入os子產品
import xlwings as xw # 導入xlwings子產品
import pandas as pd # 導入pandas子產品
app = xw.App(visible = False, add_book = False) # 啟動Excel程式
file_path = 'E:\\Python案例操作2\\商品銷售表' # 給出要制作資料透視表的工作簿所在的檔案夾路徑
file_list = os.listdir(file_path) # 列出檔案夾下所有檔案和子檔案夾的名稱
for j in file_list: # 周遊檔案夾下的檔案
if os.path.splitext(j)[1] == '.xlsx': # 判斷檔案是否是工作簿
workbook = app.books.open(file_path + '\\' + j) # 打開檔案夾中的工作簿
worksheet = workbook.sheets # 列出目前工作簿中的所有工作表
for i in worksheet: # 周遊目前工作簿中的工作表
values = i.range('A1').expand('table').options(pd.DataFrame).value # 讀取目前工作表的資料
pivottable = pd.pivot_table(values,values = '銷售金額',
index = '銷售地區',columns = '銷售分部',
aggfunc = 'sum',fill_value = 0,
margins = True,margins_name = '總計') # 用讀取的資料制作資料透視表
i.range('J1').value = pivottable # 将制作的資料透視表寫入目前工作表
workbook.save() # 儲存工作簿
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [11]:
# 案例06-1 為一個工作簿的所有工作表制作資料透視表
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('E:\\Python案例操作2\\商品銷售表.xlsx')
worksheet = workbook.sheets
for i in worksheet:
values = i.range('A1').expand('table').options(pd.DataFrame).value
pivottable = pd.pivot_table(values, values = '銷售金額',
index = '銷售地區', columns = '銷售分部',
aggfunc = 'sum', fill_value = 0,
margins = True, margins_name = '總計')
i.range('J1').value = pivottable
workbook.save()
workbook.close()
app.quit()
In [13]:
# 案例07 使用相關系數判斷資料的相關性
import pandas as pd # 導入pandas子產品
df = pd.read_excel('E:\\Python案例操作2\\相關性分析.xlsx', index_col = '代理商編号') # 從指定工作簿中讀取要進行相關性分析的資料
result = df.corr() # 計算任意兩個變量之間的相關系數
print(result) # 輸出計算出的相關系數
年銷售額（萬元） 年廣告費投入額（萬元） 成本費用（萬元） 管理費用（萬元）
年銷售額（萬元） 1.000000 0.982321 0.953981 0.012364
年廣告費投入額（萬元） 0.982321 1.000000 0.917698 -0.046949
成本費用（萬元） 0.953981 0.917698 1.000000 0.098500
管理費用（萬元） 0.012364 -0.046949 0.098500 1.000000
In [14]:
# 案例07-1 求單個變量和其他變量間的相關性
import pandas as pd
df = pd.read_excel('E:\\Python案例操作2\\相關性分析.xlsx', index_col = '代理商編号')
result = df.corr()['年銷售額（萬元）'] # 計算年銷售額與其他變量之間的皮爾遜相關系數
print(result)
年銷售額（萬元） 1.000000
年廣告費投入額（萬元） 0.982321
成本費用（萬元） 0.953981
管理費用（萬元） 0.012364
Name: 年銷售額（萬元）, dtype: float64
In [16]:
# 案例08 使用方差分析對比資料的差異
import pandas as pd # 導入pandas子產品
from statsmodels.formula.api import ols # 導入statsmodels.formula.api子產品中的ols()函數
from statsmodels.stats.anova import anova_lm # 導入statsmodels.stats.anova子產品中的anova_lm()函數
import xlwings as xw # 導入xlwings子產品
df = pd.read_excel('E:\\Python案例操作2\\方差分析.xlsx') # 讀取指定工作簿中的資料
df = df[['A型号','B型号','C型号','D型号','E型号']] # 選取“A型号”“ B型号”“ C型号”“ D型号”“ E型号”列的資料用于分析
df_melt = df.melt() # 将列名轉換為列資料，重構DataFrame
df_melt.columns = ['Treat', 'Value'] # 重命名列
df_describe = pd.DataFrame() # 建立一個空DataFrame用于彙總資料
df_describe['A型号'] = df['A型号'].describe() #計算“A型号”輪胎刹車距離的平均值、最大值和最小值等
df_describe['B型号'] = df['B型号'].describe() # 計算“B型号”輪胎刹車距離的平均值、最大值和最小值等
df_describe['C型号'] = df['C型号'].describe() # 計算“C型号”輪胎刹車距離的平均值、最大值和最小值等
df_describe['D型号'] = df['D型号'].describe() # 計算“D型号”輪胎刹車距離的平均值、最大值和最小值等
df_describe['E型号'] = df['E型号'].describe() # 計算“E型号”輪胎刹車距離的平均值、最大值和最小值等
model = ols('Value~C(Treat)', data = df_melt).fit() # 對樣本資料進行最小二乘線性拟合計算
anova_table = anova_lm(model, typ = 3) # 對樣本資料進行方差分析
app = xw.App(visible = False) # 啟動Excel程式
workbook = app.books.open('E:\\Python案例操作2\\方差分析.xlsx') # 打開要寫入分析結果的工作簿
worksheet = workbook.sheets['單因素方差分析'] # 選中工作表“單因素方差分析”
worksheet.range('H2').value = df_describe.T # 将計算出的平均值、最大值和最小值等資料轉置行列并寫入工作表
worksheet.range('H14').value = '方差分析' # 在工作表中寫入文本“方差分析”
worksheet.range('H15').value = anova_table # 将方差分析的結果寫入工作表
workbook.save() # 儲存工作簿
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
In [1]:
# 案例08-1 繪制箱形圖識别異常值
import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
df = pd.read_excel('E:\\Python案例操作2\\方差分析.xlsx')
df = df[['A型号', 'B型号', 'C型号', 'D型号', 'E型号']]
figure = plt.figure() # 建立繪圖視窗
plt.rcParams['font.sans-serif'] = ['SimHei'] # 解決中文亂碼問題
df.boxplot(grid = False) # 繪制箱型圖并删除網格線
app = xw.App(visible = False)
workbook = app.books.open('E:\\Python案例操作2\\方差分析.xlsx')
worksheet = workbook.sheets['單因素方差分析']
worksheet.pictures.add(figure, name = '圖檔1', update = True, left = 500, top = 10) # 将繪制的箱型圖插入工作表
workbook.save('E:\\Python案例操作2\\箱形圖.xlsx')
workbook.close()
app.quit()
繪制箱形圖識别異常值
In [4]:
# 案例09 使用描述統計和直方圖制定目标
import pandas as pd # 導入pandas子產品
import matplotlib.pyplot as plt # 導入Matplotlib子產品
import xlwings as xw # 導入xlwings子產品
df = pd.read_excel('E:\\Python案例操作2\\描述統計.xlsx') # 讀取指定工作簿中的資料
df.columns = ['序号', '員工姓名', '月銷售額'] # 重命名資料列
df = df.drop(columns=['序号', '員工姓名']) # 删除“序号”列和“員工姓名”列
df_describe = df.astype('float').describe() # 計算資料的個數、平均值、最大值和最小值等描述統計資料
df_cut = pd.cut(df['月銷售額'], bins = 7, precision = 2) # 将“月銷售額”列的資料分成7個均等的區間
cut_count = df['月銷售額'].groupby(df_cut).count() # 統計各個區間的人數
df_all = pd.DataFrame() # 建立一個空DataFrame用于彙總資料
df_all['計數'] = cut_count # 将月銷售額的區間及區間的人數寫入前面建立的DataFrame中
df_all_new = df_all.reset_index() # 将索引重置為數字序号
df_all_new['月銷售額'] = df_all_new['月銷售額'].apply(lambda x: str(x)) # 将“月銷售額”列的資料轉換為字元串類型
fig = plt.figure() # 建立繪圖視窗
plt.rcParams['font.sans-serif'] = ['SimHei'] # 解決中文亂碼問題
n, bins, patches = plt.hist(df['月銷售額'], bins = 7, edgecolor = 'black', linewidth = 0.5) # 使用“月銷售額”列的資料繪制直方圖
plt.xticks(bins) # 将直方圖x軸的刻度标簽設定為各區間的端點值
plt.title('月銷售額頻率分析') # 設定直方圖的圖表标題
plt.xlabel('月銷售額') # 設定直方圖x軸的标題
plt.ylabel('頻數') # 設定直方圖y軸的标題
app = xw.App(visible = False) # 啟動Excel程式
workbook = app.books.open('E:\\Python案例操作2\\描述統計.xlsx') # 打開要寫入分析結果的工作簿
worksheet = workbook.sheets['業務員銷售額統計表'] # 選中工作簿中的工作表
worksheet.range('E2').value = df_describe # 将計算出的個數、平均值、最大值和最小值等資料寫入工作表
worksheet.range('H2').value = df_all_new # 将月銷售額的區間及區間的人數寫入工作表
worksheet.pictures.add(fig, name = '圖檔1', update = True, left = 400, top = 200) # 将繪制的直方圖轉換為圖檔并寫入工作表
worksheet.autofit() # 根據資料内容自動調整工作表的行高和列寬
workbook.save('E:\\Python案例操作2\\描述統計1.xlsx') # 另存工作簿
workbook.close() # 關閉工作簿
app.quit() # 退出Excel程式
直方圖
In [5]:
# 案例09-1 使用自定義區間繪制直方圖
import pandas as pd
import matplotlib.pyplot as plt
import xlwings as xw
df = pd.read_excel('E:\\Python案例操作2\\描述統計.xlsx')
df.columns = ['序号','員工姓名','月銷售額']
df = df.drop(columns=['序号','員工姓名'])
df_describe = df.astype(float).describe()
df_cut = pd.cut(df['月銷售額'], bins = range(8, 37, 4))
cut_count = df['月銷售額'].groupby(df_cut).count()
df_all = pd.DataFrame()
df_all['計數'] = cut_count
df_all_new = df_all.reset_index()
df_all_new['月銷售額'] = df_all_new['月銷售額'].apply(lambda x:str(x))
fig = plt.figure()
plt.rcParams['font.sans-serif'] = ['SimHei']
n, bins, patches = plt.hist(df['月銷售額'], bins = range(8, 37, 4), edgecolor = 'black', linewidth = 0.5)
plt.xticks(bins)
plt.title('月銷售額頻率分析')
plt.xlabel('月銷售額')
plt.ylabel('頻數')
app = xw.App(visible = False)
workbook = app.books.open('E:\\Python案例操作2\\描述統計.xlsx')
worksheet = workbook.sheets['業務員銷售額統計表']
worksheet.range('E2').value = df_describe
worksheet.range('H2').value = df_all_new
worksheet.pictures.add(fig, name = '圖檔1', update = True, left = 400, top = 200)
worksheet.autofit()
workbook.save('E:\\Python案例操作2\\描述統計2.xlsx')
workbook.close()
app.quit()
使用自定義區間繪制直方圖
In [6]:
# 案例10 使用回歸分析預測未來值
import pandas as pd # 導入pandas子產品
from sklearn import linear_model # 導入sklearn子產品
df = pd.read_excel('E:\\Python案例操作2\\回歸分析.xlsx', header = None) # 讀取指定工作簿中的資料
df = df[2:] # 删除前兩行資料
df.columns = ['月份', '電視台廣告費', '視訊門戶廣告費', '汽車當月銷售額'] # 重命名資料列
x = df[['視訊門戶廣告費', '電視台廣告費']] # 擷取“視訊門戶廣告費”列和“電視台廣告費”列的資料作為自變量
y = df['汽車當月銷售額'] # 擷取“汽車當月銷售額”列的資料作為因變量
model = linear_model.LinearRegression() # 建立一個線性回歸模型
model.fit(x, y) # 用自變量和因變量資料對線性回歸模型進行訓練，拟合出線性回歸方程
R2 = model.score(x, y) # 計算R2值
print(R2) # 輸出R2值
0.9727262235892342
In [7]:
# 案例10-1 使用回歸方程計算預測值
import pandas as pd
from sklearn import linear_model
df = pd.read_excel('E:\\Python案例操作2\\回歸分析.xlsx', header = None)
df = df[2:]
df.columns = ['月份', '電視台廣告費', '視訊門戶廣告費', '汽車當月銷售額']
x = df[['視訊門戶廣告費', '電視台廣告費']]
y = df['汽車當月銷售額']
model = linear_model.LinearRegression()
model.fit(x,y)
coef = model.coef_
model_intercept = model.intercept_
result = 'y={}*x1+{}*x2{}'.format(coef[0], coef[1], model_intercept)
print('線性回歸方程為：', '\n', result)
a = 30
b = 20
y = coef[0] * a + coef[1] * b + model_intercept
print(y)
線性回歸方程為：
y=51.06148377665355*x1+9.133786669280708*x2-316.2888503650415
1398.2313963201793 連結：https://pan.baidu.com/s/1tolJ5u5R6xzOSUdV0WpwSw
提取碼：mkd8