Either operating on a numeric value or replacing cell with nan

374
March 04, 2017, at 12:40 PM

In a pandas.DataFrame df, let's say I have a column E.

If E contains an integer or float, I want to replace it everywhere with 10^6 * E.

However if E contains non-numeric characters or simply whitespace, I want to replace it with np.nan.

Is there a simple way to do this with apply or applymap and a lambda function?

As a sanity check, doing this works on the numeric rows:

df['E'] = df['E'] *  1000000 

This was syntactically correct but didn't change anything:

df['E'].apply(lambda x: x*1000000 if isinstance(x, (int, float)) else np.nan) 

Maybe I have to do some casting, not sure.

Answer 1

Instead of a lambda you can pass a function. Something like this seems to work.

Code:

def times_million(a_number):
    try:
        return float(a_number) * 1000000
    except:
        return np.nan

Test Code:

import pandas as pd
import numpy as np 
data = [x.strip().split() for x in """
    E
    1.0
    1
    xyzzy
""".split('\n')[1:-1]]
df = pd.DataFrame(data=data[1:], columns=data[0])
print(df['E'].apply(times_million))

Results:

0    1000000.0
1    1000000.0
2          NaN
Name: E, dtype: float64
READ ALSO
Determining if a specific, ordered vector is in a list/array

Determining if a specific, ordered vector is in a list/array

I am working on some molecular dynamics using Python, and the arrays tend to get pretty largeIt would be helpful to have a quick check to see if certain vectors appear in the arrays

366
Merging fields in Python [on hold]

Merging fields in Python [on hold]

I have a CSV dataset with 10 columns and thousands of rowsThere are 27 rows with the same incident number

417
Loading method parameters from file

Loading method parameters from file

Assume I have a python function that takes a few parameters:

372
How can I track all SQL query timings and counts in Django?

How can I track all SQL query timings and counts in Django?

I'd like to have a Django application record how much time each SQL query took

357