在Python中将describe()和文本标签的输出写入同一Excel工作表 [英] Writing output of describe() and a text label to same Excel sheet in Python

问题描述

这是此处相关问题的延续:

This is a continuation of a related question here: Creating a blank worksheet and adding text to that new worksheet using Python, ExcelWriter and openpyxl

我正在尝试使用带有openpyxl引擎的ExcelWriter将df.describe()的输出写入Excel中的特定单元格，然后将描述性标签添加到上方的单元格中.我发现各种方法都会产生一个重复的标签，并附加"_1".

I am trying to write the output of a df.describe() to an specific cell in Excel using ExcelWriter with the openpyxl engine and then add a descriptive label to the cell above. I am finding that various methods produce a duplicate tab with "_1" appended.

我的代码是这样的:

    with pd.ExcelWriter(f"data validation for {FNum}_{FName}.xlsx",engine='openpyxl') as writer:
        DWoldfiltered.to_excel(writer,sheet_name="Previous_DW_load")
        DWfiltered.to_excel(writer,sheet_name="Latest_DW_load")

        book = writer.book
        sheet = book.create_sheet("Summary_Data")
        sheet.cell(1,1).value = "Latest Load"
        sheet.cell(2,1).value = DWfiltered.describe()
    writer.save()

但是，当我执行它时，我收到错误消息

However, when I execute it I get the error message

"raise Value Error("Cannot convert {0!r} to excel".to format(value))

我正在使用的数据在前三列中用NaN表示后续描述性值，值为0，用np.nan替换为0，然后dropna不会更改错误消息.

The data I'm working with has 0 in the first three columns with NaN for subsequent descriptive values, replace 0 with np.nan and then dropna doesn't change the error message.

我要粘贴的数据是基于describe()函数的数据框，如下所示:

The data I'm trying to paste is a data frame based on the describe() function and looks like this:

         Col 1    Col 2    Col 3       col 4   Col 5
count        0        0        0         119    119
mean       NaN      NaN      NaN    24229.78  66645  
std        NaN      NaN      NaN       27877  74617
min        NaN      NaN      NaN        1080    457

推荐答案

我认为您的错误与0无关.

I don't think your error is related to 0.

我将改为执行类似的操作.

I will do something like this instead.

with pd.ExcelWriter(f"data validation for {FNum}_{FName}.xlsx",engine='openpyxl', mode='a') as writer:

    DWoldfiltered.to_excel(writer,sheet_name="Previous_DW_load") 
    DWfiltered.to_excel(writer,sheet_name="Latest_DW_load") 

    summary = pd.DataFrame({"Latest Load" : []})
    summary.to_excel(writer, sheet_name="Summary_Data")    
    describe.to_excel(writer, sheet_name ="Summary_Data", startrow=2, startcol=1)

writer.save()

这篇关于在Python中将describe()和文本标签的输出写入同一Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！