Iterate over certain columns containing strings in specific rows panas

user_12813881 0
Viewed 294

I have a dataframe containing 43 columns and i want to iterate only over 3 columns (C, D, E) if value in column A is 1,5. Those 3 columns contain either really complex sentences or NaN. I want to append value 1 to NEW column to those rows, that only contain NaN in all 3 columns while i want to append value 0 to those rows, if either of columns contain sentence.

Dataframe looks like this:

df

A       B        C                 D             E               
1.5     x     grass is green      NaN           NaN                            
1       y       NaN             ball is red     NaN
1.5     z       NaN               NaN           NaN

I suppose the beggining of the code should look something like this:

 for rating in df['A']:
      if rating == 1.5:
      for index, row in df.iterrows(): 
          print (row["C"], row["D"], row["E"]

What would be a possible solution to get result looking like this:

df

A       B        C                 D             E               NEW column
1.5     x     grass is green      NaN           NaN                 0            
1       y       NaN             ball is red     NaN
1.5     z       NaN               NaN           NaN                 1
pandas python
2 Answers

First rule in pandas should be dont loop if exist vectorized alternative like here.

So use numpy.select with masks - first compare by Series.eq for ==, second test if all rows has missing values by DataFrame.isna and DataFrame.all and set new values by list:

m1 = df['A'].eq(1.5)
m2 = df[['C','D','E']].isna().all(axis=1)

df['new'] = np.select([m1 & m2, m1 & ~m2], [1,0], default=np.nan)
print (df)

     A  B               C            D   E  new
0  1.5  x  grass is green          NaN NaN  0.0
1  1.0  y             NaN  ball is red NaN  NaN
2  1.5  z             NaN          NaN NaN  1.0

If possible mixing numeric with empty strings (not recommended, but possible):

df['new'] = np.select([m1 & m2, m1 & ~m2], [1,0], default='')
print (df)
     A  B               C            D   E new
0  1.5  x  grass is green          NaN NaN   0
1  1.0  y             NaN  ball is red NaN    
2  1.5  z             NaN          NaN NaN   1
user_29010020

We can use np.where and populate the NEW column based on the mentioned conditions:

m1 = df.A.eq(1.5) 
m2=df[['C', 'D', 'E']].isna().all(1)

df['NEW column'] = np.where(m1, (m1&m2).view('i1'), '')

print(df)

     A  B             C          D   E NEW column
0  1,5  x  grassisgreen        NaN NaN          0
1    1  y           NaN  ballisred NaN           
2  1,5  z           NaN        NaN NaN          1
​
user_96986840
Related