I wrote this to parse through an Excel doc, extract desired columns, parse certain cells and output another formatted Excel doc.

This is the original Excel doc I have condensed most of the columns for space


Here are the the results of the program that I am mostly happy with, it parses the name column and writes a unit identifier(i.e 83) along with a tower identifier(i.e 5) on a new line along with a completed date if the completed column is TRUE. I think the only thing I would change would be getting rid of the blank line under the row that has H 7


And here is the code

import xlrd
import xlwt
import re

# open and create workbook
workbookread = xlrd.open_workbook('seedtestexcelbytask.xls')
workbookwrite = xlwt.Workbook('output.xlsx')

# open and create sheets
worksheetread = workbookread.sheet_by_index(0)
worksheetwrite = workbookwrite.add_sheet('Sheet1')

timelist = []
towerlist = []

i = 0
l = 0
t = 0

# iterate through rows in worksheet, get columns completed, completed_at and name.
# add to timelist[] and towerlist[] if completed == true
for rows in range(worksheetread.nrows):
    cell0 = worksheetread.cell(rows, 2).value
    cell1 = worksheetread.cell(rows, 3).value
    cell2 = worksheetread.cell(rows, 15).value

    if cell0 == 1:
        parsedvalue = re.split(', |/', cell2)

b = 2
g = 1
for rows in range(len(timelist)):
    row = worksheetwrite.row(i)

# get current index from timelist and towerlist and assign to currenttime  and currentvalue

    currenttime = timelist[t]
    currentname = towerlist[t]

# get the length of the current index this will help us decide how to parse
    length = len(towerlist[t])

# if length <= 2 the index is not formatted properly so we only need index 1 which is the cafe and towers together
    if length <= 2:
        row.write(0, currenttime)
        row.write(1, currentname[1])
        i += 1
        l += 1

# if length = 4 index is formatted properly and we just grab the columns we need
    elif length == 4:
        a = 2

        for values in range(0, 2):
            row = worksheetwrite.row(l)
            row.write(0, currenttime)
            row.write(1, currentname[1])
            row.write(2, currentname[a])
            a += 1
            i += 1
            l += 1

# if length > 4 we need to iterate through each index and write them

        # define the length of the current cell of the excel sheet we have
        lengthd = (len(currentname)+1)/2
        length = int(lengthd)

        for values in range(length):
            # set variables that we will need to hold on to
            currentcafe = currentname[g]
            currenttower = currentname[b]

            # if the value of the current tower is 2,4,5,6,7 we need to write the current cafe and current tower
            # then move on to the next value and set that as the current cafe, we also need to jump to the
            # value after the current cafe and set that as the current tower
            if currenttower == "2" or currenttower == "4" or currenttower == "5" or currenttower == "6" or currenttower == "7":
                row = worksheetwrite.row(l)
                row.write(0, currenttime)
                row.write(1, currentcafe)
                row.write(2, currenttower)

                b += 1
                i += 1
                g = b
                b = g + 1
            # otherwise we can just write the value fo the current cafe and current tower, advance to the next value
            # which is another tower and set current tower to that
                row = worksheetwrite.row(l)
                row.write(0, currenttime)
                row.write(1, currentcafe)
                row.write(2, currenttower)
                b += 1
                i += 1

            l += 1
        i += 1
    t += 1

This is the first time I have written anything with Python and I am fairly new to programming, any comments would be greatly appreciated.

  • 2
    \$\begingroup\$ Could you expand the column width to fully show the "notes" column? Also, could you summarize what this transformation is for? \$\endgroup\$ – 200_success Jun 7 '18 at 20:55
  • \$\begingroup\$ I updated the the pic to better show relevant columns \$\endgroup\$ – azryel6500 Jun 8 '18 at 14:27
  • 1
    \$\begingroup\$ check out pandas \$\endgroup\$ – Maarten Fabré Jun 8 '18 at 14:30
  • \$\begingroup\$ If length is 3, doesn't this end up executing the >4 section of code? \$\endgroup\$ – Ray Butterworth Jan 5 '20 at 14:49

Using pandas you can do it in much simpler way.
1. Load the data in pandas dataframe from your excel sheet - https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html
2. Filter out the columns which you want to keep (you can specify the column names to be loaded while importing in step 1 also)
3. Transform the columns based on your requirement
4. Delete the rows which are null, in your case as it is H7 using pandas function dropna() - https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.dropna.html
5. Export the data to an excel sheet - https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_excel.html

For your reference, refer this blog- https://towardsdatascience.com/replacing-excel-with-python-30aa060d35e


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.