前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >用 Python 合并 Excel。

用 Python 合并 Excel。

作者头像
周萝卜
发布2021-11-16 15:41:03
4370
发布2021-11-16 15:41:03
举报
文章被收录于专栏:萝卜大杂烩萝卜大杂烩

先合并几个内容类似的 Excel,在通过一个字段把另一个Excel文件的内容添加进来,然后自动进行 groupby 处理,最好还是要一个 GUI 页面,TM 要求真多!

选择需要批量处理的 Excel 文件所在的目录和保存生成的新 Excel 的目录,以及那个需要 merge 进来的文件目录,点击 Start 按钮就可以自动完成

我们还是先看下测试数据

代码语言:javascript
复制
import pandas as pd
import numpy as np
import glob
glob.glob("./sales*.xlsx")

Output:

代码语言:javascript
复制
['.\\sales-feb-2014.xlsx', '.\\sales-jan-2014.xlsx', '.\\sales-mar-2014.xlsx']

上面的三个文件,就是我们需要合并的,那么合并代码比较简单,直接上

代码语言:javascript
复制
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:

代码语言:javascript
复制
  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 的时间对象,一行代码

代码语言:javascript
复制
all_data['date'] = pd.to_datetime(all_data['date'])

然后我们来看需要 merge 的文件

代码语言:javascript
复制
status = pd.read_excel("./customer-status.xlsx")
status

Output:

代码语言:javascript
复制
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 方法进行关联

代码语言:javascript
复制
all_data_st = pd.merge(all_data, status, how='left')
all_data_st.head()

Output:

代码语言:javascript
复制
  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 的

代码语言:javascript
复制
all_data_st[all_data_st["account number"]==737550].head()

Output:

代码语言:javascript
复制
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 函数处理下

代码语言:javascript
复制
all_data_st['status'].fillna('bronze',inplace=True)
all_data_st.head()

Output:

代码语言:javascript
复制
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

现在所有的数据都是完整的了,我们可以进行最后的报告数据处理了

代码语言:javascript
复制
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:

代码语言:javascript
复制
  mean
status  
gold  53.723889
silver  57.272714
bronze  57.371163

最终的报告内容很简单,根据 status 分组,然后获取自动 unit price 的平均值

好了,数据处理完成,我们来编写 GUI 页面吧,这次我们使用的是 wxPython 库

代码语言:javascript
复制
pip install wxpython
pip install gooey

接下来创建一个 parse_args 函数来获取必要的输入信息

  • 数据目录
  • 输出目录
  • 账户状态文件
代码语言:javascript
复制
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 文件来保存目录的配置信息

代码语言:javascript
复制
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

这样,我们就可以通过命令行来获取到相关需要传入的信息了

代码语言:javascript
复制
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 页面

代码语言:javascript
复制
from gooey import Gooey


@Gooey(program_name="Create Quarterly Marketing Report")
def parse_args():
    """ Rest of program below
    """
    ...

Gooey 可以非常方便的构建页面,这样我们的一个简单 GUI 页面就完成了

接下来我们做些优化,比如替换控件来支持文件路径选择等

代码语言:javascript
复制
parser.add_argument('data_directory',
                    action='store',
                    default=stored_args.get('data_directory'),
                    widget='DirChooser',
                    help="Source directory that contains Excel files")

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

生成的文件就在指定的目录下哦,完成!

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-11-09,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 萝卜大杂烩 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档