comparing two pandas dataframes with list in column
<p>I have two data frames df1 and df2:</p>

<pre><code>df1 : 
Name A_list
abcd (apple,orange,banana)
bcde (orange,mango)
cdef (apple,pineapple)

df2 :
City B_list
C1   (apple,mango,banana)
C2   (mango)
C3   (pineapple,banana)
</code></pre>

<p>I want to make a new dataframe df3</p>

<pre><code>Name A_list City
abcd (apple,orange,banana) (C1,C3)
bcde (orange,mango) (C1,C2)
cdef (apple,pineapple) (C1,C3)
</code></pre>

<p>i.e going through A_list in Df1 and identifying the City from which each fruit came.
I am not sure how to merge df1 and df2 using the lists A_list and B_list</p>

<h3>Setup</h3>

<pre><code>df1 = pd.DataFrame([
        ['abcd', ('apple', 'orange', 'banana')],
        ['bcde', ('orange', 'mango')],
        ['cdef', ('apple', 'pineapple')]
    ], columns=['Name', 'A_list'])
df2 = pd.DataFrame([
        ['C1', ('apple', 'mango', 'banana')],
        ['C2', ('mango')],
        ['C3', ('pineapple', 'banana')]
    ], columns=['City', 'B_list'])
</code></pre>

<p><strong><em>massage data</em></strong></p>

<pre><code>s2 = df2.set_index('City').squeeze() \
    .apply(pd.Series) \
    .stack().reset_index(1, drop=True)

s2

City
C1        apple
C1        mango
C1       banana
C2        mango
C3    pineapple
C3       banana
dtype: object
</code></pre>

<hr>

<pre><code>s1 = df1.set_index('Name').squeeze() \
    .apply(pd.Series) \
    .stack().reset_index(1, drop=True)

s1

Name
abcd        apple
abcd       orange
abcd       banana
bcde       orange
bcde        mango
cdef        apple
cdef    pineapple
dtype: object
</code></pre>

<hr>

<pre><code>df3 = pd.merge(*[s.rename('fruit').reset_index() for s in [s1, s2]])

df3
</code></pre>

<p><a href="http://i.stack.imgur.com/kQzTW.png" rel="nofollow"><img src="http://i.stack.imgur.com/kQzTW.png" alt="enter image description here"></a></p>

<pre><code>def tuplify(series):
    return tuple(set(series))

df3.groupby('Name') \
    .apply(lambda df: df.drop('Name', axis=1).apply(tuplify)) \
    .rename(columns=dict(fruit='A_list')).reset_index()
</code></pre>

<p><a href="http://i.stack.imgur.com/Apbuz.png" rel="nofollow"><img src="http://i.stack.imgur.com/Apbuz.png" alt="enter image description here"></a></p>

<p>Notice that <code>'orange'</code> is missing because it wasn't represented by a <code>'City'</code>.  If you want the same <code>A_list</code></p>

<pre><code>df3 = pd.merge(*[s.rename('fruit').reset_index() for s in [s1, s2]])
df3 = df3.groupby('Name') \
    .apply(lambda df: df.drop('Name', axis=1).apply(tuplify)) \
    .rename(columns=dict(fruit='A_list'))

df3['A_list'] = df1.set_index('Name')['A_list']
df3.reset_index()
</code></pre>

<p><a href="http://i.stack.imgur.com/F8amD.png" rel="nofollow"><img src="http://i.stack.imgur.com/F8amD.png" alt="enter image description here"></a></p>

python pandas