CODEWITHSUNDEEP
pythonpandas
ruser
ruser

Reputation: 199

Comparison operation in a multi data type pandas Dataframe

I have the following pandas Dataframe:

df = pd.DataFrame({'a': [1, 2.5, 3, 'bad', 5],
                   'b': [0.1, 'good', 0.3, "ugly", 0.5],
                   'item': ['a', 'b', 'c', 'd', 'e']})
df = df.set_index('item')

As you can see, the columns have a combination of numeric and character values. I would like to change the values of the numeric values depending on the range, like for example:

0 < value <= 1, it should be replaced by "good"

1 < value <= 2, it should be replaced by "bad"

2 < value <= 6, it should be replaced by "ugly"

Can someone help me please? Thanks in advance! The above mentioned sample dataframe consists of 2 columsn but in my actual experiment, I have about 400 columns. Thanks!

Upvotes: 1

Views: 41

Answers (1)

jezrael
jezrael

Reputation: 863166

Idea is convert all columns to numeric with non numeric to missing values, so is possible compare by masks and set new values with numpy.select:

a = df.apply(pd.to_numeric, errors='coerce')
m1 = (a > 0) & (a <= 1)
m2 = (a > 1) & (a <= 2)
m3 = (a > 2) & (a <= 6)

arr = np.select([m1, m2, m3], ['good','bad','ugly'], default=df)

df = pd.DataFrame(arr, index=df.index, columns=df.columns)
print (df)
         a     b
item            
a     good  good
b     ugly  good
c     ugly  good
d      bad  ugly
e     ugly  good

EDIT:

df1 = pd.DataFrame({'initial': [0,1,2], 'end': [1, 2, 6], 'stg': ['good', 'bad', 'ugly']})

a = df1.apply(pd.to_numeric, errors='coerce')
m1 = (a > 0) & (a <= 1)
m2 = (a > 1) & (a <= 2)
m3 = (a > 2) & (a <= 6)

arr = np.select([m1, m2, m3], ['good','bad','ugly'], default=df1)

df = pd.DataFrame(arr, index=df1.index, columns=df1.columns)
print (df)
  initial   end   stg
0       0  good  good
1    good   bad   bad
2     bad  ugly  ugly

Upvotes: 1

Related Questions