Python在多个Excel中搜索对应的数据并粘贴到新的Excel工作表中

问题描述

我的文件夹中有一些excel文件，每个文件中已经有一个工作表称为服务"

i have some excel files in a folder, there's already a worksheet call "service" in each file

Notes_111.xlsx
Notes_222.xlsx
Notes_888.xlsx

工作流程:我想打开每个.xlsx文件，例如Notes_111.xlsx，然后添加一个新工作表，名称为"code_city"，然后基于文件名 111 ，仅从主数据框中提取 code = 111 数据并粘贴到新工作表中.然后保存.

Workflow : I want to open each .xlsx file, for example, Notes_111.xlsx, then add a new worksheet, name as "code_city", then based on file's name 111, extract only the code = 111 data from the master dataframe and paste to the new worksheet. then save.

在另一个Excel文件中采样主数据框

Sample master dataframe in another excel file

    code           city
0    111            NY
1    111            CA
2    222            NJ
3    888            WE
4    888            TL

我不知道如何在循环中编写逻辑来搜索对应的数据

i don't know how to write a logic within a loop to search corresponding data

import pandas as pd
import numpy as np
import glob
from openpyxl import load_workbook

for f in glob.glob(path + "Notes_*.xlsx"):
   wb = load_workbook(f)
   ws = wb.create_sheet('code_city')
   ws['A1'] = 'how to search corresponding data and paste here???'
   wb.save(f)

请帮助.

推荐答案

使用pandas更加容易操作，我相信它仍然会在幕后使用openpyxl.

Use pandas its much easier to manipulate, I believe it uses openpyxl under the hood anyway.

import glob
import pandas as pd
import os


for f in glob.glob('Notes_*.xlsx'):
    dda = re.findall('\d+', f) #matches digits in the filename

    df_each = pd.read_excel(f) # have to save the data first, coz ExcelWriter will clear up and create a new excel, so, you paste the saved data back to new sheet
    df_1_dda = df_master[df_master['code'] == int(dda[0])] #select only those records with code in the filename

    writer = pd.ExcelWriter(f)
    df_each.to_excel(writer, 'service', index = False) #  paste the saved data back to new sheet
    df_1_dda.to_excel(writer, 'code_city', index = False)
    writer.close()

希望有帮助！

使用python 3.6.4 Anaconda-32位

using python 3.6.4 Anaconda - 32-bit

这篇关于Python在多个Excel中搜索对应的数据并粘贴到新的Excel工作表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！