Python pandas groupby conditional concatenate strings into multiple columns
<p>I am trying to group by a dataframe on one column, keeping several columns from one row in each group and concatenating strings from the other rows into multiple columns based on the value of one column.  Here is an example...</p>

<pre><code>df = pd.DataFrame({'test' : ['a','a','a','a','a','a','b','b','b','b'],
     'name' : ['aa','ab','ac','ad','ae','ba','bb','bc','bd','be'],
     'amount' : [1, 2, 3, 4, 5, 6, 7, 8, 9, 9.5],
     'role' : ['x','y','y','x','x','z','y','y','z','y']})
</code></pre>

<p>df</p>

<pre><code>      amount    name    role    test
0        1.0    aa      x       a
1        2.0    ab      y       a
2        3.0    ac      y       a
3        4.0    ad      x       a
4        5.0    ae      x       a
5        6.0    ba      z       a
6        7.0    bb      y       b
7        8.0    bc      y       b
8        9.0    bd      z       b
9        9.5    be      y       b
</code></pre>

<p>I would like to groupby on test, retain name and amount when role = 'z', create a column (let's call it X) that concatenates the values of name when role = 'x' and another column (let's call it Y) that concatenates the values of name when role = 'y'.  [Concatenated values separated by '; ']  There could be zero to many rows with role = 'x', zero to many rows with role = 'y' and one row with role = 'z' per value of test.  For X and Y, these can be null if there are no rows for that role for that test.  The amount value is dropped for all rows with role = 'x' or 'y'.  The desired output would be something like:</p>

<pre><code>     test   name     amount        X              Y
0    a      ba          6.0        aa; ad; ae     ab; ac
1    b      bd          9.0        None           bb; bc; be
</code></pre>

<p>For the concatenating part, I found <code>x.ix[x.role == 'x', X] = "{%s}" % '; '.join(x['name'])</code>, which I might be able to repeat for y.  I tried a few things along the lines of <code>name = x[x.role == 'z'].name.first()</code> for name and amount.  I also tried going down both paths of a defined function and a lambda function without success.  Appreciate any thoughts.</p>