CODEWITHSUNDEEP
pythonpandasdataframe
RKhan
RKhan

Reputation: 11

python How to sum over diagonals of data frame

Say that I have this data frame:

     1   2   3   4      
100  8   12  5   14 
99   1   6   4   3   
98   2   5   4   11  
97   5   3   7   2

In this above data frame, the values indicate counts of how many observations take on (100, 1), (99, 1), etc.

In my context, the diagonals have the same meanings:

     1   2   3   4
100  A   B   C   D 
99   B   C   D   E  
98   C   D   E   F 
97   D   E   F   G

How would I sum across the diagonals (i.e., sum the counts of the like letters) in the first data frame?

This would produce:

group  sum
A      8
B      13
C      13
D      28
E      10
F      18
G      2

For example, D is 5+5+4+14

Upvotes: 1

Views: 653

Answers (2)

RKhan
RKhan

Reputation: 11

I found answer :

digonalsum = pd.concat([df.iloc[:, i].shift(i) for i in range(df.shape[1])], axis=1).sum(axis=1)

Upvotes: 0

jezrael
jezrael

Reputation: 862581

If there are same indices in both DataFrames and columns use DataFrame.stack with aggregate sum:

df = df1.stack().groupby(df2.stack()).sum().rename_axis('group').reset_index(name='sum')
print (df)
  group  sum
0     A    8
1     B   13
2     C   13
3     D   28
4     E   10
5     F   18
6     G    2

EDIT: Because second DataFrame not exist use numpy.diag with flipping by numpy.flipud and sum:

#inspired by https://stackoverflow.com/a/59173028/2901002
c = df.to_numpy()
v = [np.sum(np.diag(np.flipud(c), k=i)) for i in range(-len(df) + 1, len(df), 1)]
print(v) 
[8, 13, 13, 28, 10, 18, 2]

df = pd.DataFrame({'sum': v})
print (df)
   sum
0    8
1   13
2   13
3   28
4   10
5   18
6    2

Upvotes: 2

Related Questions