[Python3]日付ごとにまとめた使用合計個数をExcelの表に入力したい

Question

前提・実現したいこと

python3.8で前回質問させていただいた[Python3]Excel表の中からfilterで取り出したものを日付別にして個数を合計したいのデータを統計表にまとめたいと思っております。

上記リンクのpandasで「備品ごとにいつ何個消費したかのデータ」を以下進捗管理表.xlsx(横軸は2021年度の12月まで)の日付に合わせて自動入力を行いたいのですが，備品名と日付指定をしてopenpyxlでの入力部分がわからないためお時間あればお力添えいただければと思います。

↓進捗管理表.xlsx

該当のソースコード

以下は上記リンクで解決したコードです。

import pandas as pd
import openpyxl as px
df = pd.read_excel('出庫履歴の表.xlsx', sheet_name='Sheet1')
Aall = df[df['備品名'].values == 'aaa'].groupby(['日付']).sum().drop(['備品個数'], axis=1)
print(Aall)

以下は日ごとにまとめたaaaという備品の消費個数の合計です。

            消費個数
日付              
2021-01-07     4
2021-01-08     4
2021-01-12     3
2021-01-13     1

上記の日付とExcel統計表の日付が一致，aaaの行にaaa消費個数の合計を
Aall部分はaaaという備品がいつ何個使用されたかをまとめたデータです。
これを進捗管理表.xlsxの該当セルにあてはめたいのですが，日付と備品名に考慮したセル指定の方法を自分なりに調べてもわかりませんでした。

試したこと

日付のみのデータをAall2としてセル中にそれがあるかどうかを調べようとしたが以下のエラーが出ました。

wb = px.load_workbook('進捗管理表.xlsx')
ws = wb["進捗管理表"]

A3_COL = 1
A3_ROW = 1

A8_COL = 367
A8_ROW = 1000

# 範囲データを順次処理
for row in ws.iter_rows(min_col=A3_COL, min_row=A3_ROW, max_col=A8_COL, max_row=A8_ROW):
    for cell in row:
        # 該当セルの値取得
        cell_value = cell.value
        # 日付がセル中にあるかどうか



wb.close()

    if Aall2 in cell_value:
TypeError: 'in <string>' requires string as left operand, not DataFrame

Answer 1

以下のプログラムを使ってください。

import pandas as pd
import openpyxl
import datetime

wb = openpyxl.load_workbook('進捗管理表.xlsx')
ws = wb['Sheet1']

start_row = 6
end_row = ws.max_row
start_col = 1
end_col = 1

gen = ws.iter_rows(min_row=start_row, max_row=end_row, min_col=start_col, max_col=end_col)
itemdict = dict([(x[0].value, i + start_row) for i, x in enumerate(gen)])

df = pd.read_excel('出庫履歴の表.xlsx', sheet_name='Sheet1')
Aall = df[df['備品名'].values == 'aaa'].groupby(['日付']).sum().drop(['備品個数'], axis=1)
for x in Aall.index:
    ws.cell(row=itemdict['aaa'], column=(x.date()-datetime.date(2021, 1, 1)).days + 3, value=Aall.loc[x,'消費個数'])

wb.save('進捗管理表2.xlsx')

結果は以下です。

Comments

[MM921]

ppaulさん，ご回答ありがとうございます。
以下は追加の質問ですので，お手隙の際やお時間のある時に見ていただけたら幸いです。

ご提案いただいたコード(シート名やExcelファイル名を個人用に直したもの)を試してみたところ，以下のエラーが発生いたしました。

Traceback (most recent call last):
File "C:/Users/xxxx/PycharmProjects/sample2Project/統計表書き込み.py", line 19, in <module>
ws.cell(row=itemdict['aaa'], column=((x.date()-datetime.date(2021, 1, 1)).days + 3), value=Aall.loc[x, '消費個数'])
AttributeError: 'str' object has no attribute 'date'

試してみたコードは以下の通りです。

import pandas as pd
import openpyxl
import datetime

wb = openpyxl.load_workbook('進捗管理表.xlsx')
ws = wb['進捗管理表']

start_row = 6
end_row = ws.max_row
start_col = 1
end_col = 1

gen = ws.iter_rows(min_row=start_row, max_row=end_row, min_col=start_col, max_col=end_col)
itemdict = dict([(x[0].value, i + start_row) for i, x in enumerate(gen)])

df = pd.read_excel('出庫履歴の表.xlsx', sheet_name='Sheet1')
Aall = df[df['備品名'].values == 'aaa'].groupby(['日付']).sum().drop(['備品個数'], axis=1)
for x in Aall.index:
ws.cell(row=itemdict['aaa'], column=((x.date()-datetime.date(2021, 1, 1)).days + 3), value=Aall.loc[x, '消費個数'])

wb.save('進捗管理表.xlsx')

line19のdate部分にstrやintをつけてみたり試してみましたが，この場合以下のエラーが出ました。

Traceback (most recent call last):
File "C:/Users/xxxx/PycharmProjects/sample2Project/統計表書き込み.py", line 19, in <module>
ws.cell(row=itemdict['aaa'], column=str((x.date()-datetime.date(2021, 1, 1)).days + 3), value=Aall.loc[x, '消費個数'])
File "C:\Users\xxxx\AppData\Local\Programs\Python\Python38\lib\site-packages\openpyxl\worksheet\worksheet.py", line 237, in cell
if row < 1 or column < 1:
TypeError: '<' not supported between instances of 'str' and 'int'

[ppaul]

私のところでは、

>>> print(Aall.index)
DatetimeIndex(['2021-01-07', '2021-01-08', '2021-01-12', '2021-01-13'], dtype='datetime64[ns]', name='日付', freq=None)
となります。
もし
Index(['2021-01-07', '2021-01-08', '2021-01-12', '2021-01-13'], dtype='object', name='日付')
だとすると、元のエクセルファイルの型が文字列です。
その場合は、
df = pd.read_excel('出庫履歴の表.xlsx', sheet_name='Sheet1')
Aall = df[df['備品名'].values == 'aaa'].groupby(['日付']).sum().drop(['備品個数'], axis=1)
の間に
df['日付']=pd.to_datetime(df['日付'])
を入れて
df = pd.read_excel('出庫履歴の表.xlsx', sheet_name='Sheet1')
df['日付']=pd.to_datetime(df['日付'])
Aall = df[df['備品名'].values == 'aaa'].groupby(['日付']).sum().drop(['備品個数'], axis=1)
にしてみてください。

[MM921]

ppaulさん，二度にわたって丁寧で初心者にもわかりやすいご回答に深く感謝いたします。

>>>もし
Index(['2021-01-07', '2021-01-08', '2021-01-12', '2021-01-13'], dtype='object', name='日付')
だとすると、元のエクセルファイルの型が文字列です。

print(Aall.index)を試したところ，おっしゃっているような上記の結果(excelファイルの型が文字列)になりました。
最初date部分だけにintをつけて表示しようとしていましたが，dateだけでなくそもそもExcelファイルの型によってAttributeErrorが発生していたんですね。

以上を踏まえて付け加えて実行したら無事，進捗管理表.xlsxの入ってほしいセル部分に個数表示されていました！

今回貴重なお時間を頂き非常に勉強になりました，ありがとうございました！