Q&A
> なお添付写真は、自分で作成したダミーの顧客管理データ等です。
画像ですと回答者がデータとして使えません。また添付画像はタイトル部分が見切れていて分かりません。
> 全く反応いたしませんでした。
エクセルファイルが保存されなかった、という意味でしょうか？
日付順に記載している顧客管理データ（売上も計上している）から各営業さんごとの給与明細まで出したくpythonを勉強しております。
今現在下記の項目に取り組んでおります。
データを担当ごとに給与計算表テンプレートに転記したい。転記する項目は、担当名、売上金額です。その際、担当名と歩合率でグループ化しテンプレートに転記をしたい。
流れといたしましては、
➀元のデータから、特定の項目のみを抽出し、excelに転記致します(*の部分です。)
➁上記で、転記したデータをグループ分けし、その後テンプレートに出力する。
上記となります。
お忙しいとは思いますが、助けていただきたく思います。
何卒よろしくお願い致します。
###➀エクセルの特定の文字でマークした行だけをPythonで転記
import openpyxl wb = openpyxl.load_workbook("売上管理表.xlsx") ws = wb["売上管理表"] bikou_rows = [] for row in ws.iter_rows(min_row=4): if row[16].value == "*" or row[16].value == "備考": bikou_rows.append(row) ws2 = wb.create_sheet(title="集金済み") for bikou_row in bikou_rows: ws2.append([r.value for r in bikou_row]) wb.save("売上管理表.xlsx")
###出力結果です。
左下に【集金済み】との項目ができ下記のように転記ができました。
顧客コード 日付 顧客名 担当者名(クローザー) 担当者名2(アポインター) 〒 住所1 工事内容 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 備考 1 2021-06-07 0:00:00 a1 近藤 阿部 千葉県2-12-14 工事 090-xxxx-xxxx 2200000 0.12 * 5 2021-06-11 0:00:00 a5 近藤 佐藤 沖縄県 工事 94-xxxx-xxxx 1430000 0.12 * 6 2021-06-12 0:00:00 a6 近藤 齋藤 沖縄県 工事 95-xxxx-xxxx 675000 0.11 * 8 2021-06-14 0:00:00 a8 近藤 阿部 沖縄県 工事 97-xxxx-xxxx 908000 0.06 * 近藤 阿部 1900000 0.12
➁に関する質問です。
テンプレートには給与の計算式が入っておりますので、groupbyを使用し担当名 と歩合率をグループ化して転記できれば良いのではないか、と考えております。
自分が行った手順は下記になります。(2パターン試してみました。)
パターンaとbです。
下記が、コード等になります。
ご指南の程何卒よろしくお願い致します。
###転記するためのテンプレートです。
給与計算表 顧客コード 日付 担当者名1(クローザー) 担当者名2(アポインター) 住所 〒 工事内容 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 給与支給額 ¥0 12.0% ¥0 ¥-1,000 11.0% ¥0 ¥-2,000 10.0% ¥0 ¥-3,000 9.0% ¥0 ¥-4,000 8.0% ¥0 ¥-5,000 7.0% ¥0 ¥-6,000 6.0% ¥0 ¥-7,000 5.0% ¥0 ¥-8,000 4.0% ¥0 ¥-9,000 3.0% ¥0 ¥-10,000 2.0% ¥0 ¥-11,000 1.0% ¥0 合計 ¥0
###パターンaのコードです。
ながれです。
・pandasのgroupbyを使用し、グループ分けを行う。
・上記をcsvファイルにし、保存する。
・上記csvファイルより、指定のテンプレートに出力を試みる。
問題点
・指定のファイル名(torihiki)でexcelシートは立ち上がるが、中身に何も記載されていない。
・担当ごとにexcelファイルが作成される予定だが、１つしか作成されない。
・ファイル名(torihiki)を開けると"循環参照"という注意事項が出てくる
・12％の項目がなくなってしまう。
from datetime import datetime from openpyxl import load_workbook import pandas as pd import csv df = pd.read_excel('売上管理表.xlsx',sheet_name = '集金済み') pd.set_option('display.max_columns',None) pd.set_option('display.max_rows',13) print(df) df.groupby(['担当者名2(アポインター)','歩合率']).sum()['金額'].to_csv("./appoint.csv",encoding="shift jis") filepath1 = '給与計算表.xlsx' wb1 = load_workbook(filename=filepath1) ws1 = wb1['給与計算表'] values1=[[cell.value for cell in row1] for row1 in ws1] del values1[0] conv_values1 = list(zip(*values1)) torihiki_list = list(set(conv_values1[0])) for torihiki in torihiki_list: product_data =[] for i in range(len(values1)): if values1[i][0] == torihiki: product_data.append(values1[i]) ws1.title = '給与計算表' for y, row in enumerate(product_data): for x, cell in enumerate(row): ws1.cell(row= y+2, column= x+1, value=product_data[y][x]) now = datetime.now() hiduke = now.strftime('%Y-%m-%d') filename = 'torihiki.xlsx' wb1.save(filename)
###出力結果
Unnamed: 0 Unnamed: 1 顧客コード 日付 顧客名 担当者名(クローザー) 担当者名2(アポインター) \ 0 NaN NaN 1.0 2021-06-07 a1 近藤 阿部 1 NaN NaN 5.0 2021-06-11 a5 近藤 佐藤 2 NaN NaN 6.0 2021-06-12 a6 近藤 齋藤 3 NaN NaN 8.0 2021-06-14 a8 近藤 阿部 4 NaN NaN NaN NaT NaN 近藤 阿部 〒 住所1 工事内容 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 備考 0 NaN 千葉県2-12-14 工事 090-xxxx-xxxx NaN NaN NaN 2200000 0.12 * 1 NaN 沖縄県 工事 94-xxxx-xxxx NaN NaN NaN 1430000 0.12 * 2 NaN 沖縄県 工事 95-xxxx-xxxx NaN NaN NaN 675000 0.11 * 3 NaN 沖縄県 工事 97-xxxx-xxxx NaN NaN NaN 908000 0.06 * 4 NaN NaN NaN NaN NaN NaN NaN 1900000 0.12 NaN >>>
見づらくて申し訳ありません。torihiki というexcelファイルが1つだけ出来上がりました。
###パターンbのコードです。
ながれです。
・pandasのgroupbyを使用し、グループ分けを行う。
・上記をexcelファイルにし、保存する。
・上記excelファイルより、指定のテンプレートに出力を試みる。
問題点
・エラーコードが出てしまう。
・担当者(アポインター)3名の不明なファイルが出来上がる。
from datetime import datetime from openpyxl import load_workbook import pandas as pd import csv df = pd.read_excel('売上管理表.xlsx',sheet_name = '集金済み') pd.set_option('display.max_columns',None) pd.set_option('display.max_rows',13) print(df) df.groupby(['担当者名2(アポインター)','歩合率']).sum()['金額'].to_excel("./appoint.xlsx") filepath1 = './appoint.xlsx' filepath2 = '給与計算表.xlsx' wb1 = load_workbook(filename=filepath1) ws1 = wb1['Sheet1'] wb2 = load_workbook(filename=filepath2) ws2= wb2['給与計算表'] values1=[[cell.value for cell in row1] for row1 in ws1] del values1[0] conv_values1 = list(zip(*values1)) torihiki_list = list(set(conv_values1[0])) for torihiki in torihiki_list: product_data =[] for i in range(len(values1)): if values1[i][0] == torihiki: product_data.append(values1[i]) ws2.title = 'torihiki' for y, row in enumerate(product_data): for x, cell in enumerate(row): ws1.cell(row= y+2, column= x+1, value=product_data[y][x]) now = datetime.now() hiduke = now.strftime('%Y-%m-%d') filename = torihiki wb2.save(filename)
###出力結果
===== Unnamed: 0 Unnamed: 1 顧客コード 日付 顧客名 担当者名(クローザー) 担当者名2(アポインター) \ 0 NaN NaN 1.0 2021-06-07 a1 近藤 阿部 1 NaN NaN 5.0 2021-06-11 a5 近藤 佐藤 2 NaN NaN 6.0 2021-06-12 a6 近藤 齋藤 3 NaN NaN 8.0 2021-06-14 a8 近藤 阿部 4 NaN NaN NaN NaT NaN 近藤 阿部 〒 住所1 工事内容 携帯電話 固定電話 メールアドレス 割引金額 金額 歩合率 備考 0 NaN 千葉県2-12-14 工事 090-xxxx-xxxx NaN NaN NaN 2200000 0.12 * 1 NaN 沖縄県 工事 94-xxxx-xxxx NaN NaN NaN 1430000 0.12 * 2 NaN 沖縄県 工事 95-xxxx-xxxx NaN NaN NaN 675000 0.11 * 3 NaN 沖縄県 工事 97-xxxx-xxxx NaN NaN NaN 908000 0.06 * 4 NaN NaN NaN NaN NaN NaN NaN 1900000 0.12 NaN Traceback (most recent call last): File "C:\Users\wildl\Desktop\新・給与関係\appointer.py", line 62, in <module> wb2.save(filename) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\workbook\workbook.py", line 392, in save save_workbook(self, filename) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\writer\excel.py", line 293, in save_workbook writer.save() File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\writer\excel.py", line 275, in save self.write_data() File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\site-packages\openpyxl\writer\excel.py", line 67, in write_data archive.writestr(ARC_APP, tostring(props.to_tree())) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 1802, in writestr with self.open(zinfo, mode='w') as dest: File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 1505, in open return self._open_to_write(zinfo, force_zip64=force_zip64) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 1600, in _open_to_write self.fp.write(zinfo.FileHeader(zip64)) File "C:\Users\wildl\AppData\Local\Programs\Python\Python39\lib\zipfile.py", line 759, in write n = self.fp.write(data) AttributeError: 'NoneType' object has no attribute 'write' >>>
ご指摘の程、何卒宜しくお願い致します。
meg_様
ご返信遅くなり誠に申し訳ありあせん。
エクセルのファイルが出来上がりませんでした。
いろいろと教材や、サイト等を見ながら試行錯誤してはおりますが、テンプレートに出力できない状態です。お忙しいとは思いますが、何卒、ご教授の程宜しくお願い致します。
「過去tに投稿した質問と同じ内容の質問」とのご指摘を受けました。
知らぬこととは故、ご迷惑をおかけして申し訳ありませんでした。
KeyError: '担当者名' のエラーが出るのはデータフレーム(df)が空のデータフレームだからですね。
※コードでは「df_by = df.groupby(["担当者名２", "歩合率"]).sum() 」となっていて、エラーメッセージでは「df_by = df.groupby(["担当者名", "歩合率"]).sum()」となっています。不整合がありますと回答者が混乱しますので今一度ご確認ください。
回答2件
0
まず➀について、ヘッダーも転記したいのですが、出てきませんでした。
転記するためのコードがないので当然転記されません。
下記でどうでしょうか？
Python
1for row in ws.iter_rows(min_row=4): 2 3 if row[16].value == "*" or row[16].value == "備考": 4 bikou_rows.append(row)
【追記】
panndas を使用しグループ分けまでは、できるようになりましたが、そこから先のテンプレートへの記載がうまくいきませんでした。
python
1 2print(df.groupby(['担当者名2(アポインター)','歩合率']).sum()['金額']) 3df.to_csv("./appoint.csv",encoding="shift jis")
上記コードを下記にするとグループ化したデータが保存されるかと思います。(不要な列があれば削除するコードを追加ください)
python
1 2df.groupby(['担当者名2(アポインター)','歩合率']).sum().to_csv("./appoint.csv",encoding="shift jis")
投稿2021/08/25 16:11
編集2021/08/27 05:57総合スコア10762
meg_様へ
素早いご対応、誠にありがとうございます。
for row in ws.iter_rows(min_row=4):の部分でエクセルを４行目から１行ずつ読み込んでいくので、日付の等の部分も読み込み転記できるものかと思っておりました。
ご指摘ありがとうございました。
早速、再度見返し勉強致します。
読み込んではいますが(元のコードだと)その後のif文で条件不一致でbikou_rowsには追加されません。確認ですが質問のコードはご自身で書かれたものですよね？
meg_様へ
上記➀は、教科書や色々なサイト等を調べ、そこに記載してあるものをもとにおそらくこれなら転記できるのではないか、との予測で自分なりに書いてみたものです。ですので、勉強不足で理解不十分なところも多々あります。申し訳ございません。
②についてですが、私なら①で作成したブックを保存 → pandasでそのブックからデータを読み取って加工する、の手順でやってみるかと思います。
meg_様へ
ご返信ありがとうございます。
panndas を使用しグループ分けまでは、できるようになりましたが、そこから先のテンプレートへの記載がうまくいきませんでした。
お忙しいとは思いますが、何卒アドバイスの程よろしくお願い致します。
meg_様へ
頂いたアドバイスを含め色々と試してみました。
上記のパターンｂがあと一歩のように思えるのですが、いかがでしょうか。
ご指摘の程宜しくお願い致します。
0
「全く反応いたしませんでした。」がどういう意味かわかりませんが・・・
ちらっとコードを見た限りでは、2つ目に提示されたコードがかなりおかしいように思います。
for name, date in users.items():というループがありますがusersが定義されていません。- ループ内で関数
make_user_salaraycalを呼んでいますが、定義されていません。 - 関数
make_user_salarycalを定義していますが、どこからも呼び出されていません。 - 上記がタイプミスだとしても、そもそもループ内で関数を呼び出す以前に定義されている関数は1つもありません。
make_user_salaraycal(name, data)という関数呼び出しをしていますが、dataは定義されていません。
なので、普通に考えると「エラーが出力されるという反応」があるようにおもいますので、反応がないのなら実行方法・環境がおかしいのではないでしょうか？
投稿2021/08/11 21:03
編集2021/08/11 23:46
総合スコア657
udon-ken 様
ご返信遅くなり誠に申し訳ありません。
ふあぁいる
ファイル内にエクセルシートが出来上がらなかったという意味でした。
いろいろと至らぬ点が多く誠に申し訳ありませんでした。
あなたの回答
tips
プレビュー
