Q&A
質問が何なのかがわかりません。
コードは複数のcsvファイルを読んで一つのxlsxファイルを作っていますが、タイトルはエクセル更新ですね。
残したいというコメントはどの時点でどうやって書き込まれるのでしょうか？
ということで、結局何が質問なのかがわかりません。
#実現したいこと
以下のソースコードにて機械稼働率のエクセル自動集計出力を行い、管理者が1日の機械稼働状況に問題があった場合に手入力によるコメントを残します。pythonプログラムを再実行してもエクセルに書き込んだコメントは消去されないプログラムを構築したいと思います。
手順
①以下のソースコードを実行
②機械稼働率が自動集計出力される
③管理者が集計出力されたエクセルファイルを開き、1日の機械稼働状況に関して問題があった場合にコメントを記述し、エクセルを上書き保存する
④以下のソースコードを実行
⑤稼働率が更新されたエクセルデータとコメントが入力されたエクセルデータが連結される
#使用しているpythonプログラムコード
python
1import pandas as pd 2from openpyxl import Workbook 3import glob 4 5t=input('更新したい月を入力してください') 6 7file_list=glob.glob('//192.168.1.4/pi/mydata'+t+'*') 8 9rcd={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []} 10 11for file in file_list: 12 13 a=pd.read_csv(file) 14 d=(file[23:31]) 15 x=a.iloc[:,0].count() 16 y=a.iloc[:,1].sum() 17 z=a.iloc[:,2].sum() 18 u=x-y-z 19 w=round(y/x*100,1) 20 21 rcd['日付'].append(d) 22 rcd['稼働時間(分)'].append(y) 23 rcd['無人稼働時間(分)'].append(z) 24 rcd['停止時間(分)'].append(u) 25 rcd['トータル時間(分)'].append(x) 26 rcd['稼働率(%)'].append(w) 27 28df=pd.DataFrame(rcd) 29print(df) 30 31monthly_file = f'{t}.xlsx' 32try: 33 df_m = pd.read_excel(monthly_file) 34 df_output = pd.merge(df_m, df,how='outer') 35except FileNotFoundError: 36 df_output = df.copy() 37 df_output['コメント'] = '' 38 39df_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の使用しての連結。こちらも数字と文字の連結は出来ない内容のエラーが発生
回答4件
0
ベストアンサー
エラーは、作成したdfの"日付"列は文字列なのに、xlsxから読み込んだdf_mの"日付"列が数値なので、発生しています。
read_excel で dtype を指定して、strとして読み込めばいいです。
python
1try: 2 df_m = pd.read_excel(monthly_file, dtype={'日付': str}) 3 df_output = pd.merge(df_m, df,how='outer') 4except FileNotFoundError: 5 df_output = df.copy() 6 df_output['コメント'] = None 7 8df_output.to_excel(monthly_file,index=False)
投稿2021/07/31 11:49
総合スコア4560
ありがとうございます!!!!出来ました!
ありがとうございます！！！
何度もすみません。問題が発生しまして、プログラム実行(データの更新)は月に何度も行うので、1度出力したデータが残ったまま下の行に同じデータが追加されました。実現したいことの説明が不足していたと思いますので再編集させていただきます。
違う問題なら違う質問であげてください。
どの回答が何の質問に対する回答なのかがわからなくなります。
大変申し訳ありませんでした。 bsdfanさんが作成していただいたコードの意味を理解する事で問題は解決出来ました。
0
動作テストをしていないのでエラーとかが出るかもしれませんが、基本的には以下のようにやればできます。変更したのは、最後の9行です。
python
1import pandas as pd 2from openpyxl import Workbook 3import glob 4 5t=input('更新したい月を入力してください') 6 7file_list=glob.glob('//192.168.1.4/pi/mydata'+t+'*') 8 9rcd={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '稼働率(%)': []} 10 11for file in file_list: 12 13 a=pd.read_csv(file) 14 d=(file[23:31]) 15 x=a.iloc[:,0].count() 16 y=a.iloc[:,1].sum() 17 z=a.iloc[:,2].sum() 18 w=round(y/x*100,1) 19 20 rcd['日付'].append(d) 21 rcd['稼働時間(分)'].append(y) 22 rcd['無人稼働時間(分)'].append(z) 23 rcd['稼働率(%)'].append(w) 24 25df=pd.DataFrame(rcd) 26print(df) 27 28monthly_file = f'{t}.xlsx' 29try: 30 df_m = pd.read_excel(monthly_file) 31 df_output = pd.merge(df_m, df, how='outer') 32except FileNotFoundError: 33 df_output = df.copy() 34 df_output['コメント'] = '' 35 36df_output.to_excel(monthly_file,index=False)
tryの使い方は、公式ドキュメント チュートリアル 8.3. 例外を処理するを見てください。
pd.mergeの使い方は、pandas.DataFrameを結合するmerge, join（列・インデックス基準）
を見てください。
投稿2021/07/20 14:22
編集2021/07/20 16:53総合スコア24666
ご回答ありがとうございました。ファイルの結合が上手く出来ず、You are trying to merge on int64 and object columns. If you wish to proceed you should use pd.concatというエラーが出ており、concatを使用してみたりしていますが、解決出来ておりません。
自力で解決出来なかった場合に再度質問させていただきます。
You are trying to merge on int64 and object columns. If you wish to proceed you should use pd.concatのエラーが解決出来ませんでした。数字ファイルと文字ファイルの連結が不可であり、どちらかに合わせる必要があると思っています。型変換などトライしていますがエラー発生で進みませんので、解決策をご教授お願い致します。
0
etherbegさんの解決案を組み込み、以下のプログラムとなり、上手くいきました。
python
1import pandas as pd 2from openpyxl import Workbook 3import glob 4import datetime 5 6today = datetime.date.today() 7year = today.year 8month = today.month 9 10input_value= input("{}年{:02}月分の集計を行います。Enterを押してください。 (別の月を集計する場合は6桁で年月を入力し、Enterを押してください) > ".format(year, month)) 11if not input_value: 12 input_value = "{}{:02}".format(year, month) 13 14file_list1=glob.glob('//192.168.3.141/pi/mydata'+input_value+'*') 15file_list2=glob.glob('//192.168.3.140/pi/mydata'+input_value+'*') 16file_list3=glob.glob('//192.168.3.139/pi/mydata'+input_value+'*') 17file_list4=glob.glob('//192.168.3.135/pi/mydata'+input_value+'*') 18file_list5=glob.glob('//192.168.3.132/pi/mydata'+input_value+'*') 19file_list6=glob.glob('//192.168.3.131/pi/mydata'+input_value+'*') 20file_list7=glob.glob('//192.168.3.134/pi/mydata'+input_value+'*') 21 22g='三課伸線稼働率(全機械)' 23monthly_file =g+ f'{input_value}.xlsx' 24 25rcd1={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []} 26 27for file1 in file_list1: 28 a1=pd.read_csv(file1) 29 d1=(file1[25:33]) 30 x1=a1.iloc[:,0].count() 31 y1=a1.iloc[:,1].sum() 32 z1=a1.iloc[:,2].sum() 33 u1=x1-y1-z1 34 w1=round(y1/x1*100,1) 35 rcd1['日付'].append(d1) 36 rcd1['稼働時間(分)'].append(y1) 37 rcd1['無人稼働時間(分)'].append(z1) 38 rcd1['停止時間(分)'].append(u1) 39 rcd1['トータル時間(分)'].append(x1) 40 rcd1['稼働率(%)'].append(w1) 41 42df1=pd.DataFrame(rcd1) 43 44rcd2={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []} 45 46for file2 in file_list2: 47 a2=pd.read_csv(file2) 48 d2=(file2[25:33]) 49 x2=a2.iloc[:,0].count() 50 y2=a2.iloc[:,1].sum() 51 z2=a2.iloc[:,2].sum() 52 u2=x2-y2-z2 53 w2=round(y2/x2*100,1) 54 rcd2['日付'].append(d2) 55 rcd2['稼働時間(分)'].append(y2) 56 rcd2['無人稼働時間(分)'].append(z2) 57 rcd2['停止時間(分)'].append(u2) 58 rcd2['トータル時間(分)'].append(x2) 59 rcd2['稼働率(%)'].append(w2) 60 61df2=pd.DataFrame(rcd2) 62 63rcd3={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []} 64 65for file3 in file_list3: 66 a3=pd.read_csv(file3) 67 d3=(file3[25:33]) 68 x3=a3.iloc[:,0].count() 69 y3=a3.iloc[:,1].sum() 70 z3=a3.iloc[:,2].sum() 71 u3=x3-y3-z3 72 w3=round(y3/x3*100,1) 73 rcd3['日付'].append(d3) 74 rcd3['稼働時間(分)'].append(y3) 75 rcd3['無人稼働時間(分)'].append(z3) 76 rcd3['停止時間(分)'].append(u3) 77 rcd3['トータル時間(分)'].append(x3) 78 rcd3['稼働率(%)'].append(w3) 79 80df3=pd.DataFrame(rcd3) 81 82rcd4={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []} 83 84for file4 in file_list4: 85 a4=pd.read_csv(file4) 86 d4=(file4[25:33]) 87 x4=a4.iloc[:,0].count() 88 y4=a4.iloc[:,1].sum() 89 z4=a4.iloc[:,2].sum() 90 u4=x4-y4-z4 91 w4=round(y4/x4*100,1) 92 rcd4['日付'].append(d4) 93 rcd4['稼働時間(分)'].append(y4) 94 rcd4['無人稼働時間(分)'].append(z4) 95 rcd4['停止時間(分)'].append(u4) 96 rcd4['トータル時間(分)'].append(x4) 97 rcd4['稼働率(%)'].append(w4) 98 99df4=pd.DataFrame(rcd4) 100 101rcd5={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []} 102 103for file5 in file_list5: 104 a5=pd.read_csv(file5) 105 d5=(file5[25:33]) 106 x5=a5.iloc[:,0].count() 107 y5=a5.iloc[:,1].sum() 108 z5=a5.iloc[:,2].sum() 109 u5=x5-y5-z5 110 w5=round(y5/x5*100,1) 111 rcd5['日付'].append(d5) 112 rcd5['稼働時間(分)'].append(y5) 113 rcd5['無人稼働時間(分)'].append(z5) 114 rcd5['停止時間(分)'].append(u5) 115 rcd5['トータル時間(分)'].append(x5) 116 rcd5['稼働率(%)'].append(w5) 117df5=pd.DataFrame(rcd5) 118 119rcd6={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []} 120 121for file6 in file_list6: 122 a6=pd.read_csv(file6) 123 d6=(file6[25:33]) 124 x6=a6.iloc[:,0].count() 125 y6=a6.iloc[:,1].sum() 126 z6=a6.iloc[:,2].sum() 127 u6=x6-y6-z6 128 w6=round(y6/x6*100,1) 129 rcd6['日付'].append(d6) 130 rcd6['稼働時間(分)'].append(y6) 131 rcd6['無人稼働時間(分)'].append(z6) 132 rcd6['停止時間(分)'].append(u6) 133 rcd6['トータル時間(分)'].append(x6) 134 rcd6['稼働率(%)'].append(w6) 135 136df6=pd.DataFrame(rcd6) 137 138rcd7={'日付' : [], '稼働時間(分)': [], '無人稼働時間(分)': [], '停止時間(分)': [],'トータル時間(分)': [],'稼働率(%)': []} 139 140for file7 in file_list7: 141 a7=pd.read_csv(file7) 142 d7=(file7[25:33]) 143 x7=a7.iloc[:,0].count() 144 y7=a7.iloc[:,1].sum() 145 z7=a7.iloc[:,2].sum() 146 u7=x7-y7-z7 147 w7=round(y7/x7*100,1) 148 rcd7['日付'].append(d7) 149 rcd7['稼働時間(分)'].append(y7) 150 rcd7['無人稼働時間(分)'].append(z7) 151 rcd7['停止時間(分)'].append(u7) 152 rcd7['トータル時間(分)'].append(x7) 153 rcd7['稼働率(%)'].append(w7) 154 155df7=pd.DataFrame(rcd7) 156 157try: 158 df_m1 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=0,dtype={'日付': str}) 159 df_output1=pd.merge(df1, df_m1[['日付','コメント']], on='日付', how='outer') 160except FileNotFoundError: 161 df_output1 = df1.copy() 162 df_output1['コメント'] = None 163 164try: 165 df_m2 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=1,dtype={'日付': str}) 166 df_output2=pd.merge(df2, df_m2[['日付','コメント']], on='日付', how='outer') 167except FileNotFoundError: 168 df_output2 = df2.copy() 169 df_output2['コメント'] = None 170 171try: 172 df_m3 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=2,dtype={'日付': str}) 173 df_output3=pd.merge(df3, df_m3[['日付','コメント']], on='日付', how='outer') 174except FileNotFoundError: 175 df_output3 = df3.copy() 176 df_output3['コメント'] = None 177 178try: 179 df_m4 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=3,dtype={'日付': str}) 180 df_output4=pd.merge(df4, df_m4[['日付','コメント']], on='日付', how='outer') 181except FileNotFoundError: 182 df_output4 = df4.copy() 183 df_output4['コメント'] = None 184 185try: 186 df_m5 = pd.read_excel(monthly_file,sheet_name=4,dtype={'日付': str}) 187 df_output5=pd.merge(df5, df_m5[['日付','コメント']], on='日付', how='outer') 188except FileNotFoundError: 189 df_output5 = df5.copy() 190 df_output5['コメント'] = None 191 192try: 193 df_m6 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=5,dtype={'日付': str}) 194 df_output6=pd.merge(df6, df_m6[['日付','コメント']], on='日付', how='outer') 195except FileNotFoundError: 196 df_output6 = df6.copy() 197 df_output6['コメント'] = None 198 199try: 200 df_m7 = pd.read_excel('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file,sheet_name=6,dtype={'日付': str}) 201 df_output7=pd.merge(df7, df_m7[['日付','コメント']], on='日付', how='outer') 202except FileNotFoundError: 203 df_output7 = df7.copy() 204 df_output7['コメント'] = None 205 206 207with pd.ExcelWriter('//192.168.3.225/Share\石岡工場/ＩＮＶ推進室\機械導入PJ/IOT/'+monthly_file) as writer: 208 df_output1.to_excel(writer,sheet_name='D31',index=False) 209 df_output2.to_excel(writer,sheet_name='D32',index=False) 210 df_output3.to_excel(writer,sheet_name='D33',index=False) 211 df_output4.to_excel(writer,sheet_name='D34',index=False) 212 df_output5.to_excel(writer,sheet_name='D35',index=False) 213 df_output6.to_excel(writer,sheet_name='D36',index=False) 214 df_output7.to_excel(writer,sheet_name='D37',index=False)
投稿2021/09/22 23:25
総合スコア16
0
https://teratail.com/questions/358147
上記質問でのコメントで、1日に2度プログラムを実行すると、当日分のデータがダブるという問題がある、とのことでした。
コメント中で質問者さんの提案された、「保存済みのエクセルのコメント行のみ引っ張り、計算されたデータを結合する」方法が筋が良さそうなので、その方向で改善案を考えてみました。
df_output1 = pd.merge(df_m1, df1,how='outer') の行を以下のように変更することで可能です。
python
1try: 2 df_m1 = pd.read_excel(monthly_file,sheet_name=0,dtype={'日付': str}) 3 #df_output1 = pd.merge(df_m1, df1,how='outer') # この行を以下に変更 4 pd.merge(df1, df_m1[['日付','コメント']], on='日付', how='outer') 5except FileNotFoundError: 6 df_output1 = df1.copy() 7 df_output1['コメント'] = None
変更の意味について、以下のサンプルコードで説明します。
エクセルから読み込んだ機械稼働率データのサンプルです。今日は '20210903' です。1回目の集計が行われ、コメント ('yyy') も書き込まれています。
python
1>>> df_m1 2 date a b comment 30 20210901 1 4 zzz 41 20210902 2 5 None 52 20210903 3 6 yyy
同日、2回目のプログラムの実行でデータファイルから集計しなおされたデータです。機械はまだ稼働中のため、今日のデータが更新されて違う値になっています。
python
1>>> df1 2 date a b 30 20210901 1 4 41 20210902 2 5 52 20210903 4 7
この2つを単純に how='outer' でマージすると、今日のデータがダブってしまう、という問題でした。すでにコメントが書き込まれているため、単純に新しい方のデータを残して解決とするわけにはいきません。
python
1>>> pd.merge(df_m1, df1, how='outer') 2 date a b comment 30 20210901 1 4 zzz 41 20210902 2 5 None 52 20210903 3 6 yyy 63 20210903 4 7 NaN
そこでエクセルからはコメントのみを取得すれば良いのではないか、というのが質問者さんのアイデアでした。ただしマージするときの基準となる列（キー列）が必要ですので、日付列は残す必要があるでしょう。
以下のようにすると、エクセルから読み込んだデータから、日付列とコメント列のみを取り出すことができます。
python
1>>> df_m1[['date','comment']] 2 date comment 30 20210901 zzz 41 20210902 None 52 20210903 yyy
これとデータファイルから集計されたデータをマージします。
on='date'で、キー列は日付列であると明示します。共通する列は日付列のみになったので、on='date'はなくても自動的に日付列がキー列となるのですが、明示した方が何をしているかがはっきりするので、あえて入れています。
マージ方法は 'outer' のままでOKです。
python
1>>> pd.merge(df_m1[['date','comment']], df1, on='date', how='outer') 2 date comment a b 30 20210901 zzz 1 4 41 20210902 None 2 5 52 20210903 yyy 4 7
コメントを残したまま、データがダブルことなく、今日のデータを最新の値に更新できました。
しかしコメントの列が前に来てしまいました。これは単純に、pd.merge()にマージするデータを渡す際の順番を入れ替えれば解決します。
python
1>>> pd.merge(df1, df_m1[['date','comment']], on='date', how='outer') 2 date a b comment 30 20210901 1 4 zzz 41 20210902 2 5 None 52 20210903 4 7 yyy
投稿2021/09/22 16:43
総合スコア1195
あなたの回答
tips
プレビュー
