{"id":431,"date":"2016-07-30T19:03:27","date_gmt":"2016-07-30T19:03:27","guid":{"rendered":"http:\/\/intelligentonlinetools.com\/blog\/?p=431"},"modified":"2016-08-06T13:40:53","modified_gmt":"2016-08-06T13:40:53","slug":"automating-csv-file-reading-and-writing-with-python","status":"publish","type":"post","link":"http:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/","title":{"rendered":"Automating CSV File Reading and Writing with Python"},"content":{"rendered":"<p>Python is widely used programming language in many fields, such as data science, machine learning, web development. It is also a great choice for automation of different computer tasks such as data downloading from websites or data manipulation. Python can open, read or save data in different file formats.<\/p>\n<p>In this post we will look how to use python for extracting data from different CSV files and saving the data in some archive file. This is a typical task that can appear when you download data from one or more websites into different files and then need to combine and save the data in some location that can be database or archive file. <\/p>\n<p>The so-called CSV (Comma Separated Values) format is the most common import and export format for spreadsheets and databases. There is no \u201cCSV standard\u201d, so the format is operationally defined by the many applications which read and write it. [1]<\/p>\n<p>In our sample script the python program goes through specified folder and finds all CSV files.<br \/>\nThe CSV file then is opened and needed data is selected. Here we skip first five header rows.<br \/>\nThe selected data is then saved in one master.<br \/>\nHere is the full source script.<\/p>\n<pre><code>\r\nimport os\r\nimport csv\r\n\r\n## utf8 was used to support international characters\r\n## newline=''  was used because otherwise it will put blank line between lines\r\n## F1, F2 - just field names\r\n## path is the path folders where CSV files with the data are located \r\n \r\nwith open('data.csv', 'w', encoding=\"utf8\", newline='' ) as csvfile: \r\n  fieldnames = ['F1', 'F2']\r\n  writer = csv.DictWriter(csvfile, fieldnames=fieldnames)\r\n  writer.writeheader()\r\n\r\n\r\n  path=\"C:\\\\Users\\\\Owner\\\\Downloads\"             \r\n  list_of_files = {}\r\n  for filename in os.listdir(path):\r\n     # if the element is an CSV file then...\r\n     if filename[-4:] == \".csv\":\r\n         list_of_files[filename] = path + \"\\\\\" + filename \r\n         print   (list_of_files[filename]) \r\n         with open(list_of_files[filename], encoding=\"utf8\" ) as f:\r\n            csv_f = csv.reader(f)\r\n            for i, row in enumerate(csv_f):\r\n               if i > 5 and len(row) > 1 :\r\n                 print(row)\r\n                 writer.writerow({'F1': row[0], 'F2': row[1]})\r\n\r\n<\/code><\/pre>\n<p>In case you want save combined data in database for example MongoDB here is the script source code. The script is using MongoDB in the cloud at MongoLab.  Refer to [2] if you need to install MongoDB on windows system<\/p>\n<pre><code>\r\nimport os\r\nimport csv\r\nfrom pymongo import MongoClient\r\n\r\n\r\n\r\n#To connect using the MongoDB on MongoLab via the standard MongoDB URI:\r\n#mongodb:\/\/<dbuser>:<dbpassword>@xxxxxxx.mlab.com:xxxxx\/database_name\r\n# to learn more about MongoLab visit http:\/\/www.mongolab.com     [3]\r\n# insert your data in the line below for your MongoLab connection string data\r\n\r\nconnection = MongoClient(\"mongodb:\/\/user_name:password@xxx.mlab.com:xxx\/database_name\")\r\n\r\n# insert your data for database_name and collection_name\r\ndb = connection.database_name.collection_name\r\n\r\n\r\npath=\"C:\\\\Users\\\\Owner\\\\Downloads\"             \r\nlist_of_files = {}\r\nfor filename in os.listdir(path):\r\n     # if the element is a csv file then..\r\n     if filename[-4:] == \".csv\":\r\n         list_of_files[filename] = path + \"\\\\\" + filename \r\n         print   (list_of_files[filename]) \r\n         with open(list_of_files[filename], encoding=\"utf8\" ) as f:\r\n            csv_f = csv.reader(f)\r\n            for i, row in enumerate(csv_f):\r\n               if i > 5 and len(row) > 1 :\r\n                 print(row)\r\n                 db.insert({'F1': row[0], 'F2': row[1]})\r\n                 \r\n# find all documents\r\nresults = db.find()\r\n \r\nprint()\r\nprint('==============================')\r\n \r\n# display documents from collection\r\nfor record in results:\r\n   # print out the document\r\n   print(record['F1'] + ',',record['F2'])\r\n \r\nprint()\r\n \r\n# close the connection to MongoDB\r\nconnection.close() \r\n<\/code><\/pre>\n<p>The scripts above can be easy customized for your specific needs. Feel free to ask questions or provide any feedback or comments. Below you can find references and useful links.<\/p>\n<p><strong>References<\/strong> <\/p>\n<p>1. <a href=https:\/\/docs.python.org\/2\/library\/csv.html target=\"_blank\">CSV File Reading and Writing<\/a><br \/>\n2. <a href=\"http:\/\/stackoverflow.com\/questions\/2404742\/how-to-install-mongodb-on-windows\" target=\"_blank\">How to install mongodb on windows<\/a><br \/>\n3. <a href=\"https:\/\/mlab.com\/welcome\/\" target=\"_blank\">mLab &#8211; Database-as-a-Service for MongoDB<\/a><br \/>\n4. <a href=\"https:\/\/bruceelgort.com\/2014\/03\/20\/mongodb-and-python-a-simple-example\/\">MongoDB and Python: A Simple Example<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Python is widely used programming language in many fields, such as data science, machine learning, web development. It is also a great choice for automation of different computer tasks such as data downloading from websites or data manipulation. Python can open, read or save data in different file formats. In this post we will look &#8230; <a title=\"Automating CSV File Reading and Writing with Python\" class=\"read-more\" href=\"http:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":[]},"categories":[10],"tags":[],"jetpack_publicize_connections":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.4 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Automating CSV File Reading and Writing with Python - Machine Learning Applications<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Automating CSV File Reading and Writing with Python - Machine Learning Applications\" \/>\n<meta property=\"og:description\" content=\"Python is widely used programming language in many fields, such as data science, machine learning, web development. It is also a great choice for automation of different computer tasks such as data downloading from websites or data manipulation. Python can open, read or save data in different file formats. In this post we will look ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/\" \/>\n<meta property=\"og:site_name\" content=\"Machine Learning Applications\" \/>\n<meta property=\"article:published_time\" content=\"2016-07-30T19:03:27+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2016-08-06T13:40:53+00:00\" \/>\n<meta name=\"author\" content=\"owygs156\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"owygs156\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/\",\"url\":\"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/\",\"name\":\"Automating CSV File Reading and Writing with Python - Machine Learning Applications\",\"isPartOf\":{\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#website\"},\"datePublished\":\"2016-07-30T19:03:27+00:00\",\"dateModified\":\"2016-08-06T13:40:53+00:00\",\"author\":{\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478\"},\"breadcrumb\":{\"@id\":\"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/intelligentonlinetools.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Automating CSV File Reading and Writing with Python\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#website\",\"url\":\"http:\/\/intelligentonlinetools.com\/blog\/\",\"name\":\"Machine Learning Applications\",\"description\":\"Artificial intelligence, data mining and machine learning for building web based tools and services.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/intelligentonlinetools.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478\",\"name\":\"owygs156\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g\",\"caption\":\"owygs156\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Automating CSV File Reading and Writing with Python - Machine Learning Applications","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/","og_locale":"en_US","og_type":"article","og_title":"Automating CSV File Reading and Writing with Python - Machine Learning Applications","og_description":"Python is widely used programming language in many fields, such as data science, machine learning, web development. It is also a great choice for automation of different computer tasks such as data downloading from websites or data manipulation. Python can open, read or save data in different file formats. In this post we will look ... Read more","og_url":"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/","og_site_name":"Machine Learning Applications","article_published_time":"2016-07-30T19:03:27+00:00","article_modified_time":"2016-08-06T13:40:53+00:00","author":"owygs156","twitter_card":"summary_large_image","twitter_misc":{"Written by":"owygs156","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/","url":"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/","name":"Automating CSV File Reading and Writing with Python - Machine Learning Applications","isPartOf":{"@id":"http:\/\/intelligentonlinetools.com\/blog\/#website"},"datePublished":"2016-07-30T19:03:27+00:00","dateModified":"2016-08-06T13:40:53+00:00","author":{"@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478"},"breadcrumb":{"@id":"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/intelligentonlinetools.com\/blog\/2016\/07\/30\/automating-csv-file-reading-and-writing-with-python\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/intelligentonlinetools.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Automating CSV File Reading and Writing with Python"}]},{"@type":"WebSite","@id":"http:\/\/intelligentonlinetools.com\/blog\/#website","url":"http:\/\/intelligentonlinetools.com\/blog\/","name":"Machine Learning Applications","description":"Artificial intelligence, data mining and machine learning for building web based tools and services.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/intelligentonlinetools.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/7a886dc5eb9758369af2f6d2cb342478","name":"owygs156","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/intelligentonlinetools.com\/blog\/#\/schema\/person\/image\/","url":"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g","contentUrl":"http:\/\/2.gravatar.com\/avatar\/b351def598609cb4c0b5bca26497c7e5?s=96&d=mm&r=g","caption":"owygs156"}}]}},"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p7h1IJ-6X","jetpack-related-posts":[{"id":678,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/11\/19\/web-content-extraction-is-now-easier-than-ever-using-python-scripting\/","url_meta":{"origin":431,"position":0},"title":"Web Content Extraction is Now Easier  than Ever Using Python Scripting","date":"November 19, 2016","format":false,"excerpt":"As more and more Web content is created, there is a need for simple and efficient Web data extraction tools or scripts. With some recently released python libraries Web content extraction is now easier than ever. One example of such python library package is newspaper [1]. This module can do\u2026","rel":"","context":"In &quot;Data Mining&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2529,"url":"http:\/\/intelligentonlinetools.com\/blog\/2019\/07\/10\/python-files-tracker\/","url_meta":{"origin":431,"position":1},"title":"Python Files Tracker for Reducing Time Consuming Tasks","date":"July 10, 2019","format":false,"excerpt":"Do you want to know how many python files you create or update each year? Or do you need review actions to be completed next month? Or you maybe run machine learning python models located in different folders, and find that it takes extra time to get back after switching\u2026","rel":"","context":"In &quot;Automate Time Consuming Tasks&quot;","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2019\/07\/Example-of-usage-python-files-tracker.jpg?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":766,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/12\/31\/retrieving-post-data-using-the-wordpress-api-with-python-script\/","url_meta":{"origin":431,"position":2},"title":"Retrieving Post Data Using the WordPress API with Python Script","date":"December 31, 2016","format":false,"excerpt":"In this post we will create python script that is able to get data from WordPress (WP) blog using WP API. This script will save downloaded data into csv file for further analysis or other purposes. WP API is returning data in json format and is accessible through link http:\/\/hostname.com\/wp-json\/wp\/v2\/posts.\u2026","rel":"","context":"In &quot;API Programming&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":797,"url":"http:\/\/intelligentonlinetools.com\/blog\/2017\/01\/08\/topic-extraction-from-blog-posts-with-lsi-and-lda-and-python\/","url_meta":{"origin":431,"position":3},"title":"Topic Extraction from Blog Posts with LSI , LDA and Python","date":"January 8, 2017","format":false,"excerpt":"In the previous post we created python script to get posts from Wordpress (WP) blog through WP API. This script was saving retrieved posts into csv file. In this post we will create script for topic extraction from the posts saved in this csv file. We will use the following\u2026","rel":"","context":"In &quot;Machine Learning&quot;","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1356,"url":"http:\/\/intelligentonlinetools.com\/blog\/2017\/07\/21\/application-for-machine-learning-for-analyzing-blog-text-and-google-analytics-data\/","url_meta":{"origin":431,"position":4},"title":"Application for Machine Learning for Analyzing Blog Text and Google Analytics Data","date":"July 21, 2017","format":false,"excerpt":"In the previous post we looked how to download data from WordPress blog. [1] So now we can have blog data. We can get also web metrics data from Google Analytics such us the number of views, time on the page. How do we connect post text data with metrics\u2026","rel":"","context":"In &quot;Artificial Intelligence&quot;","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2017\/07\/blog-post-analytics-300x99.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":721,"url":"http:\/\/intelligentonlinetools.com\/blog\/2016\/12\/15\/latent-dirichlet-allocation-lda-with-python\/","url_meta":{"origin":431,"position":5},"title":"Latent Dirichlet Allocation (LDA) with Python Script","date":"December 15, 2016","format":false,"excerpt":"In the previous posts [1],[2] few scripts for extracting web data were created. Combining these scripts, we will create now web crawling script with text mining functionality such as Latent Dirichlet Allocation (LDA). In LDA, each document may be viewed as a mixture of various topics. Where each document is\u2026","rel":"","context":"In &quot;Artificial Intelligence&quot;","img":{"alt_text":"Program Flow Chart for Extracting Data from Web and Doing LDA","src":"https:\/\/i0.wp.com\/intelligentonlinetools.com\/blog\/wp-content\/uploads\/2016\/12\/program-flow-300x247.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"_links":{"self":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/431"}],"collection":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/comments?post=431"}],"version-history":[{"count":22,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/431\/revisions"}],"predecessor-version":[{"id":462,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/posts\/431\/revisions\/462"}],"wp:attachment":[{"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/media?parent=431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/categories?post=431"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/intelligentonlinetools.com\/blog\/wp-json\/wp\/v2\/tags?post=431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}