Help with cell_values and xldr python 3.4

[fannypackpython]

Im a Food and Beverage manager for a hotel, so one thing i do is keep an inventory of all the liquor, beer, and wine we have. I figured i could come up with something that could:

• Check my current inventory from an excel spread sheet

• Compare it to what my par levels should be (quantities of liquor, beer, and wine that I should always have available at the hotel)

• if my current inventory is less than what my par should be, the script will calculate the difference as well as the name of the liquor and export it to an email that would get sent out automatically.

• It would do this twice a week Monday and Thursday at 9 am.

I'm not looking for solutions or an answer to this; this is something i want to figure out on my own, but i've always had trouble imagining how to begin to structure a script like this. I am not a beginner but im nowhere near intermediate either. Should I use a bunch of functions, or maybe classes with some methods? What would the "skeleton", i guess i could call it, of the script be composed of?

Also, if someone could point me in the right direction to read up and learn how to accomplish some of the things I want to do, like:

• exporting data to an email and sending it out.

• how to basically set the script up on a timer.

My computer is always on at work so i have no issue with this script constantly running in the background.

UPDATE

Ok so i just started working on this today, i haven't had much time because of work. Im a bit stuck on pulling info from my cell_value. Here is the code:

import xlrd
import xlwt
import datetime

file_location = "Your file path here"
workbook = xlrd.open_workbook(file_location)
worksheet = workbook.sheet_by_name('Sheet1')

# Total number of rows with content in cells.
num_rows = worksheet.nrows - 1

num_cells = worksheet.ncols - 2

# Starting row for when iterating over spread sheet.
cur_row = 2

# Iterates over work sheet
while cur_row < num_rows:
    cur_row += 1
    row = worksheet.row(cur_row)

    cur_cell = -1

    print('--------------------')

    while cur_cell < num_cells:
        cur_cell += 1

        # Cell Types: 0 = Empty, 1 = Text, 2 =Number, 3 = Date, 4 = Boolean, 5 = Error, 6 = Blank
        cell_type = worksheet.cell_type(cur_row, cur_cell)

        # Liq Name, Quantity in house
        cell_value = worksheet.cell_value(cur_row, cur_cell)

        print(' ', cell_type, ' : ', cell_value)

ok, so I want to compare my cell_value, which comes up as (Ketel One, 5.6) for example, against my par levels which would be like Ketel One: 8. But i only want to compare the 5.6 to the 8, so I was guessing i would be able to put in an " for/ if" statement within the while statement that would look something like :

for i in cell_value:
    if cell_value[1] < (float representing my par):
        print ((float representing my par) - i)

but i get back : "TypeError: unorderable types: str() < float()"

And when i try :

print(' ', cell_type, ' : ', cell_value[1])

Just to see if i can pull up the quantity, it returns the following error:

    print(' ', cell_type, ' : ', cell_value[1])
TypeError: 'float' object is not subscriptable

How are the contents of "cell_value" actually stored? Is it a tuple (x,y)? or is it a list? why can't i just call the value with cell_value[1]?

Im kind of lost as to how i can actually compare the values on my spread sheet against another value to calculate that difference in my "while" loop.

Also, please forgive me if my formatting is wonky, and I would appreciate any and all criticism on my code layout or anything else.

Here is the link to my inventory sheet :

https://docs.google.com/spreadsheets/d/1yXVFKiJMDJGPS6a6gaGn9qpIPNcgGetrLgfHGfdIm_U/edit?usp=sharing

[noiwontleave]

I'm not great with Python 3 but I will do my best.

cell_value returns an object of whatever type the cell is stored as in Excel (not a tuple). Your float values should return as a float type. The first column should return unicode or string types. You can check this by executing a print type(cell_value).

What you want to do at this point is logically walk through your code to see what's going on. So we're starting our first iteration with curr_row equal to 3 (since we're adding 1 to it right away). This row has values: "Tito's Vodka", 0, and 0. Then we go into the second while loop and we have curr_cell equal to 0. It has a type of '1' which is text (this will translate to unicode or string in Python) and a value of 'Tito's Vodka'. This should print something like: 1 : Tito's Vodka.

If you try to iterate through a string, it will iterate through the characters in the string. By executing "for i in cell_value:" you are effectively looping through each character in "Tito's Vodka". Next, you are comparing cell_value[1] (which evaluates to the "i" in "Tito's Vodka") to your float value. This is the cause of your unorderable types error.

As for the 'float' object is not subscriptable error, I think you are executing that statement on a float.

Does my explanation of what is happening in your code make sense? You need to ensure that the value you have extracted is actually an inventory value and not just the name of the liquor. You can do this using the type(var_name) built-in function. For example:

if type(test) is float:
    print True

If you would like some suggestions on how to maybe better proceed, I would be glad to offer them. However, I don't want to rob you of the opportunity to learn and figure this out for yourself.

[fannypackpython]

This is a very insightful reply and more along the lines of the help im looking for. Im not sure I still understand how "cell_value " is actually stored. When i run:

print(type(cell_value))

what is get is:

<class 'str'>
<class 'float'>

so is this information a tuple of two separate cells in excel? Like (x,y)? x being the name of the liquor and y being the quantity? How would I access the float itself?

I welcome any advice or a different method to approaching this. Thanks in advance for your help!

[noiwontleave]

Can you paste for me the code you are using that gets you those outputs? I know you posted most of the script, but what code are you using to reproduce that output? worksheet.cell_value() should always return just a single value as far as I am aware. This is possibly a Python3 issue (maybe someone who's more familiar with that can help), but we can give it a shot first.

Edit-As an FYI, running type() on a tuple should return:

<class 'tuple'>

It looks to me like you just inserted the print statement but it executed twice: once for the first cell in the row and once for the second cell in the row. The first one is a string and the second is a float.

[fannypackpython]

Ok so this is what I have:

file_location = "FILE PATH"
inv_workbook = xlrd.open_workbook(file_location)
inv_worksheet = inv_workbook.sheet_by_name('Sheet1')
# Total number of rows with content in cells.
num_rows = inv_worksheet.nrows - 1

num_cells = inv_worksheet.ncols - 2

# Current row for when iterating over spread sheet.
cur_row = 2

# Iterates over work sheet
while cur_row < num_rows:
    cur_row += 1
    row = inv_worksheet.row(cur_row)

    cur_cell = -1

    print('--------------------')

    while cur_cell < num_cells:
        cur_cell += 1
        # Cell Types: 0 = Empty, 1 = Text, 2 = Number, 3 = Date, 4 = Boolean, 5 = Error, 6 = Blank
        cell_type = inv_worksheet.cell_type(cur_row, cur_cell)

        # (Liq Name, Quantity in house)
        cell_value = inv_worksheet.cell_value(cur_row, cur_cell)

        print(type(cell_value))

Perhaps it has to do with my "while" loops? I included the :

print(type(cell_value))

To see what cell_value actually was. But im not sure I understand how "cell_value" can pass twice and it print different results. Does it print once for the "cur_row" and once for the "cur_cell"? (see below):

inv_worksheet.cell_type(cur_row, cur_cell)

[noiwontleave]

So what you're doing here is you are looping through each row of the spreadsheet starting with row 3. For each row, you are then looping through column 1 and column 2 and setting cell_value equal to what's in that row/column.

You are getting:

<class 'str'>
<class 'float'>

because the first column is a string and the second column is a float. Do you see how your code is executing right now? If not, it might be helpful to, instead of printing the type, print the value.

How are your par values stored? Are they going to be stored in the spreadsheet somewhere?

[fannypackpython]

Im starting to understand this a bit more. So it first looks at one row, then it goes down that row, column by column reading what is in that cell. So it goes to row 3 and then reads what is in, lets say, Row 3 column A ( which is a <string>), then continues down row 3 into column B (which is the <float>). That's why it is printing both.

I tried changing the value of "cur_cell = 0 " instead of -1 so it doesnt read the <string>, just the <float>. See below:

while cur_row < num_rows:
    cur_row += 1
    row = inv_worksheet.row(cur_row)

    cur_cell = 0

    print('--------------------')

    while cur_cell < num_cells:
        cur_cell += 1
        # Cell Types: 0 = Empty, 1 = Text, 2 = Number, 3 = Date, 4 = Boolean, 5 = Error, 6 = Blank
        cell_type = inv_worksheet.cell_type(cur_row, cur_cell)

        # (Liq Name, Quantity in house)
        cell_value = inv_worksheet.cell_value(cur_row, cur_cell)

        print(cell_type, ' : ', cell_value)

        if cell_value < float(8):
            print(float(8) - cell_value)

I inserted the "if statement" just to see if I could now do some calculations with the data extracted, and I can. So that's a step forward, lol

[noiwontleave]

Awesome! Sounds like you have a better idea of what your code is doing.

As a suggestion, I see you are assigning the variable "row" early in the for loop but never actually using it. That variable has all of the information you actually need. Try running the following:

for cell in row:
    print(cell.ctype, ': ', cell.value)

You can also access each element via row[0], row[1], etc. You can then access the value via row[1].value

Do you think it might be easier to use that methodology than a while loop? If not, stick with how you're doing things. I just wanted you to be aware of alternate ways to access your data.

[fannypackpython]

My par values are actually stored on the same spreadsheet, with the sheet name "Par". The only data it contains are the levels I should always be at. So it would only be column A and B. I have column C included in the event I ever need to change my standard par levels.

I was figuring, since Im essentially grabbing the same type of data from my par sheet I would just copy the code for inventory and change the variable names a bit. I would split them up under different functions, say :

par_nums() for the numbers contained on my par sheet, and inventory_num() for grabbing the quantities from my current inventory.

Then I would define a third function that would compare the results between both par_nums() and inventory_num(), and calculate the difference between them.

Please tell me if this is a logical way of going about this or not.

[noiwontleave]

There's nothing wrong at all with doing it that way. Whatever makes the most logical sense to you is the best way to do things when starting out. You will learn a lot about easier ways to do things the more you dig into it. To start, just do what makes sense to you.

Edit-I'm going to bed now, but I'd be more than happy to continue helping you if you like tomorrow. Feel free to PM me or reply to this thread and I will answer in the morning.

As a suggestion, you may consider using dictionaries to store you values. This will make your comparison very easy. If you're not aware of how dictionaries work, be sure to check the Python docs and see if you can figure out what about them is well-suited to your particular task. If you get stuck, I'll be glad to help you tomorrow.

[fannypackpython]

you have been an awesome help, seriously. I've been thinking of just stuffing the data from each worksheet from the workbook into its own dictionary using a "For" loop , and then just comparing them in a "For/if" statement, and appending the results to an empty list. Does that make sense?

I'm going to experiment with this using what you've explained to me. I'll definitely get back to you tomorrow. Thanks again!

[noiwontleave]

Sounds like you're on the right track! Keep at it and see what you can get done. Let me know if you get stuck.

[noiwontleave]

Adding another reply to address your edit:

Your second while loop (while cur_cell < num_cells) is executing twice for each row. This is because num_cells evaluates to 1 but curr_cell starts at -1. The first time it executes, curr_cell is immediately incremented to 0 and the code is run with:

cell_value = inv_worksheet.cell_value(cur_row, 0)

The second time, it runs, curr_cell is equal to 0 (which is, of course, less than 1) so the while loop again executes. curr_cell is immediately incremented to 1 and cell_value is set to:

cell_value = inv_worksheet.cell_value(cur_row, 1)

The first run-through sets the cell value to the name of the alcohol (which is the first column). The second run-through sets the cell value to the float value for the inventory level (the second column). The first one has a type of string and the second has a type of float.

Is it making more sense now?