How to assign value to rows which are between two rows with specific string in column in dataframe?

user_8536276 0
Viewed 109

I have dataframe with time series where one column has strings: Normal Value and Wrong Value. I would like to find all rows which are just between rows with Wrong Value and assign them 0 to new column. Rows which have Normal Value and are not between rows with Wrong Value should have value 1. Value column represents high peaks in time series.

Sample dataframe:

>>> df = pd.DataFrame({'Date': ['2019-01-01','2019-01-02','2019-01-03','2019-01-04','2019-01-05','2019-01-06','2019-01-07','2019-01-08','2019-01-09', '2019-01-10'],
...                    'Value': [-0.011295, -0.013431, 580944.426061, 0.000000, 0.000000, -0.999998, 0.000000, 0.000000, 712327.147257, -0.999999],
...                    'String': ['Normal Value', 'Normal Value', 'Wrong Value', 'Normal Value', 'Normal Value', 'Wrong Value', 'Normal Value', 'Normal Value', 'Wrong Value', 'Wrong Value']})
>>> df
         Date          Value        String
0  2019-01-01      -0.011295  Normal Value
1  2019-01-02      -0.013431  Normal Value
2  2019-01-03  580944.426061   Wrong Value
3  2019-01-04       0.000000  Normal Value
4  2019-01-05       0.000000  Normal Value
5  2019-01-06      -0.999998   Wrong Value
6  2019-01-07       0.000000  Normal Value
7  2019-01-08       0.000000  Normal Value
8  2019-01-09  712327.147257   Wrong Value
9  2019-01-10      -0.999999   Wrong Value

Expected output:

>>> df = pd.DataFrame({'Date': ['2019-01-01','2019-01-02','2019-01-03','2019-01-04','2019-01-05','2019-01-06','2019-01-07','2019-01-08','2019-01-09', '2019-01-10'],
...                    'Value': [-0.011295, -0.013431, 580944.426061, 0.000000, 0.000000, -0.999998, 0.000000, 0.000000, 712327.147257, -0.999999],
...                    'String': ['Normal Value', 'Normal Value', 'Wrong Value', 'Normal Value', 'Normal Value', 'Wrong Value', 'Normal Value', 'Normal Value', 'Wrong Value', 'Wrong Value'],
...                    'Expected Value': [1, 1, 0, 0, 0, 0, 1, 1, 0, 0]})
>>> df
         Date          Value        String  Expected Value
0  2019-01-01      -0.011295  Normal Value               1
1  2019-01-02      -0.013431  Normal Value               1
2  2019-01-03  580944.426061   Wrong Value               0
3  2019-01-04       0.000000  Normal Value               0
4  2019-01-05       0.000000  Normal Value               0
5  2019-01-06      -0.999998   Wrong Value               0
6  2019-01-07       0.000000  Normal Value               1
7  2019-01-08       0.000000  Normal Value               1
8  2019-01-09  712327.147257   Wrong Value               0
9  2019-01-10      -0.999999   Wrong Value               0
pandas dataframe python
3 Answers

basically what you want is to transform this list [1,1,0,1,1,0,1,1,0,0,...] (1 is normal and 0 is wrong values) to: [1,1,0,0,0,0,1,1,0,0,...]

A simple for-loop could do the job:

a = []
is_wrong = 0
for value in df['String'].values:
    if is_wrong == 0:
        if value == 'Normal Value':
            a.append(1)
        else:
            a.append(0)
            is_wrong = 1
    else:
        if value == 'Normal Value':
            a.append(0)
        else:
            a.append(0)
            is_wrong = 0
df['Expected Value'] = a

a (maybe) more elegant way could be:

a = []
is_wrong = False # store the current state
for value in df['String'].map({'Normal Value':True,'Wrong Value':False}).values:
    a.append(value and not is_wrong) # check the current state and output value
    is_wrong = is_wrong if value else not is_wrong # change the state if needed
df['Expected Value'] = [int(x) for x in a]

in both cases:

df['Expected Value'] = [1, 1, 0, 0, 0, 0, 1, 1, 0, 0]
user_119786270

there are few pandas functions that operate on adjacent rows: e.g. Series.diff or Series.pct_change or DataFrame.shift but I would solve this one basically by looping and an if clause (or trivial state machine with the states between or not between.).

import pandas as pd

df = pd.DataFrame({'Date': ['2019-01-01','2019-01-02','2019-01-03','2019-01-04','2019-01-05','2019-01-06','2019-01-07','2019-01-08','2019-01-09', '2019-01-10'],
                   'Value': [-0.011295, -0.013431, 580944.426061, 0.000000, 0.000000, -0.999998, 0.000000, 0.000000, 712327.147257, -0.999999],
                   'String': ['Normal Value', 'Normal Value', 'Wrong Value', 'Normal Value', 'Normal Value', 'Wrong Value', 'Normal Value', 'Normal Value', 'Wrong Value', 'Wrong Value']})

state_machine = {(0,"Normal Value"): (0,1),
                 (0,"Wrong Value") : (1,0),
                 (1,"Normal Value"): (1,0),
                 (1,"Wrong Value") : (0,0),
                }
state=0
expected_values = []
for s in df['String']:
    state, expected = state_machine[state,s]
    expected_values.append(expected)
df['Expected Value'] = expected_values

print(df)

 Date          Value        String  Expected Value
0  2019-01-01      -0.011295  Normal Value               1
1  2019-01-02      -0.013431  Normal Value               1
2  2019-01-03  580944.426061   Wrong Value               0
3  2019-01-04       0.000000  Normal Value               0
4  2019-01-05       0.000000  Normal Value               0
5  2019-01-06      -0.999998   Wrong Value               0
6  2019-01-07       0.000000  Normal Value               1
7  2019-01-08       0.000000  Normal Value               1
8  2019-01-09  712327.147257   Wrong Value               0
9  2019-01-10      -0.999999   Wrong Value               0
user_122073200

Python code:

import pandas as pd

def condition(x):
  if x == 'Wrong Value':
    return 0
  return 1

dict = {'Date': ['2019-01-01','2019-01-02','2019-01-03','2019-01-04','2019-01-05','2019-01-06','2019-01-07','2019-01-08','2019-01-09', '2019-01-10'],
        'Value': [-0.011295, -0.013431, 580944.426061, 0.000000, 0.000000, -0.999998, 0.000000, 0.000000, 712327.147257, -0.999999],
        'String': ['Normal Value', 'Normal Value', 'Wrong Value', 'Normal Value', 'Normal Value', 'Wrong Value', 'Normal Value', 'Normal Value', 'Wrong Value', 'Wrong Value']
       }
df = pd.DataFrame(dict)

new_df = df['String'].apply(condition)
idx = df.index[new_df < 1]

for i in range(0,len(idx),2):
  if idx[i+1] - idx[i] > 1:
    new_df.loc[idx[i]:idx[i+1]] = 0

df['Expected Value'] = new_df
print (df)

Output:

 Date          Value        String  Expected Value
0  2019-01-01      -0.011295  Normal Value               1
1  2019-01-02      -0.013431  Normal Value               1
2  2019-01-03  580944.426061   Wrong Value               0
3  2019-01-04       0.000000  Normal Value               0
4  2019-01-05       0.000000  Normal Value               0
5  2019-01-06      -0.999998   Wrong Value               0
6  2019-01-07       0.000000  Normal Value               1
7  2019-01-08       0.000000  Normal Value               1
8  2019-01-09  712327.147257   Wrong Value               0
9  2019-01-10      -0.999999   Wrong Value               0
user_122037470
Related