Pandas Dataframe - replace NaN with 0 if column value condition

Question

I have searched all around the internet and tried many methods before making this post, I have a dataframe where I want to:

Replace NaN value of TGT_COLUMN_SCALE to 0 If TGT_COLUMN_DATA_TYPE is equals to NUMERIC.

Kindly help me out with this issue.

I tried this code but it's not working:

df["TGT_COLUMN_SCALE"] = np.where(df["TGT_COLUMN_DATA_TYPE"] == "NUMERIC", 'NaN', 0)

df.loc[(df.TARGET_COLUMN_DATA_TYPE == "NUMERIC") & (df.TARGET_COLUMN_SCALE.isnull()), "TARGET_COLUMN_SCALE"] = 0 — srinath
– srinath, Commented Jul 28, 2022 at 9:12

srinath · Accepted Answer · 2022-07-28 09:35:19Z

2

Sample:

df = pd.DataFrame({
    "TGT_COLUMN_DATA_TYPE" : ["DATE", "NUMERIC", "STRING", "NUMERIC"],
    "TGT_COLUMN_SCALE" : [np.NaN, np.NaN, 4.0, 5.0]
})

Replace

df.loc[(df.TGT_COLUMN_DATA_TYPE == "NUMERIC") & (df.TGT_COLUMN_SCALE.isnull()), "TGT_COLUMN_SCALE"] = 0

Result:

    TGT_COLUMN_DATA_TYPE    TGT_COLUMN_SCALE
0   DATE    NaN
1   NUMERIC 0.0
2   STRING  4.0
3   NUMERIC 5.0

edited Jul 28, 2022 at 9:35

answered Jul 28, 2022 at 9:16

srinath

3,0288 gold badges39 silver badges62 bronze badges

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

9 Comments

Avi Thour Over a year ago

It replaced NaN with zero and all other values with NaN. That's not what I need.

srinath Over a year ago

It didnt.. it replaces only when TARGET_COLUMN_DATA_TYPE is Numerical and TARGET_COLUMN_DATA_TYPE is nan.

srinath Over a year ago

Look at index 3, TARGET_COLUMN_DATA_TYPE is set to NUMERIC and value is 5.0. it has not changed. only index 1 has changed.

Avi Thour Over a year ago

It created new column and then did this - i.imgur.com/zAqOcVV.png that's not what I need.

srinath Over a year ago

Updated the column names in the answer as well

|

Leonardo Boscolo · Accepted Answer · 2022-07-28 09:28:13Z

0

You just need to use loc to select the columns and then you use fillna to replace values:

df.loc[df.TGT_COLUMN_SCALE == "NUMERIC",
       "TGT_COLUMN_DATA_TYPE"] = df.loc[df.TGT_COLUMN_SCALE == "NUMERIC", "TGT_COLUMN_DATA_TYPE"].fillna(0)

Full code

TGT_COLUMN_SCALE = ('DATE', 'TIMESTAMP', 'NUMERIC', 'NUMERIC')
TGT_COLUMN_DATA_TYPE = (np.nan, np.nan, np.nan, np.nan)
df = pd.DataFrame(list(zip(TGT_COLUMN_SCALE, TGT_COLUMN_DATA_TYPE)),
                  columns=['TGT_COLUMN_SCALE', 'TGT_COLUMN_DATA_TYPE'])
df.loc[df.TGT_COLUMN_SCALE == "NUMERIC",
       "TGT_COLUMN_DATA_TYPE"] = df.loc[df.TGT_COLUMN_SCALE == "NUMERIC", "TGT_COLUMN_DATA_TYPE"].fillna(0)

edited Jul 28, 2022 at 9:28

answered Jul 28, 2022 at 9:21

Leonardo Boscolo

4774 silver badges14 bronze badges

4 Comments

Avi Thour Over a year ago

Why are you including Date, Timestamp in this? I just want to change Numeric value of NaN with Zero. Can you provide any simpler method?

Leonardo Boscolo Over a year ago

You just have to copy the last line, as you can see by copying the full code, it works.

Avi Thour Over a year ago

SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame. Try using .loc[row_indexer,col_indexer] = value instead

Leonardo Boscolo Over a year ago

it's just a warning, you should get rid of it with: pd.options.mode.chained_assignment = None

Guy · Accepted Answer · 2022-07-28 10:08:12Z

0

np.where will take the first option as the value in case the condition is true, else the second. You need to replace the order of nan and 0

df["TGT_COLUMN_SCALE"] = np.where((df["TGT_COLUMN_DATA_TYPE"] == "NUMERIC") & (df["TGT_COLUMN_SCALE"].isnull()), 0, df["TGT_COLUMN_SCALE"])

edited Jul 28, 2022 at 10:08

answered Jul 28, 2022 at 9:18

Guy

51.2k10 gold badges49 silver badges96 bronze badges

6 Comments

Avi Thour Over a year ago

It's not working now. It's still showing NaN.

Guy Over a year ago

@AviThour you need to assign the returned value, updated the answer.

Avi Thour Over a year ago

it replaced all other values which were not NaN with 0 aswell.

Avi Thour Over a year ago

this is what your code did. i.imgur.com/IaBpjZI.png It replaced non-nan value to zero also.

Guy Over a year ago

@AviThour but this is what you asked Replace NaN value of TGT_COLUMN_SCALE to 0 If TGT_COLUMN_DATA_TYPE is equals to NUMERIC

|

Collectives™ on Stack Overflow

Pandas Dataframe - replace NaN with 0 if column value condition

3 Answers 3

9 Comments

Full code

4 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Full code

4 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related