ExcelのデータをDataFrameに入れる
まずはExcelのデータをDataFrameに入れます。
|
1 2 3 4 5 6 7 |
inputfile = '月謝作成.xlsx' df = read_excel(inputfile, sheet_name='今月の購入') mei = read_excel(inputfile, sheet_name='データ') #いらない要素を落とす mei = mei.drop(['年齢', 'レベル'], axis=1) df = df.drop(['用具番号', '数量'], axis=1) |
月謝作成.xlsxの「今回の購入」シートに入力表が入っています。
そして、Excelの参照で使ったので、もういらない列を削除しておきます。
名簿と入力表の結合
合計金額を求めるのですが、まずは名簿と入力表を結合します。
|
1 2 |
#結合 df = merge(mei, df, on=['受講者番号'], how='outer') |
受講者番号をキーに、外部結合をします。
それによって、用具を購入していない人の名前も表に入ります。
同じ人の行番号リスト作成
次に、同じ人の用具代を足し合わせるために、同じ人の行番号を格納したリストを作成します。
|
1 2 3 4 5 6 |
#同じ人の行番号リスト作成 a1 = list(df.reset_index().query(f'受講者番号 == {df["受講者番号"][0]}').index) list1 = [[a1[0], a1[-1]]] for i in range(df['受講者番号'].nunique(dropna=False)-1): a1 = list(df.reset_index().query(f'受講者番号 == {df.at[df.index[a1[-1]+1], "受講者番号"]}').index) list1.append([a1[0], a1[-1]]) |
1行目から3行目まで同じ人だった場合、list1 = [[0, 2], [3, …], …]となります。
合計金額を求める
リストが完成したら、人ごとに合計金額を求めていきます。
|
1 2 3 4 5 6 |
#合計を出す df['合計'] = 0 top = 0 for i in [row[0] for row in list1]: df.loc[i, '合計']= df.loc[i, '固定費'] + df.loc[list1[top][0]:list1[top][1], '用具代' ].sum() top = top + 1 |
先ほどのリストを使い、リストに入っている行番号（上の例だと0～2）の用具代を足します。
そしてそこに固定費を足し、合計金額を求めます。
最後に成形
最後に、金額に「円」をつける作業を行い、DataFrameの作業は終了です。
|
1 2 3 4 5 |
# 円をつける df = df.drop(['番号'], axis=1) df['固定費'] = round(df['固定費'].dropna()).astype(int64).astype('str') + '円' df['用具代'] =round(df['用具代'].dropna()).astype(int64).astype('str') + '円' df['合計'] = round(df['合計'].dropna()).astype(int64).astype('str') + '円' |
成形まで終わったらExcelに出力しておきましょう。
|
1 2 3 4 |
# 出力 dt_now = datetime.now() output = f'出力/{dt_now.year}-{dt_now.month+1}.xlsx' df.to_excel(output, sheet_name = f'{dt_now.year}-{dt_now.month+1}', index=False) |
今回は日付を名前に出力フォルダに出力しています。（次の月の分を作るので、そちらを名前に入れています。）
