List in column Python + Pandas
<p>I'm new to pandas and would like to analyse some data arranged like this:</p>

<pre><code>label                     aa                        bb
index
    0           [2, 5, 1, 4]          [x1, x2, y1, z1]
    1             [3, 3, 19]              [x3, x4, y2]
    2    [6, 4, 2, 8, 9, 10]  [y1, y2, z3, z4, x1,  w]
</code></pre>

<p>in which x1,x2,x3,x4 are of type M; y1,y2 are of type N; and z1,z2,z3,z4 are of type O. Note that data[2,'bb'] is w, which does not belong to any type. This relationship is defined in mongodb as follows</p>

<pre><code>{'_id' : ObjectId(x1), type : 'M'}
{'_id' : ObjectId(y1), type : 'N'}
{'_id' : ObjectId(z1), type : 'O'}...
db.data.find({'_id' : ObjectId(w)}) is null
</code></pre>

<p>The desired output would be like this:</p>

<pre><code>label     sum_M   sum_N   sum_O
index
    0         7       1       4
    1         6      19       0
    2         9      10      10
</code></pre>

<p>Does anyone know how to do this with pandas?</p>

<p>Pandas works best when your data are in a table format and individual cells contain values, not collections. To use pandas effectively for your problem, you need to change the way you create your data table. </p>

<p>Ultimately, it looks like you want to generate a table with columns representing object "id", "amount", and "numbering".</p>

<pre><code>    id     amount    numbering
0   abc    2         x1
1   abc    5         x2
2   abc    1         y1
3   abc    4         z1
4   def    3         x3
etc.
</code></pre>

<p>To create this table, you can probably use a list of dictionaries, each dictionary containing the data for a row in your table, e.g.:</p>

<pre><code>{'id':'abc', 'amount': 2, 'numbering':'x1'}
</code></pre>

<p>You can construct a pandas DataFrame from this list: <a href="http://stackoverflow.com/questions/20638006/convert-list-of-dictionaries-to-dataframe">Convert list of dictionaries to Dataframe</a></p>

<p>Then you would add a column that represents the type associated with each "numbering" value:</p>

<pre><code>data['dbtype'] = data.numbering.map(lambda num: {'x':'M','y':'N','z':'O'}.get(num[0], 'None'))
</code></pre>

<p>Then you would use groupby:</p>

<pre><code>data.groupby('dbtype').sum()
</code></pre>

python pandas