Remove matching values from two columns in Pandas

Question

I have the following data set generated from the equation

df.loc[:,51] = [1217.0, -20.0, 13970.0, -74]

I dropped the negative values (specific values) and got this

df.loc[:,52] = [1217.0, 0, 13970.0, 0]

Now I am trying to get another column with the dropped values

df.loc[:,53] = df.drop_duplicates(subset=[df.loc[:,51], df.loc[:,52]])

I want this result. The values that are dropped

df.loc[:,53] = [0,-20, 0,-74]

But I got the following error

TypeError: 'Series' objects are mutable, thus they cannot be hashed

Welcome to stack overflow! When asking a question, please try to provide a minimal reproducible example - in this case, at the very least provide all your code used to drop negative values. If you can, try to create a dataset using code that we could work with too. Check out the drop_duplicates docs - it doesn't do what you're trying to do and the subset arg accepts column names, not a list of Series objects. — Michael Delgado
– Michael Delgado, Commented Sep 9, 2021 at 0:42

Henry Ecker · Accepted Answer · 2021-09-09 01:18:06Z

2

Try numpy.where

df.loc[:,53] = np.where(df.loc[:,51] == df.loc[:,52], 0, df.loc[:,51])

Here, I've done it with a sample data:

import pandas as pd
import numpy as np
df = pd.DataFrame({
     'brand': ['Yum Yum', 'Yum Yum', 'Indomie', 'Indomie', 'Indomie'],
     'rating': [4, -4, 3.5, -15, 5]
 })
df.loc[(df['rating'] < 0 ), 'new_col'] = 0
df.loc[(df['rating'] > 0 ), 'new_col'] = df['rating']
df['dropped'] = np.where(df['rating'] == df['new_col'], 0, df['rating'])
df

edited Sep 9, 2021 at 1:18

Henry Ecker♦

35.9k19 gold badges48 silver badges67 bronze badges

answered Sep 9, 2021 at 0:59

Adam Kubalica

1281 silver badge9 bronze badges

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

Collectives™ on Stack Overflow

Remove matching values from two columns in Pandas

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related