用 Python 合并 Excel。
先合并几个内容类似的 Excel,在通过一个字段把另一个Excel文件的内容添加进来,然后自动进行 groupby 处理,最好还是要一个 GUI 页面,TM 要求真多!
选择需要批量处理的 Excel 文件所在的目录和保存生成的新 Excel 的目录,以及那个需要 merge 进来的文件目录,点击 Start 按钮就可以自动完成
我们还是先看下测试数据
import pandas as pd
import numpy as np
import glob
glob.glob("./sales*.xlsx")
Output:
['.\sales-feb-2014.xlsx', '.\sales-jan-2014.xlsx', '.\sales-mar-2014.xlsx']
上面的三个文件,就是我们需要合并的,那么合并代码比较简单,直接上
all_data = pd.DataFrame()
for f in glob.glob("./sales*.xlsx"):
df = pd.read_excel(f)
all_data = all_data.append(df,ignore_index=True)
all_data.head()
Output:
account number name sku quantity unit price ext price date
0 383080 Will LLC B1-20000 7 33.69 235.83 2014-02-01 09:04:59
1 412290 Jerde-Hilpert S1-27722 11 21.12 232.32 2014-02-01 11:51:46
2 412290 Jerde-Hilpert B1-86481 3 35.99 107.97 2014-02-01 17:24:32
3 412290 Jerde-Hilpert B1-20000 23 78.90 1814.70 2014-02-01 19:56:48
4 672390 Kuhn-Gusikowski S1-06532 48 55.82 2679.36 2014-02-02 03:45:20
接下来还需要把数据中的时间转化为 pandas 的时间对象,一行代码
all_data['date'] = pd.to_datetime(all_data['date'])
然后我们来看需要 merge 的文件
status = pd.read_excel("./customer-status.xlsx")
status
Output:
account number name status
0 740150 Barton LLC gold
1 714466 Trantow-Barrows silver
2 218895 Kulas Inc bronze
3 307599 Kassulke, Ondricka and Metz bronze
4 412290 Jerde-Hilpert bronze
5 729833 Koepp Ltd silver
6 146832 Kiehn-Spinka silver
7 688981 Keeling LLC silver
接下来我们直接使用 pandas 的 merge 方法进行关联
all_data_st = pd.merge(all_data, status, how='left')
all_data_st.head()
Output:
account number name sku quantity unit price ext price date status
0 383080 Will LLC B1-20000 7 33.69 235.83 2014-02-01 09:04:59 NaN
1 412290 Jerde-Hilpert S1-27722 11 21.12 232.32 2014-02-01 11:51:46 bronze
2 412290 Jerde-Hilpert B1-86481 3 35.99 107.97 2014-02-01 17:24:32 bronze
3 412290 Jerde-Hilpert B1-20000 23 78.90 1814.70 2014-02-01 19:56:48 bronze
4 672390 Kuhn-Gusikowski S1-06532 48 55.82 2679.36 2014-02-02 03:45:20 silver
可以看到两组数据关联成功了,但是对于某些账号 ,比如 737550 是没有 status 的
all_data_st[all_data_st["account number"]==737550].head()
Output:
account number name sku quantity unit price ext price date status
15 737550 Fritsch, Russel and Anderson S1-47412 40 51.01 2040.40 2014-02-05 01:20:40 NaN
25 737550 Fritsch, Russel and Anderson S1-06532 34 18.69 635.46 2014-02-07 09:22:02 NaN
66 737550 Fritsch, Russel and Anderson S1-27722 15 70.23 1053.45 2014-02-16 18:24:42 NaN
78 737550 Fritsch, Russel and Anderson S2-34077 26 93.35 2427.10 2014-02-20 18:45:43 NaN
80 737550 Fritsch, Russel and Anderson S1-93683 31 10.52 326.12 2014-02-21 13:55:45 NaN
对于这种数据,我们通过 fillna 函数处理下
all_data_st['status'].fillna('bronze',inplace=True)
all_data_st.head()
Output:
account number name sku quantity unit price ext price date status
0 383080 Will LLC B1-20000 7 33.69 235.83 2014-02-01 09:04:59 bronze
1 412290 Jerde-Hilpert S1-27722 11 21.12 232.32 2014-02-01 11:51:46 bronze
2 412290 Jerde-Hilpert B1-86481 3 35.99 107.97 2014-02-01 17:24:32 bronze
3 412290 Jerde-Hilpert B1-20000 23 78.90 1814.70 2014-02-01 19:56:48 bronze
4 672390 Kuhn-Gusikowski S1-06532 48 55.82 2679.36 2014-02-02 03:45:20 silver
现在所有的数据都是完整的了,我们可以进行最后的报告数据处理了
all_data_st["status"] = all_data_st["status"].astype("category")
all_data_st["status"].cat.set_categories(["gold", "silver", "bronze"], inplace=True)
result = all_data_st.groupby(["status"])["unit price"].agg([np.mean])
result
Output:
mean
status
gold 53.723889
silver 57.272714
bronze 57.371163
最终的报告内容很简单,根据 status 分组,然后获取自动 unit price 的平均值
好了,数据处理完成,我们来编写 GUI 页面吧,这次我们使用的是 wxPython 库
pip install wxpython
pip install gooey
接下来创建一个 parse_args 函数来获取必要的输入信息
- 数据目录
- 输出目录
- 账户状态文件
from argparse import ArgumentParser
parser = ArgumentParser(description='Create Quarterly Marketing Report')
parser.add_argument('data_directory',
action='store',
help="Source directory that contains Excel files")
parser.add_argument('output_directory',
action='store',
help="Output directory to save summary report")
parser.add_argument('cust_file',
action='store',
help='Customer Account Status File')
parser.add_argument('-d', help='Start date to include')
args = parser.parse_args()
当然我们很多时候,目录配置一次,下次就不再改变了,所以我们设置一个 json 文件来保存目录的配置信息
from argparse import ArgumentParser
def parse_args():
""" Use ArgParser to build up the arguments we will use in our script
Save the arguments in a default json file so that we can retrieve them
every time we run the script.
"""
stored_args = {}
# get the script name without the extension & use it to build up
# the json filename
script_name = os.path.splitext(os.path.basename(__file__))[0]
args_file = "{}-args.json".format(script_name)
# Read in the prior arguments as a dictionary
if os.path.isfile(args_file):
with open(args_file) as data_file:
stored_args = json.load(data_file)
parser = ArgumentParser(description='Create Quarterly Marketing Report')
parser.add_argument('data_directory',
action='store',
default=stored_args.get('data_directory'),
help="Source directory that contains Excel files")
parser.add_argument('output_directory',
action='store',
default=stored_args.get('output_directory'),
help="Output directory to save summary report")
parser.add_argument('cust_file',
action='store',
default=stored_args.get('cust_file'),
help='Customer Account Status File')
parser.add_argument('-d', help='Start date to include',
default=stored_args.get('d'))
args = parser.parse_args()
# Store the values of the arguments so we have them next time we run
with open(args_file, 'w') as data_file:
# Using vars(args) returns the data as a dictionary
json.dump(vars(args), data_file)
return args
这样,我们就可以通过命令行来获取到相关需要传入的信息了
if __name__ == '__main__':
conf = parse_args()
print("Reading sales files")
sales_df = combine_files(conf.data_directory)
print("Reading customer data and combining with sales")
customer_status_sales = add_customer_status(sales_df, conf.cust_file)
print("Saving sales and customer summary data")
save_results(customer_status_sales, conf.output_directory)
print("Done")
接下来构建 GUI 页面
from gooey import Gooey
@Gooey(program_name="Create Quarterly Marketing Report")
def parse_args():
""" Rest of program below
"""
...
Gooey 可以非常方便的构建页面,这样我们的一个简单 GUI 页面就完成了
接下来我们做些优化,比如替换控件来支持文件路径选择等
parser.add_argument('data_directory',
action='store',
default=stored_args.get('data_directory'),
widget='DirChooser',
help="Source directory that contains Excel files")
这样,我们的小工具也就完成了,我们来看下效果
生成的文件就在指定的目录下哦,完成!
相关文章
- 图像处理工具Python扩展库,你了解吗?
- 十个常用的损失函数解释以及Python代码实现
- 30 个数据科学工作中必备的 Python 包
- 如何在 Windows 上安装 Python
- 几行 Python 代码就可以提取数百个时间序列特征
- 使用Python快速搭建接口自动化测试脚本实战总结
- 哪种编程语言最适合开发网页抓取工具?
- 不要在 Python 中使用循环,这些方法其实更棒!
- 震惊!用Python探索《红楼梦》的人物关系!
- 如何最简单、通俗地理解Python模块?
- 酷炫,Python实现交通数据可视化!
- 为什么急于寻找Python的替代者?
- 30 个数据工程必备的Python 包
- 去字节面试被面这题能答上来吗?谈谈你对时间轮的理解?
- 火山引擎在行为分析场景下的 ClickHouse JOIN 优化
- 用Python爬取了某宝1166家月饼数据进行可视化分析,终于找到最好吃的月饼~
- 在 Linux 上试试这个基于 Python 的文件管理器
- Python列表解析式到底该怎么用?
- 如何快速把你的 Python 代码变为 API
- 十个Python初学者常犯的错误