Apply a lambda function to a dataframe with NaN values?

user_11548079 0
Viewed 1095

I am trying to perform calculations that change between rows with variables that remain consistent. How can I use this lambda function when a row has incomplete data?

Follow up to this question: Create a new column based on calculations that change between rows?

#example
import pandas as pd
import numpy as np

conversion = [["a",5],["b",1],["c",10]]
conversion_table = pd.DataFrame(conversion,columns=['Variable','Cost'])

data1 = [[1,"2*a+b"],[2,"c"],[3,"2*c"],[4, np.NaN]]
to_solve = pd.DataFrame(data1,columns=['Day','Q1'])

#Desired dataframe: 

desired = [[1,11],[2,10],[3,20]]
desired_table=pd.DataFrame(desired,columns=['Day','desired output'])

#Using lambda to map values does not work when NaN is present.

#Map values
mapping = dict(zip(conversion_table['Variable'], conversion_table['Cost']))

desired_table["solved"]=to_solve['Q1'].map(lambda x: eval(''.join([str(mapping[i]) if i.isalpha() else str(i) for i in x])))

This code works when my columns do not contain NaN values, but I need this to work when I have incomplete data. I receive the following error: 'float' object is not iterable. I just want to leave the NaN values where they are and fill in the rest.

pandas dataframe dictionary lambda python
4 Answers
desired_table["solved"]=to_solve['Q1'].map(lambda x: ..., na_action='ignore')

should do what you want.

In [6]: to_solve['Q1'].map(lambda x: eval(''.join([str(mapping[i]) if i.isalpha() else str(i) for i in x])), na_action='ignore')                                                                            
Out[6]: 
0    11.0
1    10.0
2    20.0
3     NaN
Name: Q1, dtype: float64
user_65646200

You have to add an optional parameter na_action='ignore' to the .map method which propagate NaN values, without passing them to the mapping correspondence

Use:

def solve(x):
    expr = ''.join(str(mapping[k]) if k in mapping else str(k) for k in x)
    return pd.eval(expr)

desired_table["solved"]= to_solve['Q1'].map(solve, na_action='ignore')

This results the desired_table as:

   Day  desired output  solved
0    1              11    11.0
1    2              10    10.0
2    3              20    20.0
user_128331660

You can easily filter out NaN values:

desired_table["solved"]=to_solve.loc[~to_solve['Q1'].isna(),'Q1'].map(
      lambda x: eval(''.join([str(mapping[i]) if i.isalpha() else str(i) for i in x])))
user_35452730

The np.NaN is of type float, so when you try to iterate over it (as you would when using strings) you get that SyntaxError. In order to avoid it, just make another condition in the lambda function for defining a default value when you have a np.NaN in your data.

def mappingFunc(x):
    return eval(''.join([str(mapping[i]) if i.isalpha() else str(i) for i in x])) if x is not np.NaN else np.NaN

desired_table["solved"] = to_solve['Q1'].map(mappingFunc)
user_112185570
Related