python数据分析基础——批量进行数据分析

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)   # 启动Excel程序
workbook = app.books.open('产品销售统计表.xlsx')    # 打开要排序的工作簿
worksheet = workbook.sheets     # 列出工作簿中的所有工作表

for i in worksheet:   # 遍历工作簿中的工作表
    values = i.range('A1').expand('table').options(pd.DataFrame).value   # 读取当前工作表的数据并转换为DataFrame格式
    result = values.sort_values(by = '销售利润')   # 对“销售利润”列进行升序排列
    # result = values.sort_values(by='销售利润', ascending=False)  # 对“销售利润”列进行降序排列
    i.range('A1').value = result   # 将排序结果写入当前工作表，替换原有数据
    
workbook.save()
workbook.close()
app.quit()

2、批量排序多个工作簿中的数据

import os
import xlwings as xw
import pandas as pd  
app = xw.App(visible = False, add_book = False)
file_path = '产品销售统计表'
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()

二、筛选一个工作簿中的所有工作表数据

1、筛选一个工作簿中的所有工作表数据

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('采购表.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)
table = table.groupby('采购物品')
new_workbook = xw.books.add() 

for idx, group in table:
    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)
    new_worksheet[sum_cell_name].formula = formula 
    new_worksheet.autofit()
    
new_workbook.save('采购分类表.xlsx')
workbook.close()
app.quit()

2、在一个工作簿中筛选单一类别数据

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('采购表.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) 
    
product = table[table['采购物品'] == '保险箱'] 
new_workbook = xw.books.add() 
new_worksheet = new_workbook.sheets.add('保险箱') 
new_worksheet['A1'].options(index = False).value = product 
new_worksheet.autofit() 
new_workbook.save('保险箱.xlsx')
workbook.close()
app.quit()

三、对多个工作簿中的工作表分别进行分类汇总

1、对多个工作簿中的工作表分别进行分类汇总

import os  
import xlwings as xw  
import pandas as pd  
app = xw.App(visible = False, add_book = False) 
file_path = '销售表'  
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()

2、批量分类汇总多个工作簿中的指定工作表

import os  
import xlwings as xw 
import pandas as pd  
app = xw.App(visible = False, add_book = False) 
file_path = '销售表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()

3、将多个工作簿数据分类汇总到一个工作簿

import os
import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
file_path = '销售表'
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('汇总.xlsx')
app.quit()

四、对一个工作簿中的所有工作表分别求和

1、对一个工作簿中的所有工作表分别求和

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('采购表.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()

2、对一个工作簿中的所有工作表分别求和并将求和结果写入固定单元格

import xlwings as xw
import pandas as pd
app = xw.App(visible = False, add_book = False)
workbook = app.books.open('采购表.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        # 将当前工作表中数据的求和结果写入当前工作表的单元格F1中

workbook.save()
workbook.close()
app.quit()

五、批量统计工作簿的最大、最小值

1、批量统计工作簿的最大、最小值

import os  
import xlwings as xw  
import pandas as pd  
app = xw.App(visible = False, add_book = False)
file_path = '产品销售统计表'  
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 = '最大销售利润'  
            i.range('J1').value = max  
            i.range('I2').value = '最小销售利润' 
            i.range('J2').value = min
            
        workbook.save()  
        workbook.close()  
app.quit()

2、批量统计一个工作簿中所有工作表的最大、最小值

import xlwings as xw  
import pandas as pd 
app = xw.App(visible = False, add_book = False)
workbook = xw.Book('产品销售统计表.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()

六、批量制作数据透视表

1、批量制作数据透视表

import os 
import xlwings as xw 
import pandas as pd  
app = xw.App(visible = False, add_book = False) 
file_path = '商品销售表'  
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()

2、为一个工作簿的所有工作表制作数据透视表

import xlwings as xw  
import pandas as pd  
app = xw.App(visible = False, add_book = False) 
workbook = app.books.open('商品销售表.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()

参考书目：《超简单用python让Excel飞起来》