Split dataframe output based on values

Question

This post covered Modification of a function to return a dataframe with specified values and I would like to further modify the output. The current function and vectorized version will get all combinations of columns subtracted from each other and return relevant data accordingly.

Example and test data:

import pandas as pd
import numpy as np
from itertools import combinations

df2 = pd.DataFrame(
       {'AAA' : [80,5,6], 
        'BBB' : [85,20,30],
        'CCC' : [100,50,25],
        'DDD' : [98,50,25],
        'EEE' : [103,50,25],
        'FFF' : [105,50,25],
        'GGG' : [109,50,25]});

df2

AAA BBB CCC DDD EEE FFF GGG
0   80  85  100 98  103 105 109
1   5   20  50  50  50  50  50
2   6   30  25  25  25  25  25

v = df2.values
df3 = df2.mask((np.abs(v[:, :, None] - v[:, None]) <= 5).sum(-1) <= 1)

df3   
    AAA BBB CCC DDD EEE FFF GGG
0   80.0    85.0    100 98  103 105 109
1   NaN NaN 50  50  50  50  50
2   NaN 30.0    25  25  25  25  25

All values within thresh (5 here) are returned on a per row basis with np.abs <=5.

What needs to change?

On the first row of df3 there are two clusters of values within thresh (80,85) and (100,98,103,105,109). They are all valid but are two separate groups as not within thresh. I would like to be able to separate these values based on another thresh value.

I have attempted to demonstrate what I am looking to do with the following (flawed) code and only including this to show that Im attempting to progress this myself..

df3.mask(df3.apply(lambda x : x >= df3.T.max() \
                   - (thresh * 3))).dropna(thresh=2).dropna(axis=1)


          AAA   BBB
    0   80.0    85.0

df3.mask(~df3.apply(lambda x : x >= df3.T.max() - (thresh * 3))).dropna(axis=1)


    CCC DDD EEE FFF GGG
0   100 98  103 105 109
1   50  50  50  50  50
2   25  25  25  25  25

So my output is nice (and shows close to desired output) but the way I got this is not so nice...

---Desired output: ---

I have used multiple rows to demonstrate but when I use this code it will only be one row that needs to be output and split. So desired output is to return the separate columns as per this example for row 0.

    CCC DDD EEE FFF GGG
0   100 98  103 105 109

and

         AAA    BBB
    0   80.0    85.0

Answer 1

Well I think you can try to solve your problem differently. The idea is to get 'gaps and islands' within each row and label each group:

So, first - put your columns to rows and sort values within each initial row index:

>>> df = df2.stack().sort_values().sortlevel(0, sort_remaining=False)
>>> df
0  AAA     80
   BBB     85
   DDD     98
   CCC    100
   EEE    103
   FFF    105
   GGG    109
1  AAA      5
   BBB     20
   GGG     50
   FFF     50
   DDD     50
   CCC     50
   EEE     50
2  AAA      6
   GGG     25
   EEE     25
   DDD     25
   CCC     25
   FFF     25
   BBB     30

Next, create new DataFrame with 'prev values' together with current values:

>>> df = df2.stack().sort_values().sortlevel(0, sort_remaining=False)
>>> df = pd.concat([df, df.groupby(level=0).shift(1)], axis=1)
>>> df.columns = ['cur', 'prev']
>>> df
       cur   prev
0 AAA   80    NaN
  BBB   85   80.0
  DDD   98   85.0
  CCC  100   98.0
  EEE  103  100.0
  FFF  105  103.0
  GGG  109  105.0
1 AAA    5    NaN
  BBB   20    5.0
  GGG   50   20.0
  FFF   50   50.0
  DDD   50   50.0
  CCC   50   50.0
  EEE   50   50.0
2 AAA    6    NaN
  GGG   25    6.0
  EEE   25   25.0
  DDD   25   25.0
  CCC   25   25.0
  FFF   25   25.0
  BBB   30   25.0

And now, creating islands labels:

>>> df = (df['cur'] - df['prev'] > thresh).astype('int')
>>> df
0  AAA    0
   BBB    0
   DDD    1
   CCC    0
   EEE    0
   FFF    0
   GGG    0
1  AAA    0
   BBB    1
   GGG    1
   FFF    0
   DDD    0
   CCC    0
   EEE    0
2  AAA    0
   GGG    1
   EEE    0
   DDD    0
   CCC    0
   FFF    0
   BBB    0

>>> df.groupby(level=0).cumsum().unstack()
   AAA  BBB  CCC  DDD  EEE  FFF  GGG
0    0    0    1    1    1    1    1
1    0    1    2    2    2    2    2
2    0    1    1    1    1    1    1

Now you can filter out groups which have only one member and you're done :)

>>> dfm = df.groupby(level=0).cumsum().unstack()
>>> dfm
   AAA  BBB  CCC  DDD  EEE  FFF  GGG
0    0    0    1    1    1    1    1
1    0    1    2    2    2    2    2
2    0    1    1    1    1    1    1

>>> df2[dfm == 0].loc[0:0].dropna(axis=1)
   AAA   BBB
0   80  85.0
>>> df2[dfm == 1].loc[0:0].dropna(axis=1)
     CCC   DDD    EEE    FFF    GGG
0  100.0  98.0  103.0  105.0  109.0

Answer 2

method 1
I copied and pasted from previous question including the minor change.

---

I vectorized and embedded your closeCols for some mind numbing fun.
Notice there is no apply

• numpy broadcasting to get all combinations of columns subtracted from each other.
• np.abs
• <= 5
• sum(-1) I arranged the broadcasting such that the difference of say row 0, column AAA with all of row 0 will be laid out across the last dimension. -1 in the sum(-1) says to sum across last dimension.
• <= 1 all values are less than 5 away from themselves. So I want the sum of these to be greater than 1. Thus, we mask all less than or equal to one.

---

df2 = pd.DataFrame(
       {'AAA' : [80,5,6], 
        'BBB' : [85,20,30],
        'CCC' : [100,50,25],
        'DDD' : [98,50,25],
        'EEE' : [103,50,25],
        'FFF' : [105,50,25],
        'GGG' : [109,50,25]});

v = df2.values

# let delta be the distance threshold
# let k be the cluster size threshold
x, k = 5, 2  #  cluster size must be greater than k
df2.mask((np.abs(v[:, :, None] - v[:, None]) <= x).sum(-1) <= k)
# note that this is the same as before but k = 1 was hard coded

print(df3)   

   AAA   BBB  CCC  DDD  EEE  FFF   GGG
0  NaN   NaN  100   98  103  105   NaN
1  NaN   NaN   50   50   50   50  50.0
2  NaN  30.0   25   25   25   25  25.0

Answer 3

I felt this was deserving of a separate answer.

I wrote a clustering function that operates on one dimensional arrays. I know how to vectorize it further to 2 dimensions but I haven't gotten to it yet. As it is, I use np.apply_along_axis

This function is described in this answer to this question. I encourage you to follow the links and see the work that went into getting this seemingly simple function.

What it does is find the clusters within an array defined by margins to the left and right of every point. It sorts, then clusters, then un sorts.

delta clustering function

def delta_cluster(a, dleft, dright):
    s = a.argsort()
    y = s.argsort()
    a = a[s]
    rng = np.arange(len(a))

    edge_left = a.searchsorted(a - dleft)
    starts = edge_left == rng

    edge_right = np.append(0, a.searchsorted(a + dright, side='right')[:-1])
    ends = edge_right == rng

    return (starts & ends).cumsum()[y]

---

Onto the problem at hand

Use the cluster function for each row in df2 with np.apply_along_axis and construct a DataFrame named clusters that mirrors the same index and columns as df2. Then stack to get a Series which will make it easier to manipulate later.

clusters = pd.DataFrame(
    np.apply_along_axis(delta_cluster, 1, df2.values, 10, 10),
    df2.index, df2.columns).stack()

---

This describes the next block of code.

• I need to keep the row information of df2 when I do a groupby.
• Use transform to get the size of clusters for each row.
• stack the values of df2 and append the cluster values as part of the index. This enables the separation you are looking for.
• mask val where size is equal to 1. These are singleton clusters.

---

lvl0 = clusters.index.get_level_values(0)
size = clusters.groupby([lvl0, clusters]).transform('size')

val = df2.stack().to_frame('value').set_index(clusters, append=True).value

val.mask(size.values == 1).dropna().unstack(1)

      AAA   BBB    CCC   DDD    EEE    FFF    GGG
0 1  80.0  85.0    NaN   NaN    NaN    NaN    NaN
  2   NaN   NaN  100.0  98.0  103.0  105.0  109.0
1 3   NaN   NaN   50.0  50.0   50.0   50.0   50.0
2 2   NaN  30.0   25.0  25.0   25.0   25.0   25.0

This matches your results except I split out the first row into two rows.

     AAA   BBB    CCC   DDD    EEE    FFF    GGG
0   80.0  85.0    100    98    103    105    109
1    NaN   NaN     50    50     50     50     50
2    NaN  30.0     25    25     25     25     25