For Loop

[tmyers]

This one is a little off the wall and understand if this isn't the right place to ask.

I am trying to loop through a table in Excel and if my control column does not equal a specific value, append it to another sheet that is a copy of a report template. While I could likely do this in VBA, the kicker is I am trying to do it using Python utilizing either xlwings, openpyxl or pandas.

Long story short, I am using Python to do an API call, pull a HTML table and write it to an Excel sheet (using a pandas dataframe) then trying to parse through it and clean it up while writing to a report. Since I found it easier to do the GET request in Python, I decided to stay in python for the entire process as a learning experience. Any chance anyone could offer guidance on how to construct a typical VBA for loop in Python?

 

[arnelgp]

to be honest, you would get much answers to Python groups/forums

 

[tmyers]

I figured that was likely the case, but you guys are always so helpful I figured I would stop here first

 

[Isaac]

I cannot even remember the syntax now, but a few months ago I (at someone else's request at my job) delved into Python a tiny bit, and it was pretty straight forward to do a loop. If I remember right it almost seemed like there was a keyword and then a series being referenced - but you can probably as arnel said find better help elsewhere good luck

 

[tmyers]

The good people over at Stack Overflow got me sorted. I actually did not even have to use a loop and was able to do it all within pandas before even dropping it into Excel. Took two whole lines of code to do it (yay python).

 

[Gasman]

I

The good people over at Stack Overflow got me sorted. I actually did not even have to use a loop and was able to do it all within pandas before even dropping it into Excel. Took two whole lines of code to do it (yay python).

Pandas are cute as well.

 

[tmyers]

Just for record sake, here is what ended up being used:

import pandas as pd

df = pd.read_html(url)[0]
df = df[df['Progress Point'] != 'Cancelled']
columns_to_drop = ['Line Nbr', 'Total Qty', 'Catalog Description', 'Shipping Loc', 'Reserved Qty', 'Shipment Leg']
df = df.drop(columns=columns_to_drop)

The dataframe was populated with the HTML table, I dropped all rows that had the Cancelled value in the Progress Point column, then dropped all unwanted columns from the dataframe.

Using xlwings, I then dropped the data frame into the sheet while leaving out the indexing and headers:

import xlwings as xw

wb = xw.books.active
ws = wb.sheets['{} Report'.format(OrderNum)]
ws.range('A16').options(index=False, header=False).value = df

No loop required

 

[Isaac]

The good people over at Stack Overflow

They are good as in expert but can be strict LOL

 

[Gasman]

The good people over at Stack Overflow

Did you mention that you had crossposted?

 

[tmyers]

They are good as in expert but can be strict LOL

That is an understatement. I don't typically ever ask a question there due to how super strict they are and if the question had ever been asked, regardless of how old the original question was, they will dock you for it. Posting there gives me anxiety lol.