Python: Generate Expense Report from RBC MasterCard Transaction Data<\/p>\n
For the context read the following two first<\/p>\n
<\/a><\/p>\n <\/a> <\/p>\n <\/p>\n <\/a> <\/a> <\/p>\n <\/p>\n <\/a><\/p>\n After, you have created the merged i.e. combined csv file, you might want to create reports for your purpose. For example, you can calculate how much was spent on Amazon.ca. The code example will show similar how much was spent on Amazon.ca.<\/p>\n Just by changing the variab;e<\/p>\n you will be able to calculate how much was spent on other places such as BestBuy, Ebay or similar. The code is case sensitive; Hence, you need to provide the value exactly for the above mentioned variable.<\/p>\n Statements such as dollar, no_dol_sign_dollar, you might want to use print for them. This code was written in Jupyter Notebook and then was exported to .py file.<\/p>\n <\/em> *** . *** *** . *** . *** . *** Blog<\/strong>: http:\/\/Bangla.SaLearningSchool.com<\/a>, http:\/\/SitesTree.com<\/a><\/em> Facebook Groups\/Forums to discuss (Q & A): <\/strong><\/em> Get access to courses on Big Data, Data Science, AI, Cloud, Linux, System Admin, Web Development and Misc. related. Also, create your own course to sell to others. <\/em>http:\/\/sitestree.com\/training\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":" Python: Generate Expense Report from RBC MasterCard Transaction Data For the context read the following two first Python: Read RBC Canada: Mastercard PDF Statement Transaction Data into CSV file By Sayed Ahmed medium.com Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time By Sayed Ahmed medium.com After, you have … <\/p>\nPython: Read RBC Canada: Mastercard PDF Statement Transaction Data into CSV file<\/a><\/h2>\n
By Sayed Ahmed<\/a><\/h3>\n
\n<\/a> <\/p>\nmedium.com<\/a><\/h4>\n
\n<\/a><\/p>\nPython: Merge Multiple csv files into one to facilitate reporting on transaction data over time<\/a><\/h2>\n
By Sayed Ahmed<\/a><\/h3>\n
\n<\/a> <\/p>\nmedium.com<\/a><\/h4>\n
spend_calculation_for = \u2018AMAZON.CA<\/a>\u2019;<\/pre>\n
#!\/usr\/bin\/env python\n# coding: utf-8# In[1]:import os;\nimport glob;\nimport pandas as pd;\ndata_folder = \u2018.\/\u2019;\nos.chdir(data_folder);# In[2]:Spend_calculation_for = \u2018AMAZON.CA<\/a>\u2019;# In[3]:df = pd.read_csv(\u2018to_process_rbc_mastercard_data_combined.csv\u2019)\ndf.head(3)# In[4]:transactions = df[ df[\u20182\u2019].str.contains(Spend_calculation_for) ]# In[5]:dollar = transactions[0:50][\u20183\u2019]\ndollar = sorted(dollar)\ndollar# In[6]:no_dol_sign_dollar = [x.strip(\u201c$\u201d) for x in dollar]\nno_dol_sign_dollar# In[7]:no_dol_sign_dollar = [float(x.replace(\u201c-$\u201d, \u2018-\u2019)) for x in no_dol_sign_dollar]\nno_dol_sign_dollar# In[8]:import math\ntotal_spent = sum(no_dol_sign_dollar)\n#sum = sum + [ float(x[1:]) for x in aliexpress_dollar ]\ntotal_spent# In[9]:# just spent without refund\npos_no_dol_sign_dollar = [ x if x > 0 else 0 for x in no_dol_sign_dollar]\npos_no_dol_sign_dollar[0:10]# In[10]:spent = sum(pos_no_dol_sign_dollar)\nspent# In[11]:# just spent without refund\nneg_no_dol_sign_dollar = [ x if x < 0 else 0 for x in no_dol_sign_dollar]\n#if (len(neg_no_dol_sign_aliexpress_dollar)) > 0:\n# neg_no_dol_sign_aliexpress_dollar[-1], neg_no_dol_sign_aliexpress_dollar[-2], neg_no_dol_sign_aliexpress_dollar[-3]\nneg_no_dol_sign_dollar# In[12]:got_refund = sum(neg_no_dol_sign_dollar);\ngot_refund# In[14]:total_spent = spent \u2014 abs(got_refund);\ntotal_spent# In[ ]:<\/pre>\n
\n***<\/strong><\/em>
\nNote: Older short-notes from this site are posted on Medium: <\/strong><\/em>https:\/\/medium.com\/@SayedAhmedCanada<\/a><\/p>\n
\nSayed Ahmed<\/strong>
\n<\/em>
\nBSc. Eng. in Comp. Sc. & Eng. (BUET)<\/strong><\/em>
\nMSc. in Comp. Sc. (U of Manitoba, Canada)<\/strong><\/em>
\nMSc. in Data Science and Analytics (Ryerson University, Canada)<\/strong><\/em>
\nLinkedin<\/strong>: https:\/\/ca.linkedin.com\/in\/sayedjustetc<\/a>
\n<\/em><\/p>\n
\nTraining Courses: <\/strong>http:\/\/Training.SitesTree.com<\/a> <\/em><\/p>\n
\nhttps:\/\/www.facebook.com\/banglasalearningschool<\/a>
\nhttps:\/\/www.facebook.com\/justetcsocial<\/a><\/p>\n