如何使用Pandas每日向已存在的Excel文件追加数据？

First, let’s clarify why your initial append attempt failed: Excel files are binary formats, not plain text, so using open() with a+ mode and trying to append a DataFrame directly won’t work—you need a library that understands Excel’s structure, like pandas paired with the openpyxl engine.

Option 1: Two Separate Scripts (As You Proposed) #

Script 1: Initial File Creation (Run Once) #

This script generates your first Excel file with headers. Run it once, then use the second script daily afterward.

import pandas as pd
import time
import os

# Your existing data preparation code here
# (Define date_for_each_sheet, type_of_property, sqm_area, locations, price_per_m2, prices, publisher, link_for_offer)

dataframe_for_excel_file_structure = {
    'Date': date_for_each_sheet,
    'Type of property': pd.Series(type_of_property),
    'Area': pd.Series(sqm_area),
    'Location': pd.Series(locations),
    'Price per m2': pd.Series(price_per_m2),
    'Total Price': pd.Series(prices),
    'Published by': pd.Series(publisher),
    'link': pd.Series(link_for_offer)
}
dataframe_for_excel = pd.DataFrame(dataframe_for_excel_file_structure)

# Generate filename and save initial file with headers
filename_for_sqm = time.strftime("%Y%m%d")
# Optional: Prevent accidental overwrites
if not os.path.exists(f"{filename_for_sqm}.xlsx"):
    dataframe_for_excel.to_excel(f"{filename_for_sqm}.xlsx", index=False)
    print(f"Created initial file: {filename_for_sqm}.xlsx")
else:
    print("Initial file already exists—skipping creation.")

Script 2: Daily Data Append (Run After First Day) #

This script appends new data to the existing Excel file without re-writing headers. First, install the required dependency: run pip install openpyxl in your terminal.

import pandas as pd
import os

# Your daily data preparation code here
# (Same structure: prepare date_for_each_sheet, type_of_property, etc.)

dataframe_for_excel_file_structure = {
    'Date': date_for_each_sheet,
    'Type of property': pd.Series(type_of_property),
    'Area': pd.Series(sqm_area),
    'Location': pd.Series(locations),
    'Price per m2': pd.Series(price_per_m2),
    'Total Price': pd.Series(prices),
    'Published by': pd.Series(publisher),
    'link': pd.Series(link_for_offer)
}
dataframe_for_excel = pd.DataFrame(dataframe_for_excel_file_structure)

# Replace with your initial file's name (e.g., "20211109.xlsx")
existing_filename = "20211109.xlsx"

if os.path.exists(existing_filename):
    # Append data to the end of the existing sheet
    with pd.ExcelWriter(existing_filename, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
        dataframe_for_excel.to_excel(writer, index=False, header=False)
    print(f"Successfully appended data to {existing_filename}")
else:
    print(f"Error: {existing_filename} doesn't exist. Run the initial creation script first.")

Option 2: Single Script That Handles Both Cases #

If you prefer a single script that automatically checks for the file and either creates it (with headers) or appends to it (without headers), use this:

import pandas as pd
import os

# Your data preparation code here
# date_for_each_sheet, type_of_property, sqm_area, locations, price_per_m2, prices, publisher, link_for_offer

dataframe_for_excel_file_structure = {
    'Date': date_for_each_sheet,
    'Type of property': pd.Series(type_of_property),
    'Area': pd.Series(sqm_area),
    'Location': pd.Series(locations),
    'Price per m2': pd.Series(price_per_m2),
    'Total Price': pd.Series(prices),
    'Published by': pd.Series(publisher),
    'link': pd.Series(link_for_offer)
}
dataframe_for_excel = pd.DataFrame(dataframe_for_excel_file_structure)

# Use a fixed filename or keep your date-based one
filename = "property_data.xlsx"

if os.path.exists(filename):
    # Append mode: skip headers
    with pd.ExcelWriter(filename, mode='a', engine='openpyxl', if_sheet_exists='overlay') as writer:
        dataframe_for_excel.to_excel(writer, index=False, header=False)
    print(f"Appended new data to {filename}")
else:
    # Initial creation: include headers
    dataframe_for_excel.to_excel(filename, index=False)
    print(f"Created new file {filename} with initial data")

Key Notes: #

• Dependency: openpyxl is mandatory for pandas to modify existing Excel files—don’t forget to install it.
• if_sheet_exists='overlay': This ensures new data is added to the end of your existing sheet instead of creating a new one.
• Index: I added index=False to avoid writing pandas’ default index column to Excel (remove this if you want the index included).

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