用 Python 帮财务小妹合并 Excel，小妹二话不说直接。。。

财务小妹

萝卜哥，在不在

不在不在

萝卜

财务小妹

哈哈，不在咋还说话呢，帮忙看个问题吧

唔，额，啥

萝卜

财务小妹

几个Excel文件，我想合并下，有啥快捷方式呢

Ctrl + C和Ctrl + V啊

萝卜

财务小妹

不不不，我这个有点复杂，我来和你说

~~~~~

萝卜

财务小妹的需求

“

先合并几个内容类似的 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")

这样，我们的小工具也就完成了，我们来看下效果

生成的文件就在指定的目录下哦，完成！

财务小妹

吼吼，太帮忙啦，真香啊

嗯，废话不多说，奖励拿来！

萝卜

财务小妹

哥，别这么功利嘛，咱们细水长流

啥啥啥，还要细水长~

萝卜

财务小妹

先下班了，拜拜，明天见！

。。。

萝卜