How to get rows where column value starts with 2 or 3 digits and inch symbol (")

user_4435175 0
Viewed 66

I have a df with rows like:

index | text
0     | '28,3" LEDTV K98765 AB12345 EU'
1     | '65" LEDTV K98765 AB12345 EU'
2     | '55,3" LEDTV K98765 AB12345 EU'
3     | 'MON 22,8" LED U754 PL333 DE'
4     | 'DAB Radio Work 34RT55 Blue'

Every TV starts with the size in inch ("28,3" / "65" / "55,3") and has the word "TV" somewhere in the text.

I need to know which of those products are TVs and if yes, if they the screen size is greater than 55".

In this example row 1 and 2 both fit this criterion.

End result should be:

index | text                            | tvandbiggerthan55
0     | '28,3" LEDTV K98765 AB12345 EU' | 0 
1     | '65" LEDTV K98765 AB12345 EU'   | 1
2     | '55,3" LEDTV K98765 AB12345 EU' | 1
3     | 'MON 22,8" LED U754 PL333 DE'   | 0
4     | 'DAB Radio Work 34RT55 Blue'    | 0

How can I check this for the whole column in one go?

pandas series conditional-statements string
2 Answers

Use Series.str.extract for get numbers before ", replace , and convert to floats, so possible compare by Series.gt for greater, for second mask use Series.str.contains and for 1,0 map is used Series.view:

m1 = (df['text'].str.extract('(\d+,\d+|\d+)"', expand=False)
               .str.replace(',','.')
               .astype(float)
               .gt(55))
m2 = df['text'].str.contains('TV')
df['tvandbiggerthan55'] = (m1 & m2).view('i1')
print (df)
                              text  tvandbiggerthan55
0  '28,3" LEDTV K98765 AB12345 EU'                  0
1    '65" LEDTV K98765 AB12345 EU'                  1
2  '55,3" LEDTV K98765 AB12345 EU'                  1
3    'MON 22,8" LED U754 PL333 DE'                  0
4     'DAB Radio Work 34RT55 Blue'                  0
user_29010020

Try this chained solution;

      df['tvandbiggerthan55']=((df.assign(tvandbiggerthan55=\
df[df.text.str.contains('^\d|TV')])\
['tvandbiggerthan55'].str.extract\
('(^\d+)')).astype(float)>=55).astype(int)

                        text          tvandbiggerthan55
0  28,3" LEDTV K98765 AB12345 EU                  0
1    65" LEDTV K98765 AB12345 EU                  1
2  55,3" LEDTV K98765 AB12345 EU                  1
3    MON 22,8" LED U754 PL333 DE                  0
4     DAB Radio Work 34RT55 Blue                  0

How it works

# Extract df where text begins with a digit and also contains TV
df.assign(tvandbiggerthan55=df[df.text.str.contains('^\d|TV')])

  #modify the df above to extract RV inches
df.assign(tvandbiggerthan55=df[df.text.str.contains('^\d|TV')])['tvandbiggerthan55'].str.extract('(^\d+)')

# Converts the TV inches extracted above into a float and test if it is equal or greater than 55

((df.assign(tvandbiggerthan55=df[df.text.str.contains('^\d|TV')])['tvandbiggerthan55'].str.extract('(^\d+)')).astype(float)>=55)

 # Convert the boolean from above into integers by chaining
.astype(int)
user_89869750
Related