既存のEXCELファイルの既存SHEETに、pd.DataFrameを転記する方法

Question

実現したいこと

pd.DataFrameで作ったデータ(data1)を、既存のEXCELファイル（datafile）の既存SHEET(wb["data"])に貼り付けたいです。

現在は、下記コードにて、１セルずつ地道に代入していく手法をとっていますが、データ量が多くて
処理が重くなってしまっています。

dataframeごと一気にコピペでき、処理速度を改善できる方法がないか探しています。

該当のソースコード

row1=len(data1)
col1=len(data1.columns)
        
wb = openpyxl.load_workbook(datafile)
ws = wb["data"]
for y in range(col1):
    yname=data1.columns.values[y]
    ws.cell(row=1,column=y+1,value=yname)
    for x in range(row1):
        m = data1.iloc[x,y]
        ws.cell(row=x+2,column=y+1,value=m)

試したこと

色々調べて試してみましたが、せいぜいdata1というシートが新たに追加されるにとどまり、
既存のシートが編集されるようなコマンドを見つけることができていません。

地道にやるしかないのでしょうか。。。。

Comments

[meg_]

「data1というシートが新たに追加される」のであれば、その後に一気にコピペ出来ないでしょうか？
※openpyxlに詳しくないので具体的には分からないですが

[tomo212]

書き方が悪かったですが、「data1」に出力というのはできます。
あくまで「data」というシートに転記したく、「data1」にデータが吐き出されるのは目的の処理ではないです。。。

[meg_]

それは分かっています。ですから、data1シートからpandasで出力したデータ部分をopenpyxlでdataシートへコピーすれば、ループ処理より速くなるのでは？という提案でした。data1シートは削除しますが、その一連の処理と今のループ処理とでどちらが速いかは計測しないと分からないでしょう。

[tomo212]

すいません。
一度別シートに吐き出してから移すというところまで考えが及んでいませんでした。
ですが、エクセル内のデータを一度にコピペする方法も探しましたが、結局１セルor1行ずつ移さないといけないようでした。
一度書いて検証してみます。アドバイスありがとうございます。

Answer 1

公式documentを読んだ感じだと，複数セルをrangeで取得する方法はあるものの，PandasのDataFrameを書き込むメソッドは存在しませんでした．

そのため，forループで追記処理を行う以外には，datafile内のdataシートを一度DataFrameにコピーしてから，そのDataFrameに加えたい情報を加え，最終的にDataFrameをWorkSheetに書くという方法が現実的かと思います．

その方法のサンプルコードは貼っておきますので，timeモジュールでも利用して，現行のコードと実行時間の短い方を採用されてはいかがでしょうか？

import time as tm
import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

# define
datafile = './sample.xlsx'
sheetname = 'data'

start_time = tm.time()

# get sheet from workbook & convert to DataFrame
wb = load_workbook(filename=datafile)
ws_in = wb[sheetname]
df = pd.DataFrame(ws_in.values)

#----------------------------------------------------------
#  You can write here with regard to changing DataFrame df
#----------------------------------------------------------

# generate sheet for writing DataFrame
ws_out = wb.create_sheet(sheetname+'-tmp')
for row_data in dataframe_to_rows(df, index=False, header=False):
    ws_out.append(row_data)

# remove sheet and change title
wb.remove(ws_in)
ws_out.title = sheetname

# save workbook
wb.save(datafile)

print('exe-time : {}[s]'.format(tm.time()-start_time))

なお，xlsxからDataFrameにコピーするまでは，Pandasのdf = pd.read_excel(datafile, sheet_name=sheetname)よりも，openpyxl経由でDataFrameにキャストする方が実行時間が半分程度だったので，そちらをコード内では採用しました．

参考になれば幸いです．

Comments

[tomo212]

ご丁寧にありがとうございました。
やはりDFごと移す方法はないようですね。。。
データをpythonに移して改めて転記するアイデアはいいと思いました。一度検証してみたいと思います。