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])),
columns = ['loanid','loanamt','term','mRate','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.