python pandas drop duplicate columns by condition

Question

i want to drop duplicate columns by condition so what i want to do is where "type" is the same(duplicate) drop the "number" one

i got this

data={"col1":[2,3,4,5,9,2,6],
"col2":[4,2,4,6,0,1,5],
"col3":[7,6,0,11,3,6,7],
"col4":[14,11,22,8,6,3,9],
"col5":[0,5,7,3,8,2,9],
"type":["A","A","C","D","B","B","E"],
"number":["one","two","two","one","one","two","two"]}
df=pd.DataFrame.from_dict(data)

and i want to have this

data={"col1":[3,4,5,2,6],
"col2":[2,4,6,1,5],
"col3":[6,0,11,6,7],
"col4":[11,22,8,3,9],
"col5":[5,7,3,2,9],
"type":["A","C","D","B","E"],
"number":["two","two","one","two","two"]}
df=pd.DataFrame.from_dict(data)

jezrael · Accepted Answer · 2020-08-11T09:27:09.437

You can chain 2 conditions - select all non one values by compare for Series.ne and inverted mask with Series.duplicated:

df1 = df[df['number'].ne('one') | ~df['type'].duplicated(keep=False)]
print (df1)
   col1  col2  col3  col4  col5 type number
1     3     2     6    11     5    A    two
2     4     4     0    22     7    C    two
3     5     6    11     8     3    D    one
5     2     1     6     3     2    B    two
6     6     5     7     9     9    E    two

Another idea with ordered categoricals:

cats = pd.unique(['one'] + df['number'].unique().tolist())

df['number'] = pd.Categorical(df['number'], categories=cats, ordered=True)

df2 = df.sort_values('number').drop_duplicates(subset=['type'], keep='last').sort_index()
print (df2)
   col1  col2  col3  col4  col5 type number
1     3     2     6    11     5    A    two
2     4     4     0    22     7    C    two
3     5     6    11     8     3    D    one
5     2     1     6     3     2    B    two
6     6     5     7     9     9    E    two

I tested it on my DATA and so far it looks good Thank you – matan Aug 11 '20 at 09:27 — matan, Aug 11 '20 at 09:27

score 0 · Answer 2 · answered Aug 11 '20 at 09:23

0

Try This:

df = df.drop_duplicates(subset=['type'],keep='last')
print(df)

Output:

    col1    col2    col3    col4    col5    type    number
1   3       2       6       11      5       A       two
2   4       4       0       22      7       C       two
3   5       6       11      8       3       D       one
5   2       1       6       3       2       B       two
6   6       5       7       9       9       E       two

answered Aug 11 '20 at 09:23

L Lawliet

419
1
7
20

1

First of all thanks for the help. But this only works because of the fact that I entered ONE before TWO. If I had changed the order in one case it would not have worked – matan Aug 11 '20 at 09:29

python pandas drop duplicate columns by condition

2 Answers2