Use np.where on a Mixed data type column-CodePudding

I have a column with data type 'o'. It has numbers, as well String. For example:

Days
5
10
15
7
No Sales Data available
9

I am trying to make a separate column using np.where, where I have written the code as

np.where(df['Days']=='No Sales Data available','No Sales',np.where(df['Days']<=10, 'Less than 10 days Sales','More than 10 Days Sales'))

Naturally, the code is giving problems due to mixed data types. Any idea how to get around such cases?

CodePudding user response：

You could rewrite your statement in this way which will preserve the data type of your 'Days' column.

df['new'] = np.where(pd.to_numeric(df['Days'],errors='coerce').isna(),'No Sale',
             np.where(pd.to_numeric(df['Days'],errors='coerce') <= 10,
               'Less than 10 days Sales','More than 10 Days Sales'))
print(df)
                      Days                      new
0                        5  Less than 10 days Sales
1                       10  Less than 10 days Sales
2                       15  More than 10 Days Sales
3                        7  Less than 10 days Sales
4  No Sales Data available                  No Sale
5                        9  Less than 10 days Sales

If you don't mind changing the type of your column, you could first convert to numeric and following a similar logic:

df['Days'] = pd.to_numeric(df['Days'],errors='coerce')
df['new'] = np.where(df['Days'].isna(),'No Sale',np.where(df['Days']<=10,'Less than 10 days Sales','More than 10 Days Sales'))

print(df)

   Days                      new
0   5.0  Less than 10 days Sales
1  10.0  Less than 10 days Sales
2  15.0  More than 10 Days Sales
3   7.0  Less than 10 days Sales
4   NaN                  No Sale
5   9.0  Less than 10 days Sales