A) You’re right, output_table_1.at[i, 'Cleared?(Yes/No)'] = "Yes" must remain. My mistake.
B) There’s no need to check for the value of i: when it becomes = num_of_rows-1, the start value of the inner loop (i+1=num_of_rows) becomes greater than the stop value (num_of_rows-1) and the inner loop is skipped. The problem’s not there.
C) Both lines you quote access the same cell of the dataframe. You access the values using an array-like index, e.g.
output_table_1['Cleared?(Yes/No)'][0]
while I must use the dataframe index
output_table_1.at["Row0", 'Cleared?(Yes/No)']
so I need to reindex the dataframe in order to use the loops. I thought that the non-numerical indexing of the dataframe would cause errors, but that’s not true if one uses your syntax.
The difference between the two methods becomes relevant when you set the cell value. Your method raises a warning, and this page explains why
D) When I say
You probably mean “output_table_1[‘Cleared?(Yes/No)’][j]”
I mean “after the start of the inner loop”, because I assume you don’t want to check the same value you’ve checked in the outer loop to continue without executing the inner one
import pandas as pd
# Copy input to output
num_of_rows = len(input_table_1)
s = pd.Series([x for x in range(num_of_rows)])
input_table_1.set_index(s, inplace=True)
output_table_1 = input_table_1.copy()
for i in range(num_of_rows):
''' NO NEED: LOOP WILL STOP AT num_of_rows-1
AND NEXT LOOP WON'T START BECAUSE start value = i+1 = num_of_rows > stop value = num_of_rows-1
if i == num_of_rows: #Last row, do nothing
break
'''
if output_table_1.at[i,'Cleared?(Yes/No)'] == "Yes": #Row is already flagged, skip and move on
continue
for j in range(i+1, num_of_rows): #On to the next rows
if output_table_1.at[j,'Cleared?(Yes/No)'] == "Yes": #Row being compared to is already flagged, skip and move on
continue
if (
input_table_1.at[i, 'Cashlocation'] == input_table_1.at[j, 'Cashlocation']
and input_table_1.at[i, 'Currency'] == input_table_1.at[j, 'Currency']
and input_table_1.at[i, 'Amount'] == input_table_1.at[j, 'Amount']
and input_table_1.at[i, 'ETID'] != input_table_1.at[j, 'ETID']
and input_table_1.at[i, 'ETID'][0:2] in ["CS", "CT"]
and input_table_1.at[j,'ETID'][0:2] in ["CS", "CT"]
and (
(input_table_1.at[i, 'Transaction SubType'] == "BUY" and input_table_1.at[j, 'Transaction SubType'] == "SELL")
or
(input_table_1.at[i, 'Transaction SubType'] == "SELL" and input_table_1.at[j, 'Transaction SubType'] == "BUY")
)
):
output_table_1.at[i, 'Cleared?(Yes/No)'] = "Yes"
output_table_1.at[j, 'Cleared?(Yes/No)'] = "Yes"
print(i, j, "Case I")
break
