用python分析excel数据_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()