Create bins from spacial data in Python pandas - possibly using groupby, diff, and cut?

user_907939 0
Viewed 143

I'm having trouble coming up with a good solution to the problem below. Please share your thoughts.

I'm attempting to map X,Y spatial data (the physical location of buckets) to categories / groupby object in pandas. Each bucket has space between them physically so the data points of similar buckets are close, unique buckets have larger spaces between them. Buckets are physically spaced out in two planes X and Y and have differing number of points within them. Only X will shown below for simplicity:

df = pd.DataFrame([0,1,2,6,7,8,12,13,14],columns=['X']) 
df['Xdiff'] = df['X'].diff() #Get Diff


    X   Xdiff
0   0   NaN
1   1   1.0
2   2   1.0
3   6   4.0
4   7   1.0
5   8   1.0
6   12  4.0
7   13  1.0
8   14  1.0

I would like a groupby object whose 1st group is data index locations 0-2, the second is 3-5, the third is 6-8. Something like below

grp1.index = [0,1,2]
grp2.index = [3,4,5]
grp2.index = [6,7,8]

I have attempted cut and groupby with no luck:

new_bins = df[ (df['Xdiff'] > 1) ] #Get larger diff values

X   Xdiff
3   6   4.0
6   12  4.0

bins = pd.cut( df['X'], new_bins['Xdiff'])

0            NaN
1            NaN
2            NaN
3            NaN
4    (6.0, 12.0]
5    (6.0, 12.0]
6    (6.0, 12.0]
7            NaN
8            NaN
Name: X, dtype: category
Categories (1, interval[float64]): [(6.0, 12.0]]

I appreciate your help. Thanks!

Update: I'm now working on adding groups outside of pandas then groupby:

groups = []
for d in df['Xdiff']:
    if d < 4:
        groups.append(i)
    else:
        i+=1
        groups.append(i)
df['bin'] =groups

df.groupby('bin').count()

And the output: This works but I believe there must be a better way in pandas. Thanks!

    X   Xdiff
bin     
1   3   2
2   3   3
3   3   3
pandas-groupby pandas python
1 Answers

Let's try using boolean logic on Xdiff to create the groups instead:

import pandas as pd

df = pd.DataFrame([0, 1, 2, 6, 7, 8, 12, 13, 14], columns=['X'])
df['Xdiff'] = df['X'].diff()
# Create Bins Based on Where Xdiff is Not 1
df['bin'] = df['Xdiff'].fillna(1).ne(1).cumsum() + 1

# Groupby on new index 'bin'
df = df.groupby('bin').agg('count')
print(df)

Output:

     X  Xdiff
bin          
1    3      2
2    3      3
3    3      3
user_154978880
Related