CODEWITHSUNDEEP
python-3.xpandasgroup-by
Nickel
Nickel

Reputation: 590

How to replace column values based on previous value by condition and select rows from dataframe

I have dataframe with two columns X1 and X2

first thing: In X2 i have value 0 and 1 , if in X2 value is 1 when this change from 1 to zero then in next 20 rows should be 1 not zero.

for example : 

X2=(0,0,0,0,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0)

desired X2=(0,0,0,0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1)

Second thing: if X1=0 and X2=1 then select rows from dataframe until X2 value remains 1 I tried this piece of code but it selects only one row.

df1=df[(df['X1'] == 0) & (df['X2'] ==1)]

Upvotes: 1

Views: 1102

Answers (3)

ecortazar
ecortazar

Reputation: 1422

Edited to include both parts:

# First Thing:
df['X2'] = df['X2'].replace({0: np.nan}).ffill(limit=20).fillna(0)

# Second Thing:
df.loc[(df['X1'] == 0) & (df['X2'] == 1), 'new X2'] = 1
df.loc[(df['X2'] == 0), 'new X2'] = 0
df['new X2'] = df['new X2'].ffill()
df.loc[df['new X2'] == 1] # Selected Rows

Upvotes: 2

Frenchy
Frenchy

Reputation: 17007

Your dataframe is not big so you could use easily loop to resolve your problem:

#first prog
index = 0
while index < df.shape[0]:
    if index + 1 < df.shape[0] and df['X2'][index] == 1 and df['X2'][index + 1] == 0:
        df.loc[index +1: index + 20,'X2'] = 1            #set 1 to next 20 rows
        break;
    index = index + 1 

print(df)

#second prog assuming you have a column X1/X2
df['select'] = False
for index, row in df.iterrows():
    if index > 0 and df['select'][index - 1] == True and row.X2 == 1:
        df.loc[index, 'select'] = True
    if row.X1 == 0 and row.X2 == 1:
        df.loc[index, 'select'] = True

df = df[df['select'] == True].drop('select', axis=1) 

print(df)

Upvotes: 1

John Sloper
John Sloper

Reputation: 1821

Here is a solution to the "First thing" using numpy.

import numpy as np

locs =np.where(df['X2'].diff() == -1)[0]
for loc in locs:
    df.loc[slice(loc, loc+20), 'X2'] = 1

Upvotes: 0

Related Questions