将pandas.DataFrame添加到现有的Excel文件 [英] Adding a pandas.DataFrame to Existing Excel File

问题描述

我有一个网络抓取工具，可以为本月的抓取工作创建一个excel文件.我想在每次运行时将今天的刮擦和该月的每个刮擦作为新的表添加到该文件中.但是，我的问题是，它只会用新的工作表覆盖现有工作表，而不是将其添加为单独的新工作表.我已经尝试使用xlrd，xlwt，pandas和openpyxl来做到这一点.

I have a web scraper which creates an excel file for this month's scrapes. I want to add today's scrape and every scrape for that month into that file as a new sheet every time it is run. My issue, however, has been that it only overwrites the existing sheet with a new sheet instead of adding it as a separate new sheet. I've tried to do it with xlrd, xlwt, pandas, and openpyxl.

Python仍然是全新的，因此易于使用！

Still brand new to Python so simplicity is appreciated!

下面只是处理写入excel文件的代码.

Below is just the code dealing with writing the excel file.

# My relevant time variables
ts = time.time()
date_time = datetime.datetime.fromtimestamp(ts).strftime('%y-%m-%d %H_%M_%S')
HourMinuteSecond = datetime.datetime.fromtimestamp(ts).strftime('%H_%M_%S')
month = datetime.datetime.now().strftime('%m-%y')

# Creates a writer for this month and year
writer = pd.ExcelWriter(
    'C:\\Users\\G\\Desktop\\KickstarterLinks(%s).xlsx' % (month), 
    engine='xlsxwriter')

# Creates dataframe from my data, d
df = pd.DataFrame(d)

# Writes to the excel file
df.to_excel(writer, sheet_name='%s' % (HourMinuteSecond))
writer.save()

推荐答案

更新:

此功能已添加到熊猫0.24.0 :

ExcelWriter现在接受mode作为关键字参数，从而可以在使用openpyxl引擎时添加到现有工作簿中( GH3441 )

ExcelWriter now accepts mode as a keyword argument, enabling append to existing workbooks when using the openpyxl engine (GH3441)

以前的版本:

Pandas为此具有打开功能请求.

Previous version:

Pandas has an open feature request for this.

同时，这是一个将pandas.DataFrame添加到现有工作簿中的函数:

In the mean time, here is a function which adds a pandas.DataFrame to an existing workbook:

代码:

def add_frame_to_workbook(filename, tabname, dataframe, timestamp):
    """
    Save a dataframe to a workbook tab with the filename and tabname
    coded to timestamp

    :param filename: filename to create, can use strptime formatting
    :param tabname: tabname to create, can use strptime formatting
    :param dataframe: dataframe to save to workbook
    :param timestamp: timestamp associated with dataframe
    :return: None
    """
    filename = timestamp.strftime(filename)
    sheet_name = timestamp.strftime(tabname)

    # create a writer for this month and year
    writer = pd.ExcelWriter(filename, engine='openpyxl')

    try:
        # try to open an existing workbook
        writer.book = load_workbook(filename)

        # copy existing sheets
        writer.sheets = dict(
            (ws.title, ws) for ws in writer.book.worksheets)
    except IOError:
        # file does not exist yet, we will create it
        pass

    # write out the new sheet
    dataframe.to_excel(writer, sheet_name=sheet_name)

    # save the workbook
    writer.save()

测试代码:

import datetime as dt
import pandas as pd
from openpyxl import load_workbook

data = [x.strip().split() for x in """
                   Date  Close
    2016-10-18T13:44:59  2128.00
    2016-10-18T13:59:59  2128.75
""".split('\n')[1:-1]]
df = pd.DataFrame(data=data[1:], columns=data[0])

name_template = './sample-%m-%y.xlsx'
tab_template = '%d_%H_%M'
now = dt.datetime.now()
in_an_hour = now + dt.timedelta(hours=1)
add_frame_to_workbook(name_template, tab_template, df, now)
add_frame_to_workbook(name_template, tab_template, df, in_an_hour)

(来源)

这篇关于将pandas.DataFrame添加到现有的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！