how to change the rows value by condition on a column (python,pandas)

Question

hey all i want to change the rows by condition on a column.

so where column "type"==A

i want the cols [col1-col5] will be 1 if the value is biger 2

else i like the value to be 0

the DATA 
data={"col1":[np.nan,3,4,5,9,2,6],
"col2":[4,2,4,6,0,1,5],
"col3":[7,6,0,11,3,6,7],
"col4":[14,11,22,8,6,np.nan,9],
"col5":[0,5,7,3,8,2,9],
"type":["A","A","C","A","B","A","E"],
"number":["one","two","two","one","one","two","two"]}
df=pd.DataFrame.from_dict(data)
df

How I expect the data to be

data={"col1":[0,1,4,1,9,0,6],
      "col2":[1,0,4,1,0,0,5],
      "col3":[1,1,0,1,3,1,7],
      "col4":[1,1,22,1,6,0,9],
      "col5":[0,1,7,1,1,0,9],
      "type":["A","A","C","A","B","A","E"],
      "number":["one","two","two","one","one","two","two"]}
df=pd.DataFrame.from_dict(data)
df

Ch3steR · Accepted Answer · 2020-08-16 14:24:31Z

2

You can use df.query to get all type A rows, then use df._get_numeric_data/df.select_dtypes('number') to get all numeric fields, then use df.gt and cast them as int using df.astype, now update the DataFrame with new values using df.update

df.update(df.query('type == "A"')._get_numeric_data().gt(2).astype(int))
                                #.select_dtypes('number')
df

   col1  col2  col3  col4  col5 type number
0   0.0   1.0   1.0   1.0   0.0    A    one
1   1.0   0.0   1.0   1.0   1.0    A    two
2   4.0   4.0   0.0  22.0   7.0    C    two
3   1.0   1.0   1.0   1.0   1.0    A    one
4   9.0   0.0   3.0   6.0   8.0    B    one
5   0.0   0.0   1.0   0.0   0.0    A    two
6   6.0   5.0   7.0   9.0   9.0    E    two

answered Aug 16, 2020 at 14:24

Ch3steR

20.8k4 gold badges34 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jezrael · Accepted Answer · 2020-08-16 14:26:43Z

Use DataFrame.loc for select by condition equal A and columns between first and last column name, then compare for greater like DataFrame.gt, for map True, False to 1,0 is used convert mask to integers, last update by DataFrame.update:

df.update(df.loc[df['type'].eq('A'), 'col1':'col5'].gt(2).astype(int))
print (df)
   col1  col2  col3  col4  col5 type number
0   0.0   1.0   1.0   1.0   0.0    A    one
1   1.0   0.0   1.0   1.0   1.0    A    two
2   4.0   4.0   0.0  22.0   7.0    C    two
3   1.0   1.0   1.0   1.0   1.0    A    one
4   9.0   0.0   3.0   6.0   8.0    B    one
5   0.0   0.0   1.0   0.0   0.0    A    two
6   6.0   5.0   7.0   9.0   9.0    E    two

Or by assign back:

m = df['type'].eq('A')
df.loc[m, 'col1':'col5'] = df.loc[m, 'col1':'col5'].gt(2).astype(int)
print (df)
   col1  col2  col3  col4  col5 type number
0   0.0     1     1   1.0     0    A    one
1   1.0     0     1   1.0     1    A    two
2   4.0     4     0  22.0     7    C    two
3   1.0     1     1   1.0     1    A    one
4   9.0     0     3   6.0     8    B    one
5   0.0     0     1   0.0     0    A    two
6   6.0     5     7   9.0     9    E    two

Collectives™ on Stack Overflow

how to change the rows value by condition on a column (python,pandas)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related