财务小妹的需求
先合并几个内容类似的 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")
这样，我们的小工具也就完成了，我们来看下效果
生成的文件就在指定的目录下哦，完成！
