How to make categories from column values in dataframe then sum the total purchase?

user_16130794 0
Viewed 124

I have a dataframe like this:

df = {'product_type_name': 
        ['Calendar', 'Lanyard', 'Name Card', 'Paper Lunch Box', 'Plastic Cup', 'Poster', 'Sticker', 'T-Shirt', 'Tote Bag'], 
       'order_count': 
        [4, 44, 14, 8, 6, 39, 28, 28, 17]}

df = pd.DataFrame(df)
print(df)

Output:

output dataframe

I want to group each product_type_name into four categories that goes like this:

  • Packaging (Paper Lunch Box, Plastic Cup)
  • Marketing Materials (Poster, Sticker)
  • Office Supplies (Name Card, Calendar, Lanyard)
  • Merchandise (Tote Bag, T-Shirt)

After that I want to summarize total order for each categories based on this rules:

  • High (order >= 10)
  • Medium (order 6 - 9)
  • Low (order <=5)

The expected output is going to be like this:

category high medium low
Packaging Null Paper Lunch Box, Plastic Cup Null
Marketing Materials Poster, Sticker Null Null
Office Supplies Lanyard, Name Card Null Calendar
Merchandise Tote Bag, T-Shirt Null Null

My solution is first to make a column which contains 3 class: high, medium, low based on orders rules above.Then, make the summarize table. The problem is I don't know how to do the summarize table. Any idea how to solve this problem for me?

EDIT

I made the python live code: https://paiza.io/projects/uhBOkwo5OZkOx4eg6bdSCw

pandas dataframe python
2 Answers

Here is the code that does it:

#Creating the dataframe
df=pd.DataFrame()
df['product_type_name']=['Calendar','Lanyard','Name Card',
                         'Paper Lunch Box','Plastic Cup','Poster'
                         ,'Sticker','T-Shirt','Tote Bag']
df['order_count']=[4,44,14,8,6,39,28,28,17]

#add the categories for each set
df.loc[df.product_type_name.isin(['Paper Lunch Box','Plastic Cup'])
       ,'category']=['Packaging']
df.loc[df.product_type_name.isin(['Sticker','Poster'])
       ,'category']=['Marketing Materials']
df.loc[df.product_type_name.isin(['Name Card','Calendar','Lanyard'])
       ,'category']=['Office Supplies']
df.loc[df.product_type_name.isin(['Tote Bag','T-Shirt'])
       ,'category']=['Merchandise']
#add high, medium, low 
df.loc[df.order_count<=5,'order_volume']=['low']
df.loc[(df.order_count>5)&(df.order_count<10),'order_volume']=['medium']
df.loc[df.order_count>=10,'order_volume']=['high']
#use pivot table to split the order_volume column and join the names
pd.pivot_table(df, values=['product_type_name'],
               index=['category'],
               columns=['order_volume'],
               aggfunc=lambda x: ','.join(str(v) for v in x))

enter image description here

user_97737800

STEPS

  1. Create a mapping dict reverse expand and map the product_type_name to its category.

  2. use pd.cut to create high/medium/low labels.

  3. use pivot_table with aggfunc = ''.join to restructure the df.

d = {'Packaging ': ['Paper Lunch Box', 'Plastic Cup'],
     'Marketing Materials': ['Poster', 'Sticker'],
     'Office Supplies': ['Name Card', 'Calendar', 'Lanyard'],
     'Merchandise': ['Tote Bag', 'T-Shirt']}


df['category'] = df['product_type_name'].map(
    {i: k for k, v in d.items() for i in v})

df['rules'] = pd.cut(df.order_count, bins=[0, 5, 9, np.inf],
                     labels=['Low', 'Medium', 'High'])

df = df.pivot_table(index='category', columns='rules',
                    values='product_type_name', aggfunc=', '.join)

OUTPUT:

rules                     Low                        Medium  \
category                                                      
Marketing Materials       NaN                           NaN   
Merchandise               NaN                           NaN   
Office Supplies      Calendar                           NaN   
Packaging                 NaN  Paper Lunch Box, Plastic Cup   

rules                              High  
category                                 
Marketing Materials     Poster, Sticker  
Merchandise           T-Shirt, Tote Bag  
Office Supplies      Lanyard, Name Card  
Packaging                           NaN
user_154380330
Related