CODEWITHSUNDEEP
pandas
JamesHudson81
JamesHudson81

Reputation: 2273

removing duplicate rows pandas and remain values from each row different from zero

I have this df ( in the real case I have more duplicated rows):

    xx    yy    zz   name
A  5      6      0    A
A  0      0     -1    A
C  6      7      5    C

How could I merge the 2 rows those indices are the same and keeping only the values different from zero among them? The desired output would be :

    xx    yy    zz   name
A  5      6     -1    A
C  6      7      5    C

Upvotes: 0

Views: 38

Answers (3)

Andy L.
Andy L.

Reputation: 25249

you may try df.where and groupby first

df.where(df.ne(0)).groupby(level=0).first()

Out[161]:
    xx   yy   zz name
A  5.0  6.0 -1.0    A
C  6.0  7.0  5.0    C

Upvotes: 2

Erfan
Erfan

Reputation: 42916

You can also use replace, bfill, query and duplicated:

df.replace(0, np.NaN).bfill().query('~index.duplicated()')

Output

    xx   yy   zz name
A  5.0  6.0 -1.0    A
C  6.0  7.0  5.0    C

Upvotes: 1

Ami Tavory
Ami Tavory

Reputation: 76297

Try

df.replace({0: None}).groupby(df.index).max()

    xx  yy  zz  name
A   5   6   7   A
C   6   7   5   C

It first replaces the 0 elements by None, which is ignored by max in the groupby.

Upvotes: 1

Related Questions