用 Python 合并 Excel。

周萝卜

发布于 2021-11-16 15:41:03

4400

发布于 2021-11-16 15:41:03

文章被收录于专栏：萝卜大杂烩萝卜大杂烩

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

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