I have some problem to generate pivot tables. I try to do this with Python. Now I have test tables:
Table 1
I want to do tables that looks like this:
Table 2
I write code but still have two columns but no sum of this.
import pandas as pd
import numpy as np
table = pd.read_excel('test.xlsx',0)
print(table.head())
print(pd.pivot_table(table,values=["A","B","C","D","E","F","G"],columns=[np.sum]))
You should Transpose the dataframe and then sum the columns(axis=1).
Something like this:
In [384]: table
Out[384]:
A B C
0 1 2 4
1 1 3 2
In [386]: table.T.sum(axis=1)
Out[386]:
A 2
B 5
C 6
dtype: int64
If you want to sum only certain columns, do this:
In [468]: df
Out[468]:
A B C D E F G
0 1 2 4 5 6 5 6
1 2 3 2 1 2 3 4
In [469]: df[['C','D','E','F']]
Out[469]:
C D E F
0 4 5 6 5
1 2 1 2 3
In [471]: df[['C','D','E','F']].T.sum(axis=1)
Out[471]:
C 6
D 6
E 8
F 8
dtype: int64