Reputation: 23
Comparing rows within groups, pandas
What I need seems kind of basic, but I'm struggling with it, so I would appreciate your help.
This code:
data = pd.DataFrame({'id' : ['a100', 'a100', 'a100', 'a200', 'a200', 'a200','a300','a300', 'a300', 'a400', 'a400', 'a400', 'a500', 'a500', 'a500', 'a600', 'a600', 'a600', 'a700', 'a700', 'a700', 'a800', 'a800', 'a800', 'a900', 'a900', 'a900'],
'type': ['euro', 'dollar', 'yen', 'euro', 'dollar', 'yen','euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen'],
'model': ['EQ', 'EQ', 'EQ', 'MC', 'MC', 'MC', 'EQ','EQ', 'EQ', 'MC', 'MC', 'MC', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ'],
'status_ant': ['A', 'A', 'A', 'A', 'A', 'A', 'B', 'C', 'A', 'B', 'C', 'A', 'A', 'A', 'A', 'B', 'C', 'B', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A'],
'status': ['B', 'C', 'A', 'B', 'C', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'B', 'C', 'B', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'A', 'B', 'A', 'C', 'C']
})
that results in this df:
id type model status_ant status
0 a100 euro EQ A B
1 a100 dollar EQ A C
2 a100 yen EQ A A
3 a200 euro MC A B
4 a200 dollar MC A C
5 a200 yen MC A A
6 a300 euro EQ B A
7 a300 dollar EQ C A
8 a300 yen EQ A A
9 a400 euro MC B A
10 a400 dollar MC C A
11 a400 yen MC A A
12 a500 euro EQ A B
13 a500 dollar EQ A C
14 a500 yen EQ A B
15 a600 euro EQ B A
16 a600 dollar EQ C A
17 a600 yen EQ B A
18 a700 euro EQ A A
19 a700 dollar EQ A A
20 a700 yen EQ A B
21 a800 euro EQ A B
22 a800 dollar EQ A A
23 a800 yen EQ A B
24 a900 euro EQ A A
25 a900 dollar EQ A C
26 a900 yen EQ A C
I need to filter the rows I want using some conditions. The logic I need is this one:
First of all, these conditions I will mention should only apply to rows where model == 'EQ'.
If there's a row where:
status_ant == 'A' and status == 'B' and type == 'euro'
AND there's another row WITHIN the same group (by id) where
status_ant == 'A' and status == 'C' and type == 'dollar'
drop those two rows (else, keep them). The same logic applies for rows were:
status_ant == 'B' and status == 'A' and type == 'euro'
AND there's another row WITHIN the same group (by id) where
status_ant == 'C' and status == 'A' and type == 'dollar'
The row with type == 'yen' shouldn't be dropped whatever the values in status_ant and status are.
Note: I know it might be confusing, but the resulting dataframe I want is this one (it exemplifies very well all the conditions I mentioned):
id type model status_ant status
2 a100 yen EQ A A
3 a200 euro MC A B
4 a200 dollar MC A C
5 a200 yen MC A A
8 a300 yen EQ A A
9 a400 euro MC B A
10 a400 dollar MC C A
11 a400 yen MC A A
14 a500 yen EQ A B
17 a600 yen EQ B A
18 a700 euro EQ A A
19 a700 dollar EQ A A
20 a700 yen EQ A B
21 a800 euro EQ A B
22 a800 dollar EQ A A
23 a800 yen EQ A B
24 a900 euro EQ A A
25 a900 dollar EQ A C
26 a900 yen EQ A C
I was looking for the answer and found something similar here: Pandas: Comparing rows within groups
but couldn't make it work for my code.
Really appreciate your help and your time. Thank you
Upvotes: 1
Views: 233
Answers (2)
Reputation: 863611
You can use this solution if for each id are unique values of column type is possible test if sum of Trues per groups is 2:
m0 = data['model']=='EQ'
m1 = (data['status_ant'] == 'A') & (data['status'] == 'B') & (data['type'] == 'euro')
m2 = (data['status_ant'] == 'A') & (data['status'] == 'C') & (data['type'] == 'dollar')
m3 = (data['status_ant'] == 'B') & (data['status'] == 'A') & (data['type'] == 'euro')
m4 = (data['status_ant'] == 'C') & (data['status'] == 'A') & (data['type'] == 'dollar')
mask = m0 & (m1 | m2 | m3 | m4)
mask_groups = mask.groupby(data['id']).transform('sum').eq(2)
data = data[~mask | ~mask_groups]
print (data)
id type model status_ant status
2 a100 yen EQ A A
3 a200 euro MC A B
4 a200 dollar MC A C
5 a200 yen MC A A
8 a300 yen EQ A A
9 a400 euro MC B A
10 a400 dollar MC C A
11 a400 yen MC A A
14 a500 yen EQ A B
17 a600 yen EQ B A
18 a700 euro EQ A A
19 a700 dollar EQ A A
20 a700 yen EQ A B
21 a800 euro EQ A B
22 a800 dollar EQ A A
23 a800 yen EQ A B
24 a900 euro EQ A A
25 a900 dollar EQ A C
26 a900 yen EQ A C
If possible values are not unique is possible test by any if match per groups, chnaged data for a100:
data = pd.DataFrame({'id' : ['a100', 'a100', 'a100', 'a200', 'a200', 'a200','a300','a300', 'a300', 'a400', 'a400', 'a400', 'a500', 'a500', 'a500', 'a600', 'a600', 'a600', 'a700', 'a700', 'a700', 'a800', 'a800', 'a800', 'a900', 'a900', 'a900'],
'type': ['euro', 'dollar', 'dollar', 'euro', 'dollar', 'yen','euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen', 'euro', 'dollar', 'yen'],
'model': ['EQ', 'EQ', 'EQ', 'MC', 'MC', 'MC', 'EQ','EQ', 'EQ', 'MC', 'MC', 'MC', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ', 'EQ'],
'status_ant': ['A', 'A', 'A', 'A', 'A', 'A', 'B', 'C', 'A', 'B', 'C', 'A', 'A', 'A', 'A', 'B', 'C', 'B', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A'],
'status': ['B', 'C', 'C', 'B', 'C', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'B', 'C', 'B', 'A', 'A', 'A', 'A', 'A', 'B', 'B', 'A', 'B', 'A', 'C', 'C']
})
m0 = data['model']=='EQ'
m1 = (data['status_ant'] == 'A') & (data['status'] == 'B') & (data['type'] == 'euro')
m2 = (data['status_ant'] == 'A') & (data['status'] == 'C') & (data['type'] == 'dollar')
m3 = (data['status_ant'] == 'B') & (data['status'] == 'A') & (data['type'] == 'euro')
m4 = (data['status_ant'] == 'C') & (data['status'] == 'A') & (data['type'] == 'dollar')
mask1 = m0 & (m1 | m3)
mask2 = m0 & (m2 | m4)
mask_euro = mask1.groupby(data['id']).transform(any)
mask_dolar = mask2.groupby(data['id']).transform(any)
data = data[~(mask1 | mask2) | ~(mask_euro & mask_dolar)]
print (data)
id type model status_ant status
3 a200 euro MC A B
4 a200 dollar MC A C
5 a200 yen MC A A
8 a300 yen EQ A A
9 a400 euro MC B A
10 a400 dollar MC C A
11 a400 yen MC A A
14 a500 yen EQ A B
17 a600 yen EQ B A
18 a700 euro EQ A A
19 a700 dollar EQ A A
20 a700 yen EQ A B
21 a800 euro EQ A B
22 a800 dollar EQ A A
23 a800 yen EQ A B
24 a900 euro EQ A A
25 a900 dollar EQ A C
26 a900 yen EQ A C
Upvotes: 1
Reputation: 8508
Run against updated Data:
Code is still the same:
print (df[~(df.model.eq('EQ') & df.status_ant.ne(df.status))])
Input Data:
data = pd.DataFrame({'id' : ['a100', 'a100', 'a100', 'a200', 'a200', 'a200','a300','a300', 'a300', 'a400', 'a400', 'a400'],
'model' : ['EQ', 'EQ', 'EQ', 'MC', 'MC', 'MC', 'EQ','EQ', 'EQ', 'MC', 'MC', 'MC', ],
'status_ant' : ['A', 'A', 'A', 'A', 'A', 'A', 'B', 'C', 'A', 'B', 'C', 'A'],
'status': ['B', 'C', 'A', 'B', 'C', 'A', 'A', 'A', 'A', 'A', 'A', 'A']
})
This got loaded into the DataFrame as:
id model status_ant status
0 a100 EQ A B #exclude this row
1 a100 EQ A C #exclude this row
2 a100 EQ A A
3 a200 MC A B
4 a200 MC A C
5 a200 MC A A
6 a300 EQ B A #exclude this row
7 a300 EQ C A #exclude this row
8 a300 EQ A A
9 a400 MC B A
10 a400 MC C A
11 a400 MC A A
Updated DataFrame:
id model status_ant status
2 a100 EQ A A
3 a200 MC A B
4 a200 MC A C
5 a200 MC A A
8 a300 EQ A A
9 a400 MC B A
10 a400 MC C A
11 a400 MC A A
Earlier Answer
Are you looking for this:
print (df[~(df.model.eq('EQ') & df.status_ant.ne(df.status))])
Note that you don't need to do a group by. You cannot do a group by as you are actually trying to get a subset of the data.
First it will check if df['model'] == 'EQ'. If true, then it will check if df['status_ant'] != df['status'].
By giving df['status_ant'] != df['status'], you are checking for all the conditions.
status_ant == 'A' and status == 'B', AND status_ant == 'A' and status == 'C'
and
status_ant == 'B' and status == 'A', AND status_ant == 'C' and status == 'A'
The output of this will be:
id model status_ant status
2 a100 EQ A A
3 a200 MC A B
4 a200 MC A C
5 a200 MC A A
8 a300 EQ A A
9 a400 MC B A
10 a400 MC C A
11 a400 MC A A
Note: If you have other values in status_ant and status, this will NOT work. For example, if status_ant is A and status is D or E and you want that record to be included, then I need to add an additional condition.
If you want to limit the check only for A, B and C, then you can give this:
print (df[~(df.model.eq('EQ') & df.status_ant.isin(['A','B','C']) & df.status.isin(['A','B','C']) & df.status_ant.ne(df.status))])
The output of this will be:
Source Code I used to create the input DataFrame:
data = pd.DataFrame({'id' : ['a100', 'a100', 'a100', 'a100','a200', 'a200', 'a200','a300','a300', 'a300', 'a400', 'a400', 'a400'],
'model' : ['EQ', 'EQ', 'EQ', 'EQ','MC', 'MC', 'MC', 'EQ','EQ', 'EQ', 'MC', 'MC', 'MC', ],
'status_ant' : ['A', 'A', 'A', 'A', 'A', 'A', 'A', 'B', 'C', 'A', 'B', 'C', 'A'],
'status': ['B', 'C', 'A', 'D', 'B', 'C', 'A', 'A', 'A', 'A', 'A', 'A', 'A']
})
df = pd.DataFrame(data)
Input DataFrame:
id model status_ant status
0 a100 EQ A B #exclude this row
1 a100 EQ A C #exclude this row
2 a100 EQ A A #include this row
3 a100 EQ A D #include this row
4 a200 MC A B
5 a200 MC A C
6 a200 MC A A
7 a300 EQ B A #exclude this row
8 a300 EQ C A #exclude this row
9 a300 EQ A A #include this row
10 a400 MC B A
11 a400 MC C A
12 a400 MC A A
Output DataFrame:
id model status_ant status
2 a100 EQ A A #included
3 a100 EQ A D #included
4 a200 MC A B
5 a200 MC A C
6 a200 MC A A
9 a300 EQ A A #included
10 a400 MC B A
11 a400 MC C A
12 a400 MC A A
See index 3. It has status = 'D' and is included in your result set.
Upvotes: 1
Related Questions
- Compare records within each group in python dataframe
- Comparing group specific rows only of a data frame grouped based on a specific column
- Comparing values of same column depending on values in different column from the same pandas dataframe
- Comparing grouped data with a certain row in DataFrame
- Pandas conditional comparison: based on multiple columns
- Pandas: row comparisons by group based on a condition across 2 columns
- Compare a row of a dataframe based on multiple conditions
- Compare pandas dataframe rows based on condition
- Pandas: Comparing rows within groups
- pandas - checking a condition for each group in a dataframe