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()

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏代码永生,思想不朽

utf8中文字符串的多模式匹配算法的优化

上个月接触到了我组的一个关于在海量文本中匹配字符串业务。读源代码时发现一些问题,并针对这些问题做了优化工作,效果非常明显。

4553
来自专栏软件开发 -- 分享 互助 成长

散列表(哈希表)

序言: 如果将一系列的记录按照关键字的某种函数存储,那么在查找某个数据的时候就可以直接通过关键字计算出来了,而不在需要“比较”,这样会非常高效,这就是散列技术。...

1918
来自专栏机器之心

令人困惑的TensorFlow!

我叫 Jacob,是 Google AI Resident 项目的研究学者。我是在 2017 年夏天加入该项目的,尽管已经拥有了丰富的编程经验,并且对机器学习的...

1453
来自专栏PPV课数据科学社区

【学习】《R实战》读书笔记(第二章)

“读书会是一种在于拓展视野、宏观思维、知识交流、提升生活的活动。PPV课R语言读书会以“学习、分享、进步”为宗旨,通过成员协作完成R语言专业书籍的精读和分享,达...

3599
来自专栏c#开发者

设计模式之策略模式(Strategy)

策略模式: 策略模式属于对象行为模式[GOF95]。其用意是针对一组算法,将每一个算法封装到具体共同缄口的独立的类中,从来是的他们可以相互替换。策略模式使得算法...

37114
来自专栏简书专栏

基于python的冒泡排序和选择排序

装饰器是python的高级用法,初学者需要单独学习1天才能理解并且熟练运用。 读者如果不理解本节内容,不影响后续内容的理解。 此装饰器只是计算函数运行花费的...

1074
来自专栏ChaMd5安全团队

第二届ZCTF逆向题分析(一)

第二届ZCTF逆向题分析(一) From ChaMd5安全团队核心成员 Poyoten 比赛时由于第二天有事,第三题re没做,所以也就没有写WP。但是后来看了...

93715
来自专栏数据结构与算法

10.25解题报告

预计分数:40+50+4=94 实际分数:30+15+0=45 T1 https://www.luogu.org/problem/show?pid=T14791...

2804
来自专栏take time, save time

你所能用到的无损压缩编码(二)

     上个月项目荷兰大佬要检查,搞的我想写的东西不断推迟,现在检查完了,我决定继续把我想写的这整个一个系列写完,上一次写的是最简单的无损编码行程编码,这一次...

3609
来自专栏CreateAMind

pytorch初体验

一部分的内容在2017年1月18日Facebook发行的PyTorch相比TensorFlow、MXNet有何优势? - 罗若天的回答 - 知乎 已有。

1241

扫码关注云+社区

领取腾讯云代金券