CODEWITHSUNDEEP
pythonpandasdataframe
Rakesh Adhikesavan
Rakesh Adhikesavan

Reputation: 12826

removing duplicate rows in pandas DataFrame based on a condition

I want to delete duplicate rows with respect to column 'a' in a dataFrame with the argument 'take_last = True' unless some condition. For instance, If I had the following dataFrame

 a | b | c
 1 | S | Blue 
 2 | M | Black
 2 | L | Blue
 1 | L | Green

I want to drop duplicate rows with respect to column 'a' with the general rule as take_last = true unless some condition say, c = 'Blue', in which case I want to make the argument take_last = false.

so that I get this as my resulting df

 a | b | c
 1 | L | Green
 2 | M | Black

Upvotes: 2

Views: 1198

Answers (1)

jezrael
jezrael

Reputation: 862541

#   a  b      c
#0  1  S   Blue
#1  2  M  Black
#2  2  L   Blue
#3  1  L  Green

#get first rows of groups, sort them and reset index; delete redundant col index
df1 = df.groupby('a').head(1).sort('a').reset_index()
del df1['index']

#get last rows of groups, sort them and reset index; delete redundant col index
df2 = df.groupby('a').tail(1).sort('a').reset_index()
del df2['index']
print df1
#   a  b      c
#0  1  S   Blue
#1  2  M  Black
print df2
#   a  b      c
#0  1  L  Green
#1  2  L   Blue

#if value in col c in df1 is 'Blue' replace this row with row from df2 (indexes are same)
df1.loc[df1['c'].isin(['Blue'])] = df2
print df1
#   a  b      c
#0  1  L  Green
#1  2  M  Black

Upvotes: 2

Related Questions