How to replace negative numbers in Data Frame with contain other data types as well to by zero

Question

I am trying to find a solution to replace the negative numbers in a data frame to 0, there are a few suggestions in stack over flow (How to replace negative numbers in Pandas Data Frame by zero). But most of them work under the assumption that there is only one data type in the data frame, like float and so it doesnt work when there is a character and a date data type along with float.

Is it possible to just replace the columns which are float/numeric and ignore the date type and replace the values which are lesser than 0 to 0?

Any suggestions?

The data frame I am working with is given below:

{'ds': {'XYZ-100742': Timestamp('2023-01-01 00:00:00')}, 'X1': {'XYZ-100742': 101.0}, 'X2': {'XYZ-100742': 35.0}, 'X3': {'XYZ-100742': 33.0}, 'X4': {'XYZ-100742': 100.0}, 'X5': {'XYZ-100742': 101.0}, 'X6': {'XYZ-100742': 94.0}, 'X7': {'XYZ-100742': 116.0}, 'X8': {'XYZ-100742': 129.0}, 'X9': {'XYZ-100742': 108.0}, 'X10': {'XYZ-100742': -82.0}, 'X11': {'XYZ-100742': 47.0}, 'X12': {'XYZ-100742': 150.0}, 'X13': {'XYZ-100742': 129.0}, 'X14': {'XYZ-100742': 84.0}, 'X15': {'XYZ-100742': 48.0}, 'X16': {'XYZ-100742': -62.0}, 'X17': {'XYZ-100742': 150.0}, 'X18': {'XYZ-100742': -9.0}}

The Code which I wrote to solve this issue is given below:

import numpy as np
import pandas as pd
df = pd.read_excel ('C:/X/X/X/Book1.xlsx')
df[df<0]=0

This runs into an error. Which I am assuming is because of the different data types in the data frame. Attached is the error:

**TypeError: '<' not supported between instances of 'str' and 'int'**

Answer 1

Use DataFrame.clip with DataFrame.select_dtypes for numeric columns:

cols = df.select_dtypes(np.number).columns
df[cols] = df[cols].clip(lower=0)

Alternative:

df.update(df.select_dtypes(np.number).clip(lower=0))

Or use DataFrame.mask:

cols = df.select_dtypes(np.number).columns
df[cols] = df[cols].mask(df[cols] < 0, 0)

EDIT:

Timestamp = pd.Timestamp
d = {'ds': {'XYZ-100742': Timestamp('2023-01-01 00:00:00')}, 'X1': {'XYZ-100742': 101.0}, 'X2': {'XYZ-100742': 35.0}, 'X3': {'XYZ-100742': 33.0}, 'X4': {'XYZ-100742': 100.0}, 'X5': {'XYZ-100742': 101.0}, 'X6': {'XYZ-100742': 94.0}, 'X7': {'XYZ-100742': 116.0}, 'X8': {'XYZ-100742': 129.0}, 'X9': {'XYZ-100742': 108.0}, 'X10': {'XYZ-100742': -82.0}, 'X11': {'XYZ-100742': 47.0}, 'X12': {'XYZ-100742': 150.0}, 'X13': {'XYZ-100742': 129.0}, 'X14': {'XYZ-100742': 84.0}, 'X15': {'XYZ-100742': 48.0}, 'X16': {'XYZ-100742': -62.0}, 'X17': {'XYZ-100742': 150.0}, 'X18': {'XYZ-100742': -9.0}}

df = pd.DataFrame(d)


cols = df.select_dtypes(np.number).columns
df[cols] = df[cols].clip(lower=0)
print (df)
                   ds     X1    X2    X3     X4     X5    X6     X7     X8  \
XYZ-100742 2023-01-01  101.0  35.0  33.0  100.0  101.0  94.0  116.0  129.0   

               X9  X10   X11    X12    X13   X14   X15  X16    X17  X18  
XYZ-100742  108.0  0.0  47.0  150.0  129.0  84.0  48.0  0.0  150.0  0.0