# coding: utf-8
import re
import pandas as pd
pd.set_option('display.max_colwidth', -1)
#PREPROCESSING
#1)Copy paste only csv data from pastebin to notepad and save it as csv [data.csv].
#2)Open data.csv file in excel convert body field as string field and delete columns with
# empty fields then save file as csv format[cleandata.csv].
#3)Open that cleandata.csv file in notepad and save it as cleandata-utf8.csv as utf-8 format.
df = pd.read_csv("cleandata-utf8.csv")
df = df.drop(['date','smstype','smsflag'],axis=1)
df.head()
df['body'].head()
df['number'].unique()
#by examining all unique message title we found that every sms number column contain bank name
#like SBI in DZ-CBSSBI,TM-SBIINB etc.
#from here we extract all common bank code name in number column carefully
#Main bank code pattern : YESBNK,BOIIND,SBI,AXISBK,HDFCBK,ICICIB,PNB
#Note: look at that NaN number field carefully. It may contain some transaction details
pd.options.display.max_rows
pd.set_option('display.max_colwidth', -1)
df[df['number'].isna()]
#dont worry this is just a remainder of some pay due
#Collect All possible different templates for best accura
#Other problem
#Repeated messages
#all message not show card or account information ,so this information is incomplete.
#Distorted messages like Chq 012740 for INR 1，746
df['Credit/Debit'] = None
df['Available_balance'] = None
df['Bank']= None
df.head()
total_msg = df.shape[0]
print("Total messages:",total_msg)
#regular expression for debit
debit1=r'.*Transfer of.*?\.(\d+).*Fees \(Incl of ST\): Rs.(\d+)'
debit2=r'.*Thank you for using your SBI Debit Card .*for a purchase worth Rs(\d+).(\d+) on POS.*'
debit3=r'.*Dear Customer, break up of consolidated charges for.*: Monthly Service Fee: Rs (\d+).*'
debit4=r'.*Rs (\d+) withdrawn from A/c .* on .* at .* .Avl bal Rs (\d+).(\d+).*'
debit5=r'.*Rs (\d+) withdrawn at .*from A/c .* on .*\.Avl bal Rs (\d+).(\d+).*'
debit6=r'.*Dear Customer, break up of consolidated charges for .*for a/c no. .*: ~Value Added SMS Alert Fee: Rs (\d+) ~Total: Rs (\d+) \(excluding GST\).*'
debit7=r'.*Your A/C .*has a debit by transfer of Rs (\d+).(\d+) on.*Avl Bal Rs (\d+).(\d+).*'
debit8=r'.*Your AC .*Debited INR (\d+).(\d+) on .*Avl Bal INR (.*)\.Plz download Buddy.*'
debit9=r'.*Thank you for using your SBI Debit Card .*for a purchase worth Rs(\d+) on POS.*'
debit10=r'.*OTP is .*for txn of INR (\d+).(\d+) at .*on card ending .*'
debit11=r'.*Rs.(\d+).(\d+) was spent on ur HDFCBank CREDIT Card ending.* on .*at .*Avl bal - Rs.(\d+).(\d+).*'
#regular expression for credit
credit1=r'.*Your AC .*Credited INR (\d+).(\d+) on.*Avl Bal INR (\d+).(\d+).*'
credit2=r'.*Dear HDFCBank cardmember, Payment of Rs (\d+) was credited to your card ending.*'
credit3=r'.*DEAR CARDMEMBER, PAYMENT OF Rs. (\d+).(\d+) RECEIVED TOWARDS YOUR CREDIT CARD ENDING .*THROUGH.*YOUR AVAILABLE LIMIT IS RS.*'
credit4=r'.*Your a/c no.*is credited by Rs.(\d+).(\d+) on .*by a/c linked to mobile.*'
credit5=r'.*Your A/C .*Credited INR (.*) on .*Deposit of Cash at .*ATM. A/c Balance INR (.*)'
#balance
bal1=r'.*Avail Bal in A/c .*: Rs. (\d+).(\d+) CR.*'
for i in range(total_msg):
text = str(df.iloc[i][1])
match1 = re.match(debit1,text)
match2 = re.match(debit2,text)
match3 = re.match(debit3,text)
match4 = re.match(debit4,text)
match5 = re.match(debit5,text)
match6 = re.match(debit6,text)
match7 = re.match(debit7,text)
match8 = re.match(debit8,text)
match9 = re.match(debit9,text)
match10 = re.match(debit10,text)
match11 = re.match(debit11,text)
match12 = re.match(credit1,text)
match13 = re.match(credit2,text)
match14 = re.match(credit3,text)
match15 = re.match(credit4,text)
match16 = re.match(credit5,text)
match17 = re.match(bal1,text)
if match1:
debit = float(match1.group(1))+float(match1.group(2))
df.loc[i,'Credit/Debit']=-debit
print(i)
elif match2:
debit=float(match2.group(1))+float('.'+match2.group(2))
df.loc[i,'Credit/Debit']=-debit
elif match3:
debit=float(match3.group(1))
df.loc[i,'Credit/Debit']=-debit
elif match4:
debit=float(match4.group(1))
bal = float(match4.group(2))+float('.'+match4.group(3))
df.loc[i,'Credit/Debit']=-debit
df.loc[i,'Available_balance']=bal
elif match5:
debit=float(match5.group(1))
bal = float(match5.group(2))+float('.'+match5.group(3))
df.loc[i,'Credit/Debit']=-debit
df.loc[i,'Available_balance']=bal
elif match6:
debit=float(match6.group(2))
df.loc[i,'Credit/Debit']=-debit
elif match7:
debit=float(match7.group(1))+float('.'+match7.group(2))
bal = float(match7.group(3))+float('.'+match7.group(4))
df.loc[i,'Credit/Debit']=-debit
df.loc[i,'Available_balance']=bal
elif match8:
debit=float(match8.group(1))+float('.'+match8.group(2))
bal = (float(re.sub(',','',match8.group(3))))
df.loc[i,'Credit/Debit']=-debit
df.loc[i,'Available_balance']=bal
elif match9:
debit=float(match9.group(1))
df.loc[i,'Credit/Debit']=-debit
elif match10:
debit=float(match10.group(1))+float('.'+match10.group(2))
df.loc[i,'Credit/Debit']=-debit
elif match11:
debit=float(match11.group(1))+float('.'+match11.group(2))
bal = float(match11.group(3))+float('.'+match11.group(4))
df.loc[i,'Credit/Debit']=-debit
df.loc[i,'Available_balance']=bal
elif match12:
credit=float(match12.group(1))+float('.'+match12.group(2))
bal = float(match12.group(3))+float('.'+match12.group(4))
df.loc[i,'Credit/Debit']=credit
df.loc[i,'Available_balance']=bal
elif match13:
credit = float(match13.group(1))
df.loc[i,'Credit/Debit']=credit
df.loc[i,'Available_balance']=bal
elif match14:
credit=float(match14.group(1))+float('.'+match14.group(2))
df.loc[i,'Credit/Debit']=credit
elif match15:
credit=float(match15.group(1))+float('.'+match15.group(2))
df.loc[i,'Credit/Debit']=credit
elif match16:
credit = (float(re.sub(',','',match16.group(1))))
bal = (float(re.sub(',','',match16.group(2))))
df.loc[i,'Credit/Debit']=credit
df.loc[i,'Available_balance']=bal
elif match17:
bal = float(match17.group(1))+float('.'+match17.group(2))
df.loc[i,'Available_balance']=bal
else:
df.loc[i,'Credit/Debit']=None
df.loc[i,'Available_balance']=None
message = str(df.iloc[i][2])
match_bank1=re.match(r'.*SBI.*',message)
match_bank2=re.match(r'.*ICICI.*',message)
match_bank3=re.match(r'.*HDFC.*',message)
match_bank4=re.match(r'.*PNB.*',message)
match_bank5=re.match(r'.*BOI.*',message)
match_bank6=re.match(r'.*YESBNK.*',message)
match_bank7=re.match(r'.*AXIS.*',message)
if(match_bank1):
df.loc[i,'Bank']='SBI'
elif(match_bank2):
df.loc[i,'Bank']='ICICI'
elif(match_bank3):
df.loc[i,'Bank']='HDFC'
elif(match_bank4):
df.loc[i,'Bank']='PNB'
elif(match_bank5):
df.loc[i,'Bank']='BOI'
elif(match_bank6):
df.loc[i,'Bank']='YESBNK'
elif(match_bank7):
df.loc[i,'Bank']='AXIS'
df
# Total amount of transaction (debit + credit) for different types of accounts
df2 = df.copy()
df2['Credit/Debit'].replace('None', 0, inplace=True)
df2['Credit/Debit'] = df2['Credit/Debit'].abs()
df2.groupby(['user_id','Bank'])['Credit/Debit'].sum()
# Account Balance for different account best recognise by message with date.
# Here Date of message are encoded so little problem in arrange message according to date.
# Some account not have their Aval Balance message .