Pandas Dataframe Categorical data transformation

Question

I am having pandas dataframe as follows:

import pandas as pd
  
# dictionary with list object in values

# Item=[Item1, Item2, Item3]
details = {
    'Date' : ['2022-01-09', '2022-01-09', '2022-01-09', '2022-01-10', '2022-01-10'],
    'Item' : ['Item1', 'Item2', 'Item3', 'Item1','Item2'],
    'Price' : [23, 51, 62, 31,44],
    
}
  
# creating a Dataframe object 
df = pd.DataFrame(details)
  

df

---

Date	Item	Price
2022-01-09	Item1	23
2022-01-09	Item2	51
2022-01-09	Item3	62
2022-01-10	Item1	31
2022-01-10	Item2	44

I want to transform the above pandas dataframe as follows, can anyone please help.

Date	Item1	Item2	Item3	Total_Price
2022-01-09	23	51	62	136
2022-01-10	31	44	NAN	75

Answer 1

Use DataFrame.pivot_table with DataFrame.assign for new total column:

df1 = (df.pivot_table(index='Date', columns='Item', values='Price', aggfunc='sum')
         .assign(Total_Price = lambda x: x.sum(axis=1)))
print (df1)
Item        Item1  Item2  Item3  Total_Price
Date                                        
2022-01-09   23.0   51.0   62.0        136.0
2022-01-10   31.0   44.0    NaN         75.0