2D bin (x,y) and calculate mean of values (c) of 10 deepest data points (z)

Coen Published at Dev

Coen

For a data set consisting of:

coordinates x, y
depth z
a certain value c

I would like to do the following more efficient:

bin the data set in 2D bins based on the coordinates (x, y)
take the 10 deepest data points (z) per bin
calculate the mean value of c of these 10 data points per bin

Finally show a 2d heatmap with the calculated mean values.

I have found a working solution, but this takes too long for small bins and/or large data sets.

Is there a more efficient way of achieving the same result?

Current working example

Example dataframe:

import numpy as np
from numpy.random import rand
import pandas as pd
import math
import matplotlib.pyplot as plt

n = 10000
df = pd.DataFrame({'x':rand(n), 'y':rand(n), 'z':rand(n), 'c':rand(n)})

Bin the data set:

cell_size = 0.01

nx = math.ceil((max(df['x']) - min(df['x'])) / cell_size)
ny = math.ceil((max(df['y']) - min(df['y'])) / cell_size)

x_range = np.arange(0, nx)
y_range = np.arange(0, ny)

df['xbin'], x_edges = pd.cut(x=df['x'], bins=nx, labels=x_range, retbins=True)
df['ybin'], y_edges = pd.cut(x=df['y'], bins=ny, labels=y_range, retbins=True)

Code that now takes to long:

df = df.groupby(['xbin', 'ybin']).apply(
    lambda d: d.sort_values('z').head(10).mean())

Update an empty DataFrame for the bins without data and show result:

index = pd.MultiIndex.from_product([x_range, y_range],
    names=['xbin', 'ybin'])

tot_df = pd.DataFrame(index=index, columns=['z', 'c'])
tot_df.update(df)

zval = tot_df['c'].astype('float').values
zval = zval.reshape((nx, ny))
zval = zval.T
zval = np.flipud(zval)

extent = [min(x_edges), max(x_edges), min(y_edges), max(y_edges)]

plt.matshow(zval, aspect='auto', extent=extent)
plt.show()

Dev Khadka

you can use np.searchsorted to bin the rows by x and y and then use groupby to take 10 deep values and calculate means. As groupby will maintains the order in each group you can sort values before applying bins. groupby will perform better without apply

df = pd.DataFrame({'x':rand(n), 'y':rand(n), 'z':rand(n), 'c':rand(n)})

df = df.sort_values("z", ascending=False)
bins = np.linspace(0, 1, 11)
df["bin_x"] = np.searchsorted(bins, df['x'].values) - 1
df["bin_y"] = np.searchsorted(bins, df['y'].values) - 1

result = df.groupby(["bin_x", "bin_y"]).head(10)
result.groupby(["bin_x", "bin_y"])["c"].mean()

Result

bin_x  bin_y
0      0        0.369531
       1        0.601803
       2        0.554452
       3        0.575464
       4        0.455198
                  ...   
9      5        0.469838
       6        0.420772
       7        0.367549
       8        0.379200
       9        0.523083
Name: c, Length: 100, dtype: float64

Collected from the Internet

Please contact [email protected] to delete if infringement.