Inverse grouping
Task:List the details of installment loan: current payment amount, current interest, current principal and principal balance.
Python
| 1 | import numpy as np |
| 2 | import pandas as pd |
| 3 | loan_data = pd.read_csv('E:\\txt\\loan.csv',sep='\t') |
| 4 | loan_data['mrate'] = loan_data['Rate']/(100*12) |
| 5 | loan_data['mpayment'] = loan_data['LoanAmt']*loan_data['mrate']*np.power(1+loan_data['mrate'],loan_data['Term']) \ |
| 6 | /(np.power(1+loan_data['mrate'],loan_data['Term'])-1) |
| 7 | loan_term_list = [] |
| 8 | for i in range(len(loan_data)): |
| 9 | tm = loan_data.loc[i]['Term'] |
| 10 | loanid = np.tile(loan_data.loc[i]['LoanID'],tm) |
| 11 | loanamt = np.tile(loan_data.loc[i]['LoanAmt'],tm) |
| 12 | term = np.arange(1,tm+1) |
| 13 | rate = np.tile(loan_data.loc[i]['mrate'],tm) |
| 14 | payment = np.tile(np.array(loan_data.loc[i]['mpayment']),loan_data.loc[i]['Term']) |
| 15 | interest = np.zeros(len(loanamt)) |
| 16 | principal = np.zeros(len(loanamt)) |
| 17 | principalbalance = np.zeros(len(loanamt)) |
| 18 | loan_amt = loanamt[0] |
| 19 | for j in range(len(loanamt)): |
| 20 | interest[j] = loan_amt*loan_data.loc[i]['mrate'] |
| 21 | principal[j] = payment[j] - interest[j] |
| 22 | principalbalance[j] = loan_amt - principal[j] |
| 23 | loan_amt = principalbalance[j] |
| 24 |
loan_data_df = pd.DataFrame(np.transpose(np.array([loanid,loanamt,term,rate,payment,interest,principal,principalbalance])), |
| 25 | |
| loan_term_list.append(loan_data_df) | |
| 26 | loan_term_pay = pd.concat(loan_term_list,ignore_index=True) |
| 27 | print(loan_term_pay) |
It's very troublesome for padas to deal with such inverse grouping.
esProc
| A | ||
| 1 | E:\\txt\\loan.csv | |
| 2 | =file(A1).import@t() | |
| 3 | =A2.derive(Rate/100/12:mRate,LoanAmt*mRate*power((1+mRate),Term)/(power((1+mRate),Term)-1):mPayment) | |
| 4 | =A3.news((t=LoanAmt,Term);LoanID, LoanAmt, mPayment:payment, to(Term)(#):Term, mRate, t* mRate:interest, payment-interest:principal, t=t-principal:principlebalance) |
Using the function of inverse grouping, it is easy to solve the problem of inverse grouping.
