Pandas iterrows can't skip row during iteration if condition met

user_10659560 0
Viewed 1279

I'm using iterrows to loop through a dataframe and compare row n with row n+1. the algorithm is as follows:

if columns 0,1,2 of row_n != columns 0,1,2 of row_n+1
output row = row_n 
then check row_n+1 vs row_n+2...

if columns 0,1,2 of row_n == columns 0,1,2 of row_n+1
output row columns 0,1,2,3 = row_n columns 0,1,2,3
output row column 4 = (row_n column 4 + row_n+1 column 4)
then "skip one row" and check row_n+2 vs row_n+3...

my current code works for the first comparison but then stops. My guess is that the problem is happening when Im trying to "skip one row". Im trying to use index = index+1 but the output does not look as expected. How can I fix this?

    row_iterator = TSG_table_sorted.iterrows()
    _, row_n1 = row_iterator.__next__()

    for index, row_n0 in row_iterator:
        Terminal_ID_n0 = row_n0['Terminal_ID'];
        TSG_n0 = row_n0['TSG'];
        Date_n0 = row_n0['Date'];
        Vol_n0 = row_n0['Vol'];     

        Terminal_no_n0 = row_n0['Terminal_no'];

        Terminal_ID_n1 = row_n1['Terminal_ID'];
        TSG_n1 = row_n1['TSG'];
        Date_n1 = row_n1['Date'];
        Vol_n1 = row_n1['Vol'];        

        if (Terminal_ID_n0==Terminal_ID_n1 and TSG_n0==TSG_n1 and Date_n0==Date_n1):
            new_vol=Vol_n0+Vol_n1;
            output_table.loc[i]=[Terminal_ID_n0,TSG_n0,Date_n0,Terminal_no_n0,new_vol]
            i=i+1;
        else:
            output_table.loc[i]=[Terminal_ID_n0,TSG_n0,Date_n0,Terminal_no_n0,Vol_n0]    
            i=i+1;
            index=index+1;



    input
          Terminal_ID                TSG        Date Terminal_no  Vol
    508     t_tel_003          CashCheck   10/1/2018         003   61
    9605    t_tel_003          CashCheck   10/1/2018         003    3
    2309    t_tel_003  CommercialDeposit   10/1/2018         003   12
    4439    t_tel_003  CommercialDeposit   10/1/2018         003   10
    9513    t_tel_003  CommercialDeposit   10/1/2018         003  122
    12282   t_tel_003  CommercialDeposit   10/1/2018         003    1

    current output
          Terminal_ID                TSG        Date Terminal_no  Vol
    0       t_tel_003          CashCheck   10/1/2018         003   64
    1       t_tel_003  CommercialDeposit   10/1/2018         003   12
    2       t_tel_003  CommercialDeposit   10/1/2018         003   10
    3       t_tel_003  CommercialDeposit   10/1/2018         003  122
    4       t_tel_003  CommercialDeposit   10/1/2018         003    1

    expected output
          Terminal_ID                TSG        Date Terminal_no  Vol
    0       t_tel_003          CashCheck   10/1/2018         003   64
    1       t_tel_003  CommercialDeposit   10/1/2018         003   22
    3       t_tel_003  CommercialDeposit   10/1/2018         003  123
pandas python loops
1 Answers

Assuming your dataframe looks like (i have added 2 extra rows at the bottom since your example had nothing to reproduce the else part of the code) :

    Terminal_ID TSG                 Date       Terminal_no  Vol
0   t_tel_003   CashCheck           2018-01-10  3           61
1   t_tel_003   CashCheck           2018-01-10  3           3
2   t_tel_003   CommercialDeposit   2018-01-10  3           12
3   t_tel_003   CommercialDeposit   2018-01-10  3           10
4   t_tel_003   CommercialDeposit   2018-01-10  3           122
5   t_tel_003   CommercialDeposit   2018-01-10  3           1
6   t_tel_004   CommercialDeposit   2018-01-10  3           1
7   t_tel_003   CommercialDeposit   2018-01-10  4           1

As you see the last 2 rows are totally different and doesnot have a match considering all the 4 columns(hence the output should have these 2 rows as it is):

Using the below:

df_dup = df.groupby([df.index//2,'Terminal_ID','TSG','Date','Terminal_no'])[df.columns].apply(lambda x : x[x[x.columns[:-1]].duplicated(keep=False)]['Vol'].sum()).reset_index().rename(columns={0:'Vol'}).drop('level_0',axis=1).replace(0,np.nan).dropna()
df_uniq =df[~df[df.columns[:-1]].duplicated(keep=False)]

pd.concat([df_dup,df_uniq],ignore_index=True)

Output

    Terminal_ID TSG                 Date       Terminal_no  Vol
0   t_tel_003   CashCheck           2018-01-10  3           64.0
1   t_tel_003   CommercialDeposit   2018-01-10  3           22.0
2   t_tel_003   CommercialDeposit   2018-01-10  3           123.0
3   t_tel_004   CommercialDeposit   2018-01-10  3           1.0
4   t_tel_003   CommercialDeposit   2018-01-10  4           1.0

Explaination df_dup Groupby every 2 rows using df.index//2 under groupby , then apply function every group which checks if every group(here 2 rows excluding the last column Vol) is same and then does a sum on the Vol column.

df_uniq : Filter the totally unique values. Finally concat both to get your desired output.

Hope this helps. Let me know if not.

user_98406370
Related