有表：list_1= [['2016-01-01',1,'King', 1000], ['2016-01-02',1,'King', -200], ['2016-01-03',1,'King', 100], ['2016-01-04',1,'King',-400], ['2016-01-05',1,'King', 200], ['2016-01-06',1,'King', -200], ['2016-01-01',2,'Smith', 1000], ['2016-01-02',2,'Smith', -300], ['2016-01-03',2,'Smith', -600], ['2016-01-04',2,'Smith', 100], ['2016-01-05',2,'Smith', -100]]labels=['a_date','c_id','c_name','c_action']df=pd.DataFrame(list_1,columns=labels)df外： a_date c_id c_name c_action0 2016-01-01 1 King 10001 2016-01-02 1 King -2002 2016-01-03 1 King 1003 2016-01-04 1 King -4004 2016-01-05 1 King 2005 2016-01-06 1 King -2006 2016-01-01 2 Smith 10007 2016-01-02 2 Smith -3008 2016-01-03 2 Smith -6009 2016-01-04 2 Smith 10010 2016-01-05 2 Smith -100需要獲取表：a_date c_id c_name c_amount Balance2016-01-01 1 King 1000 10002016-01-02 1 King -200 8002016-01-03 1 King 100 9002016-01-04 1 King -400 5002016-01-05 1 King 200 7002016-01-06 1 King -200 5002016-01-01 2 Smith 1000 10002016-01-02 2 Smith -300 7002016-01-03 2 Smith -600 1002016-01-04 2 Smith 100 2002016-01-05 2 Smith -100 100所以我需要為每個客戶在每個操作后使用累積金額制作“余額”列。這相當于 SQL 查詢：SELECT *, SUM(c_amount) OVER (PARTITION BY c_id ORDER BY a_date) AS 'Balance'FROM account_actions對于兩個客戶來說，解決方案并不難，可以按c_id劃分表，總結和整合回來。但它應該是10000個客戶的動態解決方案......
1 回答
動漫人物
TA貢獻1815條經驗 獲得超10個贊
正如@Vaishali所評論的那樣，這是 和 。您可能希望確保數據按順序排序，盡管它看起來已經如此：groupbycumsumsort_values
# sort by `c_id` and `a_date`
df = df.sort_values(['c_id','a_date'])
df['balance'] = df.groupby('c_id')['c_action'].cumsum()
輸出：
a_date c_id c_name c_action balance
0 2016-01-01 1 King 1000 1000
1 2016-01-02 1 King -200 800
2 2016-01-03 1 King 100 900
3 2016-01-04 1 King -400 500
4 2016-01-05 1 King 200 700
5 2016-01-06 1 King -200 500
6 2016-01-01 2 Smith 1000 1000
7 2016-01-02 2 Smith -300 700
8 2016-01-03 2 Smith -600 100
9 2016-01-04 2 Smith 100 200
10 2016-01-05 2 Smith -100 100
添加回答
舉報
0/150
提交
取消