0

data frame

In the above dataframe I am trying to replace NaN and negative values in the last column with 0 using pandas, I have used some of the suggestions here, but it also seems to affect other columns and replace values in the other columns with zeroes.

How do I restrict it to just the selected column

mozway
  • 194,879
  • 13
  • 39
  • 75
David H
  • 5
  • 1
  • 4
  • `df.new_expenses = df.new_expenses.clip(lower=0).fillna(0)` ?? – Nk03 Aug 25 '21 at 20:17
  • An image for a dataframe isn't useful. It's better to provide some code that creates an example dataframe. – 9769953 Aug 25 '21 at 20:18
  • 1
    Not sure what it means when [Community](https://stackoverflow.com/users/-1/community) marks a duplicate, but i don't think [the linked duplicate](https://stackoverflow.com/questions/38134012/pandas-dataframe-fillna-only-some-columns-in-place) is the correct one. OP's question is _not_ simply just `fillna`. Maybe it's still a duplicate of another question somewhere, but not the currently linked duplicate. – tdy Aug 25 '21 at 22:27
  • 2
    I've reopened this question @tdy as I agree with you. Community can mark a duplicate if OP accepts the proposed duplicate and close the question themselves. [Why does this question show as “marked as duplicate by Community♦”?](https://meta.stackoverflow.com/q/287763/15497888) – Henry Ecker Aug 25 '21 at 23:09

2 Answers2

3

You can use .mask() to change negative numbers to NaN and then fillna() to 0 together with other NaN values in one go, as follows:

df['New_expenses'] = df['New_expenses'].mask(df['New_expenses'] < 0).fillna(0)

Or, even more simpler, credit to @tdy, using .where() instead:

df['New_expenses'] = df['New_expenses'].where(df['New_expenses'] >= 0, 0)

.where() keeps the values when the condition is true and replaces values when the condition is false.

In this case, we keeps the values when the values are positive and replaces the values to 0 when the condition is false (including negative numbers and NaN values).

tdy
  • 36,675
  • 19
  • 86
  • 83
SeaBean
  • 22,547
  • 3
  • 13
  • 25
2

Use clip to set the negative values to a minimum, and fillna to replace the NAs:

df['New_expenses'] = df['New_expenses'].clip(lower=0).fillna(0)
mozway
  • 194,879
  • 13
  • 39
  • 75
  • I think `df[['New_expenses', 'Month_Expenses']] = df[['New_expenses', 'Month_Expenses']].clip(lower=0).fillna(0)` works for targeting more than one column. – d.b Aug 25 '21 at 20:24
  • Correct, however it was my understanding that only the last column was the target. But now OP had the choice ;) – mozway Aug 25 '21 at 20:31