Python的excel数据匹配（以国泰安数据为例）

KT阿B

已于 2024-05-09 00:53:19 修改

阅读量2k

点赞数 29

文章标签： excel 数据库

于 2024-04-14 23:04:42 首次发布

本文链接：https://blog.csdn.net/YBY0010/article/details/137757112

版权

实现过程

引入必要的库：使用了tkinter创建GUI窗口，以及pandas进行数据处理。
文件选择和数据读取：弹出文件对话框，让用户选择要导入的两个Excel文件，并读取这两个文件的数据，分别存储在名为"表1"和"表2"的变量中。
数据清洗和处理：

删除了"表2"中公司名称中包含"ST"或"st"的行。
删除了"表2"中重复的前三列数据。这个操作是基于假设，如果123列（股票代码、公司名称、年份）有重复项出现，可能是因为下载了多个报表类型的数据。代码删除了这些重复项，只保留每组重复值的第一个。
注意：代码只会保留第一个出现的数据。且以第123列为判断标准，这意味着只要第123列的行重复，它保留的行可能是报表类型a，也可能是报表类型b。如果追求严谨，请点击取消，用excel手动筛选
将日期格式改为年份，并将年份放到第二列。
合并了第一列和第二列，并根据情况判断是否存在重复值。如果存在重复值，说明数据可能是季度数据，代码将按年份对数据进行求和，转换为年度数据。
数据合并：将处理后的"表2"与"表1"按照指定的合并键进行合并，合并结果存储在新的数据框中。
数据保存和输出：删除合并结果中的不必要列，并将最终结果保存为新的Excel文件。最后输出结果，整个匹配过程完成。

思路

需求分析

1，国泰安的数据有的只有季度数据，而我们需要的是年度数据，在excel中需要用到数据透视去筛选，分组求和。且数据时间尺度为10年-23年，重复性工作多。

2，将分表数据匹配到总表中需要耗费时间，4w行的数据多要1分钟运行时间，且每次输入lookup函数都要更换变量值，浪费时间，容易视觉疲劳导致出错。

怎么做

在代码里主要分为表1和表2，表1是总表，而表2是分表，第一阶段，该代码的目标就是先整理好表2的数据，然后第二阶段就是将表2的数据匹配到表1里，而后导出匹配好的合并表，在用这张合并表去充当表1，周而复始，从而达到多表匹配到一张表的效果。

使用注意事项

一，每次的使用都需要修改几个变量

二，有些功能需要单独使用，可以注释掉部分代码

三，公式求比如求对数需要自己用Exel表求一下

数据匹配代码展示

# 功能命名：More date in one;
# 功能描述：把表2匹配到表1
# 作者：yby
# 版本：2.0 第二阶段增加了类似lookup多条件匹配功能 2024/4/14
# 版本：3.0 修改了直接用merge函数多条件匹配，去掉了contact函数，修正了因对表2的第一列格式修改导致表1前面n行匹配不上的问题 2024/4/15
# 版本 4.0 增加了tkinter 窗口，检测合并表格导致的非正常数据；增加了st筛选功能 2024/4/17
# 4.1 增加通过tkinter来选择文件，无须复制文件地址 2024/4/18 18:00
# 4.1.1 增加了打印重复项，更好地排查错误 2024/4/18 22:00
# 4.1.2 通过将列名保存为变量,提高代码的可维护性和复用性;增加了修改文件名功能；增加日期转换双方案； 2024/4/19 20:00
# 5.0 此次更新支持将多个excel表匹配到目标表格，提高了匹配速度；修复了匹配时行数增加的问题（但仍不知问题所在，不过行数固定下来了）；批量封装代码，提高可维护性；2024/4/22 1：06
# 5.1 修改remove_st_companies函数使判断窗口更简洁以及统一  2024/4/25 0：38
# 5.2 让存放表2的文件夹按照文件名里所含数字顺序去匹配表1 2024/4/26 23:00
# 5.3 将在匹配过程里删除st公司，删除重复值，以及重复值检验的功能迁移到循环外，减少手动处理的过程；添加补0功能和删除含空值所在行 2024/5/7 1:24
# 5.4 每次都把表2的表头统一修改一下，匹配时只需保留三列 2024/5/8 20：58

import tkinter as tk
from tkinter import filedialog
from tkinter import messagebox
import pandas as pd
import os

def remove_st_companies(data_frame):
    mask = data_frame.astype(str).apply(lambda x: x.str.contains('ST|st', case=False)).any(axis=1)    # 创建一个掩码，用于标识整个数据帧中哪些行包含 "ST" 或 "st"
    print(mask)
    if mask.any():
        print("表2含有st或ST，删除包含st或ST的公司所在行。")

        root = tk.Tk()          # 使用Tkinter对话框获取用户输入
        root.withdraw()  # 隐藏Tkinter主窗口

        choice = tk.messagebox.askyesno("确认删除", "表2含有st或ST，是否执行删除st或ST代码？")  # 弹出消息框，询问用户是否继续

        if choice:
            data_frame = data_frame[~mask]              # 删除包含 "ST" 或 "st" 的行
            print('含st，已删除st公司')
        else:
            print("不执行该程序")
    else:
        print('已核实无st公司')
    return data_frame

def remove_nan_rows(data_frame):   # 删除空白值所在行
    if data_frame.isnull().values.any():
        print("存在空值行，删除空值行。")

        root = tk.Tk()         # 使用Tkinter对话框获取用户输入
        root.withdraw()  # 隐藏Tkinter主窗口

        choice = messagebox.askyesno("确认删除", "存在空值行，是否执行删除空值行代码？")          # 弹出消息框，询问用户是否继续

        if choice:
            print(data_frame[data_frame.isnull().any(axis=1)])              # 打印空值行

            data_frame = data_frame.dropna(axis=0, how='any')            # 删除空值行
            print("执行程序，删除空值行")
        else:
            print("不执行该程序")
    else:
        print("不存在空值行")
    return data_frame

def remove_duplicate_rows(data_frame, column_indices):
    if data_frame.iloc[:, column_indices].duplicated().any():
        print("存在重复行，删除重复行。")

        # 使用Tkinter对话框获取用户输入
        root = tk.Tk()
        root.withdraw()  # 隐藏Tkinter主窗口

        # 弹出消息框，询问用户是否继续
        choice = messagebox.askyesno("确认删除", "存在重复行，是否执行删除重复行代码？")

        if choice:
            # 打印重复行
            print(data_frame[data_frame.iloc[:, column_indices].duplicated()])

            # 删除重复行
            data_frame = data_frame.drop_duplicates(subset=data_frame.columns[column_indices])
            print("执行程序，删除重复行")
        else:
            print("不执行该程序")
    else:
        print("不存在重复行")
    return data_frame

def extract_number(filename):
    # 从文件名中提取数字部分
    return int(''.join(filter(str.isdigit, filename)))



# 创建Tkinter应用程序窗口 开始执行程序
root = tk.Tk()
root.withdraw()

file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx;*.xls")])  # 先读取表1 表1就是是被解释变量
# 获取文件夹中所有 Excel 文件的路径，并按文件名顺序排序
if file_path:
    表1 = pd.read_excel(file_path, header=0)
    表1.rename(columns={表1.columns[0]: 'code', 表1.columns[1]: 'year'}, inplace=True)
    file_base_name = os.path.basename(file_path)
    print('导入表1成功')
else:
    print("未选择文件。")
    exit()

# 输出当前处理的表1的部分数据
print('-------------------------------------------')
print("当前处理的表1数据：")
print(表1.head())
print('-------------------------------------------')

merged_df = 表1  # 初始化 merged_df 为表1,与这段循环末尾相呼应

numbercount = 0  # 给一个初始值0，第一次循环就是1

# 弹出文件夹对话框，用户选择文件夹
folder_selected = filedialog.askdirectory()

if not folder_selected:
    print("未选择文件夹。")
    exit()

print(f"已选择文件夹: {folder_selected}")

# 获取文件夹中所有 Excel 文件的路径，并按文件名中的数字顺序排序
excel_files = sorted([os.path.join(folder_selected, file) for file in os.listdir(folder_selected) if file.endswith(('.xlsx', '.xls'))], key=extract_number)

print(excel_files)
if not excel_files:
    print("文件夹中没有 Excel 文件。")
    exit()

# 处理每个Excel文件
for file_num, file_path in enumerate(excel_files, start=1):  # 读取表2,表2就是解释变量
    表2 = pd.read_excel(file_path, header=0)
    print(f'导入表{file_num + 1}成功')

    表2.rename(columns={表2.columns[0]: 'code', 表2.columns[1]: 'myear'}, inplace=True)

    required_data = 表2.iloc[:, 2]      # 指定第三列所有行

    try:
        表2['日期列'] = pd.to_datetime(表2['myear'], format='%Y')
    except Exception as e:
        print("年-月-日转换代码执行失败:", e)
        表2['日期列'] = pd.to_datetime(表2['myear'])
    except Exception as e:
        print("你确定表2符合code+year+匹配值格式吗？", e)

    表2['myear'] = 表2['日期列'].dt.year
    表2.drop(columns=['日期列'], inplace=True)
    年份列数据 = 表2['myear']
    表2.drop(columns=['myear'], inplace=True)

    表2.insert(1, 'year', 年份列数据)
    表2['第一列+第二列'] = 表2.iloc[:, 0].astype(str) + '-' + 表2.iloc[:, 1].astype(str)
    has_duplicates = 表2['第一列+第二列'].duplicated().any()

    if has_duplicates:                                                               # 这段是将季度数据加起来
        print('注意：第一列+第二列有重复，进行季度转换年度数据中......')
        表2['Yearsum'] = 表2.groupby(['code', 'year'])[required_data].transform('sum')
        表2.loc[表2.duplicated(subset=['code', 'year']), 'Yearsum'] = None
        表2 = 表2.dropna(subset=['Yearsum'])
        表2.drop(columns=['第一列+第二列'], inplace=True)
        表2.drop(columns=[required_data], inplace=True)
        print('表2的季度转年度数据已成功')
    else:
        表2.drop(columns=['第一列+第二列'], inplace=True)
        print("无须年度数据：")

    表1['key'] = 表1[表1.columns[0]].astype(str) + '-' + 表1[表1.columns[1]].astype(str)  # 创建新的列，内容为第一列和第二列合并后的结果

    表2['key'] = 表2[表2.columns[0]].astype(str) + '-' + 表2[表2.columns[1]].astype(str)  # 将表2的第一列和第二列合并为一个键，并转换为字符串类型

    merged_df = pd.merge(merged_df, 表2, on='key', how='left')      # 将表1和表2按照合并后的键进行合并，使用左连接保留表1的所有列
    merge_key_index = merged_df.columns.get_loc('key')
    columns_to_drop_index = list(range(merge_key_index, len(merged_df.columns) - 1))  # 因为只要表2的最后一列就可以了，所以删减到倒数第2列
    merged_df.drop(columns=merged_df.columns[columns_to_drop_index], inplace=True)

    # 输出合并后的数据
    print('-------------------------------------------')
    print("当前处理的表1数据和表2匹配后的结果：")
    print(merged_df.head())
    print('-------------------------------------------')

    print('-------------------------------------------')
    print("表1文件名:", file_base_name)

    numbercount += 1

    file_name = f"匹配成功{numbercount}_data.xlsx"

    merged_df.to_excel(file_name, index=False)
    print('匹配成功：', file_name)

    # 更新表1数据
    表1 = merged_df

root.destroy()

表1 = remove_st_companies(表1)                # 删除st，如果表2含st的行都删了
表1 = remove_duplicate_rows(表1, [0, 1, 2])         # 删除表123列重复行
表1 = remove_nan_rows(表1)

表1.iloc[:, 0] = 表1.iloc[:, 0].astype(str).str.zfill(6)           # 给第一列补0

fname = "最终数据.xlsx"
表1.to_excel(fname, index=False)
print('最终匹配成功：', fname)

该代码大部分由ai完成，本人参与修改

数据检查

检查数据的思路是主要就是看最终通过代码统计总表每列有多少空值，哪列空值多，哪列就数据有问题。以及统计删除空值后还剩多少行数据，看总体数据是否充足，达到数据的合理性要求。

import pandas as pd

# 读取 Excel 表格
df = pd.read_excel("D:\桌面文件\总表2.0.xlsx")

# 统计表格的行数
total_rows = len(df)

# 统计每列的空值数量
null_counts = df.isnull().sum()

# 计算删除空值后的行数
rows_without_null = df.dropna().shape[0]

# 输出结果
print("总行数:", total_rows)
print("每列空值数量:")
print(null_counts)
print("删除空值后剩余行数:", rows_without_null)

通过这行代码统计总表返回的数据：

图中的数字为每行的空值数量，前面的文字则是每列的表头名，不难看出画横线的几个数据都是1w甚至2w的空值，而显然这几个数据是有问题的，导致删除后只剩下7000条数据了。