Search code examples
pythonpandaspivot-tableaggregationmelt

How to aggregate DataFrame based on binary and numeric columns in Python Pandas?

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 ?

Solution

  • Try with dot

    out = df.filter(like = 'VAR').T.dot(df.filter(like = 'C'))
Out[267]: 
      C1  C2
VAR1  30  20
VAR2  17  23