CODEWITHSUNDEEP
pythonpython-2.7pandasnumpy
Imran
Imran

Reputation: 656

Place x,y coordinates into bins

I have a Pandas dataframe with two of the columns containing x,y coordinates that I plot as below:

plt.figure(figsize=(10,5))
plt.scatter(df.x, df.y, s=1, marker = ".")
plt.xlim(-1.5, 1.5)
plt.ylim(0, 2)
plt.xticks(np.arange(-1.5, 1.6, 0.1))
plt.yticks(np.arange(0, 2.1, 0.1))
plt.grid(True)
plt.show()

enter image description here

I want to split the x and y axes every 0.1 units to give 600 bins (30x20). Then I want to know how many of my points are in each bin and the indices of these points so I can look them up in my dataframe. I basically want to create 600 new dataframes for each bin.

This is what I've tried so far:

df[(df.x >= -0.1) & (df.x < 0) & (df.y >= 0.7) & (df.y < 0.8)]

This will give me part of the dataframe contained within the square (-0.1 ≤ x < 0) & (0.7 ≤ y < 0.8). I want a way to create 600 of these.

Upvotes: 3

Views: 3207

Answers (3)

piRSquared
piRSquared

Reputation: 294228

One of many ways to do it.

bins = (df // .1 * .1).round(1).stack().groupby(level=0).apply(tuple)

dict_of_df = {name: group for name, group in df.groupby(bins)}

You can get the dataframe of counts with

df.groupby(bins).size().unstack()

Upvotes: 1

Ted Petrou
Ted Petrou

Reputation: 61947

I would use the cut function to create the bins and then group by them and count

#create fake data with bounds for x and y
df = pd.DataFrame({'x':np.random.rand(1000) * 3 - 1.5,
                   'y':np.random.rand(1000) * 2})

# bin the data into equally spaced groups
x_cut = pd.cut(df.x, np.linspace(-1.5, 1.5, 31), right=False)
y_cut = pd.cut(df.y, np.linspace(0, 2, 21), right=False)

# group and count
df.groupby([x_cut, y_cut]).count()

Output

                           x    y
x            y                   
[-1.5, -1.4) [0, 0.1)    3.0  3.0
             [0.1, 0.2)  1.0  1.0
             [0.2, 0.3)  3.0  3.0
             [0.3, 0.4)  NaN  NaN
             [0.4, 0.5)  1.0  1.0
             [0.5, 0.6)  3.0  3.0
             [0.6, 0.7)  1.0  1.0
             [0.7, 0.8)  2.0  2.0
             [0.8, 0.9)  2.0  2.0
             [0.9, 1)    1.0  1.0
             [1, 1.1)    2.0  2.0
             [1.1, 1.2)  1.0  1.0
             [1.2, 1.3)  2.0  2.0
             [1.3, 1.4)  3.0  3.0
             [1.4, 1.5)  2.0  2.0
             [1.5, 1.6)  3.0  3.0
             [1.6, 1.7)  3.0  3.0
             [1.7, 1.8)  1.0  1.0
             [1.8, 1.9)  1.0  1.0
             [1.9, 2)    1.0  1.0
[-1.4, -1.3) [0, 0.1)    NaN  NaN
             [0.1, 0.2)  NaN  NaN
             [0.2, 0.3)  2.0  2.0

And to completely answer your question. You can add the categories to the original dataframe as columns and then do your searching from there like this.

# add new columns
df['x_cut'] = x_cut
df['y_cut'] = y_cut
print(df.head(15)

            x         y         x_cut       y_cut
0    1.239743  1.348838    [1.2, 1.3)  [1.3, 1.4)
1   -0.539468  0.349576  [-0.6, -0.5)  [0.3, 0.4)
2    0.406346  1.922738    [0.4, 0.5)    [1.9, 2)
3   -0.779597  0.104891  [-0.8, -0.7)  [0.1, 0.2)
4    1.379920  0.317418    [1.3, 1.4)  [0.3, 0.4)
5    0.075020  0.748397      [0, 0.1)  [0.7, 0.8)
6   -1.227913  0.735301  [-1.3, -1.2)  [0.7, 0.8)
7   -0.866753  0.386308  [-0.9, -0.8)  [0.3, 0.4)
8   -1.004893  1.120654    [-1.1, -1)  [1.1, 1.2)
9    0.007665  0.865248      [0, 0.1)  [0.8, 0.9)
10  -1.072368  0.155731    [-1.1, -1)  [0.1, 0.2)
11   0.819917  1.528905    [0.8, 0.9)  [1.5, 1.6)
12   0.628310  1.022167    [0.6, 0.7)    [1, 1.1)
13   1.002999  0.122493      [1, 1.1)  [0.1, 0.2)
14   0.032624  0.426623      [0, 0.1)  [0.4, 0.5)

And then to get the combination that you described above: df[(x >= -0.1) & (df.x < 0) & (df.y >= 0.7) & (df.y < 0.8)] you would can set the index as x_cut and y_cut and do some hierarchical index selection.

df = df.set_index(['x_cut', 'y_cut'])
df.loc[[('[-0.1, 0)', '[0.7, 0.8)')]]

Output

                             x         y
x_cut     y_cut                         
[-0.1, 0) [0.7, 0.8) -0.043397  0.702029
          [0.7, 0.8) -0.032508  0.799284
          [0.7, 0.8) -0.036608  0.709394
          [0.7, 0.8) -0.025254  0.741085

Upvotes: 8

Aaron
Aaron

Reputation: 11075

you could transform your units into their respective indices 0 - 19 and 0 - 29 and increment a matrix of zeros..

import numpy as np

shape = [30,20]
bins = np.zeros(shape, dtype=int)

xmin = np.min(df.x)
xmax = np.max(df.x)
xwidth = xmax - xmin

xind = int(((df.x - xmin) / xwidth) * shape[0])

#ymin
#ymax
#ywidth

#yind

for ind in zip(xind, yind):
    bins[ind] += 1

Upvotes: 0

Related Questions