How do I sum data from certain columns and rows in a dataframe?

Question

I have a bunch of matrices that I stored in a big dataframe. Let's say here is my dataframe.

data = pd.DataFrame([[13, 1, 3, 4, 0, 0], [0, 2, 6, 2, 0, 0], [3, 1, 5, 2, 2, 0], [0, 0, 10, 11, 6, 0], [5, 5, 21, 25, 41, 0],
[11, 1, 3, 2, 0, 1], [3, 1, 7, 3, 1, 1], [1, 1, 6, 5, 3, 1], [1, 1, 6, 7, 6, 1], [6, 6, 21, 24, 42, 1],
[17, 1, 7, 0, 0, 2], [1, 1, 6, 1, 1, 2], [2, 4, 6, 2, 1, 2], [0, 2, 11, 7, 8, 2], [5, 6, 17, 16, 46, 2],
[11, 1, 10, 2, 1, 3], [2, 2, 7, 1, 1, 3], [0, 0, 14, 4, 1, 3], [0, 0, 7, 7, 5, 3], [5, 1, 20, 18, 48, 3],
[16, 3, 7, 1, 2, 4], [1, 2, 4, 1, 0, 4], [2, 4, 7, 5, 3, 4], [3, 0, 4, 4, 7, 4], [7, 2, 13, 12, 58, 4]], 
columns=['1', '2', '3', '4', '5', 'iteration'])
print(pd.DataFrame(data))

Each data['iteration'] is a matrix on its own. So, as you can see there are 5 matrices here (iteration-0 to 4). I want to add them all, like in basic matrix addition, to get one single matrix.

I have tried the following, but there's something wrong with it. It doesn't work.

matrix = data[['1','2','3','4','5']]
print(np.sum([matrix[matrix_list['iteration']==i] for i in range(0,9)], axis=0))

How do I do this the right way?

llllllllll · Accepted Answer · 2018-05-21T16:48:14.463

2

You can use:

In [98]: d = data.set_index('iteration')

In [99]: np.sum(d.loc[i].values for i in d.index.drop_duplicates().values)
Out[99]: 
array([[ 68,   7,  30,   9,   3],
       [  7,   8,  30,   8,   3],
       [  8,  10,  38,  18,  10],
       [  4,   3,  38,  36,  32],
       [ 28,  20,  92,  95, 235]])

Or alternatively, use groupby():

np.sum(e[1].iloc[:, :-1].values for e in data.groupby('iteration'))

array([[ 68,   7,  30,   9,   3],
       [  7,   8,  30,   8,   3],
       [  8,  10,  38,  18,  10],
       [  4,   3,  38,  36,  32],
       [ 28,  20,  92,  95, 235]])

edited May 21 '18 at 16:48

answered May 21 '18 at 16:17

llllllllll

16,169
4
31
54

Thanks, it works. Can you explain it a little bit? Why do you have to use `drop_duplicates()` there? – catris25 May 21 '18 at 16:23
1

@AnnaRG `d.index.drop_duplicates().values` that part is essentially `range(5)`, in `d.index` there are many duplicated values. – llllllllll May 21 '18 at 16:24
Is that basically the same as `data[['iteration']].nunique()`? You get the unique value from `iteration`? – catris25 May 21 '18 at 16:29
1

@AnnaRG No, `nunique()` is to count unique values, `drop_duplicates()` is to give all unique values. – llllllllll May 21 '18 at 16:36
Sorry for asking again, why `[1]` though? What does it mean specifically? – catris25 May 21 '18 at 16:40
@AnnaRG https://stackoverflow.com/questions/27405483/how-to-loop-over-grouped-pandas-dataframe – llllllllll May 21 '18 at 16:47

How do I sum data from certain columns and rows in a dataframe?

1 Answers1