Q&A
画像ではなく、読み込める形でサンプルデータを載せてください。
エクセルで範囲指定してコピーしたものを質問に貼り付けたもので十分です。
日付順に記載している顧客管理データ（売上も計上している）から各営業さんごとに集計
したくpythonを勉強しております。
流れといたしましては、
➀売り上げを計上しているエクセルから各営業担当さんごとに売り上げを集計したい
↓
➁各営業担当さんごとの集計を歩合率を計算するためのエクセルシートに歩合率毎に振り分けて記載した
↓
➂上記➁で計算したものを給与明細のテンプレートに記載したい
上記がやりたいこととなります。
毎回の手動での記入をなくしたく奮闘致しております。
まだ、➀の段階でつまずいております。
下記にコード、エラー等を記載いたしますので、何卒宜しくお願い致します。
なお添付写真は、自分で作成したダミーの顧客管理データとうです。
###自作の顧客管理シートです
###エクセルからコピー致しました。行などがかなりずれてしまって見づらくて申し訳ありません。
顧客名がa1,a2... です。
顧客コード 日付 顧客名 担当者名(クローザー) 担当者名2(アポインター) 〒 住所1 工事内容 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 1 2021/6/7 a1 近藤 阿部 千葉県2-12-14 工事 090-xxxx-xxxx ¥2,200,000 12.0% 2 2021/6/8 a2 近藤 阿部 神奈川県2-12-15 工事 91-xxxx-xxxx ¥4,560,000 12.0% 3 2021/6/9 a3 近藤 阿部 北海道 工事 92-xxxx-xxxx ¥1,236,000 12.0% 4 2021/6/10 a4 近藤 田中 沖縄県 工事 93-xxxx-xxxx ¥1,230,000 12.0% 5 2021/6/11 a5 近藤 佐藤 沖縄県 工事 94-xxxx-xxxx ¥1,430,000 12.0% 6 2021/6/12 a6 近藤 齋藤 沖縄県 工事 95-xxxx-xxxx ¥675,000 11.0% 7 2021/6/13 a7 近藤 阿部 沖縄県 工事 96-xxxx-xxxx ¥324,000 10.0% 8 2021/6/14 a8 近藤 阿部 沖縄県 工事 97-xxxx-xxxx ¥908,000 6.0% 9 2021/6/15 a9 近藤 阿部 沖縄県 工事 98-xxxx-xxxx ¥564,000 9.0% 10 2021/6/16 a10 近藤 阿部 沖縄県 工事 99-xxxx-xxxx ¥234,000 8.0%
###上記を保管しているファイル名は「新・給与関係」という名前で、ファイル内は以下のようになっております。split_file というのが担当者枚に分けるコード（教えて頂いた）です。
###上記質問に対し2パターンのご回答を頂きました。自分で実行するとどうしてもエラーが出てしまし、何が原因なのか分かりません。基本的な質問で誠に申し訳ありませんが何卒よろしくお願いいたします。
###各営業担当毎にデータを集計する為のコードです(2パターン教えて頂きました。)
お教えいただいたコード➀
import openpyxl as excel, json in_file = 'matome.xlsx' out_file = 'matome.json' def split_list(): users = read_and_split(in_file) result = {} for name, rows in users.items(): result[name] = calc_user(rows) print(name, result[name]['total']) with open(out_file, "wt") as fp: json.dump(result, fp) def read_and_split(in_file): users = {} sheet = excel.load_workbook(in_file).active for i, row in enumerate(sheet.iter_rows()): #5行目からデータを読み取る if i<4: continue values = [col.value for col in row] name = values[1] if name not in users: users[name] = [] users[name].append(values) return users def calc_user(rows): total = 0 items = [] for row in rows: space,usercode, date, username, name, appointer,zipno, item, kouji, mobile, nazo, mail, cnt, price, per, bikou = row date_s = date.strftime('%m/%d') items.append([date_s, name, item, cnt, price, per]) total += (cnt or 0) * (price or 0) return {'items':items, 'total': total} if __name__ == "__main__": split_list()
###➀の実行後のエラーです。
2 0 3 0 4 0 5 0 6 0 7 0 8 0 9 0 10 0 Traceback (most recent call last): File "C:/Users/hannchou6/Desktop/新・給与関係/salesman.py", line 43, in <module> split_list() File "C:/Users/hannchou6/Desktop/新・給与関係/salesman.py", line 11, in split_list result[name] = calc_user(rows) File "C:/Users/hannchou6/Desktop/新・給与関係/salesman.py", line 36, in calc_user date_s = date.strftime('%m/%d') AttributeError: 'NoneType' object has no attribute 'strftime'
###各営業担当毎にデータを集計する為のコードです。
教えて頂いたコード➁
import pandas as pd in_file = 'matome.xlsx' df = pd.read_excel(in_file) print(df) print(df.groupby('担当者名2(アポインター)').sum()['金額'])
###➁の実行後のエラーです。
Empty DataFrame Columns: [] Index: [] Traceback (most recent call last): File "C:\Users\hannchou6\Desktop\新・給与関係\split_list.py", line 7, in <module> print(df.groupby('担当者名2(アポインター)').sum()['金額']) File "C:\Users\hannchou6\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\frame.py", line 6717, in groupby return DataFrameGroupBy( File "C:\Users\hannchou6\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\groupby\groupby.py", line 560, in __init__ grouper, exclusions, obj = get_grouper( File "C:\Users\hannchou6\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\groupby\grouper.py", line 811, in get_grouper raise KeyError(gpr) KeyError: '担当者名2(アポインター)'
ppaul様
エクセルをコピー致しましたが、ずれが生じてしましい見づらく、なかなか上手く直すことができず大変申し訳ありません。
何卒、ご確認の程宜しくお願い致します。
ppaul様へ
追加質問です。
お忙しいとは思いますが、何卒よろしくお願いいたします。
ppaul 様にご指示頂きました、コードを打ち込んだところ下記のようなエラーが出てしましました。
Empty DataFrame
Columns: []
Index: []
Traceback (most recent call last):
File "C:/Users/hannchou6/Desktop/新・給与関係/split_list.py", line 7, in <module>
print(df.groupby('担当者名2(アポインター)').sum()['金額'])
File "C:\Users\hannchou6\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\frame.py", line 6717, in groupby
return DataFrameGroupBy(
File "C:\Users\hannchou6\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\groupby\groupby.py", line 560, in __init__
grouper, exclusions, obj = get_grouper(
File "C:\Users\hannchou6\AppData\Local\Programs\Python\Python39\lib\site-packages\pandas\core\groupby\grouper.py", line 811, in get_grouper
raise KeyError(gpr)
KeyError: '担当者名2(アポインター)'
調べましたところ「存在しない辞書のキーを参照している」との事ですが、excelには 【担当者名2(アポインター)】と確かにあります。
どのようにすれば、ppaul様の実行結果の様に数値等が得られますでしょうか。
度々の、基本的質問で誠に申し訳ありませんが、再度ご教授ください。
何卒よろしくお願いいたします。
回答3件
0
このような処理では、openpyxlではなくpandasを使った方が遥かに簡単です。
①の処理は以下のように書けます。
python
1import pandas as pd 2 3in_file = 'matome.xlsx' 4df = pd.read_excel(in_file) 5print(df) 6 7print(df.groupby('担当者名2(アポインター)').sum()['金額'])
実行結果
python
1>>> print(df) 2 顧客コード 日付 顧客名 担当者名(クローザー) 担当者名2(アポインター) 〒 ... 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 30 1 2021/6/7 a1 近藤 阿部 NaN ... 090-xxxx-xxxx NaN NaN NaN 2200000 0.12 41 2 2021/6/8 a2 近藤 阿部 NaN ... 91-xxxx-xxxx NaN NaN NaN 4560000 0.12 52 3 2021/6/9 a3 近藤 阿部 NaN ... 92-xxxx-xxxx NaN NaN NaN 1236000 0.12 63 4 2021/6/10 a4 近藤 田中 NaN ... 93-xxxx-xxxx NaN NaN NaN 1230000 0.12 74 5 2021/6/11 a5 近藤 佐藤 NaN ... 94-xxxx-xxxx NaN NaN NaN 1430000 0.12 85 6 2021/6/12 a6 近藤 齋藤 NaN ... 95-xxxx-xxxx NaN NaN NaN 675000 0.11 96 7 2021/6/13 a7 近藤 阿部 NaN ... 96-xxxx-xxxx NaN NaN NaN 324000 0.10 107 8 2021/6/14 a8 近藤 阿部 NaN ... 97-xxxx-xxxx NaN NaN NaN 908000 0.06 118 9 2021/6/15 a9 近藤 阿部 NaN ... 98-xxxx-xxxx NaN NaN NaN 564000 0.09 129 10 2021/6/16 a10 近藤 阿部 NaN ... 99-xxxx-xxxx NaN NaN NaN 234000 0.08 13 14[10 rows x 14 columns] 15>>> 16>>> print(df.groupby('担当者名2(アポインター)').sum()['金額']) 17担当者名2(アポインター) 18佐藤 1430000 19田中 1230000 20阿部 10026000 21齋藤 675000 22Name: 金額, dtype: int64
上の出力の「...」は画面に入らないので表示を省略しているだけで、実際には中身が入っています。
➁各営業担当さんごとの集計を歩合率を計算するためのエクセルシートに歩合率毎に振り分けて記載
は計算式が分からないので、今は示せませんが、計算式さえ分かれば簡単にできるでしょう。
➂上記➁で計算したものを給与明細のテンプレートに記載したい
は、給与明細の仕様が分からないで、今は示せませんが、特に難しいことはないと思います。
多少の時間はかかるかもしれませんがpandasを覚えられた方がトータルの時間は短くて済むと思います。
投稿2021/06/10 15:54
総合スコア24666
ppaul様
本当にご丁寧にお教え頂き、誠にありがとうございます。
すごいです。一流のプログラマーの方は、本当にすごいです。
ppaul様のアドバイスにもありました様にpandasを勉強致します。
今後とも何卒ご教示の程宜しくお願い致します。
お忙し中、お答え頂き誠にありがとうございます。
ppaul 様へ
上記追加質問と重なってしまっていたら誠に申し訳ありません。
記入すべきコメント欄を間違えていると思い 再度こちらに書き直しました。
繰り返しになってしまい誠に申し訳ありませんが何卒、ご教示の程宜しくお願い致します。
ppaul 様に提示して頂いたコードを打ち込み、「saraly.py」として、「matome.xlsx」と同じファイルに保存をし実行致したところ下記のようなエラーが発生致しました。循環インポート というエラーらしいのですが、直し方を教えて頂けますでしょうか。繰り返し質問してしまし誠に申し訳ありませんが何卒宜しくお願い致します。
エラーコード
Traceback (most recent call last):
File "C:\Users\hannchou6\Desktop\新・給与関係\salary.py", line 1, in <module>
import pandas as pd
File "C:\Users\hannchou6\AppData\Local\Programs\Python\Python39\pandas.py", line 4, in <module>
df = pd.read_excel(in_file)
AttributeError: partially initialized module 'pandas' has no attribute 'read_excel' (most likely due to a circular import)
何卒宜しくお願い致します。
0
元のコードの
date, name, item, cnt, price, per = rowだと
画像のエクセル表と変数の数が一致していないので、
下記のコードでは一致させています。
また、5行目からデータを読み取るように修正しました。
import openpyxl as excel, json in_file = 'matome.xlsx' out_file = 'matome.json' def split_list(): users = read_and_split(in_file) result = {} for name, rows in users.items(): result[name] = calc_user(rows) print(name, result[name]['total']) with open(out_file, "wt") as fp: json.dump(result, fp) def read_and_split(in_file): users = {} sheet = excel.load_workbook(in_file).active for i, row in enumerate(sheet.iter_rows()): #5行目からデータを読み取る if i<4: continue values = [col.value for col in row] name = values[1] if name not in users: users[name] = [] users[name].append(values) return users def calc_user(rows): total = 0 items = [] for row in rows: space, usercode, date, username, name, appointer, item, kouji, mobile, nazo, mail, cnt, price, per, bikou = row date_s = date.strftime('%m/%d') items.append([date_s, name, item, cnt, price, per]) total += (cnt or 0) * (price or 0) return {'items':items, 'total': total} if __name__ == "__main__": split_list()
投稿2021/06/10 13:44
編集2021/06/17 15:12
退会済みユーザー
総合スコア0
baitokun 様
ご返信誠にありがとうございます。
五行目以降に
filepath = 'C:/Users/---/matome.xlsm' #顧客管理データは "matome.py"で保存しております。
を差し込めばよろしいでしょうか。
def split_list():
filepath = 'C:/Users/---/matome.xlsm'
users = read_and_split(in_file)
result = {}
上記の様な事でしょうか。
基本的な事で誠に申し訳ありませんがご確認の程宜しくお願い致します。
追記
担当者名 と お客様名はどのように分ければよろしいでしょうか。
両方ともに 'name' でよろしいのでしょうか。
＞五行目以降にfilepath = 'C:/Users/---/matome.xlsm' #顧客管理データは "matome.py"で保存しております。 を差し込めばよろしいでしょうか。
その必要はありません。
（コメントを修正しました）
＞追記 担当者名 と お客様名はどのように分ければよろしいでしょうか。
修正した私のコード時点ですでに分けてあります。
usernameが「顧客名」で、 name,が「担当者名(クローザー）」です。
私のコードを信じてください。
私のコードそのままで実行してエラーが出なければ正しく動いているはずですので。
baitokun様
本当に、本当にありがとうございます。
プロの方は本当にすごいです。
また差し支えなければ何卒宜しくお願い致します。
baitokun様へ
たびたび質問誠に申し訳ございません。
ご提示頂きました、コードを実行致しましたとこと、下記のようなエラーが発生致しました。
「＊」がおかしいとの事ですが、「＊」以外の記号でかけるを表すものはないと思うのですが.....
エラーコードです。
Traceback (most recent call last):
File "C:/Users/hannchou6/Desktop/新・給与関係/baitokun.py", line 42, in <module>
split_list()
File "C:/Users/hannchou6/Desktop/新・給与関係/baitokun.py", line 11, in split_list
result[name] = calc_user(rows)
File "C:/Users/hannchou6/Desktop/新・給与関係/baitokun.py", line 38, in calc_user
total += cnt * price
TypeError: unsupported operand type(s) for *: 'NoneType' and 'int'
何卒、ご教示の程宜しくお願い致します。
修正しました。cntがNoneになる場合ゼロとみなすようにしました。
0
自己解決
再度、エクセルの売上表を作り直し、ppaul様に教えて頂いたコードを実行したら、エラーなく動きました。
なぜエラーが出なくなったのかの原因は不明のままではありますが、次ののステップに移行しようと思います。ありがとうございました。
投稿2021/06/23 13:56
総合スコア15
あなたの回答
tips
プレビュー
