Mean of values in a column for unique values in another column (Python)
<p>I am using Python 2.7 (Anaconda) for processing tabular data. I have loaded a textfile with two columns, e.g. </p>

<pre><code>[[ 1.  8.]
 [ 2.  4.]
 [ 3.  1.]
 [ 4.  5.]
 [ 5.  6.]
 [ 1.  9.]
 [ 2.  0.]
 [ 3.  7.]
 [ 4.  3.]
 [ 5.  2.]]
</code></pre>

<p>my goal is to calculate the mean over all values in the second column which match the unique values in the first one, e.g. the mean value for 1 would be 8.5, for 2 it would be two, for 3 it would be 4. 
First, I filtered out the unique values in the first column by extracting the column and applying np.unique() resulting in the array "unique". I created a loop that works when I define the unique value:</p>

<pre><code>mean= 0
values=[]
for i in range(0,len(first),1):
    if first[i]==1:
        values.append(second[i])
print(np.mean(values))
</code></pre>

<p>where first and second are the specific columns.
Now I want to make this not so specific. I tried</p>

<pre><code>mean = 0
values = []
means=[]

for i in unique:
    for k in range(0,len(first),1):
        if first[k]==i:
            values.append(second[k])
            mean = np.mean(values)
            means.append(mean)
    mean=0
    values=[]
print(means)
</code></pre>

<p>but it only returns the original second column.
Does anybody have an idea on how to make this code non-specific? In reality, I have about 70k rows, so I cannot do it mannually.
Thanks and regards,
Maurus</p>

<p>Write a comparison statement checking the first column for your unique value, use that statement as a <a href="http://docs.scipy.org/doc/numpy/user/basics.indexing.html#boolean-or-mask-index-arrays" rel="nofollow">boolean index</a>, </p>

<pre><code>&gt;&gt;&gt; mask = a[:,0] == 1
&gt;&gt;&gt; a[mask]
array([[ 1.,  8.],
       [ 1.,  9.]])

for n in np.unique(a[:,0]):
    mask = a[:,0] == n
    print(np.mean(a[mask], axis = 0))

&gt;&gt;&gt; 
[ 1.   8.5]
[ 2.  2.]
[ 3.  4.]
[ 4.  4.]
[ 5.  4.]
</code></pre>

<hr>

<p>If your data file looks something like this</p>

<pre><code>'''
1.,  8.
2.,  4.
3.,  1.
4.,  5.
'''
</code></pre>

<p>and you don't really need a numpy array, just use a dictionary:</p>

<pre><code>import collections
d = collections.defaultdict(list)
with open('file.txt') as f:
    for line in f:
        line = line.strip()
        first, second = map(float, line.split(','))
        d[first].append(second)

for first, second in d.iteritems():
    print(first, sum(second) / len(second))
</code></pre>

python mean tabular-data