How to create pandas column values in dictionary?

user_12431156 0
Viewed 51

I need to create column as dictionary using existing column. df:

Period  Category  Sub-Category
FY18Q1  Footwear  Shoes
FY18Q2  Footwear  Sandal
FY18Q1  Footwear  Shoes
FY18Q3  Footwear  Boots
FY18Q1  Clothing  Shirt
FY18Q2  Clothing  Trouser
FY18Q1  Clothing  Shirt
FY18Q3  Clothing  Shirt

I want to create a two new column based on Category Level. A. Count of Sub-Category on Category Level. B. Sub-Category based on latest Period.

Period  Category  Sub-Category  freq                        Latest_freq
FY18Q1  Footwear  Shoes        {shoes:2,Sandal:1,Boots:1}   Boots(FY18Q3)
FY18Q2  Footwear  Sandal       {shoes:2,Sandal:1,Boots:1}   Boots(FY18Q3)
FY18Q1  Footwear  Shoes        {shoes:2,Sandal:1,Boots:1}   Boots(FY18Q3)
FY18Q3  Footwear  Boots        {shoes:2,Sandal:1,Boots:1}   Boots(FY18Q3)
FY18Q1  Clothing  Shirt        {Shirt:3,Trouser:1}          Shirt(FY18Q3)
FY18Q2  Clothing  Trouser      {Shirt:3,Trouser:1}          Shirt(FY18Q3)
FY18Q1  Clothing  Shirt        {Shirt:3,Trouser:1}          Shirt(FY18Q3)
FY18Q3  Clothing  Shirt        {Shirt:3,Trouser:1}          Shirt(FY18Q3)
pandas python
1 Answers

Use named aggregation for both new values per groups in lambda function with Series.value_counts and to_dict, second column is first changed by () in DataFrame.assign and aggregate by GroupBy.last, in last step use DataFrame.join:

df1=(df.assign(new = df['Sub-Category'] + '(' + df.Period + ')')
       .groupby('Category').agg(freq=('Sub-Category',lambda x : x.value_counts().to_dict()),
                                lastest_freq=('new','last')))
print (df1)
                                           freq   lastest_freq
Category                                                      
Clothing             {'Shirt': 3, 'Trouser': 1}  Shirt(FY18Q3)
Footwear  {'Shoes': 2, 'Boots': 1, 'Sandal': 1}  Boots(FY18Q3)

df = df.join(df1, on='Category')
print (df)
   Period  Category Sub-Category                                   freq  \
0  FY18Q1  Footwear        Shoes  {'Shoes': 2, 'Boots': 1, 'Sandal': 1}   
1  FY18Q2  Footwear       Sandal  {'Shoes': 2, 'Boots': 1, 'Sandal': 1}   
2  FY18Q1  Footwear        Shoes  {'Shoes': 2, 'Boots': 1, 'Sandal': 1}   
3  FY18Q3  Footwear        Boots  {'Shoes': 2, 'Boots': 1, 'Sandal': 1}   
4  FY18Q1  Clothing        Shirt             {'Shirt': 3, 'Trouser': 1}   
5  FY18Q2  Clothing      Trouser             {'Shirt': 3, 'Trouser': 1}   
6  FY18Q1  Clothing        Shirt             {'Shirt': 3, 'Trouser': 1}   
7  FY18Q3  Clothing        Shirt             {'Shirt': 3, 'Trouser': 1}   

    lastest_freq  
0  Boots(FY18Q3)  
1  Boots(FY18Q3)  
2  Boots(FY18Q3)  
3  Boots(FY18Q3)  
4  Shirt(FY18Q3)  
5  Shirt(FY18Q3)  
6  Shirt(FY18Q3)  
7  Shirt(FY18Q3)
user_29010020
Related