How to aggregate a dataframe then transpose it with Pandas-CodePudding

I'm trying to achieve this kind of transformation with

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.

CodePudding user response：

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']))