Replicate Excel operations with Python

[Lumberjack]

I hardly ever use pandas, so I don't know much about it.

I had something similar, so adapted it for you, but it doesn't use pandas

Initially, I have an Excel file with 4 columns and 2 rows.
The columns are: Weekday, Date, Product A Stock and Weekly Delta A (the weekdays are for clarity)

The second row column date should be a Monday, but I think that is not too important.
Product A Stock levels need to be checked, add more if the levels get too low.

I would be easy to add more columns for another product, or make a new sheet for each product, less bewildering.

You just need to jot down in a diary how many Product A were sold each day.

def myApp():
    import openpyxl
    from datetime import datetime, timedelta

    path2XL = '/home/pedro/myPython/openpyxl/product_sales.xlsx'
    wb = openpyxl.load_workbook(path2XL)

    # in case there is more than 1 sheet, loop
    # maybe each product on a separate sheet for easier oversight
    # sheets = wb.sheetnames

    # initially only 2 rows in the XL, the header row and the start row.

    def addStock(maxRow):
        ws = wb.active
        productA_quantity_now = ws.cell(row=maxRow, column=3).value
        if productA_quantity_now < 100:
            print('Emergency: replenish Product A stocks immediately ... ')

        print('Have you acquired new stock?')
        answer = input('Enter yes to up the stock quantity anything else to ignore this ... ')
        if answer == 'yes':
            new_stock = input('How much new stock have you acquired? Just enter a number. ')
            rest_stock = ws.cell(row=maxRow, column=3).value
            ws.cell(row=maxRow, column=3, value=rest_stock + int(new_stock))

    def addRow():
        # renew ws each time because ws.max_row is changing
        ws = wb.active
        maxRow = ws.max_row
        # check or add new stock
        print('Maybe stocks are running low?')
        answer = input('Want to check stock levels or add new stock? Enter yes to check ... ')
        if answer == 'yes':
            addStock(maxRow)        
        start_date = ws.cell(row=maxRow, column=2).value    
        new_date = start_date + add_day
        # leave out the weekends
        if start_date.weekday() == 4:
            print('We don\'t work weekends!')
            new_date = start_date + add3days
            
        the_date = new_date.strftime("%d-%m-%Y")
        print('The date now is:', the_date)
        print('How many of Product A did you sell yesterday?')
        # day is a datetime object
        #day = ws.cell(row=maxRow, column=2).value
        if new_date.weekday() == 0:
            sales_int = 0
        else:
            print('Today is not Monday. How many did you sell the day before today?')
            sales_amount = input('How many of Product A did you sell the day before today? ')
            sales_int = int(sales_amount)
            if sales_int < 1000:
                print('You lazy bastard, you will be looking for a new job very soon .... ')
        
        # put the new_day in column 1
        ws.cell(row=maxRow+1, column=1, value=days[new_date.weekday()])
        # write the weekday in column 1 for clarity
        ws.cell(row=maxRow+1, column=2, value=new_date)
        # reduce the amount of stock and write to column 3
        stock_down = ws.cell(row=maxRow, column=3).value - sales_int    
        # write the reduction in stock to column 4
        delta = ws.cell(row=maxRow, column=4).value - sales_int
        # if Monday, set column 4 to zero
        if new_date.weekday() == 0:
            delta = 0
        ws.cell(row=maxRow+1, column=3, value=stock_down)
        ws.cell(row=maxRow+1, column=4, value=delta)
        # save each time because ws.max_row is changing       
        wb.save(path2XL)

    # can change this if necessary, for weekly sales or so
    add_day = timedelta(days=+1)
    # on Friday add 3 days to get back to Monday
    add3days = timedelta(days=+3)
    days = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday']

    carry_on = 'X'
    while True:
        carry_on = input('Enter q to quit, anything else to continue adding rows ... ')
        if carry_on == 'q':
            break
        addRow()

    print('All current sales data added, hope the boss is happy!')

Thank you for your code! I like the interactive nature of it, but I would still love to get the pandas program that I wrote above working fully.

So far, it's 95% there, I just need to solve the "index out of bounds" problem that I'm encountering with one of the for loops, but I'm not sure how.

I printed the length of the dataframe, which is 25, so range(0,26) shouldn't be a problem for that for loop, yet I can only use a range up to 20. Does anybody know why the index is out of bounds after 20?

And why isn't the first for loop of my second program that includes a Python Delta B column, iterating across the Python Delta B column as it did over the Python Delta A column?