Python3分析Excel数据

3.1 内省Excel工作薄

使用xlrd和xlwt扩展包，确定工作簿中工作表的数量、名称和每个工作表中行列的数量。
1excel_introspect_workbook.py

#!/usr/bin/env python3
import sys
from xlrd import open_workbook

input_file = sys.argv[1]

workbook = open_workbook(input_file)
print('Number of worksheets: ', workbook.nsheets)
for worksheet in workbook.sheets():
    print("Worksheet name:", worksheet.name, "\tRows:",\
        worksheet.nrows, "\tColumns:", worksheet.ncols)

导入xlrd模块open_workbook函数读取和分析Excel文件。
for循环在所有工作表之间迭代，workbook对象的sheets方法可以识别出工作簿中所有的工作表。
print语句使用worksheet对象的name属性确定每个工作表名称，使用nrows和ncols属性确定每个工作表中行与列的数量。
输出结果：

Number of worksheets: 3
Worksheet name: january_2013 Rows: 7 Columns: 5
Worksheet name: february_2013 Rows: 7 Columns: 5
Worksheet name: march_2013 Rows: 7 Columns: 5

3.2 处理单个工作表

3.2.1 读写Excel文件

使用pandas分析Excel文件
pandas_read_and_write_excel.py

#!/usr/bin/env python3
import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]
data_frame = pd.read_excel(input_file, sheet_name='january_2013')
writer = pd.ExcelWriter(output_file)
data_frame.to_excel(writer, sheet_name='jan_13_output', index=False)
writer.save()

3.2.2 筛选特定行

行中的值满足某个条件

用pandas筛选出Sale Amount大于$1400.00的行。
pandas_value_meets_condition.py

#!/usr/bin/env python3
import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)
data_frame_value_meets_condition = \
    data_frame[data_frame['Sale Amount'].astype(float) > 1400.0]
writer = pd.ExcelWriter(output_file)
data_frame_value_meets_condition.to_excel(\
    writer, sheet_name='jan_13_output', index=False)
writer.save()

行中的值属于某个集合

用pandas筛选出购买日期属于集合（01/24/2013-01/31/2013）的行。
pandas提供isin函数检验一个特定值是否在一个列表中
pandas_value_in_set.py

#!/usr/bin/env python3
import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)
important_dates = ['01/24/2013', '01/31/2013']
data_in_set = data_frame[data_frame['PurchaseDate']\
.isin(important_dates)]
writer = pd.ExcelWriter(output_file)
data_in_set.to_excel(writer, sheet_name='jan_13_output', index=False)
writer.save()

行中的值匹配于特定模式

用pandas筛选出客户姓名以大写字母J开头的行。
pandas_value_matches_pattern.py

#!/usr/bin/env python3
import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)
data_frame_value = data_frame[data_frame['Customer Name']\
.str.startswith("J")]
writer = pd.ExcelWriter(output_file)
data_frame_value.to_excel(writer, sheet_name='jan_13_output', index=False)
writer.save()

3.2.3 选取特定列

有两种方法可以在Excel文件中选取特定的列：

• 使用列索引值
• 使用列标题

使用列索引值
用pandas设置数据框，在方括号中列出要保留的列的索引值或名称（字符串）。设置数据框和iloc函数，同时选择特定的行与特定的列。如果使用iloc函数来选择列，那么就需要在列索引值前面加上一个冒号和一个逗号，表示为这些特定的列保留所有的行。
pandas_column_by_index.py

#!/usr/bin/env python3
import pandas as pd
import sys

input_file = sys.argv[1]
output_file =sys.argv[2]

data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)
data_frame_index = data_frame.iloc[:, [1:4]]
writer = pd.ExcelWriter(output_file)
data_frame_index.to_excel(writer, sheet_name='jan_13_out', index=False)
writer.save()

使用列标题
用pandas基于列标题选取Customer ID和Purchase Date列的两种方法：

• 在数据框名称后面的方括号中将列名以字符串方式列出。
• 用loc函数，在列标题列表前面加上一个冒号和一个逗号，表示为这些特定的列保留所有行。

pandas_column_by_name.py

#!/usr/bin/env python3
import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, 'january_2013', index_col=None)
data_frame_column = data_frame.iloc[:, ['Customer ID', 'Purchase Date']]
writer = pd.ExcelWriter(output_file)
data_frame_column.to_excel(writer, sheet_name='jan_13_output', index=False)
writer.save()

3.3 读取工作簿中的所有工作表

3.3.1 在所有工作表中筛选特定行

pandas通过在read_excel函数中设置sheetname=None，可以一次性读取工作簿中的所有工作表。
pandas将所有工作表读入数据框字典，字典中的键就是工作表的名称，值就是包含工作表中数据的数据框。所以，通过在字典的键和值之间迭代，可以使用工作簿中所有的数据。
当在每个数据框中筛选特定行时，结果是一个新的筛选过的数据框，所以可以创建一个列表保存这些筛选过的数据框，然后将它们连接成一个最终数据框。

在所有工作表中筛选出销售额大于$2000.00的所有行。
pandas_value_ meets_condition_all_worksheets.py

#!/usr/bin/env python3
import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, sheet_name=None, index_col=None)
row_output = []
for worksheet_name, data in data_frame.items():
    row_output_append(data[data['Sale Amount'].astype(float) > 2000.0])
filtered_rows = pd.concat(row_output, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
filtered_rows.to_excel(writer, sheet_name='sale_amount_gt2000', index=False)
writer.save()

3.3.2 在所有工作表中选取特定列

有两种方法可以从工作表中选取一组列：

• 使用列索引值
• 使用列标题

在所有工作表中选取Customer Name和Sale Amount列
用pandas的read_excel函数将所有工作表读入字典。然后，用loc函数在每个工作表中选取特定的列，创建一个筛选过的数据框列表，并将这些数据框连接在一起，形成一个最终数据框。
pandas_column_ by_name_all_worksheets.py

#!/usr/bin/env python3
import pandas as pd
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

data_frame = pd.read_excel(input_file, sheet_name=None, index_col=None)
column_output = []
for worksheet_name, data in data_frame.items():
    column_output.append(data.loc[:, ['Customer Name', 'Sale Amount']])
selected_columns = pd.concat(column_output, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
selected_columns.to_excel(writer, sheet_name='selected_columns_all_worksheets',\
index=False)
writer.save()

3.4 在Excel工作簿中读取一组工作表

在一组工作表中筛选特定行

用pandas在工作簿中选择一组工作表，在read_excel函数中将工作表的索引值或名称设置成一个列表。创建索引值列表my_ sheets，在read_excel函数中设定sheetname等于my_sheets。想从第一个和第二个工作表中筛选出销售额大于$1900.00 的行。

pandas_value_meets_condition_set_of_worksheets.py

#!/usr/bin/env python
import pandas as pd
import sys

input_file = sys.argv[1]
output_file =sys.argv[2]

my_sheets = [0,1]
threshold = 1900.0
data_frame = pd.read_excel(input_file, sheet_name=my_sheets, index_col=None)
row_list = [ ]
for worksheet_name, data in data_frame.items():
    row_list.append(data[data['Sale Amount'].astype(float) > threshold])
filtered_rows = pd.concat(row_list, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
filtered_rows.to_excel(writer, sheet_name='set_of_worksheets', index=False)
writer.save()

3.5 处理多个工作簿

3.5.1 工作表计数以及每个工作表中的行列计数

在开始处理工作表之前，获取关于工作表的描述性信息非常重要。

想知道一个文件夹中工作簿的数量，每个工作簿中工作表的数量，以及每个工作表中行与列的数量：
12excel_introspect_all_ workbooks.py

#!/usr/bin/env python3
import glob
import os
import sys
from xlrd import open_work

input_directory = sys.argv[1]
workbook_counter = 0
for input_file in glob.glob(os.path.join(input_directory, '*.xls*')):
    workbook = open_workbook(input_file)
    print('Workbook: %s' % os.path.basename(input_file))
    print('Number of worksheets: %d' % workbook.nsheets)
    for worksheet in workbook.sheets():
        print('Worksheet name:', worksheet.name, '\tRows:',\
                  worksheet.nrows, '\tColumns:', worksheet.nclos)
    workbook_counter += 1
print('Number of Excel workbooks: %d' % (workbook_counter))

导入Python内置的glob模块和os模块，使用其中的函数识别和解析待处理文件的路径名。
使用Python内置的glob模块和os模块，创建要处理的输入文件列表，并对输入文件列表应用for循环，对所有要处理的工作簿进行迭代。

3.5.2 从多个工作簿中连接数据

pandas提供concat函数连接数据框。

• 如果想把数据框一个一个地垂直堆叠，设置参数axis=0。
• 如果想把数据框一个一个地平行连接，设置参数axis=1。

如果要基于某个关键字列连接数据框，pandas的merge函数提供类似SQL join的操作。

用pandas将多个工作簿中所有工作表的数据垂直连接成一个输出文件
pandas_concat_data_from_multiple_workbook.py

#!/usr/bin/env python3
import pandas as pd
import glot
import os
import sys

input_file = sys.argv[1]
output_file = sys.argv[2]

all_workbooks = glob.glob(os.path.join(input_path, '*.xls*'))
data_frames = [ ]
for workbook in all_workbooks:
    all_workbooks = pd.read_excel(workbook, sheet_name=None, index_col=None)
    for worksheet_name, data in all_worksheets.items():
            data_frames.append(data)
all_data_concatenated = pd.concat(data_frame, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer, sheet_name='all_data_all_workbooks',\
index=False)
writer.save()

3.5.3 为每个工作簿和工作表计算总数和均值

pandas在多个工作簿间迭代，在工作簿级和工作表级计算统计量。为工作簿的每个工作表计算统计量，并将结果连接成一个数据框。
接下来，计算工作簿级的统计量，将它们转换成一个数据框，然后通过基于工作簿名称的左连接将两个数据框合并在一起，并将结果数据框添加到一个列表中。
当所有工作簿级的数据框都进入列表后，将这些数据框连接成一个独立数据框，并写入输出文件。
pandas_sum_average_multiple_workbook.py

#!/usr/bin/env python3
import pandas as pd
import glob
import os
import sys

input_path = sys.argv[1]
output_file = sys.argv[2]
all_workbooks = glob.glob(os.path.join(input_path, '*.xls*'))
data_frames = [ ]
for workbook in all_workbooks:
    all_workbooks = pd.read_excel(workbook, sheet_name=None, index_col=None)
    workbook_total_sales = [ ]
    workbook_number_of_sales = [ ]
    worksheet_data_frames = [ ]
    worksheets_data_frame = None
    workbook_data_frame = None
    for worksheet_name, data in all_worksheets.items():
        total_sales = pd.DataFrame([float(str(value).strip('$').replace(\
        ',',' '))
        for value in data.loc[:, 'Sale Amount']]).sum()
        number_of_sales = len(data.loc[:, 'Sale Amount'])
        average_sales = pd.DataFrame(total_sales / number_of_sales)

        workbook_total_sales.append(total_sales)
        workbook_number_of_sales.append(number_of_sales)
        data = {'workbook': os.path.basename(workbook),
                  'worksheet': worksheet_name,
                  'worksheet_total': total_sales,
                  'wprksheet_average': average_sales}
        
        worksheet_Data_frame.append(pd.DataFrame(data, \
        columns=['workbook', ''worksheet, \
        'worksheet_total', 'worksheet_average']))
    worksheets_data_frame = pd.concat(\
    worksheet_data_frames, axis=0, ignore_index=True
    workbook_total = pd.DataFrame(workbook_total_sales).sum()
    workbook_total_number_of_sales = pd.DaraFrame(\
    workbook_numbwe_of_sales).sum()
    workbook_average = pd.DataFrame(\
    workbook_total / workbook_total_number_of_sales)

    work_stats = {'workbook': os.path.basename(workbook),
                     'workbook_total'}: workbook_total,
                     'workbook_average': workbook_average}
    workbooks_stats = pd.DataFrame(workbook_stats, columns=\
    ['workbook', 'workbook_total', 'workbook_average'])
    workbook_data_frame = pd.merge(worksheets_data_frame, workbook_stats, \
    on='workbook', how='left')
    data_frames.append(workbook_data_frame)
all_data_concatenated = pd.concat(data_frames, axis=0, ignore_index=True)
writer = pd.ExcelWriter(output_file)
all_data_concatenated.to_excel(writer, sheet_name='sums_and_averages', index=False)
writer.save()