编写Python Pandas DataFrame到Word文档

q35jwt9p  于 2023-10-14  发布在  Python
关注(0)|答案(4)|浏览(157)

我正在创建一个使用Pandas DataFrames的Python生成的报告。目前我使用的是DataFrame.to_string()方法。但是,这将作为字符串写入文件。有没有一种方法可以让我实现这一点,同时保持它作为一个表,所以我可以使用表格式化。
代码:

SEMorgkeys = client.domain_organic(url, database = "us", display_limit = 10, export_columns=["Ph,Pp,Pd,Nq,Cp,Ur,Tr"])
org_df = pd.DataFrame(SEMorgkeys)

f = open(name, 'w')
f.write("\nOrganic:\n")
f.write(org_df.to_string(index=False,justify="left"))
f.close()

当前打印输出(作为字符串):

CPC    Keyword                        Position Difference Previous Position Search Volume Traffic (%) Url                                               
75.92       small business factoring   0                   1                 210          11.69       https://www..com/small-business-f...
80.19              factoring company   0                   8                1600           5.72       https://www..com/factoring-vs-ban...
q43xntqr

q43xntqr1#

您可以使用python-docx库将表直接写入.docx文件。
如果你使用的是Conda或安装的Python使用的是Anaconda,你可以从命令行运行命令:

conda install python-docx --channel conda-forge

或者从命令行pip install:

pip install python-docx

安装完成后,我们可以使用它打开文件,添加表格,然后使用数据框数据填充表格的单元格文本。

import docx
import pandas as pd

# i am not sure how you are getting your data, but you said it is a
# pandas data frame
df = pd.DataFrame(data)

# open an existing document
doc = docx.Document('./test.docx')

# add a table to the end and create a reference variable
# extra row is so we can add the header row
t = doc.add_table(df.shape[0]+1, df.shape[1])

# add the header rows.
for j in range(df.shape[-1]):
    t.cell(0,j).text = df.columns[j]

# add the rest of the data frame
for i in range(df.shape[0]):
    for j in range(df.shape[-1]):
        t.cell(i+1,j).text = str(df.values[i,j])

# save the doc
doc.save('./test.docx')
piwo6bdm

piwo6bdm2#

def doctable(data, tabletitle, pathfile):
    from docx import Document
    from docx.shared import Pt, Mm
    import pandas as pd
    document = Document()
    section = document.sections[0]
    section.page_height = Mm(297)
    section.page_width = Mm(210)
    section.left_margin = Mm(20)
    section.right_margin = Mm(20)
    section.top_margin = Mm(20)
    section.bottom_margin = Mm(20)
    section.header_distance = Mm(12.7)
    section.footer_distance = Mm(12.7)
    data = pd.DataFrame(data) # My input data is in the 2D list form
    document.add_heading(tabletitle)
    table = document.add_table(rows=(data.shape[0]), cols=data.shape[1]) # First row are table headers!
    table.allow_autofit = True
    table.autofit = True
    for i, column in enumerate(data) :
        for row in range(data.shape[0]) :
            table.cell(row, i).text = str(data[column][row])
    document.save(pathfile)
    return 0
rhfm7lfc

rhfm7lfc3#

如果你喜欢,可以用这个来证明:

from docx import Document
import pandas as pd

def df_to_word(data: dict, report_name:str) -> docx.Document:
    assert type(data) == dict, 'data has to be dict'
    assert '.docx' in report_name, 'report_name has to be a .docx file'
    df = pd.DataFrame(data)
    doc = docx.Document()

    table = doc.add_table(df.shape[0]+1, df.shape[1])

    for j in range(df.shape[-1]):
        table.cell(0,j).text = df.columns[j]

    for i in range(df.shape[0]):
        for j in range(df.shape[-1]):
            table.cell(i+1,j).text = str(df.values[i,j])

    doc.save(f'./{report_name}')

data = {
  "calorierbes": [420, 380, 390],
  "duratierbn": [50, 40, 45],
  "durationverg": [50, 40, 45],
  "duratiorgern": [50, 40, 45],
  "calorieers": [420, 380, 390],
  "calorierbers": [420, 380, 390],
  "calorierbes": [420, 380, 390]
}
df_to_word(data, 'report_4.docx')
uujelgoq

uujelgoq4#

受上述答案的启发,我添加了一个能够包含索引的函数。

import docx
import pandas as pd
from pathlib import Path

def pd_table_to_word(df, save_to_path, include_index=False):
    if Path(save_to_path).exists():
        response = input("Document already exists and will be overwritten. Sure you want to overwrite this documents? Y/ N")
        if response.lower() not in ["y", "ye", "yes", "yeah"]:
            return "Aborted overwriting file."
    doc = docx.Document()
    # add a table to the end and create a reference variable
    # extra row is so we can add the header row
    
    n_rows, n_cols = df.shape[0], df.shape[1] +1
    if include_index:
        n_rows += 1
        
    t = doc.add_table(n_rows, n_cols)
    
    # add the header rows.
    for j in range(df.shape[-1]):
        if include_index:
            t.cell(0,j+1).text = df.columns[j]
        else:
            t.cell(0,j).text = df.columns[j]
    
    # add index names
    if include_index:
        t.cell(0, 0).text = df.index.name
        for i in range(df.shape[0]):
            t.cell(i+1, 0).text = df.index[i]

    # add the rest of the data frame
    for i in range(df.shape[0]):
        for j in range(df.shape[-1]):
            if include_index:
                t.cell(i+1, j+1).text = str(df.values[i,j])
            else:
                t.cell(i+1, j).text = str(df.values[i,j])
    doc.save(save_to_path)
    return f"Table saved to {save_to_path}"

相关问题