{"id":16964,"date":"2020-03-21T15:39:36","date_gmt":"2020-03-21T19:39:36","guid":{"rendered":"http:\/\/bangla.salearningschool.com\/recent-posts\/python-merge-multiple-csv-files-into-one-to-facilitate-reporting-on-transaction-data-over-time\/"},"modified":"2020-03-21T15:39:36","modified_gmt":"2020-03-21T19:39:36","slug":"python-merge-multiple-csv-files-into-one-to-facilitate-reporting-on-transaction-data-over-time","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=16964","title":{"rendered":"Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time"},"content":{"rendered":"

Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time<\/h3>\n

By Sayed Ahmed<\/p>\n

Merge multiple transaction files into one. This is an extension to the article:<\/p>\n

Python: Read RBC Canada: Mastercard PDF Statement Transaction Data into CSV file<\/strong>
\nBy Sayed Ahmed<\/em>medium.com<\/a><\/p>\n

The Code for Merging<\/p>\n

#!\/usr\/bin\/env python\n# coding: utf-8<\/pre>\n
# In[1]:<\/pre>\n
import os\nimport glob\nimport pandas as pd\ndata_folder = \u2018.\/\u2019\nos.chdir(data_folder)<\/pre>\n
# In[2]:<\/pre>\n
extension = \u2018csv\u2019\nall_filenames = [i for i in glob.glob(\u2018*.{}\u2019.format(extension))]<\/pre>\n
# In[ ]:<\/pre>\n
sorted(list(all_filenames))<\/pre>\n
# In[3]:<\/pre>\n
# test : Check the data for each csv files to be combined.\n# do the data align well with each other\nrow_total_count = 0\nfor i in range(0, len(all_filenames)):\nj = i + 1\nfor f in all_filenames[i:j]:\nfile = f\nprint(file)\ndf_s = pd.read_csv(f)\nprint(df_s.shape, f)\n#print(f)\nrow_total_count += df_s.shape[0]\nprint(df_s.head())<\/pre>\n
#row_total_count\n#df_s.head()<\/pre>\n
# In[10]:<\/pre>\n
# keep track of total rows in all files so that you can compare the shape with the final combined data file\nrow_total_count = 0\nfor f in all_filenames:\nfile = f\nprint(file)\ndf_s = pd.read_csv(f, header=None)\nprint(df_s.shape, f)\n#print(f)\nrow_total_count += df_s.shape[0]\n#print(df_s.head())<\/pre>\n
row_total_count\n#df_s.head()<\/pre>\n
# In[15]:<\/pre>\n
#combine all files in the list, axis =0 i.e. one after another\ncombined_csv = pd.concat([pd.read_csv(f, header=None) for f in all_filenames], axis=0)\n# combined_csv.sort_values(\u201cModel Code\u201d, inplace = True)\n# dropping ALL duplicate values\n#combined_csv.drop_duplicates(subset =\u201dModel Code\u201d, keep = False, inplace = True)<\/pre>\n
#export to csv\ncombined_csv.to_csv( \u201crbc_mastercard_data_combined\u201d, index=False, encoding=\u2019utf-8-sig\u2019)<\/pre>\n
# In[16]:<\/pre>\n
combined_csv.shape<\/pre>\n
# In[17]:<\/pre>\n
row_total_count == combined_csv.shape[0]<\/pre>\n
# In[19]:<\/pre>\n
df = pd.read_csv(\u2018rbc_mastercard_data_combined\u2019)\ndf.head(100)<\/pre>\n
# In[ ]:<\/pre>\n
df.shape<\/pre>\n
 Final Output<\/p>\n
\"1*j5ET61-3P-l-GTj2pMhePg.png\"<\/p>\n
Posted On:<\/em><\/strong><\/p>\n
https:\/\/medium.com\/@SayedAhmedCanada\/python-merge-multiple-csv-files-into-one-to-facilitate-reporting-on-transaction-data-over-time-2e4524355d43<\/a>
\n<\/em><\/strong><\/p>\n
Note: Older short-notes from this site are posted on Medium: <\/em><\/strong>https:\/\/medium.com\/@SayedAhmedCanada<\/a><\/p>\n
*** . *** *** . *** . *** . ***<\/p>\n
Sayed Ahmed<\/em><\/strong><\/p>\n
BSc. Eng. in Comp. Sc. & Eng. (BUET)<\/em><\/strong><\/p>\n
MSc. in Comp. Sc. (U of Manitoba, Canada)<\/em><\/strong><\/p>\n
MSc. in Data Science and Analytics (Ryerson University, Canada)<\/em><\/strong><\/p>\n
Linkedin<\/em><\/strong>: <\/em><\/a><\/p>\n
Blog<\/em><\/strong>: <\/em><\/a>, <\/em><\/a><\/p>\n
Training Courses: <\/em><\/strong><\/a><\/p>\n
Facebook Groups\/Forums to discuss (Q & A):<\/em><\/strong><\/p>\n
https:\/\/www.facebook.com\/banglasalearningschool<\/a><\/p>\n
https:\/\/www.facebook.com\/justetcsocial<\/a><\/p>\n
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: Merge Multiple csv files into one to facilitate reporting on transaction data over time By Sayed Ahmed Merge multiple transaction files into one. This is an extension to the article: Python: Read RBC Canada: Mastercard PDF Statement Transaction Data into CSV file By Sayed Ahmedmedium.com The Code for Merging #!\/usr\/bin\/env python # coding: utf-8 … <\/p>\n
Continue reading<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[182],"tags":[],"class_list":["post-16964","post","type-post","status-publish","format-standard","hentry","category---blog","item-wrap"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":17001,"url":"http:\/\/bangla.sitestree.com\/?p=17001","url_meta":{"origin":16964,"position":0},"title":"Python: Ecommerce: Part\u200a\u2014\u200a1: Merge Multiple Supplier Data Files into One File","author":"Sayed","date":"April 18, 2020","format":false,"excerpt":"Python: Ecommerce: Part\u200a\u2014\u200a1: Merge Multiple Supplier Data Files into One File Section: Merge multiple Supplier Data Files All code in one block #!\/usr\/bin\/env python # coding: utf-8 # # Section: Merge multiple Supplier Data Files # # In[1]: # if there is a need to merge multiple files \u2014 use\u2026","rel":"","context":"In "Build Ecommerce Software"","block_context":{"text":"Build Ecommerce Software","link":"http:\/\/bangla.sitestree.com\/?cat=1912"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":17002,"url":"http:\/\/bangla.sitestree.com\/?p=17002","url_meta":{"origin":16964,"position":1},"title":"Python: Ecommerce: Part \u2014 1: Merge Multiple Supplier Data Files into One File","author":"Sayed","date":"April 19, 2020","format":false,"excerpt":"Section: Merge multiple Supplier Data Files All code in one block #!\/usr\/bin\/env python # coding: utf-8# # Section: Merge multiple Supplier Data Files ## In[1]:# if there is a need to merge multiple files \u2014 use this block import os; import glob; import pandas as pd;# supplier data files\/feeds are\u2026","rel":"","context":"In "Build Ecommerce Software"","block_context":{"text":"Build Ecommerce Software","link":"http:\/\/bangla.sitestree.com\/?cat=1912"},"img":{"alt_text":"8112223 Canada Inc. (Justetc)","src":"https:\/\/miro.medium.com\/fit\/c\/80\/80\/0*P_esmjKoJnHlNjFX","width":350,"height":200},"classes":[]},{"id":16965,"url":"http:\/\/bangla.sitestree.com\/?p=16965","url_meta":{"origin":16964,"position":2},"title":"Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time","author":"Sayed","date":"March 21, 2020","format":false,"excerpt":"Python: Merge Multiple csv files into one to facilitate reporting on transaction data over time","rel":"","context":"In "\u09ac\u09cd\u09b2\u0997 \u0964 Blog"","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":16966,"url":"http:\/\/bangla.sitestree.com\/?p=16966","url_meta":{"origin":16964,"position":3},"title":"Python: Generate Expense Report from RBC MasterCard Transaction Data","author":"Sayed","date":"March 21, 2020","format":false,"excerpt":"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\u2026","rel":"","context":"In "\u09ac\u09cd\u09b2\u0997 \u0964 Blog"","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":16968,"url":"http:\/\/bangla.sitestree.com\/?p=16968","url_meta":{"origin":16964,"position":4},"title":"Python: Read Amex (American Express) Canada: Mastercard PDF Statement Transaction Data into CSV file","author":"Sayed","date":"March 26, 2020","format":false,"excerpt":"Python: Read Amex (American Express) Canada: PDF Statement Transaction Data into CSV file By Sayed Ahmed In general, you will get CSV files from your Amex card; However, sometimes you need to download in every 3 or 6 months. Otherwise, you might only get\/request pdf files for transactions (depends on\u2026","rel":"","context":"In "\u09ac\u09cd\u09b2\u0997 \u0964 Blog"","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":76075,"url":"http:\/\/bangla.sitestree.com\/?p=76075","url_meta":{"origin":16964,"position":5},"title":"K-Means Clustering","author":"Sayed","date":"May 18, 2024","format":false,"excerpt":"Click on the images to see them clearly #!\/usr\/bin\/env python coding: utf-8 In[1]: k-means clustering from numpy import unique from numpy import where from sklearn.datasets import make_classification from sklearn.cluster import KMeans from matplotlib import pyplot import numpy as np import pandas as pd import seaborn as sns import matplotlib.pyplot as\u2026","rel":"","context":"In "\u09ac\u09cd\u09b2\u0997 \u0964 Blog"","block_context":{"text":"\u09ac\u09cd\u09b2\u0997 \u0964 Blog","link":"http:\/\/bangla.sitestree.com\/?cat=182"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/bangla.sitestree.com\/wp-content\/uploads\/2024\/05\/image-40.png?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/bangla.sitestree.com\/wp-content\/uploads\/2024\/05\/image-40.png?resize=350%2C200 1x, https:\/\/i0.wp.com\/bangla.sitestree.com\/wp-content\/uploads\/2024\/05\/image-40.png?resize=525%2C300 1.5x"},"classes":[]}],"_links":{"self":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/16964","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=16964"}],"version-history":[{"count":0,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=\/wp\/v2\/posts\/16964\/revisions"}],"wp:attachment":[{"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=16964"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=16964"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bangla.sitestree.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=16964"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}