PreuttiP
PreuttiP

Reputation: 49

Pandas : How can I create new column using previous rows from existing column and newly created column?

From excel, I have used this formula to create new column ( column 'D' )

IF(OR(A2<>A1,AND(B2<>"000",B1="000")),D1+1,0)

I have referred data from previous row of existing column A and B as condition to create value in current row of column D and also I have refer previous column D value to make data of current cell.

If the data of column A and B are met my criteria, then I'll set my current row of D column by increasing previous data of D column by 1 else I will reset my current row of D columns of 0.

I'm try to do this in pandas (python) but I cannot make it happens.

So any recommendation? Thank you.

example from my excel

Upvotes: 1

Views: 1017

Answers (1)

PreuttiP
PreuttiP

Reputation: 49

Python Pandas Dataframe calculating new row value based on previous row value within same column

Sorry I just found this link it can help me. This code is my solution.

import pandas as pd
import numpy as np

df = pd.read_csv(r'C:\sample.csv', dtype=str)

##Adding an empty column named Active to the existing dataframe
df['answer'] = np.nan

##putting the first value as 1
df['answer'].loc[0] = 1 

df

for index in range(1,df.shape[0]):  
    if ( (df['SERIAL_NUMBER'].iloc[index] !=  df['SERIAL_NUMBER'].iloc[index-1]) | 
        (df['OPERATION'].iloc[index] != '000') & (df['OPERATION'].iloc[index-1] == '000') ):
        df['answer'].iloc[index]=df['answer'].iloc[index-1]+1
    else:
        df['answer'].iloc[index]=df['answer'].iloc[index-1]
print(df)

Upvotes: 1

Related Questions