Pandas Pivot Table counting based on condition and sum columns-CodePudding

I have DateFrame as shown below. I need to add a few columns to dft (pivot output). First one to calculate sum of products sold daily, it's like margine but only for sum column not len. I tried dft['Fruit Total']=df.iloc[:,0:3].sum(axis=1) but it didn't work. also I want to add column counting values > than 2 for each row in column sum. like in the picture

df = pd.DataFrame({
    'date': ["22.10.2021", "22.10.2021", "22.10.2021", "23.10.2021", "23.10.2021", "25.10.2021", "22.10.2021", "23.10.2021", "22.10.2021", "25.10.2021"],
    'Product': ["apple", "apple", "orange", "orange", "apple","apple", "apple", "orange", "orange", "orange"],
    'sold_kg': [2, 3, 1, 6, 2,2, 3, 1, 6, 2,]})
df['day']=pd.to_datetime(df['date']).dt.day

dft=df.pivot_table(values='sold_kg', columns ='day', index='Product', aggfunc=[np.sum,len])
dft

CodePudding user response：

Use:

dft=df.pivot_table(values='sold_kg',columns='day', index='Product', aggfunc=['sum','size'])

First flatten MultiIndex in columns with mapping:

dft.columns = dft.columns.map(lambda x: f'{x[0]}_{x[1]}')

Then select columns by DataFrame.filter and sum, for count values greater or equal use DataFrame.ge and count Trues by sum:

dft['Fruit Total'] = dft.filter(like='sum').sum(axis=1)

dft['Count >= 2'] = dft.filter(like='size').ge(2).sum(axis=1)
print (dft)
         sum_22  sum_23  sum_25  size_22  size_23  size_25  Fruit Total  \
Product                                                                   
apple         8       2       2        3        1        1           12   
orange        7       7       2        2        2        1           16   

         Count >= 2  
Product              
apple             1  
orange            2