pythonを使いエクセルデータから担当毎、商品別に売上を算出して売上明細書を作りたい

Question

前提・実現したいこと

初めての質問です不備があったら申し訳ございません。
エクセルのデータベースから
担当毎の商品の売上をエクセルの明細書へ転記したいと考えています。

pandasを使い担当者ごとの商品別売上をgroupbyで
抽出するところまではできたのですが

できたデータを担当毎でエクセルファイルへ任意の場所へ(明細書)
順に転写していき担当者ごとのファイルを作りたいです。

###参考画像
######データベース

######groupbyで抽出した結果

######明細書の完成イメージ

該当のソースコード

!pip install openpyxl

import pandas as pd
import numpy as np
import openpyxl
import glob

import_file = 'sample_auto02.xlsx'

excel_sheetname01 = '実績管理表'

df_actual = pd.read_excel(import_file, sheet_name = excel_sheetname01)

df_actual.head()

#4/1～6月末までの集計
(df_actual['売上日']>="2020-04-01") & (df_actual['売上日']<"2020-07-01")


#データフレームの角括弧の中に入れる
df_actual[(df_actual['売上日']>="2020-04-01") & (df_actual['売上日']<"2020-07-01")]

#変数に入れ直す
df_actual = df_actual[(df_actual['売上日']>="2020-04-01") & (df_actual['売上日']<"2020-07-01")]

#氏名に加えて商品分類ごとも
df_actual[['氏名','数量','単価（円）','商品分類']].groupby(['氏名','商品分類']).sum()


#こんなイメージでやってみましたがエラーばかり↓

# for i in (氏名を入れたい):
#     df_order_name = df_actual[df_actual['氏名'] == i]
#     df_order_name.to_excel(既存のEXCELのテンプレファイル（明細書）の任意のセルへ書き出したい)


#欲を言えば
#それらを氏名ごとにPDFで書き出しまで出来たらとても嬉しいです...。

試したこと

氏名を事前にunipueで取得してみたのですが
groupbyしたあとだとエラーになってしまいました。

補足情報（FW/ツールのバージョンなど）

jupyterLabを使用しています。

Comments

[aokikenichi]

ご質問内容が分かりません。

すでにフォーマットが決まっている書式のExcelにPythonの集計結果を転記ということでしょうか？
ただ例示いただいた明細書は書式はありますが、担当者から単価の列までは自由なようですので、
Pythonの出力をCSV等に出してExcelで開き、ごそっと明細書に貼り付けるだけでは？

「氏名を事前にunipueで取得してみたのですが
groupbyしたあとだとエラーになってしまいました。」
これと上記と何の関係があるのでしょうか？　これも解決したいのであればエラーコードも記載ください。

[kotaro_szk]

ご対応いただきありがとうございます。
説明不足で申し訳ございませんでした。

すでにフォーマットが決まっている書式のExcelにPythonの集計結果を転記ということでしょうか？
>>はい、おっしゃる通りでございます。
それを担当者名毎にPDF書き出しをしたいと思っていますが
やり方が分からず困っていて質問をしてみました。

ユニークを使った理由は担当者名をPDFのファイル名にしたいと思っていたので
抽出しておくほうがいいのかと思っていました。使わずに済むのであれば
無視していただいて構いません、ややこしくしてしまい申し訳ございませんでした。

[tiitoi]

入力データを作るのが大変なので、画像で添付されている5行程度でいいので、入力データをテキストで質問に転記していただくことはできますか?

[kotaro_szk]

ご連絡ありがとうございます！
このような形でよろしいでしょうか？

売上日 社員番号 氏名 支店 商品分類 商品名 単価（円） 数量 売上金額（円）
2020/1/4 a023 河野 利香 大阪 ボトムス ロングパンツ 7,000 8 56,000
2020/1/5 a003 石崎 和香菜 東京 ボトムス ジーンズ 6,000 10 60,000
2020/1/6 a003 石崎 和香菜 東京 ボトムス ロングパンツ 7,000 10 70,000
2020/1/7 a036 西尾 謙 横浜 ボトムス ロングパンツ 7,000 2 14,000
2020/1/9 a013 宮瀬 尚紀 福岡 トップス ニット 8,000 7 56,000
2020/1/10 a047 上瀬 由和 札幌 アウター ダウン 18,000 7 126,000
2020/1/11 a013 宮瀬 尚紀 福岡 トップス ニット 8,000 5 40,000
2020/1/11 a013 宮瀬 尚紀 福岡 ボトムス ハーフパンツ 3,000 9 27,000
2020/1/11 a047 上瀬 由和 札幌 ボトムス ロングパンツ 7,000 1 7,000
2020/1/16 a036 西尾 謙 横浜 ボトムス ハーフパンツ 3,000 5 15,000
2020/1/19 a003 石崎 和香菜 東京 トップス シャツ 4,000 1 4,000
2020/1/21 a003 石崎 和香菜 東京 アウター ダウン 18,000 1 18,000

[tiitoi]

それで大丈夫です。ありがとうございます。

[kotaro_szk]

こちらのほうこそ
ありがとうございます＞＜；
お手数ですがよろしくお願い致します。

[aokikenichi]

原文にpdfうんぬんは一切ありませんでした。

何が問題点で何をなさりたいのか整理して必要な情報を出しませんと正しい回答を得られる確率が低くなると思われます。

[kotaro_szk]

返信ありがとうございます。

ご指摘、アドバイスいただき助かります。
次回、質問する際は気をつけたいと思います。

ご不便おかけして申し訳ございませんでした。

Answer 1

できたデータを担当毎でエクセルファイルへ任意の場所へ(明細書)

順に転写していき担当者ごとのファイルを作りたいです。

最初にExcelを作る方法ですが、Excelテンプレートと一致するようにDataFrameを作り、to_excel()で位置を指定して書き出せば良いです。

# テンプレートと一致するDataFrame: df_clerkを作る
df_clerk = (
    df_actual[["氏名", "数量", "単価（円）", "商品分類", "売上金額（円）"]]
    .groupby(["氏名", "商品分類"])
    .sum()
)
df_clerk["備考"] = ""  # 明細書欄に備考があったので追加
df_clerk = df_clerk[["数量", "単価（円）", "備考", "売上金額（円）"]]

# テンプレートの読み込み
template_book = openpyxl.load_workbook("template.xlsx")
template_sheets = dict(
    (sheet.title, sheet) for sheet in template_book.worksheets
)

# 担当者ごとにファイルを作成して書き出し
for order_name, df_items in df_clerk.groupby(level=0):
    output_filename = f"{order_name}.xlsx"
    with pd.ExcelWriter(output_filename, engine="openpyxl") as writer:
        writer.book = template_book
        writer.sheets = template_sheets
        df_items.to_excel(
            writer,
            sheet_name="明細書",
            startrow=1,
            startcol=0,
            header=False,
            index=True,
        )

次にPDFにする方法ですがOSがWindowsかつExcelがインストールされているのであればpywin32というパッケージを使えば比較的楽にできます。もしLinuxやmacOSであれば、Excelを経由しないでMarkdownやLaTeX等を使いpandocにより変換する方がよいでしょう。

参考URL:

• PythonでExcelを自動操縦しよう - ExcelファイルをPDFに変換
• PythonでExcelファイルをPDFに変換する方法を現役エンジニアが解説【初心者向け】
• Pandoc - Demos

Comments

[kotaro_szk]

回答いただきありがとうございます！
PDFの方法まで示していただき感謝です！

記載していただいたコードを自分なりに
噛み砕いて理解しながら進めてみたいともいます＞＜！
まる１日試行錯誤してましたが素直に質問してよかったです。

また機会がありましたらよろしくお願い致します。

Answer 2

読み込んだ Excel ファイルを整形して、質問画像と同じ項目の Excel ファイルを出力するところまでできました。

列の幅や色などの見た目の体裁は openpyxl で出力されたファイルを読み込んで、適宜整えてください。

from pathlib import Path

import openpyxl as xl
import pandas as pd
from IPython.display import display

##################################
# 設定項目
##################################
# 抽出期間
start, end = "2020-01-04", "2021-04-01"
# 出力ディレクトリのパス
output_dir = Path("output")

# 保存ディレクトリを作成する。
output_dir.mkdir(exist_ok=True)

# Excel ファイルを読み込む。
df = pd.read_excel("sample.xlsx", sheet_name="実績管理表")

# 指定した期間を抽出する。
df.query(f"'{start}' <= 売上日 <= '{end}'", inplace=True)

# 氏名、商品分類でグループ化
df_by = df.groupby(["氏名", "商品分類"]).sum()


def save_excel(path, df):
    writer = pd.ExcelWriter(path)
    df.to_excel(writer, "実績管理表")
    writer.save()
    display(df)


for name, df_by_name in df_by.groupby(level=0):
    # 保存するパス
    output_path = output_dir / f"{name}.xlsx"
    print(f"Saving result to '{output_path}'")

    # 列 備考 を追加
    df_by_name.insert(0, "備考", "")
    # 列を並び替える
    df_by_name = df_by_name.reindex(columns=["数量", "単価（円）", "備考", "売上金額（円）"])
    # 列名を変更
    df_by_name.rename(columns={"単価（円）": "単価", "売上金額（円）": "価格"}, inplace=True)
    df_by_name.index.rename(["担当者名", "商品名"], inplace=True)

    save_excel(output_path, df_by_name)

output
├── 上瀬由和.xlsx
├── 宮瀬尚紀.xlsx
├── 河野利香.xlsx
├── 石崎和香菜.xlsx
└── 西尾謙.xlsx

Comments

[kotaro_szk]

早速ご対応いただきありがとうございます！

自分の環境でも試してやってみたいと思います！
本当に本当に助かりました！

[tiitoi]

デザインに関しては、Excel で中身が空のテンプレートを作っておいて、Openpyxl で読み込んで、DataFrame の値を入れて保存する形がいいかもしれません。
Openpyxl でゼロから見た目が凝った Excel ファイルを作るのは大変だと思うので。。

[kotaro_szk]

そうなんですね、書き出しすらままならないので
見た目は二の次かもしれないですが可能な限り自分でやってみたいと思います。

また機会がありましたらご教授いただければ幸いです＾＾！