CODEWITHSUNDEEP
pythonpandasdataframe
Rags Gupta
Rags Gupta

Reputation: 173

Pandas how to set column to NaN based on values in other columns using .loc

I have a pandas dataframe on which I'm calling a function to fill NaN in columns where the condition isn't met.

Following is my code:

def clean_feedback(DF):
    feed_id = DF.id_y.unique()
    for ID in feed_id:
        Min = np.argmin(np.abs(DF[DF.id_y == ID].created_at_x - DF[DF.id_y == ID].created_at_y))
        print(Min)
        DF[DF.id_y == ID].loc[DF[DF.id_y == ID].index != Min, 'comments'] = np.nan
        return DF[DF.id_y == ID]

Sample Dataframe is:

id_x    user_id merchant_id amount_spent    bill_number created_at_x    checked_in  chain_id    id_y    feedback_setting_id comments    created_at_y    updated_at  feedback_type
1097    268868  975 42  149 None    2016-12-14 12:11:14 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
2150    468876  975 42  278 None    2017-06-04 10:51:47 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
6535    5020    975 42  200 None    2015-03-25 12:37:36 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
9228    476314  975 42  676 None    2017-06-09 14:34:03 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
9601    293308  975 42  438 None    2017-01-22 13:03:18 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
10215   781647  975 42  335 None    2017-08-21 13:36:43 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
20405   5441    975 42  200 None    2015-03-29 14:24:32 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
24117   277853  975 42  220 None    2016-12-25 12:57:53 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
24432   949216  975 42  219 None    2017-10-05 10:22:52 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
24475   289288  975 42  109 None    2017-01-15 08:49:55 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
32318   767980  975 42  293 None    2017-08-16 09:41:30 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1
32820   343502  975 42  387 None    2017-03-22 12:52:48 1   NaN 219 194 Lovely cafe!    2017-03-22 12:55:05 2017-10-05 06:45:49 1

Whenever I call the function: clean_feedback(Transaction[Transaction.id_y == 219]), there aren't any changes. I'm sure its a stupid mistake but im completely stumped.

EDIT1: I've also tried doing the about with .where function, but it makes the entire dataframe NaN. Is there any way to specify for the column comments?

Upvotes: 2

Views: 9453

Answers (1)

jpp
jpp

Reputation: 164683

Try this instead:

DF.loc[(DF.id_y == ID) & (DF.index != Min), 'comments'] = np.nan

Explanation

  • pd.DataFrame.loc accepts label-based or Boolean indexing.
  • Your 2 desired criteria are for id_y to equal ID and index != Min.
  • The & operator combines 2 Boolean series to form a single Boolean indexer

Upvotes: 6

Related Questions