CODEWITHSUNDEEP
pythonpandasnumpy
babz
babz

Reputation: 479

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

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??

Upvotes: 0

Views: 375

Answers (1)

BENY
BENY

Reputation: 323306

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)))

Upvotes: 1

Related Questions