I have a table looks below.

I would like to drop the duplicates by using groupby on lon and lat but keep the most repeated value on output

for example

Could anyone tell me how to do this?

CodePudding user response：

You can combine .groupby with Series.mode:

x = df.groupby(["lon", "lat"])["output"].apply(lambda x: x.mode()[0])
print(x.reset_index())

Prints:

         lon        lat       output
0 -47.812224 -19.043365  1890.283215
1 -47.812014 -19.007094  1813.785728
2 -47.811177 -19.008053  1763.091936
3 -47.774424 -19.008700  2172.781911
4 -47.774166 -19.043847  2897.092502
5 -47.773958 -19.007574  2179.670924
6 -47.773121 -19.008533  2236.769862

CodePudding user response：

We can use the .groupby aggregate methods as an alternative to the Andrej's method of using .apply and computing for each row.

While it does solve our problem .apply methods tend to become slower for a large dataset due to lack of vectorization.

Also, reset_index works much faster when used along with 'inplace=True'.

%%timeit
df.groupby(['lat', 'lon']).agg(pd.Series.mode).reset_index(inplace=True)

Hope that helps!!