iterate over pandas columns based on conditions

Question

want to calculate C based on values of count, A and B

sample df:

count	A	B	C
yes	23	2	nan
nan	23	1	nan
yes	41	6	nan

result I want

count	A	B	C
yes	23	2	46
nan	23	1	0
yes	41	6	246

calculate C = A*B only when count value = yes otherwise C values =0 that is, it should skip nan values of count

Any help is appreciable

I am trying this

for ind, row in df.iterrows():
    if df['count'] == 'yes':
        df.loc[ ind, 'C'] =row['A'] *row['B']
    else:
        df.loc[ ind, 'C'] =0

But it's giving error : ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

user_4983450 · Answer

Another option:

df.C = df.A.mul(df.B).where(df['count'].eq('yes')).fillna(0)

df
#  count   A  B      C
#0   yes  23  2   46.0
#1   NaN  23  1    0.0
#2   yes  41  6  246.0

Or if you prefer operators: df.C = (df.A * df.B).where(df['count'] == 'yes').fillna(0)

user_14289892 · Answer

Just use this:-

df['C']=df[df['count']=='yes']['C'].fillna(value=df['A']*df['B'])
df['C']=df['C'].fillna(0)

Try this:-

for ind, row in df.iterrows():
    if row['count'] == 'yes':
        df.loc[ ind, 'C'] =row['A'] *row['B']
    else:
        df.loc[ ind, 'C'] =0

You are getting error because you write df['count']=='yes' instead of row['count'] == 'yes'

user_5032941 · Answer

pandas overloads * for this operation, provided you correctly specify the indices you want to set:

mask = df["count"].notna()
df.loc[mask, "C"] = df["A"]*df["B"]
df.C.fillna(0, inplace=True)

or a slightly more concise version that would annoy your coworkers:

df["C"] = df["A"]*df["B"]*(df["count"].notna())

In the last, df["count"].notna() returns a boolean column, which is converted to a numeric type when multiplied by numerical columns. Concise but as clear.

output for either:

  count   A  B      C
0   yes  23  2   46.0
1   NaN  23  1      0
2   yes  41  6  246.0

This will be more performant than .apply and much more performant than iterrows.

user_12416453 · Answer

You can try this with df.prod to multiply A with B and mask NaN values using df.mask.

df['C'] = df[['A', 'B']].prod(axis=1).mask(df['count'].isna(), 0)

  count   A  B    C
0   yes  23  2   46
1   NaN  23  1    0
2   yes  41  6  246

user_13138364 · Answer

The fastest method for this is np.where():

df['C'] = np.where(
   df['count'] == 'yes', # condition
   df['A'] * df['B'],    # result if true
   0,                    # result if false
)

    count    A    B    C
0     yes   23    2   46
1     NaN   23    1    0
2     yes   41    6  246

Timings of all the current answers against df = pd.concat([df] * 1000):

method (hyperlinked to SO answer)	`%timeit` (mean ± SD; 7 runs, 1K loops each)
1. `np.where()`	561 µs ± 23.9 µs per loop
2. `* df['count'].notna()`	642 µs ± 15.4 µs per loop
3. `Series.where()` with `fillna()`	844 µs ± 7.89 µs per loop
4. `loc[]` with `fillna()`	1.31 ms ± 237 µs per loop
5. `Series.mask()` with `isna()`	1.49 ms ± 280 µs per loop
6. `fillna()` x2	1.63 ms ± 103 µs per loop

iterate over pandas columns based on conditions

5 Answers