pythonプログラムにてエクセル更新をしても手入力したコメントはそのままにするプログラムを作成したい

Question

#実現したいこと
以下のソースコードにて機械稼働率のエクセル自動集計出力を行い、管理者が1日の機械稼働状況に問題があった場合に手入力によるコメントを残します。pythonプログラムを再実行してもエクセルに書き込んだコメントは消去されないプログラムを構築したいと思います。

手順
①以下のソースコードを実行
②機械稼働率が自動集計出力される
③管理者が集計出力されたエクセルファイルを開き、1日の機械稼働状況に関して問題があった場合にコメントを記述し、エクセルを上書き保存する
④以下のソースコードを実行
⑤稼働率が更新されたエクセルデータとコメントが入力されたエクセルデータが連結される

#使用しているpythonプログラムコード

import pandas as pd
from openpyxl import Workbook
import glob

t=input('更新したい月を入力してください')

file_list=glob.glob('//192.168.1.4/pi/mydata'+t+'*')

rcd={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []}

for file in file_list:

    a=pd.read_csv(file)
    d=(file[23:31])
    x=a.iloc[:,0].count()
    y=a.iloc[:,1].sum()
    z=a.iloc[:,2].sum()
    u=x-y-z
    w=round(y/x*100,1)

    rcd['日付'].append(d)
    rcd['稼働時間(分)'].append(y)
    rcd['無人稼働時間(分)'].append(z)
    rcd['停止時間(分)'].append(u) 
    rcd['トータル時間(分)'].append(x)
    rcd['稼働率(%)'].append(w)
    
df=pd.DataFrame(rcd)
print(df)

monthly_file = f'{t}.xlsx'
try:
    df_m = pd.read_excel(monthly_file)
    df_output = pd.merge(df_m, df,how='outer')
except FileNotFoundError:
    df_output = df.copy()
    df_output['コメント'] = ''

df_output.to_excel(monthly_file,index=False)

発生している問題・エラーメッセージ

連結するエクセルデータに文字と数値が混在している為、連結が出来ないと認識しています。

ValueError: You are trying to merge on int64 and object columns. If you wish to proceed you should use pd.concat

###上記プログラムにて出力されたエクセルデータ

プログラムにて自動出力されたエクセルファイルを開き、稼働状況に問題があった場合に管理者が手入力にてコメントを残し保存する。入力箇所はデータがない一番右の列に入力することとする

ラズベリーパイで採取したデータは毎日、日付毎にファイル追加されていきます
20210709、20210710・・・とファイルが増え、上記のコードを実行にて出力データ
の集計更新がかかる。

###ラズベリーパイで採取したデータ
データの保存状況

光センサーにて1分毎に稼働状況をラズベリーパイ内にエクセル記録

試したこと

concatの使用しての連結。こちらも数字と文字の連結は出来ない内容のエラーが発生

Comments

[ppaul]

質問が何なのかがわかりません。
コードは複数のcsvファイルを読んで一つのxlsxファイルを作っていますが、タイトルはエクセル更新ですね。
残したいというコメントはどの時点でどうやって書き込まれるのでしょうか？
ということで、結局何が質問なのかがわかりません。

[F91_]

修正致します。

[bsdfan]

コメントがコメントであることを識別する方法はなんでしょう？
列がE列で固定とか、ヘッダが"コメント"とか。

[F91_]

コメントをコメントであることを識別する方法は自分のプログラムにはないと思ってます。pd.merge how=outerでコメント部分も連結されると考えてます。

Answer 1

エラーは、作成したdfの"日付"列は文字列なのに、xlsxから読み込んだdf_mの"日付"列が数値なので、発生しています。
read_excel で dtype を指定して、strとして読み込めばいいです。

try:
    df_m = pd.read_excel(monthly_file, dtype={'日付': str})
    df_output = pd.merge(df_m, df,how='outer')
except FileNotFoundError:
    df_output = df.copy()
    df_output['コメント'] = None

df_output.to_excel(monthly_file,index=False)

Comments

[F91_]

ありがとうございます!!!!出来ました!

[F91_]

ありがとうございます！！！

[F91_]

何度もすみません。問題が発生しまして、プログラム実行(データの更新)は月に何度も行うので、1度出力したデータが残ったまま下の行に同じデータが追加されました。実現したいことの説明が不足していたと思いますので再編集させていただきます。

[bsdfan]

違う問題なら違う質問であげてください。
どの回答が何の質問に対する回答なのかがわからなくなります。

[F91_]

大変申し訳ありませんでした。 bsdfanさんが作成していただいたコードの意味を理解する事で問題は解決出来ました。

Answer 2

動作テストをしていないのでエラーとかが出るかもしれませんが、基本的には以下のようにやればできます。変更したのは、最後の9行です。

import pandas as pd
from openpyxl import Workbook
import glob

t=input('更新したい月を入力してください')

file_list=glob.glob('//192.168.1.4/pi/mydata'+t+'*')

rcd={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '稼働率(%)': []}

for file in file_list:

    a=pd.read_csv(file)
    d=(file[23:31])
    x=a.iloc[:,0].count()
    y=a.iloc[:,1].sum()
    z=a.iloc[:,2].sum()
    w=round(y/x*100,1)

    rcd['日付'].append(d)
    rcd['稼働時間(分)'].append(y)
    rcd['無人稼働時間(分)'].append(z)
    rcd['稼働率(%)'].append(w)

df=pd.DataFrame(rcd)
print(df)

monthly_file = f'{t}.xlsx'
try:
    df_m = pd.read_excel(monthly_file)
    df_output = pd.merge(df_m, df, how='outer')
except FileNotFoundError:
    df_output = df.copy()
    df_output['コメント'] = ''

df_output.to_excel(monthly_file,index=False)

tryの使い方は、公式ドキュメント チュートリアル 8.3. 例外を処理するを見てください。

pd.mergeの使い方は、pandas.DataFrameを結合するmerge, join（列・インデックス基準）
を見てください。

Comments

[F91_]

ご回答ありがとうございました。ファイルの結合が上手く出来ず、You are trying to merge on int64 and object columns. If you wish to proceed you should use pd.concatというエラーが出ており、concatを使用してみたりしていますが、解決出来ておりません。
自力で解決出来なかった場合に再度質問させていただきます。

[F91_]

You are trying to merge on int64 and object columns. If you wish to proceed you should use pd.concatのエラーが解決出来ませんでした。数字ファイルと文字ファイルの連結が不可であり、どちらかに合わせる必要があると思っています。型変換などトライしていますがエラー発生で進みませんので、解決策をご教授お願い致します。

Answer 3

etherbegさんの解決案を組み込み、以下のプログラムとなり、上手くいきました。

import pandas as pd
from openpyxl import Workbook
import glob
import datetime

today = datetime.date.today()
year = today.year
month = today.month

input_value= input("{}年{:02}月分の集計を行います。Enterを押してください。 (別の月を集計する場合は6桁で年月を入力し、Enterを押してください) > ".format(year, month))
if not input_value:
   input_value =  "{}{:02}".format(year, month)

file_list1=glob.glob('//192.168.3.141/pi/mydata'+input_value+'*')
file_list2=glob.glob('//192.168.3.140/pi/mydata'+input_value+'*')
file_list3=glob.glob('//192.168.3.139/pi/mydata'+input_value+'*')
file_list4=glob.glob('//192.168.3.135/pi/mydata'+input_value+'*')
file_list5=glob.glob('//192.168.3.132/pi/mydata'+input_value+'*')
file_list6=glob.glob('//192.168.3.131/pi/mydata'+input_value+'*')
file_list7=glob.glob('//192.168.3.134/pi/mydata'+input_value+'*')

g='三課伸線稼働率(全機械)'
monthly_file =g+ f'{input_value}.xlsx'

rcd1={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []}

for file1 in file_list1:
    a1=pd.read_csv(file1)
    d1=(file1[25:33])
    x1=a1.iloc[:,0].count()
    y1=a1.iloc[:,1].sum()
    z1=a1.iloc[:,2].sum()
    u1=x1-y1-z1
    w1=round(y1/x1*100,1)
    rcd1['日付'].append(d1)
    rcd1['稼働時間(分)'].append(y1)
    rcd1['無人稼働時間(分)'].append(z1)
    rcd1['停止時間(分)'].append(u1) 
    rcd1['トータル時間(分)'].append(x1)
    rcd1['稼働率(%)'].append(w1)

df1=pd.DataFrame(rcd1)

rcd2={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []}
    
for file2 in file_list2:
    a2=pd.read_csv(file2)
    d2=(file2[25:33])
    x2=a2.iloc[:,0].count()
    y2=a2.iloc[:,1].sum()
    z2=a2.iloc[:,2].sum()
    u2=x2-y2-z2
    w2=round(y2/x2*100,1)
    rcd2['日付'].append(d2)
    rcd2['稼働時間(分)'].append(y2)
    rcd2['無人稼働時間(分)'].append(z2)
    rcd2['停止時間(分)'].append(u2) 
    rcd2['トータル時間(分)'].append(x2)
    rcd2['稼働率(%)'].append(w2)
    
df2=pd.DataFrame(rcd2)

rcd3={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []}

for file3 in file_list3:
    a3=pd.read_csv(file3)
    d3=(file3[25:33])
    x3=a3.iloc[:,0].count()
    y3=a3.iloc[:,1].sum()
    z3=a3.iloc[:,2].sum()
    u3=x3-y3-z3
    w3=round(y3/x3*100,1)
    rcd3['日付'].append(d3)
    rcd3['稼働時間(分)'].append(y3)
    rcd3['無人稼働時間(分)'].append(z3)
    rcd3['停止時間(分)'].append(u3) 
    rcd3['トータル時間(分)'].append(x3)
    rcd3['稼働率(%)'].append(w3)
    
df3=pd.DataFrame(rcd3)

rcd4={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []}

for file4 in file_list4:
    a4=pd.read_csv(file4)
    d4=(file4[25:33])
    x4=a4.iloc[:,0].count()
    y4=a4.iloc[:,1].sum()
    z4=a4.iloc[:,2].sum()
    u4=x4-y4-z4
    w4=round(y4/x4*100,1)
    rcd4['日付'].append(d4)
    rcd4['稼働時間(分)'].append(y4)
    rcd4['無人稼働時間(分)'].append(z4)
    rcd4['停止時間(分)'].append(u4) 
    rcd4['トータル時間(分)'].append(x4)
    rcd4['稼働率(%)'].append(w4)
    
df4=pd.DataFrame(rcd4)

rcd5={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []}
            
for file5 in file_list5:
    a5=pd.read_csv(file5)
    d5=(file5[25:33])
    x5=a5.iloc[:,0].count()
    y5=a5.iloc[:,1].sum()
    z5=a5.iloc[:,2].sum()
    u5=x5-y5-z5
    w5=round(y5/x5*100,1)
    rcd5['日付'].append(d5)
    rcd5['稼働時間(分)'].append(y5)
    rcd5['無人稼働時間(分)'].append(z5)
    rcd5['停止時間(分)'].append(u5) 
    rcd5['トータル時間(分)'].append(x5)
    rcd5['稼働率(%)'].append(w5)
df5=pd.DataFrame(rcd5)

rcd6={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []}
    
for file6 in file_list6:
    a6=pd.read_csv(file6)
    d6=(file6[25:33])
    x6=a6.iloc[:,0].count()
    y6=a6.iloc[:,1].sum()
    z6=a6.iloc[:,2].sum()
    u6=x6-y6-z6
    w6=round(y6/x6*100,1)
    rcd6['日付'].append(d6)
    rcd6['稼働時間(分)'].append(y6)
    rcd6['無人稼働時間(分)'].append(z6)
    rcd6['停止時間(分)'].append(u6) 
    rcd6['トータル時間(分)'].append(x6)
    rcd6['稼働率(%)'].append(w6)

df6=pd.DataFrame(rcd6)
    
rcd7={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []}

for file7 in file_list7:
    a7=pd.read_csv(file7)
    d7=(file7[25:33])
    x7=a7.iloc[:,0].count()
    y7=a7.iloc[:,1].sum()
    z7=a7.iloc[:,2].sum()
    u7=x7-y7-z7
    w7=round(y7/x7*100,1)
    rcd7['日付'].append(d7)
    rcd7['稼働時間(分)'].append(y7)
    rcd7['無人稼働時間(分)'].append(z7)
    rcd7['停止時間(分)'].append(u7) 
    rcd7['トータル時間(分)'].append(x7)
    rcd7['稼働率(%)'].append(w7)

df7=pd.DataFrame(rcd7)

try:
    df_m1 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=0,dtype={'日付': str})
    df_output1=pd.merge(df1, df_m1[['日付','コメント']], on='日付', how='outer')
except FileNotFoundError:
    df_output1 = df1.copy()
    df_output1['コメント'] = None

try:
    df_m2 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=1,dtype={'日付': str})
    df_output2=pd.merge(df2, df_m2[['日付','コメント']], on='日付', how='outer')
except FileNotFoundError:
    df_output2 = df2.copy()
    df_output2['コメント'] = None
    
try:
    df_m3 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=2,dtype={'日付': str})
    df_output3=pd.merge(df3, df_m3[['日付','コメント']], on='日付', how='outer')
except FileNotFoundError:
    df_output3 = df3.copy()
    df_output3['コメント'] = None

try:
    df_m4 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=3,dtype={'日付': str})
    df_output4=pd.merge(df4, df_m4[['日付','コメント']], on='日付', how='outer')
except FileNotFoundError:
    df_output4 = df4.copy()
    df_output4['コメント'] = None
    
try:
    df_m5 = pd.read_excel(monthly_file,sheet_name=4,dtype={'日付': str})
    df_output5=pd.merge(df5, df_m5[['日付','コメント']], on='日付', how='outer')
except FileNotFoundError:
    df_output5 = df5.copy()
    df_output5['コメント'] = None

try:
    df_m6 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=5,dtype={'日付': str})
    df_output6=pd.merge(df6, df_m6[['日付','コメント']], on='日付', how='outer')
except FileNotFoundError:
    df_output6 = df6.copy()
    df_output6['コメント'] = None

try:
    df_m7 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=6,dtype={'日付': str})
    df_output7=pd.merge(df7, df_m7[['日付','コメント']], on='日付', how='outer')
except FileNotFoundError:
    df_output7 = df7.copy()
    df_output7['コメント'] = None

    
with pd.ExcelWriter('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file) as writer:    
    df_output1.to_excel(writer,sheet_name='D31',index=False)
    df_output2.to_excel(writer,sheet_name='D32',index=False)
    df_output3.to_excel(writer,sheet_name='D33',index=False)
    df_output4.to_excel(writer,sheet_name='D34',index=False)
    df_output5.to_excel(writer,sheet_name='D35',index=False)
    df_output6.to_excel(writer,sheet_name='D36',index=False)
    df_output7.to_excel(writer,sheet_name='D37',index=False)

Answer 4

https://teratail.com/questions/358147
上記質問でのコメントで、1日に2度プログラムを実行すると、当日分のデータがダブるという問題がある、とのことでした。

コメント中で質問者さんの提案された、「保存済みのエクセルのコメント行のみ引っ張り、計算されたデータを結合する」方法が筋が良さそうなので、その方向で改善案を考えてみました。

df_output1 = pd.merge(df_m1, df1,how='outer') の行を以下のように変更することで可能です。

try:
    df_m1 = pd.read_excel(monthly_file,sheet_name=0,dtype={'日付': str})
    #df_output1 = pd.merge(df_m1, df1,how='outer')  # この行を以下に変更
    pd.merge(df1, df_m1[['日付','コメント']], on='日付', how='outer')
except FileNotFoundError:
    df_output1 = df1.copy()
    df_output1['コメント'] = None

---

変更の意味について、以下のサンプルコードで説明します。

エクセルから読み込んだ機械稼働率データのサンプルです。今日は '20210903' です。1回目の集計が行われ、コメント ('yyy') も書き込まれています。

>>> df_m1
       date  a  b comment
0  20210901  1  4     zzz
1  20210902  2  5    None
2  20210903  3  6     yyy

同日、2回目のプログラムの実行でデータファイルから集計しなおされたデータです。機械はまだ稼働中のため、今日のデータが更新されて違う値になっています。

>>> df1
       date  a  b
0  20210901  1  4
1  20210902  2  5
2  20210903  4  7

この2つを単純に how='outer' でマージすると、今日のデータがダブってしまう、という問題でした。すでにコメントが書き込まれているため、単純に新しい方のデータを残して解決とするわけにはいきません。

>>> pd.merge(df_m1, df1, how='outer')
       date  a  b comment
0  20210901  1  4     zzz
1  20210902  2  5    None
2  20210903  3  6     yyy
3  20210903  4  7     NaN

そこでエクセルからはコメントのみを取得すれば良いのではないか、というのが質問者さんのアイデアでした。ただしマージするときの基準となる列（キー列）が必要ですので、日付列は残す必要があるでしょう。

以下のようにすると、エクセルから読み込んだデータから、日付列とコメント列のみを取り出すことができます。

>>> df_m1[['date','comment']]
       date comment
0  20210901     zzz
1  20210902    None
2  20210903     yyy

これとデータファイルから集計されたデータをマージします。
on='date'で、キー列は日付列であると明示します。共通する列は日付列のみになったので、on='date'はなくても自動的に日付列がキー列となるのですが、明示した方が何をしているかがはっきりするので、あえて入れています。
マージ方法は 'outer' のままでOKです。

>>> pd.merge(df_m1[['date','comment']], df1, on='date', how='outer')
       date comment  a  b
0  20210901     zzz  1  4
1  20210902    None  2  5
2  20210903     yyy  4  7

コメントを残したまま、データがダブルことなく、今日のデータを最新の値に更新できました。
しかしコメントの列が前に来てしまいました。これは単純に、pd.merge()にマージするデータを渡す際の順番を入れ替えれば解決します。

>>> pd.merge(df1, df_m1[['date','comment']], on='date', how='outer')
       date  a  b comment
0  20210901  1  4     zzz
1  20210902  2  5    None
2  20210903  4  7     yyy