28
$\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 '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$ 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$ Feb 14 '19 at 6:20
32
$\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 '20 at 21:50
11
$\begingroup$

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

df['C'].fillna(df.A * df.B)
$\endgroup$
5
$\begingroup$

Another option:

df.loc[(pd.isnull(df.C)), 'C'] = df.A * df.B
$\endgroup$
4
$\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 '18 at 7:18
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, privacy policy and cookie policy

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