Replicate Excel operations with Python

[Lumberjack]

EDIT: Second post features the latest updates

I have this Excel file where I track two products A and B and their weekly differences compared to their stock at the beginning of each week.

It looks like this:



As you can see, I use the monday numbers of product A and B as their weekly "anchor" points. I then subtract this anchor number from the following weekday numbers. Monday - Monday, Tuesday - Monday, Wednesday - Monday, Thursday - Monday and Friday - Monday and then the cycle begins anew next week.

The difference between the rest of the weekdays compared to monday is expressed in the weekly delta columns.

If I only had the product A and product B columns available, how would I calculate the weekly delta columns with python?

Here's my idea (df = dataframe of the whole excel file):

int column_a = 1
int column_b = 2
 
for x in range(0, 26):
 
    If date of the excel file row x = monday (don't know how to check for this in python)
 
    int row_monday = x
 
    int monday_a = df.iat[row_monday-1 , column_a]
 
    int delta_a = 0
 
    "write delta_a into excel file at row x and column "column_a + 2"
 
    for y in range (1,5):
 
        int current_a = df.iat[x-1+y, column_a]
 
        delta_a = current_a - monday_a
 
        write delta_a into excel file at row x-1+y and column_a + 2

Is my general approach to this correct? How should I code out the parts that are still unclear to me?

Larz60+ write May-08-2022, 09:51 PM:
Please fix indentation and use 'python' BBCode tags.

[Lumberjack]

Here's what I came up with after some tinkering (the code works for the most part now):



And here's the code that I used, in conjunction with the Excel File from above, to generate that output:

import pandas as pd

df = pd.read_excel('Delta.xlsx', index_col = 0)

df['Python Delta A'] = 0

df['dates'] = df.index

df['day_of_the_week'] = df['dates'].dt.day_name()

current_a = 0

for x in range(0,20):
    if df.iat[x,6] == 'Monday':
        
        current_a = df.iat[x,0]
        
        df.iat[x,4] = 0
        
        for y in range(1,6):
            df.iat[x+y, 4] = df.iat[x+y, 0] - current_a

len(df)
df

df.to_excel(r'newdelta.xlsx', index = True)

A couple of questions that I still wasn't able to solve:

1. As you can see, on the 31st January, I should have a 0 in the Python Delta A column (Python Delta A should be identical to Weekly Delta A), but the code above gives me an error for range values between (0,25) for the x loop. (0,20) is as high as I'm allowed to go, otherwise I get the "index 25 is out of bounds for axis 0 with size 25" error. If I only use 20, the isn't giving me the values I want in the later part of the column.

2. When I export this dataframe to excel, all I get are a bunch of # signs instead of the dates as displayed inside the dataframe. How can I fix this?

3. If I don't want to do this on a weekly but on a monthly basis, how would I need to adjust the code? I.e. From the First monday of each month until the last friday of the last week that still begins in that month.

I experimented with another for loop to get the product A and B delta values at once, but this code leaves the Python delta B column blank and messes with the Python delta A column:

import pandas as pd

df = pd.read_excel('Delta.xlsx', index_col = 0)

df['Python Delta A'] = 0

df['Python Delta B'] = 0

df['dates'] = df.index

df['day_of_the_week'] = df['dates'].dt.day_name()

anchor_value = 0

for z in range(0,2):
    
    for x in range(0,20):
        
        if df.iat[x,7] == 'Monday':
        
            anchor_value = df.iat[x+z,0]
        
            df.iat[x+z,4] = 0
        
            for y in range(1,6):
            
                df.iat[x+y+z, 4] = df.iat[x+y+z, 0] - current_a


len(df)
df

df.to_excel(r'newdelta.xlsx', index = True)

[Pedroski55]

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!')

[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?