I have DataFrame in Python Pandas like below:
ID | VAR1 | VAR2 | C1 | C2
-----|------|------|------|-------
111 | 1 | 0 | 12 | 0
222 | 1 | 1 | 11 | 18
333 | 0 | 1 | 6 | 5
444 | 1 | 0 | 7 | 2
And as an output I need somethin like below:
If someone has '1' in VAR1 sum his values in C1 --> column "C1" below
If someone has '1' in VAR2 sum his values in C2 --> column "C2" below
COL1 | C1 | C2 |
---|---|---|
VAR1 | 30 | 20 |
VAR2 | 17 | 23 |
How can I do such aggregation in Python Pandas ?
Try with dot
out = df.filter(like = 'VAR').T.dot(df.filter(like = 'C'))
Out[267]:
C1 C2
VAR1 30 20
VAR2 17 23