Remove duplicate rows and keep value in columns that are non-zero

user_14005491 0
Viewed 300

I have a dataframe that looks like this:

ID  |in_tier_1 | tier_1_location |in_tier_2 | tier_2_location
100 |1         |Sydney           |0         | 0
100 |0         |0                |1         | Paris
112 |0         |0                |1         | Paris
112 |1         |NYC              |          |

And I want to remove duplicates but keep the non-zero values in the columns

ID  |in_tier_1 | tier_1_location |in_tier_2 | tier_2_location
100 |1         |Sydney           |1         | Paris
112 |1         |NYC              |1         | Paris

I tried to groupby id and use the .agg() function but it is not working out. Can you please help?

pandas dataframe group-by python
1 Answers

Group by your ID and backfill NaN after replacing 0 and '0' with NaN so to make it easier to backfill. Then, because bfill() broadcasts back to the data frame, go back to another grouping on the same key and pick the first element.

>>> df.replace(0, np.nan).replace('0', np.nan).groupby(level=0).bfill().groupby(level=0).first()
     in_tier_1 tier_1_location  in_tier_2 tier_2_location
ID                                                       
100        1.0          Sydney        1.0           Paris
112        1.0             NYC        1.0           Paris

If ID is not your index and is instead a column, substitute groupby('ID') for groupby(level=0) (in the first instance only: because the first groupby returns a df with index ID, you would need to use level=0 to group for the second time, unless you also pass as_index=False).

Alternatively one could do an ffill picking last, but that's just a mirrored symmetry.

user_27410910
Related