Q&A
> range(
どのブック、シートに対してのrange(範囲)？
Excelデータからグラフを複数作成とExcelに表示
pythonで
以下のような元のExcelデータ(同じようなシートが3つある)を縦に結合して新たなファイルを作成しました
名前 往復走 立ち幅跳び ボール投げ 反復横跳び 長座体前屈 体支持持続時間 握力
a 51 83 65 67 65 68 61
b 37 22 63 46 73 79 43
c 60 73 61 15 59 71 5
d 66 79 52 92 80 67 26
e 5 19 36 5 39 44 9
新たなファイル上にグラフを複数作成したいです
関数を使って作ろうとしています
subplotsを使用しないで，一つずつグラフ化したいです
それらをExcel上に横に７つ表示させ，人数分縦に繰り返し表示させたいです
エラーの原因，修正の仕方がわからず困っています
どなたか教えていただけないでしょうか？
発生している問題・エラーメッセージ
UFuncTypeError: ufunc 'add' did not contain a loop with signature matching types (dtype('<U11'), dtype('<U11')) -> dtype('<U11') make_graph_save(third_national_ave,df_row_count0) plt.barh(x_axis,y_axis,tick_label=x_labels) の２か所が指摘されました
該当のソースコード
python
1#各ライブラリを使用可能に 2import openpyxl as op 3import pandas as pd 4 5file_name="example" #保存した元のファイルの名前を入れる 6original_file_path="C:\Users\####\Desktop\Python test\"+file_name+".xlsx" #元のファイルのパス 7excel_sheet1_name="年長" #１つ目のシート名 8excel_sheet2_name="年中" #２つ目のシート名 9excel_sheet3_name="年少" #３つ目のシート名 10new_file_path="C:\Users\####\Desktop\Python test\output\"+file_name+"_after.xlsx" #新しく作るファイル名,"ファイル名_after.xlxs"となる 11 12#各シートを読み込み,input_sheetに代入 13df0=pd.read_excel(original_file_path, sheet_name=excel_sheet1_name,index_col=0) 14input_sheet0=df0[["往復走","立ち幅跳び","ボール投げ","反復横跳び","長座体前屈","体支持持続時間","握力"]] 15df1=pd.read_excel(original_file_path, sheet_name=excel_sheet2_name,index_col=0) 16input_sheet1=df1[["往復走","立ち幅跳び","ボール投げ","反復横跳び","長座体前屈","体支持持続時間","握力"]] 17df2=pd.read_excel(original_file_path, sheet_name=excel_sheet3_name,index_col=0) 18input_sheet2=df2[["往復走","立ち幅跳び","ボール投げ","反復横跳び","長座体前屈","体支持持続時間","握力"]] 19 20combined_df=pd.concat([input_sheet0,input_sheet1,input_sheet2]) #”名前”をインデックスに上記のシートを縦に結合，一つのシートに3つのシートを貼付 21combined_df.to_excel(new_file_path) #結合したファイルを"ファイル名_after.xlxs"としてDesktopにあるPython testのoutputファイルに保存 22 23combined_df_row_count=(combined_df.iloc[:,0]).count() #０列目(Excel上A列目)の人数(総数)を数える,ilocは0から 24 25workbook=op.load_workbook(new_file_path) #openpyxlでExcelファイル読み込み 26worksheet=workbook.worksheets[0] #編集したいシート番号の指定 27 28#平均値を算出，新しく作ったExcelファイルに表示 29from numpy import average 30worksheet.cell(row=combined_df_row_count+3,column=1).value="種目平均" #”種目平均”(文字列)をExcel内に出力 31j=2 32event_ave=[] #種目平均を入れるリスト 33for i in range(0,7): #indexを抜かした列から，0=B列になる 34 worksheet.cell(row=combined_df_row_count+3,column=j).value=average(combined_df.iloc[:,i]).round(1) #各種目平均をExcel内に出力 35 event_ave.append(average(combined_df.iloc[:,i]).round(1)) #各種目の平均を２~８列目の順でリストに追加 36 j+=1 37 38#全国平均をリストに代入 39third_national_ave=[8.5,105,5,13,28.4,58,7] #全国平均を種目順に入力,third=年長,second=年中,first=年少 40second_national_ave=[4,10,7,36,22,17,5] 41first_national_ave=[10,7,32,113,14,45,6] 42#文字列をExcel内に出力 43worksheet.cell(row=combined_df_row_count+4,column=1).value="全国平均(年長)" 44worksheet.cell(row=combined_df_row_count+5,column=1).value="全国平均(年中)" 45worksheet.cell(row=combined_df_row_count+6,column=1).value="全国平均(年少)" 46#全国平均をExcel内に出力 47j=2 48for i in range(0,7): 49 worksheet.cell(row=combined_df_row_count+4,column=j).value=third_national_ave[i] 50 worksheet.cell(row=combined_df_row_count+5,column=j).value=second_national_ave[i] 51 worksheet.cell(row=combined_df_row_count+6,column=j).value=first_national_ave[i] 52 j+=1 53 54#関数，クラスをインポート 55from openpyxl.styles import Font #フォントの書体やサイズ変更の関数 56from openpyxl.styles.alignment import Alignment #セルの文字の配置を調整する関数 57from openpyxl.drawing.image import Image #画像ファイルを挿入するクラス 58 59font=Font(name="メイリオ", size=12) #フォントをメイリオ,文字サイズを12に変更 60#A1からHの最後の行までを格納 61sheet_range=worksheet.iter_rows(min_row=1,min_col=1,max_row=worksheet.max_row,max_col=worksheet.max_column) #指定範囲を格納 62#セルをのフォント，文字サイズを一つずつ変更 63for row in sheet_range: 64 for cell in row: 65 worksheet[cell.coordinate].font=font 66 67#セルの列幅を調整 68for col in ["C","D","E","F"]: 69 worksheet.column_dimensions[col].width=14 70worksheet.column_dimensions["A"].width=16 71worksheet.column_dimensions["B"].width=10 72worksheet.column_dimensions["G"].width=19 73worksheet.column_dimensions["H"].width=9 74 75#すべて(A~H列)を中央寄せ 76for i in range(1,9): 77 for t in range(1,combined_df_row_count+7): 78 worksheet.cell(row=t,column=i).alignment=Alignment(horizontal="center") 79 80import matplotlib.pyplot as plt #matplotlibをpltとして使用可能に 81plt.rcParams["font.family"]="MS Gothic" #日本語表記を可能に 82 83x_labels=["全国平均","園の平均","記録"] #各棒グラフの名前を入力(x軸側に表示用) 84x_axis=[1,2,3] 85 86import numpy as np 87#グラフを作成，保存する関数 88def make_graph_save(grade_national_ave,count_row): 89 for t in range(2,count_row+2): 90 j=2 91 for i in range(0,7): 92 plt.figure(figsize=(5.5,2)) #グラフを描くキャンバスを作成，サイズ指定込み 93 x_labels=np.array(['全国平均','園の平均','記録']) 94 x_axis=np.arange(len(x_labels)) 95 y_axis=np.array([grade_national_ave[i],event_ave[i],worksheet.cell(t,j).value]) # y軸 96 plt.barh(x_axis,y_axis,tick_label=x_labels) 97 plt.title(worksheet.cell(row=1,column=j).value,loc="center") 98 plt.show() 99 plt.savefig("C:\Users\####\Pictures\Saved Pictures\graph_"+worksheet.cell(t,1).value+"_"+worksheet.cell(1,j).value+".png", dpi=100) 100 img=Image("C:\Users\####\Pictures\Saved Pictures\graph_"+worksheet.cell(t,1).value+"_"+worksheet.cell(1,j).value+".png") 101 plt.close() 102 j+=1 103 104#openpyxlで元のExcelファイル(original_file_path)読み込み 105workbook=op.load_workbook(original_file_path) 106 107df_row_count0=(df0.iloc[:,0]).count() #元のExcelファイルのシート1のデータの総数を数える 108df_row_count1=(df1.iloc[:,0]).count() #元のExcelファイルのシート2のデータの総数を数える 109df_row_count2=(df2.iloc[:,0]).count() #元のExcelファイルのシート3のデータの総数を数える 110 111worksheet=workbook.worksheets[0] #操作したいシートを読み込み 112make_graph_save(third_national_ave,df_row_count0) 113 114worksheet=workbook.worksheets[1] #操作したいシートを読み込み 115make_graph_save(second_national_ave,df_row_count1) 116 117worksheet=workbook.worksheets[2] #操作したいシートを読み込み 118make_graph_save(first_national_ave,df_row_count2) 119 120#新しく作ったExcelファイル(new_file_path)にグラフを出力 121combined_df_row_count_for_graph=combined_df_row_count+7 #d少し下に表示したいため+7 122workbook=op.load_workbook(new_file_path) 123j=2 124for t in range(2,combined_df_row_count+2): 125 for i in range(0,7): 126 img=Image("C:\Users\####\Pictures\Saved Pictures\graph_"+worksheet.cell(row=t,column=1).value+"_"+worksheet.cell(row=1,column=i).value+".png") #横に7つのグラフを表示 127 worksheet.add_image(img,(worksheet.cell(row=combined_df_row_count_for_graph,column=j).value)) 128 j+=8 129 combined_df_row_count_for_graph+=13 #グラフ出力の行位置をずらす 130 131workbook.save(new_file_path)
試したこと
関数を使わない方法を試しました
しかし，plt.barh(x_axis,y_axis,tick_label=x_labels)
の部分を指摘され，同じエラーが発生しました
補足情報（FW/ツールのバージョンなど）
最近始めたばかりなので，最新だと思います
windowsを使っています
元のファイルのimpの各シートに対する範囲です
workbook=op.load_workbook(imp)
worksheet=workbook.worksheets[0]
これらで指定しているつもりです
それなら
> worksheet.range(
じゃない？
'Worksheet' object has no attribute 'range'
とエラーになります
for t in range(2,count_sheet+2):
for i in range(0,7):
count_sheetは各シートの総数です
rangeにシート指定をする必要はありますか？
根本的なことですみません
ああそういう言う事か
> df_count0=(df0.iloc[:,1]).count()
この意味を取り違えていたよ
df0はここでいきなり変数が書かれているんだけど
なにが入っているの?(ilocでなんの値を抽出しようとしているの)
あと多分「moji」って’’A,’B’,’C’とかが入ってる？
※ 勘違いの理由はVBAにもセル選択で"range"が存在します
pythonのexcel操作で"range"という書き方はしませんが
df0がてっきりセルの位置が入った情報と認識して
書きこんだわけです。
https://pg-chain.com/python-excel-cell
ちなみにセルの参照なら
シート変数.['セル番地']
シート変数.cell(row=行番号,column=列番号)
だから
わかりづらく申し訳ありません
mojiにはB~Hが入ります
種目名を取得するためです
df0=pd.read_excel(imp, sheet_name=ex_sn1,index_col="名前")
impは元のファイル，ex_sn1はシート名を入れています
ilocを使って，一つのシートにいる人数を数えています
ちょっとここまで整理
> df0=pd.read_excel(imp, sheet_name=ex_sn1,index_col="名前")
[ex_sn1]シートにある列"名前"を取得？なぜ番号じゃない...？
> df_count0=(df0.iloc[:,1]).count()
登録されている列数を取得
> for t in range(2,count_sheet+2):
B列から登録されている列数分ループしたい
> for i in range(0,7):
作りたいキャンバスの数？
> height=[grade_national_ave[i],ave_result[i],worksheet[moji[i]+str(t)].value]
"height"高さとは?Y軸の事？
>second_national_ave[i]
キャンバス番号?
>ave_result[i]
ここでイキナリ"ave_result"変数が出てきているので内容不明
>worksheet[moji[i]+str(t)].value
ここが前回誤解した原因
>worksheet.[moji[i]+str(t)].value
これならまだ意味がわかるんだけど...
> plt.barh(left,height,tick_label=labels)
ここでイキナリ"labels"変数が出てきているので内容不明
"left"左とは?Y軸の事？
値は
せめて
x_axis # X軸
y_axis # Y軸
x_label # X軸ラベル
y_label # Y軸ラベル
col # 列番号
row # 行番号
とかにしてくれ...
あとセルの取得の際、
・番号で指定するのか
・文字で指定するのか
は決めてほしい
・[ex_sn1]シートにある列"名前"を取得？なぜ番号じゃない...？
→これは，”名前”がA1に必ず来るので，”名前”を列名にインデックスを指定しました
列名，１でもよさそうですね
・> df_count0=(df0.iloc[:,1]).count()
登録されている列数を取得
→1行目を除く，２列目(ExcelでいうBの列)の行数を取得です
iloc[:,0]でもいい気がしました
> for t in range(2,count_sheet+2):
B列から登録されている列数分ループしたい
→行数分ループ
> for i in range(0,7):
作りたいキャンバスの数？
→種目が７つなので，グラフを一人につき7個作成したいです
"height"高さとは?Y軸の事？
→Y軸です
leftはX軸
>second_national_ave[i]
キャンバス番号?
→全国平均です
>ave_result[i]
ここでイキナリ"ave_result"変数が出てきているので内容不明
→Excel上にあるデータの各種目の平均が格納されてます
>worksheet[moji[i]+str(t)].value
ここが前回誤解した原因
>worksheet.[moji[i]+str(t)].value
これならまだ意味がわかるんだけど...
→ドットを付けるとinvalid syntaxでエラーになりますよ？
> plt.barh(left,height,tick_label=labels)
ここでイキナリ"labels"変数が出てきているので内容不明
→labelsは各棒グラフの名前を表しています
あとセルの取得の際、
・番号で指定するのか
・文字で指定するのか
→今のところ文字で統一しているつもりです
番号のほうがわかりやすいですかね
わかりづらく大変申し訳ございません
投稿もしくは修正を改めていたします
まず
> →ドットを付けるとinvalid syntaxでエラーになりますよ？
悪かったです。普段cellで取得しているから確認で参考にしたURLが悪かった
思いっきりまちがえてるよねそのURLの情報...
今回みたいなことあるからできれば
・番号で指定するのか
に統一してほしいかな？（文字の変換もいらないからすっきりする）
だいぶソース自体が変わったので再度確認します。
今回みたいなことあるからできれば
・番号で指定するのか
に統一してほしいかな？（文字の変換もいらないからすっきりする）
→私も修正時に、番号で指定したほうが分かりやすかったです
ご意見ありがとうございました
一点質問です
sheet_rangeのところ(書体や大きさを調整してるところ)では、番号指定のやり方がわかりませんでした
これは行、列の番号で指定はできないのでしょうか？
ご確認よろしくお願いいたします
何度も申し訳ありません
結局「番号で指定する」に統一されたのですね
これで正確にコードが読めます。
>df_row_count0=(df0.iloc[:,0]).count() #元のExcelファイルのシート1のデータの総数を数える
A列から登録されている行数分ループしたい
> for t in range(2,count_sheet+2):
※できれば変数名"count_sheet"ではなく"count_row"がいいなか？
例えばcount_sheetに10(行)入っていたとする
そうするとrange(2, 10 + 2) → range(2, 12) → 2行から11行目?
この場合range(2, count_sheet + 1) → range(2, 10 + 1) → range(2, 11) → 2行から10行目
ではないですか？
> for i in range(0,7):
ここは range(0,7) → 0から6の競技種類(7競技)（OK）
グラフに必要な値を各所から集めてきています。(確認の為)
> x_axis=[1,2,3]
> x_labels=["全国平均","園の平均","記録"]
> y_axis=[grade_national_ave[i],event_ave[i],worksheet.cell(row=t,column=i).value] # y軸
問題ないように見られます。
ちなみに略して「worksheet.cell(t,i)」でも大丈夫だから
> plt.barh(x_axis,y_axis,tick_label=x_labels)
横向き棒グラフだよね？ここでエラーまだ発生している？エラーは前と同じ？
> plt.title(worksheet.cell(row=1,column=i).value,loc="center")
タイトル登録の後
plt.show()
しないとグラフ自体描写されないよ？
ちなみに[人数] x [競技数] x [年長、年中、年少]分グラフが作成されるけど
いいんだよね？
>sheet_rangeのところ
例2. 指定したセル範囲を取得する(セル数値)
https://pg-chain.com/python-excel-cell-range
> sheet_range=worksheet["A1":"H"+str(combined_df_row_count+6)] #指定範囲をタプル型で格納
> sheet_range=worksheet.iter_rows(min_row=1, min_col=1, max_row=combined_df_row_count+6, max_col=8)
ちなみに最大範囲は
worksheet.max_row # 最大行数
worksheet.max_column # 最大列数
で取得できます。
※ あくまで保存されている最大なので空白行や空白列が入る事もあるので注意
例えばcount_sheetに10(行)入っていたとする
そうするとrange(2, 10 + 2) → range(2, 12) → 2行から11行目?
この場合range(2, count_sheet + 1) → range(2, 10 + 1) → range(2, 11) → 2行から10行目
ではないですか？
→10だとすると，データが入っている行は2~11行目になりますよね？(最初の行は種目名が入っているため)
2~11行目に入ってるデータを取り出したいので+2をしました
横向き棒グラフだよね？ここでエラーまだ発生している？エラーは前と同じ？
→横向きグラフです．同じエラーが発生しています
plt.show()
しないとグラフ自体描写されないよ？
→グラフを作成と保存をしたいだけなので，plt.show()は省きました
jupyterlab上に表示したいときはplt.show()が必要だと解釈しています
plt.show()がなくてもグラフ作成自体はできると思っています．どうなのでしょうか？
ちなみに[人数] x [競技数] x [年長、年中、年少]分グラフが作成されるけど
いいんだよね？
→いいえ，"全国平均","園の平均","記録(個人の記録)"を棒グラフで3つ表示させたいと思っています
x_axisでgrade_national_aveには学年平均，event_aveには種目平均，worksheet.cell(row=t,column=i).valueには個々の記録が入っています．
> sheet_range=worksheet.iter_rows(min_row=1, min_col=1, max_row=combined_df_row_count+6, max_col=8)
→ありがとうございます
修正いたします
横向き棒グラフだよね？ここでエラーまだ発生している？エラーは前と同じ？
→横向きグラフです．同じエラーが発生しています
このエラーは翻訳すると
UFuncTypeError：ufunc'add 'には、署名が一致するタイプ（dtype（' <U11 '）、dtype（' <U11 '））-> dtype（' <U11 '）のループが含まれていませんでした
になります。
でエラーが発生するタイミングなんだけど
https://spcx8.hatenablog.com/entry/2017/07/17/225226
https://qiita.com/ryo_naka/items/76ca7441eb87c42a9bbc
配列文字列の結合等で発生します。
https://stats.biopapyrus.jp/python/barplot.html
とかでは
> x = np.array(['ERS1', 'ERS2', 'ETR1', 'ETR2', 'EIN4'])
と処理しているのですが、他のURLだとしていない事の方が多い
(私もした覚えがが無い)
一度表示しない事には始まらないのでループも止めて
> import numpy as np
> t = 2 # for t in range(2,count_row+2):
> i = 2 # for i in range(2,9):
> x_labels=np.array(['全国平均','園の平均','記録'])
> x_axis=np.arange(len(x_labels))
> y_axis=np.array([grade_national_ave[i],event_ave[i],worksheet.cell(row=t,column=i).value]) # y軸
でためしてみましょうか？
※ for i in range(2,9): にしたら今度は
third_national_aveの取得の際エラーになりますよ
あと
plt.show()
だけど
https://teratail.com/questions/254492
で回答されているけど「描写を明確にする」役割があります。
この後の話になるんだけど必要にはなってくると思います。
あと今のエラーが回避できてからの話だから書かなかったんだけど
複数だとややこしい事になって
https://punhundon-lifeshift.com/graph_layout
https://py-memo.com/python/matplotlib-graph3/
のようになるんだよ
一度表示されれば判るんだけど
関数の部分を修正いたしました
エラーが消え，グラフの表示ができました
しかし，補足に追加した画像のようなグラフになってしまいます
(ほかのグラフも画像のものと同様なグラフになります)
なぜでしょうか？
subplotsは使わずにグラフを作成したいです
使ったほうが簡単ですかね？
自分でももう一度やってみます
> 関数の部分を修正いたしました
np.arrayが効果ありましたか。でも不思議だバージョンとかでしょうか？
> (ほかのグラフも画像のものと同様なグラフになります)
そうですねそうなるとは判っていたのですが、エラーを解決しないと
「なぜそうなるか」理解できないとおもい伏せていました。
> subplotsは使わずにグラフを作成したいです
ええここまで頑張ったのに使わないのですが...
そうするとはじめの「subplotsで表示した内容をExcelへ画像として登録」からはずれてしまいます。
方法論ですが「新たなExcelにデータを張り付けExcelのグラフ機能を使い表示を行う」でしょうか？
もしそうするのであれば一度この質問を閉じて新たに立て直してください。
その際手動で一度「Excelのグラフ」を作ってみて下さい。
そしてその画像を質問に張り付けておけば回答はすぐつきます。
ええここまで頑張ったのに使わないのですが...
→一度作成したときにうまくできず，subplots を使わない方法を探していました
subplotsを使用する場合，結構修正必要ですよね...
subplotsで作ってみます
自分でも考えますが，
どのようにしていけばよいでしょうか．
subplotsを使用したほうが，早く完成しますかね？
質問ばかりで申し訳ございません
これはアドバイスとして書きますので聞き流されても良いです。
質問者様の中には「この様なグラフを作りたい」というイメージが完全にできているように思います。
これはプログラムする際とても重要です。
今回「subplots」を選ばれましたが多分イメージを具体化するのに手間がかかりすぎている
その為本筋のことができなく感じています。
そうすると先にかきましたが手動で「Excelで一度イメージしたグラフを作成」する事をオススメします。
あとはそのグラフを
「ExcelだからExcelのグラフ機能を使う」
→あとから数値変更等行える。どのような表現ができるかダイレクトに確認できる。
「Pythonだからこそsubplotsを使う」
→変更できない事も利点の一つ。また表現力もsubplotsでしかできない事もある。
またPythonの学習の意味もあるならこちらの方法でしょう。
利点欠点両方に存在します。それを踏まえた上で選択されてはいかがでしょうか？
アドバイス感謝いたします
グラフ作成を一から行いたいと思います
また問題がありましたら，teratail上で質問いたします
機会がありましたら，またご教授よろしくお願いいたします
毎度ご丁寧に返信いただきありがとうございました
同時に，多くのことを学ぶこともできました
誠にありがとうございます
いいえ、一度で回答すると質問者様が「丸投げ質問」しちゃいけないのに
回答者が「丸投げ回答」するのもおかしな話ですから。
意地悪に見えたかもしれませんが経験も学習の内でその機会をなくしたくなく
この様な長い説明となりました。
きちんと作りたいイメージがあることは素晴らしい資質です。頑張ってみて下さい。
あなたの回答
tips
プレビュー
