Python/Pandas: How to combine cumsum and cumcount with agg function? -


i have dataframe grouping internal score , issue date (by quarter). want create statistical table includes cumulative counts of number of loans (represented distinct count of loan #), cumulative sum of loan amounts, , sums of actual loss , outstanding principal. cumulative sum , cumulative count should include snapshot of first date until particular point in time. (i.e. cumulative sum of q1 2015 q2 2015, q1 2015 q3 2015, q1 2015 q4 2015, etc.)

sample dataset: 

   loan #   amount issue date tu status list internal score  last actual paid  \ 0   57144  3337.76 2017-04-03              b                            0.0    1   57145  5536.46 2017-04-03              b              c               0.0    2   57160  3443.91 2017-04-03              b              b               0.0    3   57161  1162.79 2017-04-03              b              b               0.0    4   57162  3845.98 2017-04-03              b              b               0.0    5   57163  3441.50 2017-04-03              b              b               0.0    6   57164  2039.96 2017-04-03              b              c               0.0    7   57165  4427.53 2017-04-03              b                            0.0    8   57166  4427.53 2017-04-03              b                            0.0    9   57167  1617.77 2017-04-03              b              b               0.0        outstanding-principal  actual loss   0                3337.76          0.0   1                5536.46          0.0   2                3443.91          0.0   3                1162.79          0.0   4                3845.98          0.0   5                3441.50          0.0   6                2039.96          0.0   7                4427.53          0.0   8                4427.53          0.0   9                1617.77          0.0

i tried this:

container = [] in ['a', 'b', 'c', 'd']:      subdf = df[df['internal score'].str.contains(i)]      # calculate quarterly vintages     subdf.set_index('issue date', inplace=true)     df2 = subdf.groupby(pd.timegrouper('q')).agg({'outstanding-principal': np.sum, 'actual loss': np.sum,                                                   'amount': cumsum, 'loan #': cumcount})     df2['internal score'] =     container.append(df2)  ddf = pd.concat(container)

you can first use groupby , apply cumsum afterwards.

i modified dummy data while changing dates span across quarters make example more clear:

print(df)      loan #  amount      issue date  internal score  outstanding principal   actual loss 0   57144   3337.76     2017-04-03                3337.76                 0.0 1   57145   5536.46     2017-04-03  c               5536.46                 0.0 2   57160   3443.91     2017-04-03  b               3443.91                 0.0 3   57161   1162.79     2017-04-03  b               1162.79                 0.0 4   57162   3845.98     2017-04-03  b               3845.98                 0.0 5   57163   3441.50     2017-07-03  b               3441.50                 0.0 6   57164   2039.96     2017-07-03  c               2039.96                 0.0 7   57165   4427.53     2017-07-03                4427.53                 0.0 8   57166   4427.53     2017-07-03                4427.53                 0.0 9   57167   1617.77     2017-07-03  b               1617.77                 0.0

first, create column containing key identifies quarter , year of given timestamp:

# in case not timestamp df["issue date"] = pd.to_datetime(df["issue date"])  dt = df["issue date"].dt df["quarter"] = dt.strftime("%y").str.cat(dt.quarter.astype(str), " q")  print(df["quarter"])  0    2017 q2 1    2017 q2 2    2017 q2 3    2017 q2 4    2017 q2 5    2017 q3 6    2017 q3 7    2017 q3 8    2017 q3 9    2017 q3 name: quarter, dtype: object

now, aggreagte:

funcs = {'outstanding principal': np.sum,           'actual loss': np.sum,           'amount': np.sum,           'loan #': len}  result = df.groupby(['internal score', "quarter"]).agg(funcs) print(result)                              outstanding principal   amount      actual loss     loan # internal score  quarter                                2017 q2     3337.76                 3337.76     0.0             1                 2017 q3     8855.06                 8855.06     0.0             2              b  2017 q2     8452.68                 8452.68     0.0             3                 2017 q3     5059.27                 5059.27     0.0             2              c  2017 q2     5536.46                 5536.46     0.0             1                 2017 q3     2039.96                 2039.96     0.0             1

and use transform cumsum:

cum_cols = ["amount", "loan #"] cumsums = result.groupby(level="internal score")[cum_cols].transform(lambda x: x.cumsum()) result.loc[:, cum_cols] = cumsums  print(result)                             outstanding principal   amount      actual loss     loan # internal score  quarter                                2017 q2     3337.76                 3337.76     0.0             1                 2017 q3     8855.06                12192.82     0.0             3              b  2017 q2     8452.68                 8452.68     0.0             3                 2017 q3     5059.27                13511.95     0.0             5              c  2017 q2     5536.46                 5536.46     0.0             1                 2017 q3     2039.96                 7576.42     0.0             2

Comments

Post a Comment

Popular posts from this blog

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

c# - Update a combobox from a presenter (MVP) -

i using mvp in project, new in mvp. have 2 comboboxes. when select option in combobox, combobox should filled new data. this action in presenter. view 'view1' in presenter, , introduced combobox1 , combobox2 properties in 'view1', because need 'datasource', 'displaymember', 'valuemember' , 'refresh()' in method below. but, when using pattern, enough send property like public string combobox2 { { return combobox1.selectedvalue.tosstring(); } } into presenter not whole combobox. how can solve problem? public void onselectedindexchangedcombobox1() { if (view1.combobox1.selectedindex == -1) { return; } datatable dt = tools.getdatatable("a path"); var query = (from o in dt.asenumerable() o.field<string>("afield") == farmerview.combobox1.selectedvalue.tostring() orderby o.field<string>("anotherfield") select...
Read more

How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -

i trying put lock on table while writing on table , if happened in between roll-back . trying convert below code lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code? @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening . thanks edit-1 attaching code , doing in jdbcdaoimpl.java , mentioned problem comment above test() . app.java package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applic...
Read more