Replace values in Pandas DataFrame with Unique values from the same DataFrame

Question

I have a pandas DataFrame which has values that are not correct

data = {'Model':['A', 'B', 'A', 'B', 'A'], 'Value':[20, 40, 20, 40, -1]}
df = pd.DataFrame(data)
df

Out[46]: 
  Model  Value
0     A     20
1     B     40
2     A     20
3     B     40
4     A     -1

I would like to replace -1 with the unique values of A. In this case it should be 20.

How do I go about it. I have tried the following. In my case its a large DF with 2million rows.


df2 = df[df.model != -1]
pd.merge(df, df2, on='model', how='left')

Out:
MemoryError: Unable to allocate 5.74 TiB for an array with shape (788568381621,) and data type int64

Quang Hoang · Accepted Answer · 2020-04-28 13:24:44Z

1

You don't need to merge, which creates all possible pairs of rows with the same Model. The following will do

df['Value'] = df['Value'].mask(df['Value']!=-1).groupby(df['Model']).transform('first')

Or you can also use map:

s = (df[df['Value'] != -1].drop_duplicates('Model')
         .set_index('Model')['Value'])
df['Value'] = df['Model'].map(s)

answered Apr 28, 2020 at 13:24

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

1 Comment

John Doe Over a year ago

Thank you. The first method didn't work since I got a column with NaN and -1 values. The second/map method worked and I understand it better

CHRD · Accepted Answer · 2020-04-28 13:32:58Z

1

Here's a quick solution:

df['Value'] = df.groupby('Model').transform('max')

answered Apr 28, 2020 at 13:32

CHRD

1,9573 gold badges21 silver badges46 bronze badges

Collectives™ on Stack Overflow

Replace values in Pandas DataFrame with Unique values from the same DataFrame

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related