Replicate Excel operations with Python - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: General Coding Help (https://python-forum.io/forum-8.html) +--- Thread: Replicate Excel operations with Python (/thread-37174.html) |
Replicate Excel operations with Python - Lumberjack - May-09-2022 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 + 2Is my general approach to this correct? How should I code out the parts that are still unclear to me? RE: Replicate Excel operations with Python - Lumberjack - May-09-2022 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) RE: Replicate Excel operations with Python - Pedroski55 - May-10-2022 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!') RE: Replicate Excel operations with Python - Lumberjack - May-10-2022 (May-10-2022, 01:09 AM)Pedroski55 Wrote: I hardly ever use pandas, so I don't know much about it. 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? |