# 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 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.