how to compare two rows and get the result via index in Python?

user_11382158 0
Viewed 79

I have a Dataframe 

  Day Value
1  Mon   0
2  Mon   1
3  Fri   1
4  Mon   1
5  Mon   1
6  Mon   1
7  Mon   0
8  Tue   1
9  Tue   1

And I want to find a row having two consecutive 1 by Grouping with Day column.

Expected Output:

  Day  Value
5 Mon   1
6 Mon   1
9 Tue   1
pandas-groupby pandas python-3.x python
2 Answers

Use boolean indexing for filtering:

m = df['Value'].eq(1)
s = df['Day'].ne(df['Day'].shift()).cumsum()
df = df[s[m].duplicated() & m]
print (df)
   Day  Value
5  Mon      1
6  Mon      1
9  Tue      1

Details:

First create consecutive Series with Series.shift and Series.ne and Series.cumsum:

print (df['Day'].ne(df['Day'].shift()).cumsum())
1    1
2    1
3    2
4    3
5    3
6    3
7    3
8    4
9    4
Name: Day, dtype: int32

then filter by 1 values in Value: 

print (s[m])
2    1
3    2
4    3
5    3
6    3
8    4
9    4
Name: Day, dtype: int32

And get Series.duplicated for all dupes of helper Series called s:

print (s[m].duplicated())
2    False
3    False
4    False
5     True
6     True
8    False
9     True
Name: Day, dtype: bool

Last chain with bitwise AND by & for mask with same size like original:

print (s[m].duplicated() & m)
1    False
2    False
3    False
4    False
5     True
6     True
7    False
8    False
9     True
dtype: bool
user_29010020

I'm not familiar with pandas as a data structure so I can't provide working code, but this would be my approach on this problem:

previous_value = 0
all_correct_rows = []
for i in data_row:
   if current_value == 1 and previous_value == 1:
      all_correct_rows.append(current_row)
   previous_value = current_value

Where previous_value and current_value are the values in the day column.

user_83113300
Related