質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.34%

pandasを使って、sumifsのような集計をしたい。

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 1,725

score 812

やりたいこと

下記のように、工事一覧と支払い一覧のデータがあります。

  • 工事一覧
code name complete_date month_end
1 A工事 2018/10/2 2018/10/31
2 B工事 2018/11/2 2018/11/30
3 C工事 2018/12/2 2018/12/31

codeは工事番号、nameは工事名、complete_dateは完成日、month_endは完成日の月末です。

  • 支払い一覧
date code amount month_end
2018/10/10 1 100000 2018/10/31
2018/10/10 2 200000 2018/10/31
2018/10/10 3 400000 2018/10/31
2018/11/10 1 300000 2018/11/30
2018/11/10 2 400000 2018/11/30
2018/11/10 3 500000 2018/11/30
2018/12/10 1 300000 2018/12/31
2018/12/10 2 200000 2018/12/31
2018/12/10 3 400000 2018/12/31

dateは支払日、codeは工事番号、amountは支払金額、month_endは支払日の月末です。

工事が完成するまでは、支払金額は未成工事支出金という勘定科目に記帳します。
月末に、完成した工事について、月末時点の未成工事支出金残高を、完成工事原価に振り替えます。

工事別に、当該月末に振り替えるべき完成工事原価の金額を算出したいです。

今までは、エクセルで管理していたので、sumifs関数を用いて下記のような表を作って集計していました。
表の形は、どうであれ、月毎、工事毎のtransferの金額が分かれば大丈夫です。

  • 未成工事支出金一覧
code name index 2018/10/31 2018/11/30 2018/12/31
1 A工事 start 0 0 0
1 A工事 increase 100000 300000 300000
1 A工事 transfer -100000 -300000 -300000
1 A工事 end 0 0 0
2 B工事 start 0 200000 0
2 B工事 increase 200000 400000 200000
2 B工事 transfer 0 -600000 -200000
2 B工事 end 200000 0 0
3 C工事 start 0 400000 900000
3 C工事 increase 400000 500000 400000
3 C工事 transfer 0 0 -1300000
3 C工事 end 400000 900000 0

startが月初残高、increaseが当月支払金額、transferが完成工事原価振替金額、endが月末残高です。

コード

下記のように途中までは作っています。

import pandas as pd
# 工事一覧
site_list_df = pd.DataFrame([
    [1, 'A工事', '2018-10-2'],
    [2, 'B工事', '2018-11-2'],
    [3, 'C工事', '2018-12-2']
], columns=['code', 'name', 'complete_date'])
site_list_df['complete_date'] = pd.to_datetime(site_list_df['complete_date'])

#支払い一覧
cost_list_df = pd.DataFrame([
    ['2018/10/10', 1, 100000, '2018/10/31'], 
    ['2018/10/10', 2, 200000, '2018/10/31'],
    ['2018/10/10', 3, 400000, '2018/10/31'],
    ['2018/11/10', 1, 3000000, '2018/11/30'],
    ['2018/11/10', 2, 4000000, '2018/11/30'],
    ['2018/11/10', 3, 5000000, '2018/11/30'],
    ['2018/12/10', 1, 3000000, '2018/12/31'],
    ['2018/12/10', 2, 2000000, '2018/12/31'],
    ['2018/12/10', 3, 400000, '2018/12/31'],      
], columns=['cost_date', 'code', 'amount', 'month_end'])

# 下記のようにpivot_tableを使えば、月毎、工事毎の支払金額(increase)は集計できました。
df = pd.pivot_table(cost_list_df, index='code',  columns='month_end', aggfunc=np.sum).fillna(0)

上記まではやってみたのですが、この後、どのようなコードを書けば集計できるのか分かりません。
pd.MultiIndexを使えば良いのかなど、考えてみたのですが、分かりません。
もし、お分かりの方見えましたら、ご教示頂ければ幸いです。
よろしくお願い致します。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • m.ts10806

    2019/05/05 14:37 編集

    回答依頼いただいて申し訳ないのですが、Pythonは全くわかりませんので、今後はなるべく関連タグのスコアの高いユーザーに振っていただきたく。

    キャンセル

  • mulberryfields

    2019/05/05 14:39

    申し訳ありません。承知しましたm(__)m

    キャンセル

回答 1

checkベストアンサー

+1

とりあえずDataFrame.pivot_table()などを使ってDataFrameの形式を見やすく変更するのは後回しにして、まずは各工事毎に 'start''increase''transfer' , 'end' を算出する処理を行いってください。

各工事毎に処理を行うには、DataFrame.groupby()にてループ処理、または DataFrame.groupby().apply() にて関数を呼び出すと良いかと思います。

今回の場合はこんな感じになるでしょうか。

def fnc(df):
    # 工事が完了後の支払いか?
    finished = (df['complete_date'] <= df['cost_date'])
    # amountの累積和を求める
    df['total'] = df['amount'].cumsum()
    # end列に工事完了後の場合は 0, 工事完了前の場合は amountの累積和を入れる
    df['end'] =  df['total'].mask(finished, 0)
    # start列に一つ前のend列の値を入れる
    df['start'] = df['end'].shift(1, fill_value=0)
    # increase列にamount列の値を入れる
    df['increase'] = df['amount']
    # start列・end列・increase列の値からtransfer列の値を算出する
    df['transfer'] = df['end'] - df['start'] - df['increase']
    # データフレームの一部(必用な部分のみ)を戻す
    return df.loc[:,['name', 'month_end', 'start','increase','transfer','end']]


# const_list_df と site_list_df を結合して、'name'毎に関数を呼び出す。
df = cost_list_df.merge(site_list_df, on='code').groupby('name', group_keys=False).apply(fnc2)
print(df)
#    name  month_end   start  increase  transfer     end
#0  A工事 2018-10-31       0    100000   -100000       0
#1  A工事 2018-11-30       0    300000   -300000       0
#2  A工事 2018-12-31       0    300000   -300000       0
#3  B工事 2018-10-31       0    100000         0  100000
#4  B工事 2018-11-30  100000    300000   -400000       0
#5  B工事 2018-12-31       0    300000   -300000       0
#6  C工事 2018-10-31       0    100000         0  100000
#7  C工事 2018-11-30  100000    300000         0  400000
#8  C工事 2018-12-31  400000    300000   -700000       0

ここまでくると後は好みの形式に変形するだけです。

df = df.set_index(['name','month_end']).stack().unstack(level='month_end')
print(df)
#month_end      2018-10-31  2018-11-30  2018-12-31
#name
#A工事  start              0           0           0
#       increase      100000      300000      300000
#       transfer     -100000     -300000     -300000
#       end                0           0           0
#B工事  start              0      100000           0
#       increase      100000      300000      300000
#       transfer           0     -400000     -300000
#       end           100000           0           0
#C工事  start              0      100000      400000
#       increase      100000      300000      300000
#       transfer           0           0     -700000
#       end           100000      400000           0

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/09 17:23

    まさに、こういうことをやりたかったです。
    ありがとうございました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.34%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る