Cross label two dataframes using arbitrary comparison of two columns using pandas

Question

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.

Answer 1

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