Pandas group by recurring state

user_335427 0
Viewed 44

I have a dataset with hosts sorted by time and a state if isCorrect or not. I would like to get only the hosts that have been rated "False" for at least 3 consecutive times. That is if there is a True in between the counter should reset.

data = {'time': ['10:01', '10:02', '10:03', '10:15', '10:16', '10:18','10:20','10:21','10:22', '10:23','10:24','10:25','10:26','10:27'],
        'host': ['A','B','A','A','A','B','A','A','B','B','B','B','B','B'],
        'isCorrect': [True, True, False, True, False, False, True, True, False, False, True, False, False, False]}

     time host  isCorrect
0   10:01    A       True
1   10:02    B       True
2   10:03    A      False
3   10:15    A       True
4   10:16    A      False
5   10:18    B      False
6   10:20    A       True
7   10:21    A       True
8   10:22    B      False
9   10:23    B      False
10  10:24    B       True
11  10:25    B      False
12  10:26    B      False
13  10:27    B      False

With this example dataset there should be 2 clusters:

  1. Host B due to row 5,8,9 since they were False for 3 times in a row.
  2. Host B due to row 11,12,13

Note that it should be 2 clusters rather than 1 made of 6 items. Unfortunately my implementation does exactly that.

df = pd.DataFrame(data)
df = df[~df['isCorrect']].sort_values(['host','time'])

mask = df['host'].map(df['host'].value_counts()) >= 3

df = df[mask].copy()
df['Group'] = pd.factorize(df['host'])[0]

Which returns

     time host  isCorrect  Group
5   10:18    B      False      0
8   10:22    B      False      0
9   10:23    B      False      0
11  10:25    B      False      0
12  10:26    B      False      0
13  10:27    B      False      0

Expected is an output like so:

     time host  isCorrect  Group
5   10:18    B      False      0
8   10:22    B      False      0
9   10:23    B      False      0
11  10:25    B      False      1
12  10:26    B      False      1
13  10:27    B      False      1
pandas dataframe python
1 Answers

Solution was changed for generate new column Group after sorting with cumulative sum of Trues (because tested Falses), so is generated unique groups which are factorized in last step:

df = df.sort_values(['host','time'])

df['Group'] = df['isCorrect'].cumsum()
df = df[~df['isCorrect']]

mask = df['Group'].map(df['Group'].value_counts()) >= 3

df = df[mask].copy()
df['Group'] = pd.factorize(df['Group'])[0]
print (df)
     time host  isCorrect  Group
5   10:18    B      False      0
8   10:22    B      False      0
9   10:23    B      False      0
11  10:25    B      False      1
12  10:26    B      False      1
13  10:27    B      False      1

df = df.sort_values(['host','time'])

df['Group'] = df['isCorrect'].cumsum()
df = df[~df['isCorrect']]

mask = df['Group'].map(df['Group'].value_counts()) >= 5

df = df[mask].copy()
df['Group'] = pd.factorize(df['Group'])[0]
print (df)
Empty DataFrame
Columns: [time, host, isCorrect, Group]
Index: []
user_29010020
Related