{"id":157,"date":"2013-04-01T23:28:45","date_gmt":"2013-04-02T04:28:45","guid":{"rendered":"http:\/\/homepages.uc.edu\/~yaozo\/wordpress\/?p=157"},"modified":"2013-04-01T23:28:45","modified_gmt":"2013-04-02T04:28:45","slug":"manipulating-excel-files-using-python-part-1-reading-excel-files","status":"publish","type":"post","link":"https:\/\/zhuoyao.net\/index.php\/2013\/04\/01\/manipulating-excel-files-using-python-part-1-reading-excel-files\/","title":{"rendered":"Manipulating Excel files using Python part 1: Reading Excel Files"},"content":{"rendered":"

It is often the case that the freely available data online are in Excel format. If one has Excel, then one has the ability to do some sort of basic manipulation of the files. But if Excel is not available, or your analysis software does not read Excel files, there is another way: use Python to manipulate Excel files.<\/p>\n

\"excelPreview\"Before continuing, let\u2019s discuss the basic idea of Excel. This programs stores data in what is called aWorkbook<\/strong>. Think of this as the file on your computer (the .xls). A Workbook can then have one or more\u00a0Sheets<\/strong>, the little tabs on the bottom left corner usually. Within each Sheet, you can find an arrangement of\u00a0Cells<\/strong>\u00a0in a matrix form, which are referenced by Column Name and Row Number (so the top left cell is A1, the one directly to its right is A2, the one below is B2, etc. The image should help shed some light if you are not familiar with the concept of spreadsheets.<\/p>\n

In order to\u00a0read<\/strong>\u00a0Excel sheets in Python without using any Microsoft Office code, we need to use the excellentxlrd packge<\/a>\u00a0by\u00a0Lingfo<\/a>. The basic workflow is as follows:<\/p>\n

    \n
  1. Open a workbook using\u00a0book = xlrd.open_workbook(\u201cfiletoread.xls\u201d)<\/em><\/li>\n
  2. Access a sheet in the workbook using\u00a0sheet = book.sheet_by_index(0)<\/em>\u00a0to open the first sheet, or if you know the name,\u00a0sheet = book.sheet_by_name(\u201cName of Sheet\u201d)<\/em><\/li>\n
  3. Retrieve data using any of the multiple methods:\u00a0cellValue =sheet.cell_value(rowx, colx)<\/em>, or to retrieve a whole column use\u00a0colValues = sheet.col_values(colx, start_rowx=0, end_rowx=None)<\/em>\u00a0or the retrieve a whole row use\u00a0rowValues = sheet.row_values(rowx, start_colx=0, end_colx=None)<\/em><\/li>\n<\/ol>\n

    The basic idea is very simple, and we can easily write a little Python script that will allow us to read Excel files without the need to use Excel itself. The following example will offer a preview of an Excel file\u2019s first sheet using the first 5 rows and columns of a file.<\/p>\n

    \n
    \n
    ?<\/a><\/div>\n\n\n\n
    \n
    1<\/div>\n
    2<\/div>\n
    3<\/div>\n
    4<\/div>\n
    5<\/div>\n
    6<\/div>\n
    7<\/div>\n
    8<\/div>\n
    9<\/div>\n
    10<\/div>\n<\/td>\n
    		\n
    \n
    import<\/code> xlrd <\/code># Import the package<\/code><\/div>\n
    book <\/code>=<\/code> xlrd.open_workbook(&amp;amp;quot;sample.xls&amp;amp;quot;) <\/code># Open an .xls file<\/code><\/div>\n
    sheet <\/code>=<\/code> book.sheet_by_index(<\/code>0<\/code>) <\/code># Get the first sheet<\/code><\/div>\n
    for<\/code> counter <\/code>in<\/code> range<\/code>(<\/code>5<\/code>): <\/code># Loop for five times<\/code><\/div>\n
    # grab the current row<\/code><\/div>\n
    rowValues <\/code>=<\/code> sheet.row_values(counter,start_col<\/code>=<\/code>0<\/code>, end_colx<\/code>=<\/code>4<\/code>)<\/code><\/div>\n
    # Print the values of the row formatted to 10 characters wide<\/code><\/div>\n
    print<\/code> &amp;amp;quot;<\/code>%<\/code>-<\/code>10s<\/code> | <\/code>%<\/code>-<\/code>10s<\/code> | <\/code>%<\/code>-<\/code>10s<\/code> | <\/code>%<\/code>-<\/code>10s<\/code> | <\/code>%<\/code>-<\/code>10s<\/code>&amp;amp;quot; <\/code>%<\/code> tuple<\/code>(rowValues)<\/code><\/div>\n
    # Print row separator<\/code><\/div>\n
    print<\/code> &amp;amp;quot;<\/code>-<\/code>&amp;amp;quot; <\/code>*<\/code>62<\/code><\/div>\n<\/div>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n

    That is all there is to it. Quick, fast and no need for Excel whatsoever. Sample output is presented below:<\/p>\n

    \"sampleOutput\"<\/p>\n","protected":false},"excerpt":{"rendered":"

    It is often the case that the freely available data online are in Excel format. If one has Excel, then one has the ability to… <\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"class_list":["post-157","post","type-post","status-publish","format-standard","hentry","category-python"],"_links":{"self":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/157","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/comments?post=157"}],"version-history":[{"count":0,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/posts\/157\/revisions"}],"wp:attachment":[{"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/media?parent=157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/categories?post=157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zhuoyao.net\/index.php\/wp-json\/wp\/v2\/tags?post=157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}