pandas return dataframe rows that are not numbers between 0 and 1

user_15075354 0
Viewed 310

I have a dataframe:

  Region     Value       ID
    Asia       N/A    XYAZZ
    Asia       '0'    ZSJDS
    EMEA       '2'    KKKKK
    USME    '0.50'    KLJSA
    APAC    NODATA    JKUUS
    APAC    '1.00'    JKUUC

In 'Value' column I have strings that may contain a number as string, N/A or any other string. I need a code that will return me any values that are NOT in this range: 0 < x <= 1, like this:

  Region     Value       ID
    Asia       N/A    XYAZZ
    Asia       '0'    ZSJDS
    EMEA       '2'    KKKKK
    APAC    NODATA    JKUUS

I tried something like this

  data_types_dict = {'Value': float}
  df = df.astype(data_types_dict, errors='ignore')
  df = df[(df['Value'] <= 0) | (df['Value'] > 1)]

but it works only when there are only numbers and string-numbers, it fails when converting NODATA to float. I thought that I could first filter out all rows with strings that are non convertible to float, then perform the above operation, then concat it, like this:

df1 = df1['Value'].StringsNonFloat
df2 = df[(df['Value'] <= 0) | (df['Value'] > 1)]
final_df = concat([df1, df2])

but is this pythonic? is there such a function?

Edit for Wilian

thats my data before and after:

print(' - - - BEFORE - - - :')
print(df['Value'].head(27).to_dict())
df = df[~(df['Value'].apply(pd.to_numeric, errors='coerce').notnull() & df['Value'].apply(pd.to_numeric, errors='coerce').between(0, 1, inclusive='right'))]
print(' - - - AFTER - - - :')
print(df['Value'].head(27).to_dict())

Output:

 - - - BEFORE - - - :
{24: '-1', 67: 'NODATA', 114: '1.1', 148: '-1', 164: '0', 326: '1.1', 370: '0.012', 682: '0.012', 861: '0', 900: '', 1120: '0.012', 1226: '0.12775331008000002', 1329: '0.028300000000000002', 1512: '0.012', 1540: '0.012', 1783: '0.012', 1788: 'N/A', 1900: '0.012', 1927: '0.048', 2127: '0.012', 2137: '0.012', 2236: '0.012', 2265: '0.012', 2515: '0.012', 2713: '0.012', 2807: '1.0', 2843: '1.0'}
 - - - AFTER- - - :
{24: '-1', 67: 'NODATA', 114: '1.1', 148: '-1', 326: '1.1', 900: '', 1788: 'N/A'}

line 164 and 861 are missing. At the same time '' from line 900 or N/A from 1788 are catched, the problem is clearly with 0.

In Excel, 164 is '0 , and 861 is just 0, but the point is that I can't trust that data and there might more more sources in the future so it must all happen here.

dtype pandas dataframe python
2 Answers

I believe you may be looking for something like this:

df = df[(df["Value"].astype(float) > 1) | (df["Value"].astype(float) <= 0)]

So taking the data frame where the value as type float is greater than one or less than/equal to 0.

This is very similar to your code but using .astype(float) instead.

Edit (following comments)

I would do the following:

Start by converting value to float if possible

df["Value"] = [float(value) if value.isdigit() else value for value in df["Value"]]

Then subset by only those that are float:

df = df[df["Value"].apply(lambda x: isinstance(x,float))]

Then subset again by those that are in the desired range:

df = df[(df["Value"] > 1) | (df["Value"] <= 0)]
user_154318470 
df = df[~(df['Value'].apply(pd.to_numeric, errors='coerce').notnull() & df['Value'].apply(pd.to_numeric, errors='coerce').
    between(0, 1, inclusive='right'))]

print(df)

output:

  Region   Value     ID
0   Asia     NaN  XYAZZ
1   Asia       0  ZSJDS
2   EMEA       2  KKKKK
4   APAC  NODATA  JKUUS
user_162677930
Related