ValueError: invalid literal for int() with base 10: 'Pending '

Question

I am trying to update existing xlsm file depending on dataframe values rows by informing the columns I want to update in the excl file by using this code of pandas df

frm_mwfy_to_te_col = pd.read_excel(r'' + mydir + 'Governance_Tracker - Copy - Copy.xlsm',
                                   usecols=['Pending  ', 'Pending Status'], header=1, axis=1)

and this is the part of my code which I have error there

for index, row in filtered_data_cond1['SiteCode'].items():
    for col, _ in frm_mwfy_to_te_col.items(): ws.cell(row=index + 3,
                                                      column=int(col)).value = 'Value1', 'Value2'

as the filtered_data_cond1['SiteCode'] is the rows I want to update

filtered_data_cond1 = all_data.loc[all_data['SiteCode'].str.contains('|'.join(frm_mwfy_to_te.Subject))]

and this is my all_data

all_data = pd.read_excel(r'' + mydir + 'Governance_Tracker - Copy - Copy.xlsm'
                         , header=1).drop(['#'], axis=1)

and I found this error

Traceback (most recent call last):
  File "C:/Users/DELL/PycharmProjects/MyALLRefProf/MyExp.py", line 54, in <module>
    column=int(col).isnull()).value = 'Value1', 'Value2'  # example
ValueError: invalid literal for int() with base 10: 'Pending

'

Answer 1

The error is that the columns in your frm_mwfy_to_te_col dataframe are (from what I can gather) called 'Pending ' and 'Pending Status'. When you try int(col) in that line of code, you are attempting to cast the string 'Pending ' to an int, which can't be done.

Possibly try renaming the columns as integers. Or, even better, rework your logic to allow for the column names to be strings in a more pythonic way.

Edit:

Try changing your problem section of code to:

for index, row in filtered_data_cond1['SiteCode'].items():
    for col_num in range(len(frm_mwfy_to_te_col.columns)): 
        ws.cell(row=index + 3, column=col_num + 1).value = 'Value1', 'Value2'