30
$\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$
3
  • $\begingroup$ You can't do this because the size will not be equal $\endgroup$ Jul 7, 2018 at 5:53
  • $\begingroup$ Can you expand your answer? Why isn't it possible and what could he possibly do to solve problem? $\endgroup$ Jul 7, 2018 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$ Feb 14, 2019 at 6:20

6 Answers 6

33
$\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$
1
  • $\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$ Jan 3, 2020 at 21:50
14
$\begingroup$

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

df['C'].fillna(df.A * df.B)
$\endgroup$
5
$\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
  • 2
    $\begingroup$ Or np.where(pd.isnull(df.c), df.a * df.b, df.c) $\endgroup$
    – Valentas
    Apr 16, 2018 at 7:18
5
$\begingroup$

Another option:

df.loc[(pd.isnull(df.C)), 'C'] = df.A * df.B
$\endgroup$
0
$\begingroup$
for i in df["a"]:
if i%2==0:
    df["c"].fillna(value=df["a"]*df["b"])
else:
    df["c"].fillna(value=df["a"]+df["b"])

A simple solution for beginners.

$\endgroup$
0
$\begingroup$

In the first case you can simply use fillna:

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

In the second case you need to create a temporary column:

df['temp'] = np.where(df.a % 2 == 0, df.a * df.b, df.a + df.b)
df['c'] = df.c.fillna(df.temp)
df.drop('temp', axis=1, inplace=True)
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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