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:

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