如何合并匹配指定邮箱的DataFrame并生成单个PDF报告？

我有一份包含指定邮箱列表的users.csv文件，以及一份包含业务数据的report.csv文件，希望生成仅包含与users.csv中邮箱匹配数据的PDF文件。

users.csv内容 #

users
victor.uriel@domain.com
uriel.victor@domain.com

report.csv内容 #

Manager1      Location    User         Email                        Name          Notes 
Man           loc1        vicuri       victor.uriel@domain.com      XXKKYY        Blah Blah
Man           loc2        vicuri       victor.uriel@domain.com      XXKKYY        Blah Blah
Man           loc1        someUsr      non.existent@domain.com      XXYYkk        Blah Blah
Man           loc1        someUsr      non.existent@domain.com      XXYYkk        Blah Blah
Man           loc1        someUsr      non.existent@domain.com      XXYYkk        Blah Blah
Man           loc1        urivic       uriel.victor@domain.com      YYKKXX        Blah Blah
Man           loc2        urivic       uriel.victor@domain.com      YYKKXX        Blah Blah
Man           loc3        urivic       uriel.victor@domain.com      YYKKXX        Blah Blah
Man           loc1        someUsr      non.existent@domain.com      XXYYkk        Blah Blah
Man           loc1        someUsr      non.existent@domain.com      XXYYkk        Blah Blah
Man           loc1        someUsr      non.existent@domain.com      XXYYkk        Blah Blah

期望输出 #

Location    User         Email                        Name          Notes 
loc1        vicuri       victor.uriel@domain.com      XXKKYY        Blah Blah blah
loc2        vicuri       victor.uriel@domain.com      XXKKYY        Blah Blah blah
loc1        urivic       uriel.victor@domain.com      YYKKXX        Blah Blah blah
loc2        urivic       uriel.victor@domain.com      YYKKXX        Blah Blah blah
loc3        urivic       uriel.victor@domain.com      YYKKXX        Blah Blah blah

目前我能遍历users.csv生成多个DataFrame并各自生成PDF，但希望合并所有匹配数据为一个DataFrame，只生成一份PDF。现有代码如下：

import pandas as pd
from fpdf import FPDF
import csv


def output_df_to_pdf(df,mwc):
    pdf_w= 420
    table_cell_width = 25
    table_cell_height = 8
    path= 'reports\\'
    

    loc_w = mwc['Location_w'] 
    use_w = mwc['user_w'] 
    ema_w = mwc['email_w'] 
    nam_w = mwc['name_w']
    not_w = mwc['notes_w']

    pdf = FPDF('L','mm','A3')
    pdf.add_page()
    pdf.set_font('Arial', 'B', 20)
    # A cell is a rectangular area, possibly framed, which contains some text
    # Set the width and height of cell
    pdf.cell(20,10,'Report')
    pdf.image('..\common\Logo.jpg',x= 420 - 100, y = 13, w =80, h=table_cell_height+5)
    pdf.ln(20)
    # Select a font as Arial, bold, 8
    pdf.set_font('Arial', 'B', 8)
    pdf.set_fill_color(76,104,162)
    pdf.set_draw_color(224,224,224)
    pdf.set_text_color(255,255,255)

    # Creates Header  with main DF  width 
    cols = df.columns
    col_vals = {}
    for col in cols:
        col_vals[col] = col

    # Set  heade col with  correct  with 

    pdf.cell(loc_w +10, table_cell_height, col_vals['Location'], align='L', border=1, fill=1)
    pdf.cell(use_w +10, table_cell_height, col_vals['User'], align='L', border=1, fill=1)
    pdf.cell(ema_w +17, table_cell_height, col_vals['Email'], align='L', border=1, fill=1)
    pdf.cell(nam_w +10, table_cell_height, col_vals['Name'], align='L', border=1, fill=1)
    pdf.cell(not_w - 85, table_cell_height, col_vals['Notes'], align='L', border=1, fill=1)

    pdf.ln(table_cell_height)
    pdf.set_font('Arial', '', 6)

    for row in df.itertuples():
        val = {}
        for coli in cols:
            value = str(getattr(row, coli))
            val[coli]= value

        pdf.set_text_color(0,0,0)
        pdf.set_draw_color(224,224,224)
        # pdf.multi_cell(not_w - 80, table_cell_height, val['Notes'], align='L', border=1)

        if len(val['Notes']) > 230:

            pdf.cell(loc_w +10, table_cell_height *2, val['Location'], align='L', border=1)
            pdf.cell(use_w +10, table_cell_height *2, val['User'], align='L', border=1)
            pdf.cell(ema_w +17, table_cell_height *2, val['Email'], align='L', border=1)
            pdf.cell(nam_w +10, table_cell_height *2, val['Name'], align='L', border=1)
            pdf.multi_cell(not_w - 85,table_cell_height,val['Notes'],align='L', border=1)
            
        else:

            pdf.cell(loc_w +10, table_cell_height, val['Location'], align='L', border=1)
            pdf.cell(use_w +10, table_cell_height, val['User'], align='L', border=1)
            pdf.cell(ema_w +17, table_cell_height, val['Email'], align='L', border=1)
            pdf.cell(nam_w +10, table_cell_height, val['Name'], align='L', border=1)
            pdf.cell(not_w - 85, table_cell_height, val['Notes'], align='L', border=1)
            pdf.ln(table_cell_height)

    pdf.output(path + '2022 Awesome Report.pdf', 'F')


#start of program 
mails_df = pd.read_csv('users.csv')
# 注意：users.csv的列名是users，不是Email
mlist = mails_df['users'].dropna().unique()
report_df = pd.read_csv('report.csv')
mdata = report_df


# get  max length  of  column  
mwc = { 
    'Location_w' : report_df['Location'].str.len().max(),
    'user_w' : report_df['User'].str.len().max(),
    'email_w' : report_df['Email'].str.len().max(),
    'name_w' : report_df['Name'].str.len().max(),
    'notes_w' : report_df['Notes'].str.len().max()
}

# 初始化空DataFrame用于合并所有匹配数据
merged_df = pd.DataFrame()

for data in mlist:
    res = mdata.loc[mdata['Email'].str.lower() == data.lower()].dropna()
    if not res.empty:
        df_segment = res.drop('Manager1', axis=1)
        # 合并到主DataFrame
        merged_df = pd.concat([merged_df, df_segment], ignore_index=True)

# 生成合并后的PDF
if not merged_df.empty:
    output_df_to_pdf(merged_df, mwc)

关键修改说明 #

1. 修复列名错误：users.csv的列名是users，原代码中mails_df['Email']会报错，改为mails_df['users']。
2. 初始化合并DataFrame：创建空的merged_df，循环中用pd.concat将每个匹配的片段合并进去，避免覆盖。
3. 统一邮箱大小写匹配：比较邮箱时统一转小写，避免大小写不匹配导致的数据遗漏。
4. 空DataFrame判断：生成PDF前检查merged_df是否为空，防止无匹配数据时报错。
5. 修复PDF表头错误：原代码中表头写的col_vals['Title']，实际列名是Location，已修正。

内容的提问来源于stack exchange，提问作者victorR