CODEWITHSUNDEEP
python-3.xpandas
jalazbe
jalazbe

Reputation: 2005

pandas select rows that can not be converted to int or float

I am working on a project in which a column "ORIGINAL_VALUE" is filled with data. In some cases the server responds with an error that is not a number.

By doing the following I am able to detect when there has been one of those errors. 

try:
    df['ORIGINAL_VALUE'] = pd.to_numeric(df['ORIGINAL_VALUE']) 
except ValueError:
    # I want to register on my log the message recived on ORIGINAL VALUE
    log.exception("")

For example:

df['ORIGINAL_VALUE'] contains:
    1.1
    2.0
    3.3
    'Wrong date format' 
    5.0
    6.0

I would like to register the error found by selecting the rows that can't be transformed to float.

Upvotes: 6

Views: 5923

Answers (1)

jezrael
jezrael

Reputation: 862511

Add parameter errors='coerce' to to_numeric for convert problematic values to NaNs, so for check use Series.isna with boolean indexing - then if necessary convert to list and join to string:

try:
    df['ORIGINAL_VALUE'] = pd.to_numeric(df['ORIGINAL_VALUE']) 
except ValueError:
    # I want to register on my log the message recived on ORIGINAL VALUE
    mask = pd.to_numeric(df['ORIGINAL_VALUE'], errors='coerce').isna() 
    #if possible missing values
    #mask = pd.to_numeric(df['ORIGINAL_VALUE'].fillna('0'), errors='coerce').isna() 
    L = df.loc[mask, 'ORIGINAL_VALUE'].tolist()
    print ("Not converted values are: " + ", ".join(L))
    #Not converted values are: Wrong date format

    log.exception("Not converted values are: " + ", ".join(L))

If need all problematic values in separate log:

try:
    df['ORIGINAL_VALUE'] = pd.to_numeric(df['ORIGINAL_VALUE']) 
except ValueError:
    # I want to register on my log the message recived on ORIGINAL VALUE
    mask = pd.to_numeric(df['ORIGINAL_VALUE'].fillna('0'), errors='coerce').isna() 
    L = df.loc[mask, 'ORIGINAL_VALUE'].tolist()
    for val in L:
        print (f"Not converted values are: {val}")
        log.exception(f"Not converted values are: {val}")

Upvotes: 10

Related Questions