pandas操作excel表格

点击查看代码

import pandas as pd
from tqdm import tqdm

file1 = r"files/1.xls"
file2 = r"files/2.xls"

df_File1 = pd.read_excel(file1, sheet_name='keyWord', keep_default_na=False)
key_column = df_File1['key'].fillna('').astype(str).tolist()

df_File2 = pd.read_excel(file2, sheet_name=0, keep_default_na=False)

progress_bar = tqdm(total=len(key_column), desc='Processing')

# result_df = pd.DataFrame(columns=df_File1.columns)  # 创建一个与files2相同列的df
rowNum = 0  # 初始化行数

for key in key_column:
    matched_lines = df_File2[df_File2['key'].str.contains(key)]  # 匹配含有指定列的关键字的一行

    names = ''
    for name in matched_lines['name']:
        names = names+name+'\n'

    if names != '':
        df_File1.values[rowNum][1] = names
    rowNum = rowNum + 1

    # result_df = pd.concat([result_df, matched_lines], ignore_index=True)  # 合并取出的行数据
    progress_bar.update(1)

# print(df_File1.values)
df_File1.to_excel(r"D:\security\web\python_tools\excelOperate\files\4.xls",index = False,header=False)
# result_df.to_excel(r"D:\security\web\python_tools\excelOperate\files\3.xls", index=False)  # 新建筛选出的数据
progress_bar.close()

# https://blog.csdn.net/qq_48078719/article/details/131946481
# https://blog.csdn.net/qq_40878316/article/details/131648258