{"id":1263,"date":"2021-07-18T10:04:25","date_gmt":"2021-07-18T09:04:25","guid":{"rendered":"http:\/\/roglacup.com\/klaus62\/?p=1263"},"modified":"2021-07-19T08:56:30","modified_gmt":"2021-07-19T07:56:30","slug":"phyton","status":"publish","type":"post","link":"https:\/\/roglacup.com\/klaus62\/2021\/07\/18\/phyton\/","title":{"rendered":"Phyton &#038; Excel"},"content":{"rendered":"\n<p style=\"font-size:29px\"><strong>1. Reading an excel file using Pythons<\/strong><\/p>\n\n\n\n<p>Using xlrd module, one can retrieve information from a spreadsheet. For example, reading, writing or modifying the data can be done in Python. Also, the user might have to go through various sheets and retrieve data based on some criteria or modify some rows and columns and do a lot of work.<\/p>\n\n\n\n<p><strong>xlrd&nbsp;<\/strong>module is used to extract data from a spreadsheet.&nbsp;<br>&nbsp;&nbsp;<br>Command to install xlrd module :&nbsp;<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">pip install xlrd\n\n<\/pre>\n\n\n\n<p><strong>Input File:<\/strong>&nbsp;<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/media.geeksforgeeks.org\/wp-content\/uploads\/excel-file.png\" alt=\"Sample excel file\"\/><\/figure>\n\n\n\n<p>Posted from: <a href=\"https:\/\/www.geeksforgeeks.org\/reading-excel-file-using-python\/\">https:\/\/www.geeksforgeeks.org\/reading-excel-file-using-python\/<\/a><\/p>\n\n\n\n<p style=\"font-size:29px\"><strong>2. How to Work with Excel files in Pandas<\/strong><\/p>\n\n\n\n<p id=\"9919\">From what I have seen so far, CSV seems to be the most popular format to store data among data scientists. And that\u2019s understandable, it gets the job done and it\u2019s a quite simple format; in Python, even without any library, one can build a simple CSV parser in under 10 lines of code.<\/p>\n\n\n\n<p id=\"d4a6\">But you may not always find the data that you&nbsp;need in CSV format. Sometimes the only available format may be an Excel file. Like, for example, this dataset on&nbsp;<a href=\"https:\/\/www.ons.gov.uk\/peoplepopulationandcommunity\/crimeandjustice\/datasets\/crimeinenglandandwalesannualtrendanddemographictables\">ons.gov.uk<\/a>&nbsp;about crime in England and Wales, which is only in xlsx format; dataset that I will use in the examples below.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"ed36\">Reading Excel files<\/h2>\n\n\n\n<p id=\"31fd\">The simplest way to read Excel files into pandas data frames is by using the following function (assuming you did&nbsp;<code>import pandas as pd<\/code>):<\/p>\n\n\n\n<p id=\"54af\"><code>df = pd.read_excel(\u2018path_to_excel_file\u2019, sheet_name=\u2019\u2026\u2019)<\/code><\/p>\n\n\n\n<p id=\"a0fc\">Where&nbsp;<code>sheet_name<\/code>&nbsp;can be the name of the sheet we want to read, it\u2019s index, or a list with all the sheets we want to read; the elements of the list can be mixed: sheet names or indices. If we want all the sheets, we can use&nbsp;<code>sheet_name=None<\/code>. In the case in which we want more sheets to be read, they will be returned as a dictionary of data frames. The keys of such a dictionary will be either the index or name of a sheet, depending on how we specified in&nbsp;<code>sheet_name<\/code>; in the case of&nbsp;<code>sheet_name=None<\/code>, the keys will be sheet names.<\/p>\n\n\n\n<p id=\"0d99\">Now, if we use it to read our Excel file we get:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*ejAaGMCqCwhNFsFtyDhW2g.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"ce4d\">That\u2019s right, an error! It turns out that pandas cannot read Excel files on its own, so we need to install another python package to do that.<\/p>\n\n\n\n<p id=\"7d24\">There are 2 options that we have:&nbsp;<code>xlrd<\/code>&nbsp;and&nbsp;<code>openpyxl<\/code>. The package&nbsp;<code>xlrd<\/code>&nbsp;can open both Excel 2003 (.xls) and Excel 2007+ (.xlsx) files, whereas&nbsp;<code>openpyxl<\/code>&nbsp;can open only Excel 2007+ (.xlsx) files. So, we will install&nbsp;<code>xlrd<\/code>&nbsp;as it can open both formats:<\/p>\n\n\n\n<p id=\"d582\"><code>pip install xlrd<\/code><\/p>\n\n\n\n<p id=\"7f5f\">Now, if we try to read the same data again:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*XWugHDByRpBvb4no03XxXw.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"a547\">It works!<\/p>\n\n\n\n<p id=\"1b5e\">But Excel files can be a little bit messier. Besides data, they may have other comments\/explanations in the first and\/or last couple of rows.<\/p>\n\n\n\n<p id=\"318a\">To tell pandas to start reading an Excel sheet from a specific row, use the argument header = 0-indexed row where to start reading. By default, header=0, and the first such row is used to give the names of the data frame columns.<\/p>\n\n\n\n<p id=\"0e45\">To skip rows at the end of a sheet, use skipfooter = number of rows to skip.<\/p>\n\n\n\n<p id=\"d3dc\">For example:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*7PdgBUYR3u50qMyNZSK5bw.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"6f4d\">This is a little better. There are still some issues that are specific to this data. Depending on what we want to achieve we may also need to rearrange the data values into another way. But in this article, we will focus only on reading and writing to and from data frames.<\/p>\n\n\n\n<p id=\"919e\">Another way to read Excel files besides the one above is by using a&nbsp;<code>pd.ExcelFile<\/code>&nbsp;object. Such an object can be constructed by using the&nbsp;<code>pd.ExcelFile(\u2018excel_file_path\u2019)<\/code>&nbsp;constructor. An&nbsp;<code>ExcelFile<\/code>&nbsp;object can be used in a couple of ways. Firstly, it has a&nbsp;<code>.sheet_names<\/code>&nbsp;attribute which is a list of all the sheet names inside the opened Excel file.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/456\/1*jBhpJzIk8pK6yzCSzmvwNQ.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"954a\">Then, this&nbsp;<code>ExcelFile<\/code>&nbsp;object also has a&nbsp;<code>.parse()<\/code>&nbsp;method that can be used to parse a sheet from the file and return a data frame. The first parameter of this method can be the index of the sheet we want to parse or its name. The rest of the parameters are the same as in the&nbsp;<code>pd.read_excel()<\/code>&nbsp;function.<\/p>\n\n\n\n<p id=\"17ae\">An example of parsing the second sheet (index 1):<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*76Acu7h7NIj7-9ForUC7dA.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"1056\">\u2026 and here we parse the same sheet using its name instead of an index:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*eb3jEfwSQ9tHSRljgZKIZg.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"b51c\"><code>ExcelFile<\/code>s can also be used inside&nbsp;<code>with \u2026 as \u2026<\/code>&nbsp;statements, and if you want to do something a little more elaborate, like parsing only sheets with 2 words in their name, you can do something like:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/526\/1*2srUQX2BhuYCvhIslN2lWw.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"7ace\">The same thing you can do by using&nbsp;<code>pd.read_excel()<\/code>&nbsp;instead of&nbsp;<code>.parse()<\/code>&nbsp;method, like this:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/537\/1*L2uZKMEqiocgeA0pl4WsnA.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"79e9\">\u2026 or, if you simply want all the sheets, you can do:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/516\/1*DLC17H-FUUypEMcT9CCNMA.png\" alt=\"\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"196c\">Writing Excel Files<\/h2>\n\n\n\n<p id=\"9b08\">Now that we know how to read excel files, the next step for us is to be able to also write a data frame to an excel file. We can do that by using the data frame method&nbsp;<code>.to_excel(\u2018path_to_excel_file\u2019, sheet_name=\u2019\u2026\u2019)<\/code>.<\/p>\n\n\n\n<p id=\"aca2\">Let\u2019s first create a simple data frame for writing to an excel file:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/496\/1*YWrHbFOgFuO4zA3QANGPtQ.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"da55\">Now we want to write it to an excel file:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*z-XwP3jIDh2YreYKIETW4g.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"3be8\">\u2026 and we got an error.<\/p>\n\n\n\n<p id=\"811e\">Again, pandas can\u2019t write to excel files on its own; we need another package for that. The main options that we have are:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code>xlwt<\/code>&nbsp;\u2014 works only with Excel 2003 (.xls) files; append mode not supported<\/li><li><code>xlsxwriter<\/code>&nbsp;\u2014 works only with Excel 2007+ (.xlsx) files; append mode not supported<\/li><li><code>openpyxl<\/code>&nbsp;\u2014 works only with Excel 2007+ (.xlsx) files; supports append mode<\/li><\/ul>\n\n\n\n<p id=\"4941\">If we want to be able to write to the old .xls format we should install&nbsp;<code>xlwt<\/code>&nbsp;as it is the only that handles those files. For .xlsx files, we will choose&nbsp;<code>openpyxl<\/code>&nbsp;as it also supports the append mode.<\/p>\n\n\n\n<p id=\"ff77\"><code>pip install xlwt openpyxl<\/code><\/p>\n\n\n\n<p id=\"e583\">Now if we run again the above code, it works; an excel file was created:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*X4IgORWtwebj-go-aE_z8w.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"b031\">By default, pandas also writes the index column along with our columns. To get rid of it, use&nbsp;<code>index=False<\/code>&nbsp;like in the code below:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/476\/1*RyrQynxPmiakI-IjPRHRZw.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"0914\">The index column isn\u2019t there now:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*5dT2Ar6ZQyI61Qjzq-C1hQ.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"cfde\">What if we want to write more sheets? If we want to add a second sheet to the previous file, do you think that the below code will work?<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/478\/1*0uH0sY30rCi9y4Wed6fFWg.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"c31a\">The answer is&nbsp;<strong>no<\/strong>. It will just overwrite the file with only one sheet: sheet2.<\/p>\n\n\n\n<p id=\"c0f6\">To write more sheets to an Excel file we need to use a&nbsp;<code>pd.ExcelWriter<\/code>&nbsp;object as shown below. First, we create another data frame for sheet2, then we open an Excel file as an&nbsp;<code>ExcelWriter<\/code>&nbsp;object in which we write the 2 data frames:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/518\/1*TQ8ie6EpJJoimOcdv66I0w.png\" alt=\"\"\/><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/461\/1*YHdgKTc2FOsGUJayRsDwFQ.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"2491\">Now our Excel file should have 2 sheets. If we then want to add another sheet to it, we need to open the file in append mode and run code similar to the previous one. For example:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/461\/1*W1PS9Ny_IOwEYRHiATU0WA.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"1a45\">Our Excel file, now, has 3 sheets and looks like this:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*vkJ6wmz-Gr6CH09K4jnm2Q.png\" alt=\"\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"531c\">Working with Excel Formulas<\/h2>\n\n\n\n<p id=\"091c\">Probably you are wondering, at this point, about Excel formulas. What about them? How to read from files that have formulas? How to write them to Excel files?<\/p>\n\n\n\n<p id=\"5811\">Well\u2026 good news. It is quite easy. Writing formulas to Excel files is as simple as just writing the string of the formula, and these strings will be automatically interpreted by Excel as formulas.<\/p>\n\n\n\n<p id=\"cdcd\">As an example:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/695\/1*Fe3y_T1HTFrf2bCl4AYwXA.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"fce1\">The Excel file produced by the code above is:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/700\/1*sx0WvMu4I4nVY40bpoEWgA.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"2f8c\">Now, if we want to read an Excel file with formulas in it, pandas will read into data frames the result of those formulas.<\/p>\n\n\n\n<p id=\"80a0\">For example, let\u2019s read our previously created file:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/miro.medium.com\/max\/446\/1*HpYimYsfGuAAdatRtpwj9Q.png\" alt=\"\"\/><\/figure>\n\n\n\n<p id=\"c2be\">Sometimes you need to save the Excel file manually for this to work and not get zeros instead of the result of formulas (hit CTRL+S before executing the above code).<\/p>\n\n\n\n<p>Posted from :  <a href=\"https:\/\/towardsdatascience.com\/how-to-work-with-excel-files-in-pandas-c584abb67bfb\">https:\/\/towardsdatascience.com\/how-to-work-with-excel-files-in-pandas-c584abb67bfb<\/a><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>Series and Indexes are equipped with a set of string processing methods that make it easy to operate on each element of the array. Perhaps most importantly, these methods exclude missing\/NA values automatically. These are accessed via the&nbsp;<em>str&nbsp;<\/em>attribute and generally, have names matching the equivalent (scalar) built-in string methods.<\/p>\n\n\n\n<p style=\"font-size:29px\"><strong>3. Python Pandas Working With Text Data <\/strong><\/p>\n\n\n\n<p>Series and Indexes are equipped with a set of string processing methods that make it easy to operate on each element of the array. Perhaps most importantly, these methods exclude missing\/NA values automatically. These are accessed via the&nbsp;<em>str&nbsp;<\/em>attribute and generally, have names matching the equivalent (scalar) built-in string methods.<\/p>\n\n\n\n<p>In order to lowercase a data, we use&nbsp;<a rel=\"noreferrer noopener\" href=\"https:\/\/www.geeksforgeeks.org\/python-pandas-series-str-lower-upper-and-title\/\" target=\"_blank\">str.lower()<\/a>&nbsp;this function converts all uppercase characters to lowercase. If no uppercase characters exist, it returns the original string. In order to uppercase a data, we use&nbsp;<a rel=\"noreferrer noopener\" href=\"https:\/\/www.geeksforgeeks.org\/python-pandas-series-str-lower-upper-and-title\/\" target=\"_blank\">str.upper()<\/a>&nbsp;this function converts all lowercase characters to uppercase. If no lowercase characters exist, it returns the original string.<\/p>\n\n\n\n<p><strong>Code #1:<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><code># Import pandas package <\/code><code>import<\/code> <code>pandas as pd <\/code>&nbsp;<code># Define a dictionary containing employee data <\/code><code>data <\/code><code>=<\/code> <code>{<\/code><code>'Name'<\/code><code>:[<\/code><code>'Jai'<\/code><code>, <\/code><code>'Princi'<\/code><code>, <\/code><code>'Gaurav'<\/code><code>, <\/code><code>'Anuj'<\/code><code>], <\/code><code>'Age'<\/code><code>:[<\/code><code>27<\/code><code>, <\/code><code>24<\/code><code>, <\/code><code>22<\/code><code>, <\/code><code>32<\/code><code>], <\/code><code>'Address'<\/code><code>:[<\/code><code>'Delhi'<\/code><code>, <\/code><code>'Kanpur'<\/code><code>, <\/code><code>'Allahabad'<\/code><code>, <\/code><code>'Kannauj'<\/code><code>], <\/code><code>'Qualification'<\/code><code>:[<\/code><code>'Msc'<\/code><code>, <\/code><code>'MA'<\/code><code>, <\/code><code>'MCA'<\/code><code>, <\/code><code>'Phd'<\/code><code>]} <\/code>&nbsp;<code># Convert the dictionary into DataFrame&nbsp; <\/code><code>df <\/code><code>=<\/code> <code>pd.DataFrame(data) <\/code>&nbsp;<code># converting and overwriting values in column <\/code><code>df[<\/code><code>\"Name\"<\/code><code>]<\/code><code>=<\/code> <code>df[<\/code><code>\"Name\"<\/code><code>].<\/code><code>str<\/code><code>.lower()<\/code>&nbsp;<code>print<\/code><code>(df)<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p>Posted from: <a href=\"https:\/\/www.geeksforgeeks.org\/python-pandas-working-with-text-data\/\">https:\/\/www.geeksforgeeks.org\/python-pandas-working-with-text-data\/<\/a><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n\n\n\n<p style=\"font-size:29px\"><strong>4. Reading and Writing CSV Files in Python with Pandas<\/strong><\/p>\n\n\n\n<p><a href=\"https:\/\/stackabuse.com\/reading-and-writing-csv-files-in-python-with-pandas\">https:\/\/stackabuse.com\/reading-and-writing-csv-files-in-python-with-pandas<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>1. Reading an excel file using Pythons Using xlrd module, one can retrieve information from a spreadsheet. For example, reading, writing or modifying the data can be done in Python. Also, the user might have to go through various sheets and retrieve data based on some criteria or modify some rows and columns and do&hellip;&nbsp;<a href=\"https:\/\/roglacup.com\/klaus62\/2021\/07\/18\/phyton\/\" class=\"\" rel=\"bookmark\">Read More &raquo;<span class=\"screen-reader-text\">Phyton &#038; Excel<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":1271,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","footnotes":""},"categories":[20],"tags":[],"class_list":["post-1263","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-script"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts\/1263","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/comments?post=1263"}],"version-history":[{"count":7,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts\/1263\/revisions"}],"predecessor-version":[{"id":1272,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/posts\/1263\/revisions\/1272"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/media\/1271"}],"wp:attachment":[{"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/media?parent=1263"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/categories?post=1263"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/roglacup.com\/klaus62\/wp-json\/wp\/v2\/tags?post=1263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}