22
$\begingroup$

Suppose I have a 5*3 data frame in which third column contains missing value

1 2 3
4 5 NaN
7 8 9
3 2 NaN
5 6 NaN

I hope to generate value for missing value based rule that first product second column

1 2 3
4 5 20 <--4*5
7 8 9
3 2 6 <-- 3*2
5 6 30 <-- 5*6

How can I do it use data frame? Thanks.

How to add condition to calculate missing value like this?

if 1st % 2 == 0 then 3rd = 1st * 2nd else 3rd = 1st + 2nd

1 2 3
4 5 20 <-- 4*5 because 4%2==0
7 8 9
3 2 5 <-- 3+2 because 3%2==1
5 6 11 <-- 5+6 because 5%2==1
$\endgroup$
  • $\begingroup$ You can't do this because the size will not be equal $\endgroup$ – Mayur Dangar Jul 7 '18 at 5:53
  • $\begingroup$ Can you expand your answer? Why isn't it possible and what could he possibly do to solve problem? $\endgroup$ – Damian Melniczuk Jul 7 '18 at 6:25
  • $\begingroup$ hey even i have the same question . but what if the data i deal with is textual ? that is the condition is like "if 'ingredients' contains chicken then 'type'= non-veg" $\endgroup$ – user7389747 Feb 14 '19 at 6:20
19
$\begingroup$

Assuming three columns of your dataframe is a, b and c. This is what you want:

df['c'] = df.apply(
    lambda row: row['a']*row['b'] if np.isnan(row['c']) else row['c'],
    axis=1
)

Full code:

df = pd.DataFrame(
    np.array([[1, 2, 3], [4, 5, np.nan], [7, 8, 9], [3, 2, np.nan], [5, 6, np.nan]]), 
    columns=['a', 'b', 'c']
)
df['c'] = df.apply(
    lambda row: row['a']*row['b'] if np.isnan(row['c']) else row['c'],
    axis=1
)
$\endgroup$
  • $\begingroup$ A few years late but this only works when the columns are numeric. np.isnan does not support non-numeric data. It's not an issue here as the OP had numeric columns and arithmetic operations but otherwise pd.isnull is a better alternative. $\endgroup$ – Adarsh Chavakula Jan 3 at 21:50
4
$\begingroup$

What about using the fillna() method of the dataframe?

df['C'].fillna(df.A * df.B)

$\endgroup$
3
$\begingroup$

Another option:

df.loc[(pd.isnull(df.C)), 'C'] = df.A * df.B

$\endgroup$
2
$\begingroup$

Assuming that the three columns in your dataframe are a, b and c. Then you can do the required operation like this:

values = df['a'] * df['b']
df['c'] = values.where(df['c'] == np.nan, others=df['c'])
$\endgroup$
  • 1
    $\begingroup$ Or np.where(pd.isnull(df.c), df.a * df.b, df.c) $\endgroup$ – Valentas Apr 16 '18 at 7:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.