Python: row level calculations inside if statements

user_16051588 0
Viewed 116

I'm trying to create indexes (Recurring Field) based on conditions (Year, Month and Amount) inside data. Example:

Year Month Amount Recurring
2021 3 100 1
2020 10 1000 1
2020 11 1000 2
2020 12 1000 3
2020 2 1000 1
2021 3 1000 1
2021 5 1000 1
2019 7 10000 1

The logic I'm trying to create is:

If the current row of Year is equal the previous row of Year then check 
if the current row of Amount is equal to previous row of Amount if it is true 
check if the previous row of Month is not equal to the current row of Month-1, 
if it is true then 1 else use the previous value of Recurring+1, everything else 1. 
Follows below a simple formula that runs on Excel: 
   =IF(A2=A1,IF(C2=C1,IF(B1<>(B2-1),1,D1+1),1),1)

Follows below the code that I'm trying to run in Python:

for i in range(1, len(df_pivoted)):
    if df.loc[i+1 ,'Year'] == df.loc[i ,'Year']:
        if df.loc[i+1 , 'amount'] == df_.loc[i , 'amount']:
            if df.loc[i , 'Month'] != df.loc[(i+1)-1 , 'Month']:
                df.loc[i, 'Recurring'] = 1
            else:
                    df.loc[i, 'Recurring'] = df.loc[i+1, 'Recurring']
        else:
                df.loc[i, 'Recurring'] = 1
    else:
            df.loc[i, 'Recurring'] = 1

Can someone help me out here? I am new to python and I don't know what can I do to get this working fine. Regards.

jupyter-notebook pandas python
1 Answers

If I understood your question correctly, this is what you need:

df.at[0, "Recurring"] = 1
for index in df.index[1:]:
    if df.at[index, "Year"] == df.at[index-1, "Year"] and df.at[index, "Amount"] == df.at[index-1, "Amount"] and df.at[index, "Month"] == df.at[index-1, "Month"]+1:
        df.at[index, "Recurring"] = df.at[index-1, "Recurring"]+1
    else:
        df.at[index, "Recurring"] = 1
user_98576310
Related