Efficiently collect links in dataframe
<p>Say I have a dataframe of type</p>

<pre><code>individual,  location, food
         1          A     a
         1          A     b
         1          B     a
         1          A     c
         2          C     a
         2          C     b
</code></pre>

<p>where individuals are creating links between location and food. I would like to collect all links on the individual basis. That is, if an individual was observed at locations <code>A</code> and <code>B</code> and had (eventually) food at <code>a</code>, <code>b</code>, and <code>c</code>, I want to link <em>all</em> these locations and food types against each other:</p>

<pre><code>    location   food
           A      a
           A      b
           A      c
           B      a
           B      b
           B      c
           C      a
           C      b
</code></pre>

<p>One - extremely inefficient - way of doing so is</p>

<pre><code>import itertools
def foo(group):
    list1 = group.location.unique()
    list2 = group.food.unique()
    return pd.DataFrame(data=list(itertools.product(list1, list2)), columns=['location', 'food'])
df.groupby(df.individual).apply(foo)
</code></pre>

<p>Is there any better way to get this done?</p>

<p>You can pick up some efficiency by using numpy's <code>meshgrid</code>. </p>

<pre><code>import itertools
import numpy as np
def foo(group):
    list1 = group.location.unique()
    list2 = group.food.unique()
    return pd.DataFrame(data=list(itertools.product(list1, list2)), columns=['location', 'food'])

def bar(group):
    list1 = group.location.unique()
    list2 = group.food.unique()
    product = np.meshgrid(list1, list2)
    # reversing the order is necessary to get the same output as foo
    list3 = np.dstack([product[1], product[0]]).reshape(-1, 2)
    return pd.DataFrame(data=list3, columns=['location', 'food'])
</code></pre>

<p>On my machine there was a small, (~20 %) speedup</p>

<pre><code>In [66]: %timeit df.groupby(df.individual).apply(foo)
100 loops, best of 3: 2.57 ms per loop

In [67]: %timeit df.groupby(df.individual).apply(bar)
100 loops, best of 3: 2.16 ms per loop
</code></pre>

python pandas