やりたいこと

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

工事一覧

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が月末残高です。

コード

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

python
1import pandas as pd
2# 工事一覧
3site_list_df = pd.DataFrame([
4    [1, 'A工事', '2018-10-2'],
5    [2, 'B工事', '2018-11-2'],
6    [3, 'C工事', '2018-12-2']
7], columns=['code', 'name', 'complete_date'])
8site_list_df['complete_date'] = pd.to_datetime(site_list_df['complete_date'])
9
10#支払い一覧
11cost_list_df = pd.DataFrame([
12    ['2018/10/10', 1, 100000, '2018/10/31'], 
13    ['2018/10/10', 2, 200000, '2018/10/31'],
14    ['2018/10/10', 3, 400000, '2018/10/31'],
15    ['2018/11/10', 1, 3000000, '2018/11/30'],
16    ['2018/11/10', 2, 4000000, '2018/11/30'],
17    ['2018/11/10', 3, 5000000, '2018/11/30'],
18    ['2018/12/10', 1, 3000000, '2018/12/31'],
19    ['2018/12/10', 2, 2000000, '2018/12/31'],
20    ['2018/12/10', 3, 400000, '2018/12/31'],      
21], columns=['cost_date', 'code', 'amount', 'month_end'])
22
23# 下記のようにpivot_tableを使えば、月毎、工事毎の支払金額（increase）は集計できました。
24df = pd.pivot_table(cost_list_df, index='code',  columns='month_end', aggfunc=np.sum).fillna(0)
25

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

m.ts10806

2019/05/05 05:39 編集

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

退会済みユーザー

2019/05/05 05:39

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

行動規範の内容に同意します

回答1件

ベストアンサー

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

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

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

Python
1def fnc(df):
2    # 工事が完了後の支払いか？
3    finished = (df['complete_date'] <= df['cost_date'])
4    # amountの累積和を求める
5    df['total'] = df['amount'].cumsum()
6    # end列に工事完了後の場合は 0, 工事完了前の場合は amountの累積和を入れる
7    df['end'] =  df['total'].mask(finished, 0)
8    # start列に一つ前のend列の値を入れる
9    df['start'] = df['end'].shift(1, fill_value=0)
10    # increase列にamount列の値を入れる
11    df['increase'] = df['amount']
12    # start列・end列・increase列の値からtransfer列の値を算出する
13    df['transfer'] = df['end'] - df['start'] - df['increase']
14    # データフレームの一部(必用な部分のみ)を戻す
15    return df.loc[:,['name', 'month_end', 'start','increase','transfer','end']]
16
17
18# const_list_df と site_list_df を結合して、'name'毎に関数を呼び出す。
19df = cost_list_df.merge(site_list_df, on='code').groupby('name', group_keys=False).apply(fnc2)
20print(df)
21#    name  month_end   start  increase  transfer     end
22#0  A工事 2018-10-31       0    100000   -100000       0
23#1  A工事 2018-11-30       0    300000   -300000       0
24#2  A工事 2018-12-31       0    300000   -300000       0
25#3  B工事 2018-10-31       0    100000         0  100000
26#4  B工事 2018-11-30  100000    300000   -400000       0
27#5  B工事 2018-12-31       0    300000   -300000       0
28#6  C工事 2018-10-31       0    100000         0  100000
29#7  C工事 2018-11-30  100000    300000         0  400000
30#8  C工事 2018-12-31  400000    300000   -700000       0

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

Python
1df = df.set_index(['name','month_end']).stack().unstack(level='month_end')
2print(df)
3#month_end      2018-10-31  2018-11-30  2018-12-31
4#name
5#A工事  start              0           0           0
6#       increase      100000      300000      300000
7#       transfer     -100000     -300000     -300000
8#       end                0           0           0
9#B工事  start              0      100000           0
10#       increase      100000      300000      300000
11#       transfer           0     -400000     -300000
12#       end           100000           0           0
13#C工事  start              0      100000      400000
14#       increase      100000      300000      300000
15#       transfer           0           0     -700000
16#       end           100000      400000           0