Pandas: Replace missing dataframe values / conditional calculation: fillna

user_4395271 0
Viewed 1856

I want to calculate a pandas dataframe, but some rows contain missing values. For those missing values, i want to use a diffent algorithm. Lets say:

  • If column B contains a value, then substract A from B
  • If column B does not contain a value, then subtract A from C
import pandas as pd
df = pd.DataFrame({'a':[1,2,3,4], 'b':[1,1,None,1],'c':[2,2,2,2]})
df['calc'] = df['b']-df['a']

results in:

print(df)
   a    b  c  calc
0  1  1.0  2   0.0
1  2  1.0  2  -1.0
2  3  NaN  2   NaN
3  4  1.0  2  -3.0

Approach 1: fill the NaN rows using .where:

df['calc'].where(df['b'].isnull()) = df['c']-df['a']

which results in SyntaxError: cannot assign to function call.

Approach 2: fill the NaN rows using .iterrows():

for index, row in df.iterrows():
    i = df['calc'].iloc[index]

    if pd.isnull(row['b']):
        i = row['c']-row['a']
        print(i)
    else:
        i = row['b']-row['a']
        print(i)

is executed without errors and calculation is correct, these i values are printed to the console:

0.0
-1.0
-1.0
-3.0

but the values are not written into df['calc'], the datafram remains as is:

print(df['calc'])
0    0.0
1   -1.0
2    NaN
3   -3.0

What is the correct way of overwriting the NaN values?

pandas dataframe nan python
3 Answers

Finally, I stumbled over .fillna:

df['calc'] = df['calc'].fillna( df['c']-df['a'] )

gets the job done! Can anyone explain what is wrong with above two approaches...?

user_43952710

Approach 2:

you are assigning it to i value. but this won't modify your original dataframe.

for index, row in df.iterrows():
    i = df['calc'].iloc[index]

    if pd.isnull(row['b']):
        i = row['c']-row['a']
        print(i)
    else:
        i = row['b']-row['a']
        print(i)
    df.loc[index,'calc'] = i #<------------- here

also don't use iterrows() it is too slow.

Approach 1: Pandas where() method is used to check a data frame for one or more condition and return the result accordingly. By default, The rows not satisfying the condition are filled with NaN value.

it should be:

df['calc'] = df['calc'].where(df['b'].isnull(), df['c']-df['a'])

but this will only find those row value where you have non zero value and fill that with the given value.

Use:

df['calc'] = df['calc'].where(~df['b'].isnull(), df['c']-df['a'])

OR

df['calc'] = np.where(df['b'].isnull(), df['c']-df['a'], df['calc'])
user_66603730

Instead of subtracting b from a then c from a what you can do is first fill the nan values in column b with the values from column c, then subtract column a:

df['calc'] = df['b'].fillna(df['c']) - df['a']

   a    b  c  calc
0  1  1.0  2   0.0
1  2  1.0  2  -1.0
2  3  NaN  2  -1.0
3  4  1.0  2  -3.0
user_128331660
Related