How to filter one row, calculate range and find similar rows from it falling within that range in a dictionary?

user_5718264 0
Viewed 76

How to filter one row, calculate range and find similar rows from it falling within that range in a dictionary with id as key and id's falling in that range as values using multiprocessing?

Suppose I have a data frame:

id  val1   val2
1    10     20
2    9.5    19
3    100    200
4    9.3    19.2
5    96     196
6    99     198
7    103    202
8    140    280

For each id i, I will calculate: 

upper_val1 = df[df.id==i].val1 * (1+0.1) 
lower_val1 = df[df.id==i].val1 * (1-0.1) 
upper_val2 = df[df.id==i].val2 * (1+0.1) 
lower_val2 = df[df.id==i].val2 * (1-0.1)

Subset df:

sub_df = df[(df.val1<=upper_val1)&df.val1>=lower_val1)&(df.val1<=upper_val2)&df.val1>=lower_val2)

For whichever id, val1 lies between this range, that will be put in the dictionary. For eg. the output of this df will be:

{1:[2,4], 2:[1,4], 4:[1,2], 3:[5,6,7], 5:[3,6,7], 6:[3,5,7], 7:[3,5,6]}

I have a data frame with millions of records and this step should be repeated for each row, so how it can be done using multiprocessing?

pandas dataframe parallel-processing python-3.x python
1 Answers

To accomplish this, we'll apply a function over the dataframe that computes the IDs where values lie in a range of the dataframe's rows.

df = pd.DataFrame.from_records([
         {'id': 1, 'val1': 10.0, 'val2': 20.0},
         {'id': 2, 'val1': 9.5, 'val2': 19.0},
         {'id': 3, 'val1': 100.0, 'val2': 200.0},
         {'id': 4, 'val1': 9.3, 'val2': 19.2},
         {'id': 5, 'val1': 96.0, 'val2': 196.0},
         {'id': 6, 'val1': 99.0, 'val2': 198.0},
         {'id': 7, 'val1': 103.0, 'val2': 202.0},
         {'id': 8, 'val1': 140.0, 'val2': 280.0}]
)

def rows_in_range(row):
    index = row.name
    val1 = row['val1']
    val2 = row['val2']
    return (df['val1'].between(val1*(1-.1), val1*(1+.1)) &
            df['val2'].between(val2*(1-.1), val2*(1+.1)) &
            (df.index != index)
           )

# row.name gives the index value for that row
ids = df.apply(
    lambda row: df.loc[rows_in_range(row), 'id'].tolist(),
    axis=1,
    result_type='reduce'
)

indices
0       [2, 4]
1       [1, 4]
2    [5, 6, 7]
3       [1, 2]
4    [3, 6, 7]
5    [3, 5, 7]
6    [3, 5, 6]
7           []
dtype: object

Now all we have to is convert the index of this to IDs as well.

indices.index = df.loc[indices.index, 'id']
indices.to_dict()
{1: [2, 4],
 2: [1, 4],
 3: [5, 6, 7],
 4: [1, 2],
 5: [3, 6, 7],
 6: [3, 5, 7],
 7: [3, 5, 6],
 8: []}

I'm curious whether this is performant enough for millions of rows, but at least it's correct.

user_54964330
Related