Pythonのopenpyxlでデータの集計を自動化する方法

Question

前提

初めて質問をさせていただきます。
Pythonのopenpyxlを使用し、データの集計の自動化をしようとしています。

調べてもどうしても上手くプログラムを書くことができず、ご教授頂けますと助かります・・・。

###実施したいこと
◆集計したいデータについて、以下のような記述の複数あるCSVファイルがあります。
ID 色 回数 データ
101 Blue 1 11
101 Blue 2 22
101 Blue 3 33
101 Blue 4 44
101 Blue 5 55
101 pink 1 0.3
101 pink 2 0.4
101 pink 3 0.5
101 pink 4 0.6
101 pink 5 1.4
108 Blue 1 90
108 Blue 2 94
108 Blue 3 67
108 Blue 4 67.3
108 Blue 5 70.3
108 pink 1 3.3
108 pink 2 5.4
108 pink 3 6.5
108 pink 4 7.6
108 pink 5 8.4

CSVファイルから値を取得し、以下のように続くように値を取得、Excelに反映させたいです。
.　 101 101 108 108 #ID
. 　 blue pink　 blue pink　 #色
１　　　11 0.3　 90 3.3 #データ
２　　　22 0.4 67 5.4
３　　　33 0.5 67.3 6.5
４　　　44 0.6 70.3 7.6
５　　　55 1.4 3.3　　8.4
↑回数

該当のソースコード

import openpyxl
import  os

# 新規スプレッドシート作成
wb = openpyxl.Workbook()
sheet = wb.active

n = 0

for filename in os.listdir("."):
    if not filename.endswith('.csv'):
        continue
    
    #ファイルごと列番号を1,2,3...と増やしていく
    n += 1
    text_file = open(filename, 'r')
    
    #行に追加する m =index番号, line = 要素の値
    for m, line in enumerate(text_file):
        sheet.cell(row=m+1, column=n, value=line.strip())
            
　　text_file.close()

wb.save('data.xlsx')    
    

試したこと

for m, line in enumerate(text_file):
sheet.cell(row=m+1, column=n+1, value=line.strip())
を参考に書き換えてみたのですが、上手くいかず・・（あまりにもグチャグチャなので載せるのはやめておきます。。。）

現状では、ファイルのデータが一行に集約されて入っている状態です。。
初歩的な質問で恐縮ですが、何卒よろしくお願いいたします。

ファイルの回数指定についても
for i in range(1,5):
cell = sheet.cell(row=i+1, column=1, value=(i)

としても上手く反映ができずでした。。

Comments

[takutakuya]

回数、ID、色が全て同じデータは存在しない前提でしょうか？

[t_obara]

単にエクセルのpivotテーブルを使うのではなく、あくまでもpythonを利用したいのですか？
pythonを使うにしても、DataFrameのpivotを使えば簡単かと思いますよ。

[strawberry_]

takutakuyaさん
＞回数、ID、色が全て同じデータは存在しない前提でしょうか？
ご閲覧ありがとうございます！
はい、同じデータは存在しない前提となります。

[strawberry_]

t_obaraさん
＞単にエクセルのpivotテーブルを使うのではなく、あくまでもpythonを利用したいのですか？
pythonを使うにしても、DataFrameのpivotを使えば簡単かと思いますよ。

ありがとうございます！
Pythonもしくは、Excel内で処理をしようと思っていました！
一時諦めてExcel上で操作をしようとしていたところ、上手くできなかったので、pivotテーブル、参考にさせていただきます！
pythonのDataFrameのpivotについても、調べてみます！
ありがとうございます・・！

Answer 1

Excelの結果でいいのでしたらcolumnsを複数指定でいいのでは

df3 = df2.pivot(index="回数", columns=["ID", "色"], values="データ")
df3.index.name = None
df3.to_excel("output.xlsx")

参考までに
Excelのピボットテーブル

小計と総計は削除しました

Comments

[strawberry_]

barobaroさん

ありがとうございます・・！
無事に期待通りに値を取得することができました・・・！
大変助かりましたm(._.)m

Answer 2

openpyxlだけでやるのは大変そうなので、pandasを使って作ったものです。

>>> df = pd.read_excel('input.xlsx')
>>> print(df)
     ID     色  回数   データ
0   101  Blue   1  11.0
1   101  Blue   2  22.0
2   101  Blue   3  33.0
3   101  Blue   4  44.0
4   101  Blue   5  55.0
5   101  pink   1   0.3
6   101  pink   2   0.4
7   101  pink   3   0.5
8   101  pink   4   0.6
9   101  pink   5   1.4
10  108  Blue   1  90.0
11  108  Blue   2  94.0
12  108  Blue   3  67.0
13  108  Blue   4  67.3
14  108  Blue   5  70.3
15  108  pink   1   3.3
16  108  pink   2   5.4
17  108  pink   3   6.5
18  108  pink   4   7.6
19  108  pink   5   8.4

ここから加工します。

>>> df2 = df.copy()
>>> df2['ID色'] = df2.apply(lambda x:(x['ID'], x['色']), axis=1)
>>> df3 = df2.pivot(index='回数', columns='ID色', values='データ')
>>> df4 = df3.T.reset_index()
>>> df4.insert(0,'色', df4['ID色'].apply(lambda x: x[1]))
>>> df4.insert(0,'ID', df4['ID色'].apply(lambda x: x[0]))
>>> df4.drop('ID色', axis=1, inplace = True)
>>> df5 = df4.T
>>> print(df5)
       0     1     2     3
回数
ID   101   101   108   108
色   Blue  pink  Blue  pink
1     11   0.3    90   3.3
2     22   0.4    94   5.4
3     33   0.5    67   6.5
4     44   0.6  67.3   7.6
5     55   1.4  70.3   8.4

最後に保存です。

>>> df5.to_excel('output.xlsx', header=False)
>>>

結果のエクセルは以下でした。

Comments

[strawberry_]

ppaulさん、早速ありがとうございます・・・！
openpyxlだけでは難しかったのですね。。
pandasについてかなり知識が浅いので、大変勉強になります。
参考にさせていただきますm(._.)m

Answer 3

BAに大変悩みましたが、迅速にアドバイスいただきました、ppaulさんにさせて頂きました・・！
barobaroさん、ご閲覧頂いた皆様ありがとうございました・・！