1
$\begingroup$

I have two dataframes each with geometric data (shapely shape files). Call them df1 and df2. The geometry in df1 is a polygon (an area) and the geometry in df2 are points. All polygons are unique and non-overlapping. I would like to use the points in df2 to determine which polygon in df1 they are inside, and move a label from df1 to df2.

I know how to do the comparison for a single point and single polygon, but I'm not sure how to combine the two and check a list of objects against another list of objects. As far as I can tell a simple merge isn't possible.

I've tried to devise a simple example that shows what I would like to do:

df1 = pd.DataFrame({"a":['i','j','k'],"b":[[6,7],[8,11],[9,10]]})
df2 = pd.DataFrame({"c":[1,2,3,4],"d":[9,7,8,7]})

df1=

   a        b
0  i   [6, 7]
1  j  [8, 11]
2  k  [9, 10]

df2 =

   c  d
0  1  9
1  2  7
2  3  8
3  4  7

Find which row in df1 corresponds to the entries of column d in df2 which results in an output that can be either df2 or a new dataframe called df3:

df3 =

   c  d  a
0  1  9  k
1  2  7  i
2  3  8  j
3  4  7  i

In this example 9 is apart of the df1 list in row 2, put a k in df2. Another example is 7 is apart of df1 row 0 list, so put an 'i' in df2.

This sounds like a double for loop problem, but I'm trying to use Pandas functions. My understanding is they are faster and more interpretable. I feel like the answer lies in apply and/or groupby, but I can't quite pull it down.

$\endgroup$
1
  • $\begingroup$ How about you post your first try of using 'apply' and readers can give you feedback for that? $\endgroup$ Commented May 16 at 7:14

1 Answer 1

0
$\begingroup$

I believe the best way to do this is to expand the lists in df1 (using 'explode') to make new rows, then merge the new df1 with df2.

df1 = pd.DataFrame({"a": ['i', 'j', 'k'], "b": [[6, 7], [8, 11], [9, 10]]})
df2 = pd.DataFrame({"c": [1, 2, 3, 4], "d": [9, 7, 8, 7]})

df1 = df1.explode('b')
df3 = df2.merge(df1, left_on='d', right_on='b')

In []: df1
Out[]: 
   a   b
0  i   6
0  i   7
1  j   8
1  j  11
2  k   9
2  k  10 

In []: df3
Out]: 
   c  d  a  b
0  1  9  k  9
1  2  7  i  7
2  4  7  i  7
3  3  8  j  8
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.