フォルダ内にある複数の.xlsxファイルに対して同じ処理を実行し、末尾に"_集計"を付けて保存したい。

Question

前提・実現したいこと

▪フォルダ内にある複数の.xlsxファイルに対して同じ処理を行いたい。
（プログラミングを初めて2週間の初心者です。初めて質問を投稿するため、質問の仕方が悪いかもしれませんがよろしくお願いいたします。）

現在会社の業務の効率化のために、半期ごとの売上実績のデータから製品の型名ごとに合計数量をまとめて、"元データ名_集計"という名前の.xlsxファイルを生成するというプログラミングを書いています。
現在は元データおよび生成されるデータのファイルパスは.pyファイルと同じ場所に指定しており、処理を行う.xlsxファイルの指定は、input()を使用し、元データ名を記入させるという方法を取っています。
しかし、これでは1件ずつしか処理が行えないことに加え、元データ名の記入ミス等が発生すると考えています。
そのため、以下の内容を実現したいです。

・フォルダ内にあるすべての.xlsxファイルについて同様の処理を行いたい。
・生成前データフォルダ、生成後データフォルダをそれぞれ作り、生成前データフォルダに格納した.xlsxファイルすべてに対して処理を行ったものを生成後データフォルダに保存したい。
・生成後のデータは"元データ名_集計.xlsx"という名前にしたい。

発生している問題・エラーメッセージ

まだ、なにもコーディングを行えていないため、エラーメッセージもありません。

該当のソースコード

import pandas as pd
import openpyxl as px

in_filepath_wo_extention = "./" + input("今回集計したいファイルのファイル名（拡張子抜き）を入れてください。（例：73上_HC_実績）")

in_filepath = in_filepath_wo_extention + '.xlsx'
out_filepath = in_filepath_wo_extention + '_集計.xlsx'

#元データからデータフレームの読み込み
df = pd.read_excel(in_filepath)

#小分類の列から型名ごとにデータを抽出
get_v3 = df.query('小分類.str.contains("C13440-20CU")', engine='python')
get_fusion = df.query('小分類.str.contains("C14440-20UP")', engine='python')
get_fusionbt = df.query('小分類.str.contains("C15440-20UP")', engine='python')
get_spark = df.query('小分類.str.contains("C11440-36U")', engine='python')
get_lightning = df.query('小分類.str.contains("C14120")', engine='python')
get_42U30 = df.query('小分類.str.contains("C11440-42U30")', engine='python')
get_52U30 = df.query('小分類.str.contains("C11440-52U30")', engine='python')
get_vga = df.query('小分類.str.contains("C12741-03")', engine='python')
get_qvga = df.query('小分類.str.contains("C14041-10")', engine='python')
get_imagem = df.query('小分類.str.contains("C9100-23B")', engine='python')
get_imagem1k = df.query('小分類.str.contains("C9100-24B")', engine='python')
get_gemini = df.query('小分類.str.contains("A12801-01")', engine='python')
get_gemini2c = df.query('小分類.str.contains("A12801-10")', engine='python')

#型名ごとに別シートに転記
with pd.ExcelWriter(out_filepath) as writer:
    get_v3.to_excel(writer, sheet_name="C13440-20CU")
    get_fusion.to_excel(writer, sheet_name="C14440-20UP")
    get_fusionbt.to_excel(writer, sheet_name="C15440-20UP")
    get_spark.to_excel(writer, sheet_name="C11440-36U")
    get_lightning.to_excel(writer, sheet_name="C14120-20P")
    get_42U30.to_excel(writer, sheet_name="C11440-42U30")
    get_52U30.to_excel(writer, sheet_name="C11440-52U30")
    get_vga.to_excel(writer, sheet_name="C12741-03")
    get_qvga.to_excel(writer, sheet_name="C14041-10U")
    get_imagem.to_excel(writer, sheet_name="C9100-23B")
    get_imagem1k.to_excel(writer, sheet_name="C9100-24B")
    get_gemini.to_excel(writer, sheet_name="A12801-01")
    get_gemini2c.to_excel(writer, sheet_name="A12801-10")


wb = px.load_workbook(filename = out_filepath)
wb.create_sheet("集計", 0)

sheet1 = wb["集計"]

#「集計」シートに以下の内容を記入
sheet1["B1"] = "型名"
sheet1["C1"] = "数量"
sheet1["B2"] = "C13440-20CU"
sheet1["B3"] = "C14440-20UP"
sheet1["B4"] = "C15440-20UP"
sheet1["B5"] = "C11440-36U"
sheet1["B6"] = "C14120-20P"
sheet1["B7"] = "C11440-42U30"
sheet1["B8"] = "C11440-52U30"
sheet1["B9"] = "C12741-03"
sheet1["B10"] = "C14041-10U"
sheet1["B11"] = "C9100-23B"
sheet1["B12"] = "C9100-24B"
sheet1["B13"] = "A12801-01"
sheet1["B14"] = "A12801-10"

# 各型名の販売数量の合計値を「集計」シートに記入
sheet1["C2"] = sum(get_v3.数量)
sheet1["C3"] = sum(get_fusion.数量)
sheet1["C4"] = sum(get_fusionbt.数量)
sheet1["C5"] = sum(get_spark.数量)
sheet1["C6"] = sum(get_lightning.数量)
sheet1["C7"] = sum(get_42U30.数量)
sheet1["C8"] = sum(get_52U30.数量)
sheet1["C9"] = sum(get_vga.数量)
sheet1["C10"] = sum(get_qvga.数量)
sheet1["C11"] = sum(get_imagem.数量)
sheet1["C12"] = sum(get_imagem1k.数量)
sheet1["C13"] = sum(get_gemini.数量)
sheet1["C14"] = sum(get_gemini2c.数量)

#上記の処理を行ったデータを保存
wb.save(out_filepath)
wb.close()

試したこと

https://watlab-blog.com/2019/07/29/bundle-resize/

こちらの方の記事を参考にしようと試みましたが、処理内容の複雑さが違ったためfor文内にどのように処理を入れればよいかが分からず困っています。

globを使用して、フォルダ内のデータをまとめて読み込むというところまでは理解できたつもりですが、その後読み込んだファイル１つ１つに同じ処理を適用する部分のコーディングの仕方が分からない状況です。

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

pythonのバージョンは3.8.6です。
上記の他にどのような情報を提供すればよいかもわかっていない状況のため、必要な情報があればお申し付けいただけますと幸いです。
何卒よろしくお願い申し上げます。

Comments

[takutakuya]

.xlsxファイルに対する処理自体は提示していただいたコードで問題なくできていて、それと同じ処理を複数のファイルに一括でできるようにしたい、という認識で合ってますでしょうか？

[arumi_k]

takutakya様　ご指摘の通りです。現状のコードでやりたい処理自体はできております。

Answer 1

Pythonのファイルと同じフォルダーにある.xlsxファイルを全て読み込んで、同じ処理をし、ファイル名の末尾に「_集計」を付けて保存するコードです。

import glob
import openpyxl

file_list = glob.glob('*.xlsx')

print(file_list)

for file in file_list:
    wb = openpyxl.load_workbook(file)
    ws = wb["Sheet1"]
    ws["C1"].value = ws["A1"].value + ws["B1"].value
    rename_file = file.replace('.xlsx', '_集計.xlsx')
    wb.save(rename_file)
    print(file,'->',rename_file)

.xlsxファイルへの処理自体はA1セルとB1セルの和をC1セルに追加する単純なものにしています。

追記

pandasも使われていたので、pandasバージョンも作ってみました。
ファイル名をfor文の中で回していること自体は変わっていません。

import glob
import pandas as pd

file_list = glob.glob('*.xlsx')

print(file_list)

for file in file_list:
    df = pd.read_excel(file)
    df['C'] = df['A'] + df['B']

    rename_file = file.replace('.xlsx', '_集計.xlsx')

    df.to_excel(rename_file, index=False)

    print(file, '->', rename_file)

openpyxlとpandasを組み合わせて使われていますが、pandasだけでも実現できそうな感じはします。

for文について

何度も追記してすみません。
for文に関しては説明不要かもしれませんが、念の為。
globでPythonファイルと同じフォルダーにある.xlsxファイルのファイル名をリスト形式で取得しています。
そのリストを上記のコードではfile_listに格納しています。
file_listの中身は下記のようになっています。

['test2.xlsx', 'test1.xlsx']

このfile_listをfor文で使っていますが、for文の中のfileという変数にfile_listにあるファイル名を1つずつ取り出して格納され、for文の中の処理が実行されます。

そのため、file_listにあるファイル名の数だけ処理が実行されます。

不明点があれば、遠慮なくコメントください。

Comments

[arumi_k]

非常に分かりやすくご教示いただきまして誠にありがとうございます。
初心者のためpandasの場合、openpyxlの場合、for文の中身の説明と、丁寧にご教示いただきまして助かりました。

pandasｎバージョンを参考にし処理の中身を全てfor文内にいれることで解決しました。

<h1>ソースコード</h1>
念のため、実行ができたコードを記載しておきます。

import pandas as pd
import openpyxl as px
import glob

files_list = glob.glob("*.xlsx")

print(files_list)

for file in files_list:
df = pd.read_excel(file)
#小分類の列から型名ごとにデータを抽出
get_v3 = df.query('小分類.str.contains("C13440-20CU")', engine='python')
get_fusion = df.query('小分類.str.contains("C14440-20UP")', engine='python')
get_fusionbt = df.query('小分類.str.contains("C15440-20UP")', engine='python')
get_spark = df.query('小分類.str.contains("C11440-36U")', engine='python')
get_lightning = df.query('小分類.str.contains("C14120")', engine='python')
get_42U30 = df.query('小分類.str.contains("C11440-42U30")', engine='python')
get_52U30 = df.query('小分類.str.contains("C11440-52U30")', engine='python')
get_vga = df.query('小分類.str.contains("C12741-03")', engine='python')
get_qvga = df.query('小分類.str.contains("C14041-10")', engine='python')
get_imagem = df.query('小分類.str.contains("C9100-23B")', engine='python')
get_imagem1k = df.query('小分類.str.contains("C9100-24B")', engine='python')
get_gemini = df.query('小分類.str.contains("A12801-01")', engine='python')
get_gemini2c = df.query('小分類.str.contains("A12801-10")', engine='python')

rename_file = file.replace(".xlsx", "_集計.xlsx")

#型名ごとに別シートに転記
with pd.ExcelWriter(rename_file) as writer:
get_v3.to_excel(writer, sheet_name="C13440-20CU")
get_fusion.to_excel(writer, sheet_name="C14440-20UP")
get_fusionbt.to_excel(writer, sheet_name="C15440-20UP")
get_spark.to_excel(writer, sheet_name="C11440-36U")
get_lightning.to_excel(writer, sheet_name="C14120-20P")
get_42U30.to_excel(writer, sheet_name="C11440-42U30")
get_52U30.to_excel(writer, sheet_name="C11440-52U30")
get_vga.to_excel(writer, sheet_name="C12741-03")
get_qvga.to_excel(writer, sheet_name="C14041-10U")
get_imagem.to_excel(writer, sheet_name="C9100-23B")
get_imagem1k.to_excel(writer, sheet_name="C9100-24B")
get_gemini.to_excel(writer, sheet_name="A12801-01")
get_gemini2c.to_excel(writer, sheet_name="A12801-10")

wb = px.load_workbook(filename = rename_file)
wb.create_sheet("集計", 0)

sheet1 = wb["集計"]

#「集計」シートに以下の内容を記入
sheet1["B1"] = "型名"
sheet1["C1"] = "数量"
sheet1["B2"] = "C13440-20CU"
sheet1["B3"] = "C14440-20UP"
sheet1["B4"] = "C15440-20UP"
sheet1["B5"] = "C11440-36U"
sheet1["B6"] = "C14120-20P"
sheet1["B7"] = "C11440-42U30"
sheet1["B8"] = "C11440-52U30"
sheet1["B9"] = "C12741-03"
sheet1["B10"] = "C14041-10U"
sheet1["B11"] = "C9100-23B"
sheet1["B12"] = "C9100-24B"
sheet1["B13"] = "A12801-01"
sheet1["B14"] = "A12801-10"

# 各型名の販売数量の合計値を「集計」シートに記入
sheet1["C2"] = sum(get_v3.数量)
sheet1["C3"] = sum(get_fusion.数量)
sheet1["C4"] = sum(get_fusionbt.数量)
sheet1["C5"] = sum(get_spark.数量)
sheet1["C6"] = sum(get_lightning.数量)
sheet1["C7"] = sum(get_42U30.数量)
sheet1["C8"] = sum(get_52U30.数量)
sheet1["C9"] = sum(get_vga.数量)
sheet1["C10"] = sum(get_qvga.数量)
sheet1["C11"] = sum(get_imagem.数量)
sheet1["C12"] = sum(get_imagem1k.数量)
sheet1["C13"] = sum(get_gemini.数量)
sheet1["C14"] = sum(get_gemini2c.数量)

#上記の処理を行ったデータを保存
wb.save(rename_file)
wb.close()

[arumi_k]

takutakuya様
もう一点追加でご教示いただきたいことがございます。
処理前のファイルを格納しているパスの指定方法と、保存先の指定についてです。

上記のコードでは、.pyファイルと、.xlsxは同じフォルダ内に格納しており、処理実行後に生成されたファイルも同じフォルダ内に生成されるという形になっています。

理想的には以下のような階層構造で、変換前ファイル入れに入れたデータを変換後データに保存するという方法を取りたいです。（.pyファイルを誤って削除してしまうリスク低減のため）
python（フォルダ名）
-----oo.py（pythonフォルダの直下）
|
|--変換前ファイル入れ---test1.xlsx, test2.xlsx
|
|--変換後ファイル入れ---test1_集計.xlsx, test2_集計.xlsx

これを実行することは可能でしょうか。

■試したこと
files_list = glob.glob("./変換前ファイル入れ/")
というふうにして実行してみましたが、PermissionError: [Errno 13] Permission denied: './変換前ファイル入れ/'　というエラーが出てしまいました。
変換前ファイル入れのフォルダのプロパティから読み取り専用のチェックを外そうとしましたが、うまく外れませんでした。

[takutakuya]

私の環境（Mac）では2箇所を変更するだけでできました。

file_list = glob.glob('./変換前ファイル入れ/*.xlsx')

rename_file = file.replace('変換前', '変換後').replace('.xlsx', '_集計.xlsx')

ただ、フォルダーへのアクセス権を「読み出しのみ」にすると、
PermissionError: [Errno 13] Permission denied: './変換後ファイル入れ/test2_集計.xlsx'
とエラーが出たので、「変換後ファイル入れ」フォルダーには書き込み権限を付与していただく必要があると思います。

[takutakuya]

https://itojisan.xyz/trouble/10228/

この辺りの方法で解決できると良いのですが。

[arumi_k]

ありがとうございます！
読み取り専用のチェックを消すことはできなかったのですが、なぜか実行できるようになりました！
参考になりました。ありがとうございます。

[takutakuya]

よかったです^^