Keep only the first value on duplicated column (set 0 to others)

user_12832931 0
Viewed 133

Supposing I have the following situation:

A dataframe where the first column ['ID'] will eventually have duplicated values.

import pandas as pd
df = pd.DataFrame({"ID": [1,2,3,4,4,5,5,5,6,6], 
                  "l_1": [10,12,32,45,45,20,20,20,20,20], 
                  "l_2": [11,12,32,11,21,27,38,12,9,6], 
                  "l_3": [5,9,32,12,21,21,18,12,8,1],
                  "l_4": [6,21,12,77,77,2,2,2,8,8]})


ID  l_1 l_2 l_3 l_4
1   10  11  5   6
2   12  12  9   21
3   32  32  32  12
4   45  11  12  77
4   45  21  21  77
5   20  27  21  2
5   20  38  18  2
5   20  12  12  2
6   20  9   8   8
6   20  6   1   8

When duplicated IDs occurs:

  • I need to keep only the first values for column l_1 and l_4 (other duplicated rows must be zero).
  • Columns 'l_2' and 'l_3' must stay the same.
  • When duplicated IDs occurs, the values on these rows on columns l_1 and l_4 will be also duplicated.

Expected output:

ID  l_1 l_2 l_3 l_4
1   10  11  5   6
2   12  12  9   21
3   32  32  32  12
4   45  11  12  77
4   0   21  21  0
5   20  27  21  2
5   0   38  18  0
5   0   12  12  0
6   20  9   8   8
6   0   6   1   0

Is there a Straightforward way using pandas or numpy to accomplish this ?

I could just accomplish it doing all these steps:

x1 = df[df.duplicated(subset=['ID'], keep=False)].copy()

x1.loc[x1.groupby('ID')['l_1'].apply(lambda x: (x.shift(1) == x)), 'l_1'] = 0

x1.loc[x1.groupby('ID')['l_4'].apply(lambda x: (x.shift(1) == x)), 'l_4'] = 0

df = df.drop_duplicates(subset=['ID'], keep=False)

df = pd.concat([df, x1])
pandas numpy
1 Answers

Isn't this just:

df.loc[df.duplicated('ID'), ['l_1','l_4']] = 0

Output:

   ID  l_1  l_2  l_3  l_4
0   1   10   11    5    6
1   2   12   12    9   21
2   3   32   32   32   12
3   4   45   11   12   77
4   4    0   21   21    0
5   5   20   27   21    2
6   5    0   38   18    0
7   5    0   12   12    0
8   6   20    9    8    8
9   6    0    6    1    0
user_42384080
Related