Python Pandas无法写入Excel文件问题求助

问题详情 #

我编写的Python代码可成功将数据写入grades.txt文件，但无法写入Excel表格。此前代码可正常运行，未做任何修改后突然失效，已查阅最新文档未发现语法问题，恳请提供解决建议。

相关代码 #

# import required libraries
import pandas as pd
import os

# get current working directory
cwd = os.getcwd()

# get user input for number of kids and maximum marks
number_of_inputs = int(input("Enter the number of kids to input: "))
max_marks = int(input("Enter the maximum marks: "))

# create an empty DataFrame to hold all the kids' data
df = pd.DataFrame(columns=['Name', 'Mark', 'Max Marks', 'Grade', 'Percentage'])

# define main function to get user input, calculate grades, and append data to DataFrame
def main():

    # access the global DataFrame variable
    global df

    # get input from user
    name = input("Enter the student's name: ")
    mark = int(input("Enter the student's mark: "))
    print()

    # calculate grade and percentage
    percentage = (mark / max_marks) * 100
    percentage = round(percentage, 2)

    # calculate grade boundaries via percentage
    fail_grade_boundaries = (max_marks * 0.5)  # 50%
    fail_grade_boundaries = round(fail_grade_boundaries)
    pass_grade_boundaries = (max_marks * 0.7)  # 70%
    pass_grade_boundaries = round(pass_grade_boundaries)
    merit_grade_boundaries = (max_marks * 0.9)  # 90%
    merit_grade_boundaries = round(merit_grade_boundaries)

    # determine the grade based on the mark
    if mark < fail_grade_boundaries:
        grade = "Fail"
    elif mark >= fail_grade_boundaries and mark < pass_grade_boundaries:
        grade = "Pass"
    elif mark >= pass_grade_boundaries and mark < merit_grade_boundaries:
        grade = "Merit"
    elif mark >= merit_grade_boundaries:
        grade = "Distinction"

    # append the new data to the existing DataFrame
    new_data = pd.DataFrame({"Name": [name],
                             "Mark": [mark],
                             "Max Marks": [max_marks],
                             "Grade": [grade],
                             "Percentage": [percentage]})

    df = pd.concat([df, new_data], ignore_index=True)

    # writes the data to a text file for backup and checking correct input is being logged
    with open("grades.txt", "a", 'encoding=utf-8') as txt_file:
        txt_file.write(f"{name}, {mark}, {max_marks}, {grade}, {percentage}\n")

# checks if this is the main prograam and run the main function for the number of kids specified
if __name__ == "__main__":
    for current_kid in range(number_of_inputs):
        main()

    #write the complete DataFrame to an Excel file named output
    with pd.ExcelWriter('output.xlsx') as writer:
        df.to_excel(writer, sheet_name='Sheet1', index=False)

运行时警告与错误 #

FutureWarning提示（不影响程序继续运行） #

Enter the number of kids to input: 2
Enter the maximum marks: 10
Enter the student's name: guy
Enter the student's mark: 6

/workspaces/big-schoolwork-repo/algorithmic thinkng/grade setter/main.py:55: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.
  df = pd.concat([df, new_data], ignore_index=True)
Enter the student's name: 

致命错误（输入完成后触发） #

Enter the number of kids to input: 2
Enter the maximum marks: 10
Enter the student's name: guy
Enter the student's mark: 6

/workspaces/big-schoolwork-repo/algorithmic thinkng/grade setter/main.py:55: FutureWarning: The behavior of DataFrame concatenation with empty or all-NA entries is deprecated. In a future version, this will no longer exclude empty or all-NA columns when determining the result dtypes. To retain the old behavior, exclude the relevant entries before the concat operation.
  df = pd.concat([df, new_data], ignore_index=True)
Enter the student's name: test
Enter the student's mark: 9

Traceback (most recent call last):
  File "/workspaces/big-schoolwork-repo/algorithmic thinkng/grade setter/main.py", line 67, in <module>
    with pd.ExcelWriter('output.xlsx') as writer:
  File "/home/codespace/.local/lib/python3.10/site-packages/pandas/io/excel/_openpyxl.py", line 57, in __init__
    from openpyxl.workbook import Workbook
ModuleNotFoundError: No module named 'openpyxl'

解决建议 #

1. 解决Excel写入失败的致命错误 #

ModuleNotFoundError: No module named 'openpyxl' 是因为pandas写入xlsx格式文件需要依赖openpyxl库，直接通过pip安装即可：

pip install openpyxl

如果之前安装过但突然失效，大概率是Python环境切换（比如虚拟环境变更）导致的，重新安装即可恢复。

2. 消除FutureWarning提示 #

警告来自于初始创建的空DataFrame与新数据concat时的行为变更。可以修改数据追加逻辑，避免空DataFrame的concat操作：
将原代码中追加数据的部分：

df = pd.concat([df, new_data], ignore_index=True)

替换为：

if df.empty:
    df = new_data
else:
    df = pd.concat([df, new_data], ignore_index=True)

或者更高效的方式：初始化时不用空DataFrame，改用列表收集数据，最后再转换为DataFrame：

# 初始化改为列表
data_list = []

# 在main函数中追加数据到列表
data_list.append({
    "Name": name,
    "Mark": mark,
    "Max Marks": max_marks,
    "Grade": grade,
    "Percentage": percentage
})

# 最后转换为DataFrame
df = pd.DataFrame(data_list)

两种方式都能消除该警告。

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