nested Iteration over two large data frame in python

Ahmed Nabil 0
Viewed 34

Assuming I have the following data frames:

# data frame circles
    ID   x   y
    1    4   5
    2    5   6
# data frame points
    ID   x   y
    1    2   1
    2    1   2

I'm going to check if every point is located inside every circle or not And if the point located inside (based on some calculation), take the circle's ID and keep it in a separate list

# output of the list (ID of the circles)
[1]
[1 2]

This means point 1 is inside circle 1 and point 2 is located in both circles.

Now, I wrote the following function to do that locating work.

        for i in range(len(points)):
            for j in range(len(circles)):
                get_point_coordinates = (points.loc[i].at["x"], points.loc[i].at["y"])
                get_circle_coordinates = (circles.loc[j].at["x"], circles.loc[j].at["y"])
                 ### calling library's function to calculate
                distance = distance.great_circle(get_point_coordinates, get_circle_coordinates ).km
                if distance <= 5:
                    list[i].append(circles.loc[j].at["ID"])

It's a nested loop to iterate every single point and check all the circles one by one.

THE PROBLEM IS: the original data frame is over 100,000 rows, so it takes forever to iterate.

I read some posts about using apply to deal with massive data. so, I tried the following but it didn't work (error: The truth value of a Series is ambiguous).

    for i in range(len(circles)):
        newlist = newDataFrame['result'].apply(get_distance_function(circles.loc[i].at["x"], circles.loc[i].at["y"], points['x'], points['y']))

Yet I think it would still be a problem because I got rid of the inner for loop only, I still have to iterate 100,000 times instead of 100,000 * 100,000

So, any better ideas? or this approach is the shortest and I should fix the error?

dataframe pandas python
1 Answers
import pandas as pd
points = pd.DataFrame({"ID": [1, 2], "x": [4, 5], "y": [5, 6]})
circle = pd.DataFrame({"ID": [1, 2], "x": [2, 1], "y": [1, 2]})

To get all the combinations of the (point, circle), we can do a cross join.

new_df = points.merge(circle, how='cross', suffixes=["_point", "_circle"])
new_df

    ID_point   x_point  y_point ID_circle   x_circle    y_circle
0          1         4        5         1          2           1
1          1         4        5         2          1           2
2          2         5        6         1          2           1
3          2         5        6         2          1           2

With that, we can compare a point with a circle at each row level. We use apply at the row level (axis=1). Distance is calculated and added as a new column.

import math

# this is a Euclidean distance function (feel free to change it to suit your need)
def get_distance_function(x1, y1, x2, y2):
    return math.sqrt((x1-x2)**2 + (y1-y2)**2)


new_df["distance"] = new_df.apply(lambda row: get_distance_function(row["x_point"], row["y_point"], row["x_circle"], row["y_circle"]), axis=1)
new_df

    ID_point    x_point y_point ID_circle   x_circle    y_circle    distance
0          1          4       5         1          2           1    4.472136
1          1          4       5         2          1           2    4.242641
2          2          5       6         1          2           1    5.830952
3          2          5       6         2          1           2    5.656854

With the distance, we can check if it is within the radius (set as 5 in this example) and group the ID_point by ID_circle and make it a list.

radius = 5
new_df[new_df["distance"]<=radius].groupby("ID_circle")["ID_point"].apply(list).reset_index()

   ID_circle    ID_point 
0          1         [1]
1          2         [1]
wavetitan0
Related