Select values from one columns based on values from another column - python

Question

I have a large dataframe df1 that looks like this:

DeviceID     Location
1            Internal
1            External   
2            Internal   
2            Internal   
3            Internal   
3            External   
3            Internal
4            Internal   
4            Internal
5            External   
5            Internal

I'm trying to find and select the rows where a single DeviceID is recorded with both "Internal" AND "External" values in the Location column.

The next step would be to drop these rows from the dataframe. The final dataframe df2 would look like so:

DeviceID     Location
2            Internal   
2            Internal   
4            Internal   
4            Internal

What I've attempted so far is: indexDI = df[(df['Location'] == 'Internal') & df['Location'] == 'External') ].index df.drop(indexDI, inplace = True)

but this seems to have to dropped all the rows with "Internal".

Any help would be appreciated :)

yatu · Accepted Answer · 2019-09-25 12:13:32Z

1

You can groupby, transform with the nunique to see which gorups contain two different values and use the result to perform boolean indexing on the dataframe:

df[df.groupby('DeviceID').Location.transform('nunique').eq(1)]

     DeviceID  Location
2         2  Internal
3         2  Internal
7         4  Internal
8         4  Internal

Simple add reset_index(drop=True) for a panda's RangeIndex

edited Sep 25, 2019 at 12:13

answered Sep 25, 2019 at 12:02

yatu

88.6k12 gold badges93 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Quang Hoang Over a year ago

You don't need .loc. reset_index is not really needed either.

yatu Over a year ago

Yup, though a RangeIndex will presumably be what op wants. But yeah I guess iI'll just add a note @QuangHoang

JSRB · Accepted Answer · 2019-09-25 12:04:39Z

0

One solution would be to loop through the entire df and drop the rows if both conditions are met. Right now your solution checks the same row which can't have both criteria at once.

answered Sep 25, 2019 at 12:04

JSRB

2,6252 gold badges20 silver badges58 bronze badges

1 Comment

Mohamed Thasin ah Over a year ago

It would be appreciable if you show running example.

Mohamed Thasin ah · Accepted Answer · 2019-09-25 12:16:12Z

0

one more solution without .loc,

df[(df.groupby('DeviceID').transform('nunique')!=2).values]

O/P:

   DeviceID  Location
2         2  Internal
3         2  Internal
7         4  Internal
8         4  Internal

answered Sep 25, 2019 at 12:16

Mohamed Thasin ah

11.2k11 gold badges65 silver badges120 bronze badges

Collectives™ on Stack Overflow

Select values from one columns based on values from another column - python

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related