pandas and numpy logic using 'isin' and 'str'

Question

Trying to create a query that returns either 3 or 2 or 1

Table

ID Field1 Field2 1 J JLP10A 2 J JLP22A 3 S JLP25C

I want if Field1=J AND the first 4 letters of Field2='JLP10' then return 3, else if Field1=J then return 2 else return 1. So ID 1 should return 3, ID 2 should return 2 and ID 3 should return 1.

I have tried the following:

Table=Table.assign(Field3=np.where(((Table.Field1=='J')&(Table.Field2.astype(str).str[0:4].isin(['JLP10', 'JLP15']))),3, np.where(Table.Field1=='J'),2,1))))

This does not return 3 for ID1..

When I remove the [0:4] condition and make Field2 actually match I get 3 for ID1.

Table=Table.assign(Field3=np.where(((Table.Field1=='J')&(Table.Field2.isin(['JLP10A', 'JLP15']))),3, np.where(Table.Field1=='J'),2,1))))

So the code is not reading the 0:4 correctly.. any ideas why??

BENY · Accepted Answer · 2018-03-06 19:02:04Z

1

The number of ')' is wrong :-) , and for str[0:4] should be str[0:5]

Table=Table.assign(Field3=np.where((Table.Field1=='J')&(Table.Field2.astype(str).str[0:5].isin(['JLP10', 'JLP15'])),3, np.where(Table.Field1=='J',2,1))) Table Out[124]: ID Field1 Field2 Field3 0 1 J JLP10A 3 1 2 J JLP22A 2 2 3 S JLP25C 1 #Table=Table.assign(Field3=np.where((Table.Field1=='J')&(Table.Field2.astype(str).str.startswith('JLP10','ABCD')),3, np.where(Table.Field1=='J',2,1)))

edited Mar 6, 2018 at 19:02

answered Mar 6, 2018 at 18:41

BENY

324k22 gold badges176 silver badges250 bronze badges

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

8 Comments

babz Over a year ago

WEN YOU THE BEST! But can you explain why it is 0:5? Wouldnt that mean first 6 characters??

BENY Over a year ago

@babz nope , it will be first 5 character :-) that why we also can do str[:5]

babz Over a year ago

I am really sorry but there are cases where Field2 is 'ABCD ', 2 blank spaces at the end. This script returns an error for those cases saying 'invalid character in identifier'

BENY Over a year ago

@babz before you doing those condition Table.Field2=Table.Field2.str.strip(), link here pandas.pydata.org/pandas-docs/stable/generated/…

BENY Over a year ago

@babz let us try str.startswith

Table=Table.assign(Field3=np.where((Table.Field1=='J')&(Table.Field2.astype(str).str.startswith('JLP10','ABCD')),3, np.where(Table.Field1=='J',2,1)))

|

Collectives™ on Stack Overflow

pandas and numpy logic using 'isin' and 'str'

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related