Python program to transform an Excel document, with some parsing

Question

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)
        timelist.append(cell1)
        towerlist.append(parsedvalue)

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
    else:

        # 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
            else:
                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
workbookwrite.save('output.xls')

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

Answer 1

Using pandas you can do it in much simpler way.
Steps:
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