How to aggregate a dataframe then transpose it with Pandas

August 10, 2022

I’m trying to achieve this kind of transformation with Pandas.

I made this code but unfortunately it doesn’t give the result I’m searching for.

CODE :

import pandas as pd

df = pd.read_csv('file.csv', delimiter=';')

df = df.count().reset_index().T.reset_index()

df.columns = df.iloc[0]
df = df[1:]

df

RESULT :

Do you have any proposition ? Any help will be appreciated.

>Solution :

First create columns for test nonOK and then use named aggregatoin for count, sum column Values and for count Trues values use sum again, last sum both columns:

df = (df.assign(NumberOfTest1 = df['Test one'].eq('nonOK'),
                NumberOfTest2 = df['Test two'].eq('nonOK'))
        .groupby('Category', as_index=False)
        .agg(NumberOfID = ('ID','size'),
             Values = ('Values','sum'),
             NumberOfTest1 = ('NumberOfTest1','sum'),
             NumberOfTest2 = ('NumberOfTest2','sum'))
        .assign(TotalTest = lambda x: x['NumberOfTest1'] + x['NumberOfTest2']))